热门标签
热门文章
当前位置: article > 正文
mysqlbinlog恢复delete的数据
作者：笔触狂放9 | 2024-05-07 06:44:03
赞
踩
mysqlbinlog恢复delete的数据
实验目的
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
实验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 ,
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 再用文本工具编译为恢复的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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 恢复数据
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 恢复后检查
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
另外如何用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语句
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
实验总结
达到实验目的
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/547857
推荐阅读
相关标签
