使用binlog2sql还原MySQL到任意时间点

发表于 更新于 分类于 Valine： 本文字数： 9.1k 阅读时长 ≈ 8 分钟

准备工作

创建binlog2sql账号

1
CREATE USER binlog2sql@'%' IDENTIFIED BY 'binlog2sql';

授权

1
2
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO binlog2sql@'%' identified by 'binlog2sql';
FLUSH privileges;

服务器配置

默认配置文件路径

1
/etc/mysql/mysql.conf.d/mysqld.cnf

查找server-id，附近的选项按如下设置

1
2
3
4
5
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log # 开启binlog，设置路径
max_binlog_size = 1G # 设置单个binlog大小上限
binlog_format = row # 设置binlog模式，binlog2sql要求必须是row模式
binlog_row_image = full # 默认的，可以不设

重启mysql

1
service mysql restart

下载binlog2sql

1
git clone https://github.com/danfengcao/binlog2sql.git /opt/binlog2sql

安装依赖

1
pip install -r /opt/binlog2sql/requirements.txt

创建binlog2sql别名

为了方便使用binlog2sql.py脚本，可以把别名设置写在~/.bash_aliases文件中

1
alias binlog2sql='python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 127.0.0.1 -u binlog2sql -p binlog2sql'

如何查看一个binlog文件记录的position的范围？

1
mysqlbinlog -v mysql-bin.000010|tail -n 100|less

输出

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
60
61
62
63
64
……
# at 4356297
#220402 14:12:19 server id 1  end_log_pos 4356362 CRC32 0xeb816e97      Anonymous_GTID  last_committed=5723     sequence_number=5
724    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4356362
#220402 14:12:19 server id 1  end_log_pos 4356439 CRC32 0x91cd3acc      Query   thread_id=45    exec_time=0     error_code=0
SET TIMESTAMP=1648879939/*!*/;
BEGIN
/*!*/;
# at 4356439
#220402 14:12:19 server id 1  end_log_pos 4356548 CRC32 0x6635890f      Table_map: `gogoal_v2`.`rpt_target_price_adjust` mapped t
o number 226
# at 4356548
#220402 14:12:19 server id 1  end_log_pos 4356766 CRC32 0xa3f8f451      Write_rows: table id 226 flags: STMT_END_F

BINLOG '
Q+lHYhMBAAAAbQAAAMR5QgAAAOIAAAAAAAEACWdvZ29hbF92MgAXcnB0X3RhcmdldF9wcmljZV9h
ZGp1c3QAFAMDDw8PAwMPDwoKBQUFAxISCBISETwAlgDCAZYAWAIICAgAAAAA/v8PD4k1Zg==
Q+lHYh4BAAAA2gAAAJ56QgAAAOIAAAAAAAEAAgAU////AAD8HekUABzdFQAGMDAwMDAyB+S4h+en
kUE3AOS4h+enkUHvvJrliKnmtqbnjofljovlipvph4rmlL7vvIzmlrDkuJrliqHlhZHnjrDlop7p
lb8WAAAAPQAAAAzlub/lj5Hor4HliLgXAOmDremVhyzkuZDliqDmoIss6YKi6I6YgcwPS8wPKVyP
wvVoPEAzMzMzM3M5QGZmZmZmJjNAAgAAAJmshMqAmayEyoBC83pdAQAAAFH0+KM=
'/*!*/;
### INSERT INTO `gogoal_v2`.`rpt_target_price_adjust`
### SET
###   @1=1370397
###   @2=1432860
###   @3='000002'
###   @4='万科A'
###   @5='万科A：利润率压力释放，新业务兑现增长'
###   @6=22
###   @7=61
###   @8='广发证券'
###   @9='郭镇,乐加栋,邢莘'
###   @10='2022:04:01'
###   @11='2022:02:11'
###   @12=28.410000000000000142
###   @13=25.449999999999999289
###   @14=19.149999999999998579
###   @15=2
###   @16='2022-04-02 12:42:00'
###   @17='2022-04-02 12:42:00'
###   @18=5863306050
###   @19=NULL
###   @20=NULL
# at 4356766
#220402 14:12:19 server id 1  end_log_pos 4356797 CRC32 0x5d69576f      Xid = 715129
COMMIT/*!*/;
# at 4356797
#220402 14:12:19 server id 1  end_log_pos 4356862 CRC32 0x941d7ebe      Anonymous_GTID  last_committed=5724     sequence_number=5725    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4356862
#220402 14:12:19 server id 1  end_log_pos 4356939 CRC32 0x25f3bc96      Query   thread_id=45    exec_time=0     error_code=0
SET TIMESTAMP=1648879939/*!*/;
BEGIN
/*!*/;
# at 4356939
#220402 14:12:19 server id 1  end_log_pos 4357048 CRC32 0x94e54d42      Table_map: `gogoal_v2`.`rpt_target_price_adjust` mapped to number 226
# at 4357048
#220402 14:12:19 server id 1  end_log_pos 4357304 CRC32 0x6be0ae0b      Write_rows: table id 226 flags: STMT_END_F
……

其中的 #at 后面的数字就是position，文件首个和最后一个position就是这个binlog记录的position的范围。

