In the daily maintenance of database, developers are the most headache. Many times, there are problems in writing SQL statements, resulting in server problems and resource depletion. The most dangerous operation is to forget to add the where condition during DML operation, resulting in full table update. How can we deal with it as an operation and maintenance or DBA? Here are the processing methods for updating the whole table caused by forgetting to add the where condition for update and delete operations.
I update forgets to add the where condition to recover data from misoperation (binglog format must be ROW)
1. Create a data table for testing
mysql> create table t1 (
-> id int unsigned not null auto_increment,
-> name char(20) not null,
-> sex enum('f','m') not null default 'm',
-> address varchar(30) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.31 sec)
mysql>
2. Insert test data
mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 (name,sex,address)values('tom','f','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 (name,sex,address)values('liany','m','beijing');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 (name,sex,address)values('lilu','m','zhuhai');
Query OK, 1 row affected (0.05 sec)
mysql>
3. Now you need to change the address of the user with id equal to 2 to zhuhai, and the where condition is not added during update
mysql> select * from t1; +----+-------+-----+-----------+ | id | name | sex | address | +----+-------+-----+-----------+ | 1 | daiiy | m | guangzhou | | 2 | tom | f | shanghai | | 3 | liany | m | beijing | | 4 | lilu | m | zhuhai | +----+-------+-----+-----------+ 4 rows in set (0.01 sec) mysql> update t1 set address='zhuhai'; Query OK, 3 rows affected (0.09 sec) Rows matched: 4 Changed: 3 Warnings: 0 mysql> select * from t1; +----+-------+-----+---------+ | id | name | sex | address | +----+-------+-----+---------+ | 1 | daiiy | m | zhuhai | | 2 | tom | f | zhuhai | | 3 | liany | m | zhuhai | | 4 | lilu | m | zhuhai | +----+-------+-----+---------+ 4 rows in set (0.00 sec) mysql>
4. It should be more complicated to start the recovery. Lock the table first to avoid data contamination again. (lock the table to see which binary log is being written)
mysql> lock tables t1 read ; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000024 | 1852 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>
5. Analyze the binary log and find relevant records in it. When updating, it is address='zhuhai ', which can be filtered out in the log.
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 'zhuhai'
# at 1629 # at 1679 #140305 10:52:24 server id 1 end_log_pos 1679 Table_map: `db01`.`t1` mapped to number 38 #140305 10:52:24 server id 1 end_log_pos 1825 Update_rows: table id 38 flags: STMT_END_F ### UPDATE db01.t1 ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### UPDATE db01.t1 ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### UPDATE db01.t1 ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
You can see that the changes of each line are recorded, which is why the binglog format must be row. Where @ 1, @ 2, @ 3, @ 4 respectively correspond to the ID, name, sex and address fields in the table. I'm sure you'll understand a little here. Yes, yes, as you guessed, we convert relevant records into sql statements and re import them into the database.
6. Processing binary logs for analysis
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 1679/,/COMMIT/p' > t1.txt [root@localhost mysql]# cat t1.txt # at 1679 #140305 10:52:24 server id 1 end_log_pos 1679 Table_map: `db01`.`t1` mapped to number 38 #140305 10:52:24 server id 1 end_log_pos 1825 Update_rows: table id 38 flags: STMT_END_F ### UPDATE db01.t1 ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### UPDATE db01.t1 ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### UPDATE db01.t1 ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ # at 1825 #140305 10:52:24 server id 1 end_log_pos 1852 Xid = 26 COMMIT/*!*/; [root@localhost mysql]#
sed here is a little complicated, and children's shoes need to study it by themselves. I won't say more here.
[root@localhost mysql]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql
[root@localhost mysql]# cat recover.sql UPDATE db01.t1 SET @1=1 , @2='daiiy' , @3=2 , @4='guangzhou' , WHERE @1=1 ; UPDATE db01.t1 SET @1=2 , @2='tom' , @3=1 , @4='shanghai' , WHERE @1=2 ; UPDATE db01.t1 SET @1=3 , @2='liany' , @3=2 , @4='beijing' , WHERE @1=3 ; [root@localhost mysql]#
Replace @ 1, @ 2, @ 3, @ 4 in the file with id, name, sex and address fields in the t1 table, and delete the "," sign of the last field
[root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql [root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql [root@localhost mysql]# cat recover.sql UPDATE db01.t1 SET id=1 , name='daiiy' , sex=2 , address='guangzhou' WHERE id=1 ; UPDATE db01.t1 SET id=2 , name='tom' , sex=1 , address='shanghai' WHERE id=2 ; UPDATE db01.t1 SET id=3 , name='liany' , sex=2 , address='beijing' WHERE id=3 ; [root@localhost mysql]#
7. The log is processed here, and can be imported now (after importing data, unlock the table);
mysql> source recover.sql; Query OK, 1 row affected (0.12 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1; +----+-------+-----+-----------+ | id | name | sex | address | +----+-------+-----+-----------+ | 1 | daiiy | m | guangzhou | | 2 | tom | f | shanghai | | 3 | liany | m | beijing | | 4 | lilu | m | zhuhai | +----+-------+-----+-----------+ 4 rows in set (0.00 sec) mysql>
It can be seen that the data has been completely recovered. The advantage of this method is fast and convenient.
mysql> select * from t1; +----+-------+-----+-----------+ | id | name | sex | address | +----+-------+-----+-----------+ | 1 | daiiy | m | guangzhou | | 2 | tom | f | shanghai | | 3 | liany | m | beijing | | 4 | lilu | m | zhuhai | +----+-------+-----+-----------+ 4 rows in set (0.00 sec) mysql> delete from t1; Query OK, 4 rows affected (0.03 sec) mysql> select * from t1; Empty set (0.00 sec) mysql>
2. Find relevant records in binglog
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p' > delete.txt [root@localhost mysql]# cat delete.txt ### DELETE FROM db01.t1 ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### DELETE FROM db01.t1 ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### DELETE FROM db01.t1 ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### DELETE FROM db01.t1 ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='lilu' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ # at 2719 #140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78 COMMIT/*!*/; [root@localhost mysql]#
3. Convert records to SQL statements
[root@localhost mysql]# cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > t1.sql [root@localhost mysql]# cat t1.sql INSERT INTO db01.t1 SELECT 1 , 'daiiy' , 2 , 'guangzhou' ; INSERT INTO db01.t1 SELECT 2 , 'tom' , 1 , 'shanghai' ; INSERT INTO db01.t1 SELECT 3 , 'liany' , 2 , 'beijing' ; INSERT INTO db01.t1 SELECT 4 , 'lilu' , 2 , 'zhuhai' ; [root@localhost mysql]#
4. Import data and verify data integrity
mysql> source t1.sql; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t1; ERROR 1046 (3D000): No database selected mysql> select * from db01.t1; +----+-------+-----+-----------+ | id | name | sex | address | +----+-------+-----+-----------+ | 1 | daiiy | m | guangzhou | | 2 | tom | f | shanghai | | 3 | liany | m | beijing | | 4 | lilu | m | zhuhai | +----+-------+-----+-----------+ 4 rows in set (0.00 sec) mysql>
Here, the data will be complete. Setting the binglog format to row has advantages and disadvantages. The advantage is that it records the actual changes of each line, and it is not easy to have problems in master-slave replication. However, due to the change of each line, it will occupy a lot of disk, and the bandwidth will be consumed during master-slave replication. Whether to use row or mixed needs to be measured in actual work, but on the whole, setting the format of binglog to row is the only choice.
Summary:
Therefore, we need to be extra careful in the process of database operation. Of course, we need to control permissions in the development side, but there is a parameter that can solve our problems, so we don't have to worry about similar problems:
Open this parameter in the [mysql] paragraph:
safe-updates
In this way, when we forget to add the where condition during DML operation, the mysqld server will not perform the operation:
mysql> select * from t1; +----+------------------+ | id | name | +----+------------------+ | 1 | yayun | | 2 | atlas | | 3 | mysql | | 6 | good yayun heheh | +----+------------------+ 4 rows in set (0.00 sec) mysql> delete from t1; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql>
Author: Atlas
Source: Atlas blog http://www.cnblogs.com/gomysql
Your support is the greatest encouragement to bloggers. Thank you for your careful reading. The copyright of this article belongs to the author. Reprint is welcome, but please keep this statement. If you need technical support, I also provide paid services.