有就修改没有就新增语句mysql

                  通过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)