另外，举例来说，如果我们要解析出的insert语句，需要从4356362解析到4356766，从BEGIN上面的position到COMMIT上面的position，至于update或delete应该是类似的。

1
binlog2sql -d gogoal_v2 --start-file=mysql-bin.000010 --start-position=4356362 --stop-position=4356766

输出

1
INSERT INTO `gogoal_v2`.`rpt_target_price_adjust`(`id`, `report_id`, `stock_code`, `stock_name`, `title`, `report_type`, `organ_id`, `organ_name`, `author`, `current_create_date`, `previous_create_date`, `current_target_price`, `previous_target_price`, `current_price`, `price_adjust_mark`, `entrytime`, `updatetime`, `tmstamp`, `UTSUPTIME`, `UTSFIRSTTIME`) VALUES (1370397, 1432860, '000002', '万科A', '万科A：利润率压力释放，新业务兑现增长', 22, 61, '广发证券', '郭镇,乐加栋,邢莘', '2022-04-01', '2022-02-11', 28.41, 25.45, 19.15, 2, '2022-04-02 12:42:00', '2022-04-02 12:42:00', 5863306050, NULL, NULL); #start 4356439 end 4356766 time 2022-04-02 14:12:19

如何保留一个数据库的全量备份？

有两种形式，一种是通过mysqldump把数据库导出成sql，正常应该通过这种形式

1
mysqldump --master-data=2 gogoal_v2> /data/dbbackup/20220406/gogoal_v2.sql

这里的–master-data=2选项会在生成的sql中生成被注释掉的 CHANGE MASTER 命令，里面有binlog文件编号和位置编号

另一种是直接备份数据库目录，我们的mysql中只有一个数据库gogoal_v2，所以备份目录是最方便的。
那么我们就要手动确定备份下来截止的binlog编号和位置编号，为了防止一边备份数据库一边在变，我们需要先停止数据库，而恰好我们操作的这个数据库又不是生产环境使用的，可以随时停止

首先停止数据库

1
service mysql stop

查看/data/mysql-binlog下最新的binlog文件和最新的position并记录下来，可以写在后面的备份目录下

1
mysql-bin.000012 5055197

备份/var/lib/mysql下的全部文件

1
cp -rp /var/lib/mysql /data/dbbackup/20220406/

最后在恢复启动数据库

1
service mysql start

如何还原一个全量备份？

我们将备份还原到一个新的mysql容器中

首先创建一个mysql容器

1
docker run --name mysql -Pdit daimingzhuang/mysql

daimingzhuang/mysql和官方mysql容器的区别是按原mysql的配置做了字符编码、大小写相关的设置，允许root远程访问，设置了默认密码

1
2
3
4
5
lower_case_table_names=1
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true

实际使用的命令还包含了资源使用限制

1
2
3
4
5
6
7
8
9
docker run --name mysql -Pdit \
-h mysql \
--cpuset-cpus="1,3" \
--memory=4G \
--device-read-bps /dev/sdd:80mb \
--device-write-bps /dev/sdd:50mb \
--device-read-bps /dev/sdb:80mb \
--device-write-bps /dev/sdb:50mb \
daimingzhuang/mysql

然后连到容器停止mysql服务

1
2
docker exec -it mysql bash
service mysql stop

把备份文件复制到容器中

1
docker cp /data/dbbackup/20220406/mysql mysql:/var/lib/

还需要复制debian.cnf解决服务控制权限问题（里面有debian-sys-maint用户的密码）

1
docker cp /etc/mysql/debian.cnf mysql:/etc/mysql/debian.cnf

再到容器中修改目录权限并启动mysql

1
2
chown -R mysql:mysql /var/lib/mysql
service mysql start

如何用binlog还原增量备份？

下面将在前面已经还原了全量备份的基础上，继续利用binlog还原到指定时间点
我们先看一下当前binlog文件

1
ll /var/log/mysql

输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
total 1221304
drwx------ 2 mysql mysql      1024 Apr  7 06:25 ./
drwxr-xr-x 8 root  root       1024 Apr  6 20:14 ../
-rw-r----- 1 mysql mysql 164683302 Mar 26 06:25 mysql-bin.000001
-rw-r----- 1 mysql mysql   1146243 Mar 27 06:25 mysql-bin.000002
-rw-r----- 1 mysql mysql   5487816 Mar 28 06:25 mysql-bin.000003
-rw-r----- 1 mysql mysql 176003823 Mar 29 06:25 mysql-bin.000004
-rw-r----- 1 mysql mysql 180791383 Mar 30 06:25 mysql-bin.000005
-rw-r----- 1 mysql mysql 189424937 Mar 31 06:25 mysql-bin.000006
-rw-r----- 1 mysql mysql 177521662 Apr  1 06:25 mysql-bin.000007
-rw-r----- 1 mysql mysql   1704056 Apr  1 10:43 mysql-bin.000008
-rw-r----- 1 mysql mysql 172659973 Apr  2 06:25 mysql-bin.000009
-rw-r----- 1 mysql mysql   4357335 Apr  2 18:28 mysql-bin.000010
-rw-r----- 1 mysql mysql   1212646 Apr  2 22:33 mysql-bin.000011
-rw-r----- 1 mysql mysql   5055220 Apr  6 17:01 mysql-bin.000012
-rw-r----- 1 mysql mysql 166213563 Apr  7 06:25 mysql-bin.000013
-rw-r----- 1 mysql mysql   4326594 Apr  7 14:12 mysql-bin.000014
-rw-r----- 1 mysql mysql       504 Apr  7 06:25 mysql-bin.index

