一、场景
每天晚上23:00进行数据库的全备和 binlog 备份。每天中午12:00,进行 binlog 备份。
故障:某人员周二上午10点误删了一个核心表,binlog 和全备都是好的。
恢复思路:
• 首先从全备中使用命令过滤出和核心表相关的语句,恢复到上一天晚上23:00状态。
• 然后从全备和binlog日志中找出开始和结束的position号,使用binlog2sql去指定过滤核心表相关的数据。
• 最后将过滤好数据拿到数据库中去恢复。
二、模拟环境
(1)模拟原始数据
mysql> create database userdata;
mysql> use userdata;
mysql> create table user1 (id int);
mysql> insert user1 values(1),(2),(3);
mysql> create table user2 (id int);
mysql> insert user2 values(4),(5),(6);
(2)模拟周一晚上23:00全备
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --max_allowed_packet=64M | gzip > /backup/full_$(date +%F).sql.gz
(3)模拟周二白天数据变化
mysql> update userdata.user2 set id=1;
(4)模拟周二10点的误删核心表
mysql> delete from userdata.user2; // 注意如果是drop删表的话,用binlog2sql恢复就不行。
三、过滤全备中核心表数据
[root@db01 backup]# gunzip full_2020-11-06.sql.gz
[root@db01 backup]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `user2`/!d;q' /backup/full_2020-11-06.sql > /backup/table1.sql
[root@db01 backup]# grep -i 'INSERT INTO `user2`' /backup/full_2020-11-06.sql >> /backup/table1.sql
[root@db01 backup]# cat table1.sql
DROP TABLE IF EXISTS `user2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `user2` VALUES (4),(5),(6);
四、使用binlog2sql过滤数据
(1)获取开始position号
[root@db01 backup]# grep "-- CHANGE MASTER TO" /backup/full_2020-11-06.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1221;
(2)安装binlog2sql
[root@db01 ~]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
[root@db01 binlog2sql]# yum install python3
[root@db01 binlog2sql]# pip3 install -r requirements.txt
(3)过滤核心表数据
[root@db01 binlog2sql]# python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p000000 -duserdata -t user2 --start-file='mysql-bin.000003' --start-pos='1221'
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=4 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=5 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=6 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
DELETE FROM `userdata`.`user2` WHERE `id`=1 LIMIT 1; #start 1503 end 1738 time 2020-11-06 05:32:09
DELETE FROM `userdata`.`user2` WHERE `id`=1 LIMIT 1; #start 1503 end 1738 time 2020-11-06 05:32:09
DELETE FROM `userdata`.`user2` WHERE `id`=1 LIMIT 1; #start 1503 end 1738 time 2020-11-06 05:32:09
我们来看上面过滤,效果看起来挺不错,只要去掉后三行的delete操作就好了,但是因为以上是我们操作数据量少,所以一目了然。如果数据量很大,必然update和delete操作记录交错混杂,那么如何去过滤,当然花时间肯定可以过滤出。
最方便还是要确定删表之前结束的position号,我们可以通过以下方式去分析:
数据库内show查看binlog日志内容。
mysql> show binlog events in 'mysql-bin.000003'; # 下面列出的就是删表的事件。
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
......省略
| mysql-bin.000003 | 1568 | Query | 1 | 1636 | BEGIN |
| mysql-bin.000003 | 1636 | Table_map | 1 | 1688 | table_id: 149 (userdata.user2) |
| mysql-bin.000003 | 1688 | Delete_rows | 1 | 1738 | table_id: 149 flags: STMT_END_F |
| mysql-bin.000003 | 1738 | Xid | 1 | 1769 | COMMIT /* xid=595 */ |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
因为delete是DML操作,相对DDL操作记录的信息可读性差,通过上面执行结果我们也可以看到,不好确定。
我这数据量小,从Event_type列中的Delete_rows就分析出来,结束position号为1568。
使用mysqlbinlog命令查看分析binlog日志文件。通过这种方式确定结束position号可能要准确些,确定结束position号为1568,当然数据量大确定起来也麻烦,因为输出信息超级多,多利用三剑客。哈哈,啰嗦了,也不绝对大家随意,只要可以分析出来,恢复数据就好。
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -v --start-position=1221 mysql-bin.000003
......省略
# at 1568
#201106 5:32:09 server id 1 end_log_pos 1636 CRC32 0x453c8283 Query thread_id=24 exec_time=0 error_code=0
SET TIMESTAMP=1604658729/*!*/;
BEGIN
/*!*/;
# at 1636
#201106 5:32:09 server id 1 end_log_pos 1688 CRC32 0xba01b7e9 Table_map: `userdata`.`user2` mapped to number 149
# at 1688
#201106 5:32:09 server id 1 end_log_pos 1738 CRC32 0x6fc8d5e4 Delete_rows: table id 149 flags: STMT_END_F
### DELETE FROM `userdata`.`user2`
### WHERE
### @1=1
### DELETE FROM `userdata`.`user2`
### WHERE
### @1=1
### DELETE FROM `userdata`.`user2`
### WHERE
### @1=1
# at 1738
......
再次过滤核心表数据
[root@db01 binlog2sql]# python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p000000 -duserdata -t user2 --start-file='mysql-bin.000003' --start-pos=1221 --stop-pos=1568
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=4 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=5 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
UPDATE `userdata`.`user2` SET `id`=1 WHERE `id`=6 LIMIT 1; #start 1221 end 1472 time 2020-11-06 05:31:46
[root@db01 binlog2sql]# python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p000000 -duserdata -t user2 --start-file='mysql-bin.000003' --start-pos=1221 --stop-pos=1568 > /backup/table2.sql
五、恢复数据
mysql> set sql_log_bin=0;
mysql> use userdata;
mysql> source /backup/table1.sql;
mysql> select * from user2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
mysql> source /backup/table2.sql;
mysql> select * from user2;
+------+
| id |
+------+
| 1 |
| 1 |
| 1 |
+------+
mysql> set sql_log_bin=1;
以上是我对binlog2sql一些基本用法和粗浅见解,有什么问题望大佬们指正。binlog2sql还有不少用法例如可以通过时间去过滤、使用flashback模式生成回滚sql等,这里就不介绍了,文档比我说的好,文档地址:https://github.com/danfengcao/binlog2sql。
参考文章:https://www.jianshu.com/p/6fbdcb7695cb