实验目的
delete数据后，用mysqlbinlog进行数据恢复
实验过程
原表
mysql> select * from mytest;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | xw01 | 90 |
| 2 | xw02 | 92 |
| 3 | xw03 | 93 |
| 4 | xw04 | 94 |
| 5 | xw05 | 93 |
| 6 | xw06 | 96 |
| 7 | xw7 | 97 |
+----+------+-------+
7 rows in set (0.00 sec)
mysql>
# 查看binlog日志文件
root@dg02-xianwetitest-dy03:/var/log/mysql# ls
error.log mysql-bin.000001 mysql-bin.000002 mysql-bin.index
root@dg02-xianwetitest-dy03:/var/log/mysql#
mysql> show binlog events in 'mysql-bin.000002' limit 10;
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.33-0ubuntu0.16.04.1-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000002 | 291 | Table_map | 1 | 344 | table_id: 108 (test.mytest) |
| mysql-bin.000002 | 344 | Update_rows | 1 | 408 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 408 | Xid | 1 | 439 | COMMIT /* xid=91 */ |
| mysql-bin.000002 | 439 | Anonymous_Gtid | 1 | 504 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 504 | Query | 1 | 576 | BEGIN |
| mysql-bin.000002 | 576 | Table_map | 1 | 629 | table_id: 108 (test.mytest) |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
10 rows in set (0.00 sec)
# 测试删除3行数据
mysql> delete from mytest where id=1 or id=2 or id=3;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from mytest;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 4 | xw04 | 94 |
| 5 | xw05 | 93 |
| 6 | xw06 | 96 |
| 7 | xw7 | 97 |
+----+------+-------+
4 rows in set (0.00 sec)
实验mysqlbin找到删除的命令
mysql> show binlog events in 'mysql-bin.000002' ;
.... 此次省略部分输出
| mysql-bin.000002 | 2811 | Query | 1 | 2883 | BEGIN |
| mysql-bin.000002 | 2883 | Table_map | 1 | 2936 | table_id: 115 (test.mytest) |
| mysql-bin.000002 | 2936 | Write_rows | 1 | 2984 | table_id: 115 flags: STMT_END_F |
| mysql-bin.000002 | 2984 | Xid | 1 | 3015 | COMMIT /* xid=331 */ |
| mysql-bin.000002 | 3015 | Anonymous_Gtid | 1 | 3080 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 3080 | Query | 1 | 3152 | BEGIN |
| mysql-bin.000002 | 3152 | Table_map | 1 | 3205 | table_id: 115 (test.mytest) |
| mysql-bin.000002 | 3205 | Delete_rows | 1 | 3282 | table_id: 115 flags: STMT_END_F |
| mysql-bin.000002 | 3282 | Xid | 1 | 3313 | COMMIT /* xid=338 */ |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set (0.00 sec)
#找到“Delete_rows”对应所在的pos，是3080到3282
# 实验mysqlbin从binlog文件查找到删除对应的语句
root@dg02-xianwetitest-dy03:/var/log/mysql# mysqlbinlog --start-position=3080 --stop-position=3282 --database=test mysql-bin.000002 -vv |grep ^"###"
### DELETE FROM `test`.`mytest`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='xw01' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @3=90 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`mytest`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='xw02' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @3=92 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`mytest`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='xw03' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @3=93 /* INT meta=0 nullable=1 is_null=0 */
mysqlbinlog --start-position=3080 --stop-position=3282 --database=test mysql-bin.000002 -vv | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g'
INSERT INTO `test`.`mytest`
SELECT
1 ,
'xw01' ,
90 ,
INSERT INTO `test`.`mytest`
SELECT
2 ,
'xw02' ,
92 ,
INSERT INTO `test`.`mytest`
SELECT
3 ,
'xw03' ,
93 ,
- 再用文本工具编译为恢复的sql语句，如果太多可以用bin2sql工具处理
INSERT INTO `test`.`mytest`
SELECT
1 ,
'xw01' ,
90 ;
INSERT INTO `test`.`mytest`
SELECT
2 ,
'xw02' ,
92 ;
INSERT INTO `test`.`mytest`
SELECT
3 ,
'xw03' ,
93
- 恢复数据
mysql> INSERT INTO `test`.`mytest`
-> SELECT
-> 1 ,
-> 'xw01' ,
-> 90 ;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `test`.`mytest`
-> SELECT
-> 2 ,
-> 'xw02' ,
-> 92 ;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `test`.`mytest`
-> SELECT
-> 3 ,
-> 'xw03' ,
-> 93 ;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
- 恢复后检查
mysql> select * from mytest;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | xw01 | 90 |
| 2 | xw02 | 92 |
| 3 | xw03 | 93 |
| 4 | xw04 | 94 |
| 5 | xw05 | 93 |
| 6 | xw06 | 96 |
| 7 | xw7 | 97 |
+----+------+-------+
7 rows in set (0.00 sec)
另外如何用mysqlbinlog语言转换太麻烦可以用binlog2sql工具，使用方法如下
参考文档：https://juejin.cn/post/7150801079196712967
root@dg02-xianwetitest-dy03:~/bin2sql/bin2sql/binlog2sql# /usr/bin/python2.7 binlog2sql/binlog2sql.py --start-position=3080 --stop-position=3282 --start-file='mysql-bin.000002' -h127.0.0.1 -uroot -proot@123 -dtest -tmytest
DELETE FROM `test`.`mytest` WHERE `score`=90 AND `id`=1 AND `name`='xw01' LIMIT 1; #start 3080 end 3282 time 2024-04-29 18:01:43
DELETE FROM `test`.`mytest` WHERE `score`=92 AND `id`=2 AND `name`='xw02' LIMIT 1; #start 3080 end 3282 time 2024-04-29 18:01:43
DELETE FROM `test`.`mytest` WHERE `score`=93 AND `id`=3 AND `name`='xw03' LIMIT 1; #start 3080 end 3282 time 2024-04-29 18:01:43
root@dg02-xianwetitest-dy03:~/bin2sql/bin2sql/binlog2sql#
root@dg02-xianwetitest-dy03:~/bin2sql/bin2sql/binlog2sql#
root@dg02-xianwetitest-dy03:~/bin2sql/bin2sql/binlog2sql#
root@dg02-xianwetitest-dy03:~/bin2sql/bin2sql/binlog2sql# /usr/bin/python2.7 binlog2sql/binlog2sql.py --start-position=3080 --stop-position=3282 --start-file='mysql-bin.000002' -h127.0.0.1 -uroot -proot@123 -dtest -tmytest -B
INSERT INTO `test`.`mytest`(`score`, `id`, `name`) VALUES (93, 3, 'xw03'); #start 3080 end 3282 time 2024-04-29 18:01:43
INSERT INTO `test`.`mytest`(`score`, `id`, `name`) VALUES (92, 2, 'xw02'); #start 3080 end 3282 time 2024-04-29 18:01:43
INSERT INTO `test`.`mytest`(`score`, `id`, `name`) VALUES (90, 1, 'xw01'); #start 3080 end 3282 time 2024-04-29 18:01:43
上面就是需要恢复数据的sql语句
实验总结
达到实验目的