mysql 通过binlog来进行忘记where误操作(delete/update)的数据恢复
通过binlog如何对误操作(update/delete)的数据来进行恢复
一、场景准备
1)以下数据恢复是针对delete忘记加where条件误删除恢复（binlog格式必须是row格式）
1.创建测试的数据表
mysql> create table t1(
-> id int 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.22 sec)
2、插入测试数据
mysql> insert into t1 (name,sex,address)values('范冰冰','f','北京市朝阳区');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t1 (name,sex,address)values('赵四','m','河北省石家庄市');
Query OK, 1 row affected (0.00 sec)
3、删除数据
mysql> delete from t1; Query OK, 2 rows affected (0.06 sec)
二、查询当前mysql的binlog文件
1、查询mysql所有的binlog文件
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 597 | | mysql-bin.000004 | 217 | | mysql-bin.000005 | 369 | | mysql-bin.000006 | 3336 | +------------------+-----------+ 6 rows in set (0.00 sec)
2、查询当前mysql已经记录到哪个binlog子文件中
mysql> show master status; +------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+ | mysql-bin.000006 | 3336 | | mysql | 05abc6e5-f6c6-11e7-b4c7-00163e003b50:1, 66e6f5ed-3ee4-11e8-b871-0050562b0065:1-16 | +------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3、通过binlog文件来删除被恢复的数据
1）查看binlog的路径：
show variables like "log_%"; +----------------------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------------------+ | log_bin | ON | | log_bin_basename | /home/data/mysql57/log/mysql-bin | | log_bin_index | /home/data/mysql57/log/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_builtin_as_identified_by_password | OFF | | log_error | /home/data/mysql57/log/mysqld.log | | log_error_verbosity | 3 | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | ON | | log_slow_admin_statements | OFF
2)通过mysqlbinlog命令打开binlog文件，并在mysql-bin.000006文件中查找“删除”记录
[root@master etc]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /home/data/mysql57/log//mysql-bin.000006 | grep -B 15 -A 15 'DELETE FROM' # at 2971 #180413 21:23:04 server id 24 end_log_pos 3036 CRC32 0xc76df89d GTID last_committed=12 sequence_number=13 SET @@SESSION.GTID_NEXT= '66e6f5ed-3ee4-11e8-b871-0050562b0065:16'/*!*/; # at 3036 #180413 21:23:04 server id 24 end_log_pos 3108 CRC32 0x7b282a6f Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1523625784/*!*/; BEGIN /*!*/; # at 3108 #180413 21:23:04 server id 24 end_log_pos 3146 CRC32 0x2629a4e5 Rows_query # delete from t1 # at 3146 #180413 21:23:04 server id 24 end_log_pos 3200 CRC32 0xd864e964 Table_map: `test`.`t1` mapped to number 244 # at 3200 #180413 21:23:04 server id 24 end_log_pos 3305 CRC32 0x2e77ce47 Delete_rows: table id 244 flags: STMT_END_F ### DELETE FROM `test`.`t1` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='范冰冰' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='北京市朝阳区' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### DELETE FROM `test`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='赵四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='河北省石家庄市' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ # at 3305 #180413 21:23:04 server id 24 end_log_pos 3336 CRC32 0x8a4a7fb1 Xid = 106 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
3）、拷贝mysql-bin.000006文件中的删除记录到指定位置的文件中/home/data/delete.txt
[root@master log]#/usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /home/data/mysql57/log/mysql-bin.000006| sed -n '/### DELETE FROM `test`.`t1`/,/COMMIT/p' > /home/data/delete.txt
打开/home/data/delete.txt文件，这时可以看到该文件中有之前删除数据的记录了
[root@master data]# cat delete.txt ### DELETE FROM `test`.`t1` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='范冰冰' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='北京市朝阳区' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### DELETE FROM `test`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='赵四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='河北省石家庄市' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ # at 3305 #180413 21:23:04 server id 24 end_log_pos 3336 CRC32 0x8a4a7fb1 Xid = 106 COMMIT/*!*/;
4）将delete.txt文件中的记录转换成sql语句
[root@master data]# cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT//\1;/g' | sed 's/@[1-9]=//g' > source.sql
5）在source.sql文件所在的目录下登录mysql并且导入source.sql文件
[root@master data]# mysql -uroot -p <source.sql;
6）查看数据库test.t1删除的数据是否恢复回来
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +----+-----------+-----+-----------------------+ | id | name | sex | address | +----+-----------+-----+-----------------------+ | 1 | 范冰冰 | f | 北京市朝阳区 | | 2 | 赵四 | m | 河北省石家庄市 | +----+-----------+-----+-----------------------+ 2 rows in set (0.00 sec)
可以看见数据已经完全恢复，这种方法的优点是快速，方便
以上所做的操作时针对delete操作进行的恢复，接下来我们针对update操作进行恢复
2）、update忘记加where条件误操作恢复数据（binlog格式必须是row格式）
1.插入测试数据
mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou');
Query OK, 1 row affected (0.00 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.00 sec)
2.现在将id等于3的用户的地址改为zhuhai,update时没有添加条件
mysql> select * from t1; +----+-----------+-----+-----------------------+ | id | name | sex | address | +----+-----------+-----+-----------------------+ | 1 | 范冰冰 | f | 北京市朝阳区 | | 2 | 赵四 | m | 河北省石家庄市 | | 3 | daiiy | m | guangzhou | | 4 | tom | f | shanghai | | 5 | liany | m | beijing | | 6 | lilu | m | zhuhai | +----+-----------+-----+-----------------------+ 6 rows in set (0.00 sec) mysql> update t1 set address='zhuhai'; Query OK, 5 rows affected (0.05 sec) Rows matched: 6 Changed: 5 Warnings: 0 mysql> selete * from t1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selete * from t1' at line 1 mysql> select * from t1; +----+-----------+-----+---------+ | id | name | sex | address | +----+-----------+-----+---------+ | 1 | 范冰冰 | f | zhuhai | | 2 | 赵四 | m | zhuhai | | 3 | daiiy | m | zhuhai | | 4 | tom | f | zhuhai | | 5 | liany | m | zhuhai | | 6 | lilu | m | zhuhai | +----+-----------+-----+---------+ 6 rows in set (0.00 sec)
3、开始恢复，在线上的话，应该比较复杂，要先进行锁表，以免数据再次被污染。（锁表，查看正在写入哪个二进制日志文件中）
mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 (name,sex,address)values('lilu','m','zhuhai');
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
mysql> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000006 | 6113 | | mysql | 05abc6e5-f6c6-11e7-b4c7-00163e003b50:1,
66e6f5ed-3ee4-11e8-b871-0050562b0065:1-23 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、分析二进制日志，并且在其中找到相关记录，在更新是address='zhuhai',我们可以在日志中过滤出来。
[root@master ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /home/data/mysql57/log//mysql-bin.000006 | grep -B 15 'zhuhai'
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 5182
#180413 22:41:36 server id 24 end_log_pos 5213 CRC32 0xa5d74212 Xid = 131
COMMIT/*!*/;
# at 5213
#180413 22:41:42 server id 24 end_log_pos 5278 CRC32 0xd2de884f GTID last_committed=18 sequence_number=19
SET @@SESSION.GTID_NEXT= '66e6f5ed-3ee4-11e8-b871-0050562b0065:22'/*!*/;
# at 5278
#180413 22:41:42 server id 24 end_log_pos 5350 CRC32 0xa625e46e Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1523630502/*!*/;
BEGIN
/*!*/;
# at 5350
#180413 22:41:42 server id 24 end_log_pos 5434 CRC32 0x351792e1 Rows_query
# insert into t1 (name,sex,address)values('lilu','m','zhuhai')
# at 5434
#180413 22:41:42 server id 24 end_log_pos 5488 CRC32 0x8f5474bf Table_map: `test`.`t1` mapped to number 244
# at 5488
#180413 22:41:42 server id 24 end_log_pos 5541 CRC32 0xb1e14604 Write_rows: table id 244 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
### @1=6 /* 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 5541
#180413 22:41:42 server id 24 end_log_pos 5572 CRC32 0x854ae387 Xid = 132
COMMIT/*!*/;
# at 5572
#180413 22:45:07 server id 24 end_log_pos 5637 CRC32 0x804341c4 GTID last_committed=19 sequence_number=20
SET @@SESSION.GTID_NEXT= '66e6f5ed-3ee4-11e8-b871-0050562b0065:23'/*!*/;
# at 5637
#180413 22:45:07 server id 24 end_log_pos 5709 CRC32 0x75119db3 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1523630707/*!*/;
BEGIN
/*!*/;
# at 5709
#180413 22:45:07 server id 24 end_log_pos 5763 CRC32 0xdf50d048 Rows_query
# update t1 set address='zhuhai'
# at 5763
#180413 22:45:07 server id 24 end_log_pos 5817 CRC32 0xcc17c4fd Table_map: `test`.`t1` mapped to number 244
# at 5817
#180413 22:45:07 server id 24 end_log_pos 6082 CRC32 0xaefe4c06 Update_rows: table id 244 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='范冰冰' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='北京市朝阳区' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='范冰冰' /* 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 `test`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='赵四' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='河北省石家庄市' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='赵四' /* 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 `test`.`t1`
### WHERE
### @1=3 /* 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=3 /* 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 `test`.`t1`
### WHERE
### @1=4 /* 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=4 /* 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 `test`.`t1`
### WHERE
### @1=5 /* 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=5 /* 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 */
可以看见里面记录了每一行的变化，这也是binglog格式要一定是row才行的原因。其中@1,@2,@3,@4,分别对应表中id,name,sex,address字段。相信大家看到这里有点明白了吧，对，没错，你猜到了，我们将相关记录转换为sql语句，重新导入数据库。
4、处理要分析处理的二进制日志
[root@master data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /home/data/mysql57/log//mysql-bin.000006 | sed -n '/# at 5763/,/COMMIT/p' > t1.txt
[root@master data]# ll
total 16
-rw-r--r-- 1 root root 695 Apr 13 22:09 delete.txt
drwxr-xr-x 6 mysql mysql 4096 Apr 9 03:43 mysql57
-rw-r--r-- 1 root root 168 Apr 13 22:21 source.sql
-rw-r--r-- 1 root root 3023 Apr 13 23:06 t1.txt
[root@master data]# cat t1.txt
# at 5763
#180413 22:45:07 server id 24 end_log_pos 5817 CRC32 0xcc17c4fd Table_map: `test`.`t1` mapped to number 244
# at 5817
#180413 22:45:07 server id 24 end_log_pos 6082 CRC32 0xaefe4c06 Update_rows: table id 244 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='范冰冰' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='北京市朝阳区' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='范冰冰' /* 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 `test`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='赵四' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='河北省石家庄市' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='赵四' /* 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 `test`.`t1`
### WHERE
### @1=3 /* 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=3 /* 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 `test`.`t1`
### WHERE
### @1=4 /* 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=4 /* 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 `test`.`t1`
### WHERE
### @1=5 /* 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=5 /* 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 6082
#180413 22:45:07 server id 24 end_log_pos 6113 CRC32 0x718f8ad3 Xid = 134
COMMIT/*!*/;
5、这里的sed有点复杂了，大家自行研究
[root@master data]# 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@master data]# cat recover.sql UPDATE `test`.`t1` SET @1=1 , @2='范冰冰' , @3=1 , @4='北京市朝阳区' , WHERE @1=1 ; UPDATE `test`.`t1` SET @1=2 , @2='赵四' , @3=2 , @4='河北省石家庄市' , WHERE @1=2 ; UPDATE `test`.`t1` SET @1=3 , @2='daiiy' , @3=2 , @4='guangzhou' , WHERE @1=3 ; UPDATE `test`.`t1` SET @1=4 , @2='tom' , @3=1 , @4='shanghai' , WHERE @1=4 ; UPDATE `test`.`t1` SET @1=5 , @2='liany' , @3=2 , @4='beijing' , WHERE @1=5 ;
6、将文件中的@1,@2,@3,@4替换为t1表中id，name，sex，address字段，并删除最后字段的","号
[root@master data]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql [root@master data]# sed -i -r 's/(address=.*),/\1/g' recover.sql
[root@master data]# cat recover.sql UPDATE `test`.`t1` SET id=1 , name='范冰冰' , sex=1 , address='北京市朝阳区' WHERE id=1 ; UPDATE `test`.`t1` SET id=2 , name='赵四' , sex=2 , address='河北省石家庄市' WHERE id=2 ; UPDATE `test`.`t1` SET id=3 , name='daiiy' , sex=2 , address='guangzhou' WHERE id=3 ; UPDATE `test`.`t1` SET id=4 , name='tom' , sex=1 , address='shanghai' WHERE id=4 ; UPDATE `test`.`t1` SET id=5 , name='liany' , sex=2 , address='beijing' WHERE id=5 ;
7.到这里日志就处理好了，现在导入即可（导入数据后，解锁表）；
mysql> source recover.sql; Query OK, 1 row affected (4 min 40.59 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.00 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.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
查看数据，是否恢复完整
mysql> select * from t1; +----+-----------+-----+-----------------------+ | id | name | sex | address | +----+-----------+-----+-----------------------+ | 1 | 范冰冰 | f | 北京市朝阳区 | | 2 | 赵四 | m | 河北省石家庄市 | | 3 | daiiy | m | guangzhou | | 4 | tom | f | shanghai | | 5 | liany | m | beijing | | 6 | lilu | m | zhuhai | +----+-----------+-----+-----------------------+ 6 rows in set (0.00 sec)
参考文章：http://www.cnblogs.com/gomysql/p/3582058.html
浙公网安备 33010602011771号