526互联

使用mysqlbinlog恢复数据库（逆向或正向都可以）

发布时间 2023-12-05 10:20:14作者: smallfishy

我确实喜欢写一些不一样的东西。

如果我们在网上搜索“mysql恢复数据库”、“使用mysqlbinlog恢复数据库”、“mysql使用binlog恢复被删除的表”等，一般可以搜索到很多，但是仔细分析会发现，这许多的基本都在讲几件事：
1、查看是否已开启在线日志，如果没有开启就开启
2、查看日志文件位置
3、查看删除命令(delete或drop)执行前的POS码
4、执行恢复命令恢复到被删之前的状态


以上操作不能算是错，但是在某些情况下却没能恢复数据，笔者这几天就遇到了这个情况。
我先复盘一下具体操作：

方法一：
1、查看是否已开启在线日志。
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_replica_updates | ON |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
+----------------------------------------+----------------------------------------+
21 rows in set (0.01 sec)
上文中log_bin的值为ON，说明日志已开启。其实现在mysql默认配置就已经开始了在线日志。


2、查看日志文件位置
不用查了，上面已经显示了。就在/var/lib/mysql/binlog目录下(log_bin_basename的值)。


3、查看删除命令(delete或drop)执行前的POS码
mysql> show binlog events in 'binlog.000044';
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000044 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.28, Binlog ver: 4 |
| binlog.000044 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000044 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000044 | 236 | Query | 1 | 710 | use `qq`; /* ApplicationName=DBeaver 23.3.0 - SQLEditor <Script.sql> */ CREATE TABLE `zjzx_yxb` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL,
`phone` char(11) NOT NULL,
`idcar` char(18) DEFAULT NULL,
`company` varchar(50) DEFAULT NULL,
`date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 /* xid=39 */ |
| binlog.000044 | 710 | Anonymous_Gtid | 1 | 789 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000044 | 789 | Query | 1 | 862 | BEGIN |
| binlog.000044 | 862 | Table_map | 1 | 931 | table_id: 236 (qq.zjzx_yxb) |
| binlog.000044 | 931 | Write_rows | 1 | 1014 | table_id: 236 flags: STMT_END_F |
| binlog.000044 | 1014 | Xid | 1 | 1045 | COMMIT /* xid=61 */ |
| binlog.000044 | 1045 | Anonymous_Gtid | 1 | 1124 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
|
......
| binlog.000044 | 36862 | Anonymous_Gtid | 1 | 36939 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000044 | 36939 | Query | 1 | 37056 | use `qq`; alter table zjzx AUTO_INCREMENT=1 /* xid=231 */ |
| binlog.000044 | 37056 | Anonymous_Gtid | 1 | 37135 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000044 | 37135 | Query | 1 | 37208 | BEGIN |
| binlog.000044 | 37208 | Table_map | 1 | 37283 | table_id: 254 (qq.zjzx) |
| binlog.000044 | 37283 | Delete_rows | 1 | 41281 | table_id: 254 flags: STMT_END_F |
| binlog.000044 | 41281 | Xid | 1 | 41312 | COMMIT /* xid=232 */ |
| binlog.000044 | 41312 | Anonymous_Gtid | 1 | 41391 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
......

上文中：Delete_rows的POS码是37283，但是要想恢复该操作，应找到完整操作的开始POS码：37056。因此，我们执行操作：
mysqlbinlog --no-defaults --stop-position=37056 --database=qq /var/lib/mysql/binlog.000044 |mysql -uroot -p'&@j5gGpBP6@PJ8#k' qq

 

执行完毕，进入数据查看，没有。
这就尴尬了。
想要回复的数据表为qq.zjzx，但是仍然找不到这张表。

还以为是自己操作有什么问题，我又重复了几次操作，仍然不能成功。最后不得已，采用另一种方式：

既然逆向恢复不了，那我就从头开始重做一遍binlog。

 

方法二：重头再来


主要思路是这样：
1、找到创建数据表的开始语句
2、按顺序找到中间所有binlog，并找到每个binlog有效的开始、结束POS码
3、按顺序一个一个重做恢复数据


下面是详细操作
1、找到创建数据表的开始语句
mysql> show binlog events in 'binlog.000038';
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000038 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.28, Binlog ver: 4 |
| binlog.000038 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000038 | 157 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 234 | Query | 1 | 342 | create database zjzx /* xid=15 */ |
| binlog.000038 | 342 | Anonymous_Gtid | 1 | 421 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 421 | Query | 1 | 653 | ALTER USER 'qq'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$R\\r5=Ik
6?#rWxjYwza9slCQGeIm24T0Lg7SEo7FDXa9Wi5cBAVd50' /* xid=65 */ |
| binlog.000038 | 653 | Anonymous_Gtid | 1 | 732 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 732 | Query | 1 | 1083 | use `qq`; CREATE TABLE `zjzx` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) ,
`account` varchar(50) ,
`idcar` char(18) ,
`company` varchar(50) ,
`phone` char(11) ,
`date` char(8) ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 /* xid=83 */ |
| binlog.000038 | 1083 | Anonymous_Gtid | 1 | 1160 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 1160 | Query | 1 | 1277 | use `qq`; alter table zjzx AUTO_INCREMENT=1 /* xid=84 */ |
| binlog.000038 | 1277 | Anonymous_Gtid | 1 | 1354 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 1354 | Query | 1 | 1470 | use `qq`; ALTER TABLE zjzx ADD sex char(5) /* xid=85 */ |
| binlog.000038 | 1470 | Anonymous_Gtid | 1 | 1547 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
......
| binlog.000038 | 5331 | Anonymous_Gtid | 1 | 5410 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 5410 | Query | 1 | 5483 | BEGIN |
| binlog.000038 | 5483 | Table_map | 1 | 5558 | table_id: 117 (qq.zjzx) |
| binlog.000038 | 5558 | Delete_rows | 1 | 5646 | table_id: 117 flags: STMT_END_F |
| binlog.000038 | 5646 | Xid | 1 | 5677 | COMMIT /* xid=121 */ |
| binlog.000038 | 5677 | Anonymous_Gtid | 1 | 5754 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 5754 | Query | 1 | 5871 | use `qq`; alter table zjzx AUTO_INCREMENT=1 /* xid=123 */ |
| binlog.000038 | 5871 | Stop | 1 | 5894 |

上文中，数据表qq.zjzx创建的POS码是732，但是该操作开始的POS码是653。查看文件(binlog.000038)结束的POS码是5677。


2、按顺序找到中间所有binlog，并找到每个binlog有效的开始、结束POS码
具体示例，我就不再展示，只列出结果：
POS开始码 POS结束码 binlog文件
653 5677 binlog.000038
157 47460 binlog.000040
157 2462 binlog.000041
157 1018 binlog.000042
157 2588 binlog.000043


3、按顺序一个一个重做恢复数据
/usr/bin/mysqlbinlog --start-position=653 --stop-position=5677 --database=qq /var/lib/mysql/binlog.000038 | mysql -uroot -p'&@j5gGpBP6@PJ8#k' qq
/usr/bin/mysqlbinlog --start-position=157 --stop-position=47460 --database=qq /var/lib/mysql/binlog.000040 | mysql -uroot -p'&@j5gGpBP6@PJ8#k' qq
/usr/bin/mysqlbinlog --start-position=157 --stop-position=2462 --database=qq /var/lib/mysql/binlog.000041 | mysql -uroot -p'&@j5gGpBP6@PJ8#k' qq
/usr/bin/mysqlbinlog --start-position=157 --stop-position=1018 --database=qq /var/lib/mysql/binlog.000042 | mysql -uroot -p'&@j5gGpBP6@PJ8#k' qq
/usr/bin/mysqlbinlog --start-position=157 --stop-position=2588 --database=qq /var/lib/mysql/binlog.000043 | mysql -uroot -p'&@j5gGpBP6@PJ8#k' qq

最后再查看，找到了想要的数据表。