我们可以估计需要还原到的数据点在哪个binlog文件中，比如我想还原到20220407 0点，那就应该在这里 Apr 7 06:25 mysql-bin.000013 ，事实上我们把000014或更多的binlog包含进来不要紧，只是浪费了执行命令的过滤时间

在原mysql上从binlog解析出全量备份后的增量sql

1
binlog2sql -d gogoal_v2 --start-file=mysql-bin.000013 --stop-file=mysql-bin.000014 --stop-datetime='2022-04-07 00:00:00' > inc_20220407000000.sql

–start-file和–start-position根据全量备份时记录的位置设置，我们全量备份时候记录的是mysql-bin.000012 5055197 ，而事实上因为我们在做全量备份的时候停止了mysql服务，重启后会写入新的binlog，所以我们可以从mysql-bin.000013开始还原，–stop-datetime 指定了还原到的时间点

把增量sql复制到容器内

1
docker cp ./inc_20220407000000.sql mysql:/root/

在容器内执行还原

1
2
docker exec -it mysql bash
mysql gogoal_v2 < /root/inc_20220407000000.sql

如何在没有全量备份的情况下将数据库退回到指定时间点？

当前时间 2022-04-12 13:38:00

假设我们想退回到 2022-04-11 15:00:00

在有全量备份的情况下我们可以通过上面的方法先还原全量备份再叠加通过binlog导出的增量sql来还原，但在没有全量备份或者全量备份比较久远的情况下，我们也可以通过binlog2sql导出基于当前数据的回退sql来还原。

首先停止数据库

1
service mysql stop

还是先看一下当前binlog文件

1
ll /var/log/mysql

输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
total 1732017
drwx------ 2 mysql mysql      1024 Apr 12 13:39 ./
drwxr-xr-x 8 root  root       1024 Apr  6 20:14 ../
-rw-r----- 1 mysql mysql 164683302 Mar 26 06:25 mysql-bin.000001
-rw-r----- 1 mysql mysql   1146243 Mar 27 06:25 mysql-bin.000002
-rw-r----- 1 mysql mysql   5487816 Mar 28 06:25 mysql-bin.000003
-rw-r----- 1 mysql mysql 176003823 Mar 29 06:25 mysql-bin.000004
-rw-r----- 1 mysql mysql 180791383 Mar 30 06:25 mysql-bin.000005
-rw-r----- 1 mysql mysql 189424937 Mar 31 06:25 mysql-bin.000006
-rw-r----- 1 mysql mysql 177521662 Apr  1 06:25 mysql-bin.000007
-rw-r----- 1 mysql mysql   1704056 Apr  1 10:43 mysql-bin.000008
-rw-r----- 1 mysql mysql 172659973 Apr  2 06:25 mysql-bin.000009
-rw-r----- 1 mysql mysql   4357335 Apr  2 18:28 mysql-bin.000010
-rw-r----- 1 mysql mysql   1212646 Apr  2 22:33 mysql-bin.000011
-rw-r----- 1 mysql mysql   5055220 Apr  6 17:01 mysql-bin.000012
-rw-r----- 1 mysql mysql 166213563 Apr  7 06:25 mysql-bin.000013
-rw-r----- 1 mysql mysql 173374732 Apr  8 06:25 mysql-bin.000014
-rw-r----- 1 mysql mysql 171723561 Apr  9 06:25 mysql-bin.000015
-rw-r----- 1 mysql mysql    471311 Apr 10 06:25 mysql-bin.000016
-rw-r----- 1 mysql mysql   3800429 Apr 11 06:25 mysql-bin.000017
-rw-r----- 1 mysql mysql 176073129 Apr 12 06:25 mysql-bin.000018
-rw-r----- 1 mysql mysql   1845781 Apr 12 12:41 mysql-bin.000019
-rw-r----- 1 mysql mysql       684 Apr 12 06:25 mysql-bin.index

我们可以估计需要还原到的时间点 2022-04-11 15:00:00 应该在 mysql-bin.000018 中，那么回退需要用到的binlog就是从000018到000019，导出回退sql的命令如下

1
binlog2sql --flashback -d gogoal_v2 --start-file=mysql-bin.000018 --stop-file=mysql-bin.000019 --start-date="2022-04-11 15:00:00" > flashback.sql

和之前命令的区别就是–flashback选项，表示导出符合指定条件内的回退sql语句。

之后创建一个新的mysql容器，把当前数据的/var/lib/mysql复制到容器中，修正权限。不要忘了重新启动前面停止的mysql服务。再把flashback.sql导入到容器数据库中即可实现回退。具体语句参照前面已经讲过的内容。