MySQL Binlog 数据恢复

一、恢复原理

发现很多网上的教程都是不完整的, 整个流程中完全不符合实际生产环境, 所以这里做一下简要说明.

1.1、Binlog 是什么

MySQL Binary Log (BinLog) is a record of all changes made to a MySQL database. It serves as an audit trail of changes and can be used for various purposes, such as data recovery, replication, and database monitoring. BinLogs are created by the MySQL server and contain a record of all SQL statements that modify data.

简而言之, Binlog 是 MySQL 内部记录数据修改的 “日志”, 通过 Binlog 我们可以重放以前的执行流程.

1.2、Binlog 恢复前提

想要使用 MySQL Binlog 进行恢复数据, 大致需要两个前提:

  • 1、你要有 Binlog, 也就是说 MySQL 服务器必须已经开启了 Binlog
  • 2、你需要有一个被删除时间点之前的完整备份

网上很多 Binlog 恢复都不谈核心问题, 核心问题就是你想做恢复之前必须有一份删除时间点之前的完整数据库备份, 因为本质上恢复流程就是重放所有 SQL 执行, 只不过只重放到被删之前而已.

二、数据库配置

对于 MySQL 8.0.x 可以使用以下配置让 MySQL 开启 Binlog 记录, 样例中有些配置不是必须的, 请自行参考引用文章:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 开启 binlog
log_bin=/data/mysql/binlogs/mysql-bin
# 作为从库时，同步信息依然写入 binlog，方便链式同步
log_slave_updates=1
# 每 n 次事务 binlog 刷新到磁盘
# refs http://liyangliang.me/posts/2014/03/innodb_flush_log_at_trx_commit-and-sync_binlog/
sync_binlog=100
innodb_flush_log_at_trx_commit=2
# binlog 格式(refs https://zhuanlan.zhihu.com/p/33504555)
binlog_format=row
# binlog 自动清理时间(20d)
binlog_expire_logs_seconds=1728000
# 开启 relay-log，一般作为 slave 时开启
relay_log=mysql-replay
# 每个 session binlog 缓存
binlog_cache_size=4M
# binlog 滚动大小
max_binlog_size=1024M

三、测试环境准备

本测试中所有数据库版本为 8.0.35, 理论上 5.x 版本和更高版本思路应该一致.

3.1、测试环境

为了测试数据恢复搭建了一套测试环境, 测试环境中所有节点统一采用 Percona MySQL 8.0.35, 备份工具采用 Percona XtraBackup 执行完整全量备份. 其中数据库安装系统为 Ubuntu 22.04, 一个主库一个从库, 另外恢复操作在单独的测试库进行; 全部节点如下:

  • 172.16.11.251(bintest1): 主库, 假设在此进行数据删除
  • 172.16.11.252(bintest2): 从库, 实时同步主库数据, 生产环境负责定时备份
  • 172.16.11.253(bintest3): 测试库, Binlog 数据恢复在此进行

3.2、测试数据

测试时采用独立的数据库(bintest), 测试删除数据的表为 userinfo, 建表语句以及测试数据如下所示:

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
# 创建数据库
CREATE DATABASE bintest CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 创建测试表
CREATE TABLE userinfo
(
    id          BIGINT AUTO_INCREMENT,
    name        VARCHAR(255)           NOT NULL,
    idno        VARCHAR(255)           NOT NULL,
    address     VARCHAR(255)           NOT NULL,
    create_time DATETIME DEFAULT NOW() NOT NULL,
    CONSTRAINT userinfo_pk
        PRIMARY KEY (id)
);

# 插入测试数据
INSERT INTO userinfo(name,idno,address) VALUES ('李瑞芳','636920199802136451','山西省临汾市顺靯路5032号疞嶥小区14单元2141室');
INSERT INTO userinfo(name,idno,address) VALUES ('方方竣','146324198806075248','甘肃省天水市婄煄路3816号没卾小区17单元229室');
INSERT INTO userinfo(name,idno,address) VALUES ('常明珠','441439198304217281','湖南省湘西土家族苗族自治州僄籓路3511号贪堕小区7单元2074室');
INSERT INTO userinfo(name,idno,address) VALUES ('陆奕然','629162201311010724','广西壮族自治区玉林市秈瑨路704号鮗捤小区11单元2409室');
INSERT INTO userinfo(name,idno,address) VALUES ('郝博雅','138088201811117959','河南省驻马店市紎筦路1380号攃摍小区13单元2363室');
INSERT INTO userinfo(name,idno,address) VALUES ('夏婉君','122839198907076789','山西省忻州市聜鳁路24号詿臜小区12单元1961室');
INSERT INTO userinfo(name,idno,address) VALUES ('邱新宜','129192199311154795','辽宁省葫芦岛市攣盗路7356号嚓檧小区14单元1326室');
INSERT INTO userinfo(name,idno,address) VALUES ('吴东','642398199908131195','陕西省渭南市污岕路1288号鯭蕄小区10单元1252室');
INSERT INTO userinfo(name,idno,address) VALUES ('尉迟妙己','719031198308109317','陕西省宝鸡市逳彄路1441号骘鼽小区11单元1128室');
INSERT INTO userinfo(name,idno,address) VALUES ('公孙昱晔','372078200010227375','福建省莆田市顪夹路4222号蝣宣小区2单元2046室');

3.3、备份方式

前面已经说过, Binlog 恢复先决条件是有一份完整的备份, 本文中备份和恢复会使用 xtrabackup, 假定定时任务会每小时执行一次完整备份; xtrabackup 工具请自行安装(需要与 MySQL 大版本匹配), 同时 xtrabackup 工具会使用 qpress 进行压缩和解压备份, 需要单独安装. 备份和恢复脚本如下:

backup.sh

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
#!/usr/bin/env bash

set -e

MYSQL_BACKUP_USER='root'
BACKUP_DIR="/data/mysql_backup"
BACKUP_NAME=full_$(date "+%Y%m%d%H%M%S")
MYSQL_CONFIG="/etc/mysql/mysql.conf.d/mysqld.cnf"

function cleanup(){
    echo "Clean Backup Dir ---> ${BACKUP_DIR}/${BACKUP_NAME}"
    rm -rf ${BACKUP_DIR}/${BACKUP_NAME}
}

trap cleanup EXIT

echo "Running xtrabackup command to create backup files..."
xtrabackup --backup \
    --dump-innodb-buffer-pool \
    --parallel=4 \
    --compress \
    --compress-threads=4 \
    --user=${MYSQL_BACKUP_USER} \
    --target-dir=${BACKUP_DIR}/${BACKUP_NAME}

echo "Copy MySQL config [${MYSQL_CONFIG}] to backup dir..."
cp ${MYSQL_CONFIG} ${BACKUP_DIR}/${BACKUP_NAME}/mysqld.cnf.xtra

echo "Generate backup information..."
echo "$(mysql -V)" > ${BACKUP_DIR}/${BACKUP_NAME}/mysql_backup.info
echo "" >> ${BACKUP_DIR}/${BACKUP_NAME}/mysql_backup.info
echo "$(xtrabackup -v)" >> ${BACKUP_DIR}/${BACKUP_NAME}/mysql_backup.info

echo "Pack backup files..."
tar -C ${BACKUP_DIR} -cvf ${BACKUP_DIR}/${BACKUP_NAME}.tar ${BACKUP_NAME}

echo "New Backup File ---> ${BACKUP_DIR}/${BACKUP_NAME}.tar"

restore.sh

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
#!/usr/bin/env bash

set -ex

BACKUP_FILE=${1:-""}

if [ ! ${BACKUP_FILE} ]; then
    echo "BACKUP_FILE is empty."
    exit 1
fi

tar -xvf ${BACKUP_FILE}

BACKUP_FILE_DIR=${BACKUP_FILE%\.tar}

systemctl stop mysql

# clean old config and data file
rm -rf /etc/mysql/mysql.conf.d/mysqld.cnf /var/lib/mysql/ /var/lib/mysql-keyring/ /var/lib/mysql-files/ /var/log/mysql/ /data/mysql/

# create dir
mkdir -p /data/mysql/{binlogs,mysql_data,mysql_tmp} /var/lib/mysql/ /var/lib/mysql-keyring/ /var/lib/mysql-files/ /var/log/mysql/

# modify permissions
chown -R mysql:mysql /data/mysql /var/lib/mysql/ /var/lib/mysql-keyring/ /var/lib/mysql-files/ /var/log/mysql/

# copy config file
cp ${BACKUP_FILE_DIR}/mysqld.cnf.xtra /etc/mysql/mysql.conf.d/mysqld.cnf

# xtrabackup process
xtrabackup --decompress --parallel=4 --target-dir=${BACKUP_FILE_DIR}
xtrabackup --prepare --target-dir=${BACKUP_FILE_DIR}
xtrabackup --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --copy-back --target-dir=${BACKUP_FILE_DIR}

# modify permissions
chown -R mysql:mysql /data/mysql

3.4、SLAVE 创建

由于真实环境会涉及到 SLAVE 备份, 故本文测试时会加入 SLAVE 节点; SLAVE 创建过程这里不再过多赘述, 具体可以参考 How to set up a replica for replication in 6 simple steps with Percona XtraBackup.

四、测试数据恢复

4.1、基础恢复

基础数据恢复流程中将会模拟最理想化的环境: 由于误操作在主库发生了删除, 同时主库临近时间点有完整备份, 且主库的 Binlog 未滚动(与备份时使用相同的 Binlog 文件). 整个场景的事件发生顺序如图所示:

  • 1、在 T1 时刻定时任务执行了 backup.sh 对主库进行了备份
  • 2、在 T2 时刻执行 UPDATE userinfo SET idno=1111111 WHERE id = 8 数据发生了更改
  • 3、在 T3 时刻误操作删除了数据(DELETE FROM userinfo WHERE id = 8), 我们需要找回误删除的数据

数据变化过程如下图所示:

T1 时刻

T2 时刻

T3 时刻

4.1.1、恢复备份

在这种情况下首先要做的就是立即使用主库在 T1 时刻的完整备份在测试库进行还原, 保证测试库与备份时刻的数据一致:

1
2
3
4
5
# 复制备份文件
root@bintest1 ~ # ❯❯❯ scp /data/mysql_backup/full_20240110182316.tar 172.16.11.253:~

# 恢复 MySQL 备份
root@bintest3 ~ # ❯❯❯ ./restore.sh full_20240110182316.tar

由于备份是在 T1 时刻创建的, 所以备份不包含 T2 时刻的修改; 也就是说备份数据还原后应该与初始化数据一致:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from userinfo;
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
| id | name         | idno               | address                                                                           | create_time         |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
|  1 | 李瑞芳       | 636920199802136451 | 山西省临汾市顺靯路5032号疞嶥小区14单元2141室                                      | 2024-01-05 13:53:07 |
|  2 | 方方竣       | 146324198806075248 | 甘肃省天水市婄煄路3816号没卾小区17单元229室                                       | 2024-01-05 13:53:07 |
|  3 | 常明珠       | 441439198304217281 | 湖南省湘西土家族苗族自治州僄籓路3511号贪堕小区7单元2074室                         | 2024-01-05 13:53:07 |
|  4 | 陆奕然       | 629162201311010724 | 广西壮族自治区玉林市秈瑨路704号鮗捤小区11单元2409室                               | 2024-01-05 13:53:07 |
|  5 | 郝博雅       | 138088201811117959 | 河南省驻马店市紎筦路1380号攃摍小区13单元2363室                                    | 2024-01-05 13:53:07 |
|  6 | 夏婉君       | 122839198907076789 | 山西省忻州市聜鳁路24号詿臜小区12单元1961室                                        | 2024-01-05 13:53:07 |
|  7 | 邱新宜       | 129192199311154795 | 辽宁省葫芦岛市攣盗路7356号嚓檧小区14单元1326室                                    | 2024-01-05 13:53:07 |
|  8 | 吴东         | 642398199908131195 | 陕西省渭南市污岕路1288号鯭蕄小区10单元1252室                                      | 2024-01-05 13:53:07 |
|  9 | 尉迟妙己     | 719031198308109317 | 陕西省宝鸡市逳彄路1441号骘鼽小区11单元1128室                                      | 2024-01-05 13:53:07 |
| 10 | 公孙昱晔     | 372078200010227375 | 福建省莆田市顪夹路4222号蝣宣小区2单元2046室                                       | 2024-01-05 13:53:07 |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
10 rows in set (0.00 sec)

4.1.2、找到起始 Pos 点

由于 Binlog 恢复逻辑就是重复执行, 所以对于起始 Pos 点来说就是备份时间的 Pos 点; 这里可以直接从 xtrabackup 备份文件中找到:

1
2
root@bintest3 ~ # ❯❯❯ cat full_20240110182316/xtrabackup_binlog_info
mysql-bin.000007        157

4.1.3、找到结束 Pos 点

对于结束 Pos 点来说, 它应当是执行数据误删除(DELETE FROM userinfo WHERE id = 8)之前的最后一个点, 这里就需要借助 mysqlbinlog 命令来进行查找和解析:

首先在主库查看当前使用的 Binlog

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     1048 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

接下来通过 mysqlbinlog 工具转换成 SQL 并进行搜索

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
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000007 -vv | grep -A 20 -B 20 'DELETE'
/*!80014 SET @@session.immediate_server_version=80035*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 739
#240110 18:38:14 server id 11251  end_log_pos 817 CRC32 0x747d3221      Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1704883094/*!*/;
BEGIN
/*!*/;
# at 817
#240110 18:38:14 server id 11251  end_log_pos 888 CRC32 0xd733973c      Table_map: `bintest`.`userinfo` mapped to number 89
# has_generated_invisible_primary_key=0
# at 888  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 删除前 Pos 点
#240110 18:38:14 server id 11251  end_log_pos 1017 CRC32 0x9ea5d5b0     Delete_rows: table id 89 flags: STMT_END_F

BINLOG '
lnOeZRPzKwAARwAAAHgDAAAAAFkAAAAAAAEAB2JpbnRlc3QACHVzZXJpbmZvAAUIDw8PEgf8A/wD
/AMAAAEBAAIB4DyXM9c=
lnOeZSDzKwAAgQAAAPkDAAAAAFkAAAAAAAEAAgAF/wAIAAAAAAAAAAYA5ZC05LicBwAxMTExMTEx
PQDpmZXopb/nnIHmuK3ljZfluILmsaHlspXot68xMjg45Y+36a+t6JWE5bCP5Yy6MTDljZXlhYMx
MjUy5a6kmbJK3Uew1aWe
'/*!*/;
### DELETE FROM `bintest`.`userinfo`
### WHERE
###   @1=8 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='吴东' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @3='1111111' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @4='陕西省渭南市污岕路1288号鯭蕄小区10单元1252室' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @5='2024-01-05 13:53:07' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
# at 1017
#240110 18:38:14 server id 11251  end_log_pos 1048 CRC32 0x60cc5630     Xid = 55
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过查看生成的 SQL 可以看到, 在删除之前的最后一个 Pos 点为 888.

4.1.4、生成重做 SQL

有了起始 Pos 点和结束 Pos 点, 我们就可以在主库上通过 Binlog 来生成 从备份到删除之前所有的执行 SQL:

1
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000007 -vv --start-position=157 --stop-position=888 > ~/redo.sql

4.1.5、恢复数据到删除前

有了重做 SQL 以后, 我们就可以直接在测试库上重新应用它, 让测试库 “回到” 被删除之前的状态:

1
2
3
4
5
# 复制重做 SQL 到测试库
root@bintest1 ~ # ❯❯❯ scp redo.sql 172.16.11.253:~

# 执行应用
root@bintest3 ~ # ❯❯❯ mysql < redo.sql

接下来可以在测试库查看恢复结果, 测试库应该回到了 T2 时刻即删除前的最后一刻:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
root@bintest3 ~ # ❯❯❯ mysql
mysql> select * from userinfo;
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
| id | name         | idno               | address                                                                           | create_time         |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
|  1 | 李瑞芳       | 636920199802136451 | 山西省临汾市顺靯路5032号疞嶥小区14单元2141室                                      | 2024-01-05 13:53:07 |
|  2 | 方方竣       | 146324198806075248 | 甘肃省天水市婄煄路3816号没卾小区17单元229室                                       | 2024-01-05 13:53:07 |
|  3 | 常明珠       | 441439198304217281 | 湖南省湘西土家族苗族自治州僄籓路3511号贪堕小区7单元2074室                         | 2024-01-05 13:53:07 |
|  4 | 陆奕然       | 629162201311010724 | 广西壮族自治区玉林市秈瑨路704号鮗捤小区11单元2409室                               | 2024-01-05 13:53:07 |
|  5 | 郝博雅       | 138088201811117959 | 河南省驻马店市紎筦路1380号攃摍小区13单元2363室                                    | 2024-01-05 13:53:07 |
|  6 | 夏婉君       | 122839198907076789 | 山西省忻州市聜鳁路24号詿臜小区12单元1961室                                        | 2024-01-05 13:53:07 |
|  7 | 邱新宜       | 129192199311154795 | 辽宁省葫芦岛市攣盗路7356号嚓檧小区14单元1326室                                    | 2024-01-05 13:53:07 |
|  8 | 吴东         | 1111111            | 陕西省渭南市污岕路1288号鯭蕄小区10单元1252室                                      | 2024-01-05 13:53:07 |
|  9 | 尉迟妙己     | 719031198308109317 | 陕西省宝鸡市逳彄路1441号骘鼽小区11单元1128室                                      | 2024-01-05 13:53:07 |
| 10 | 公孙昱晔     | 372078200010227375 | 福建省莆田市顪夹路4222号蝣宣小区2单元2046室                                       | 2024-01-05 13:53:07 |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
10 rows in set (0.00 sec)

最后提取数据重新还原到主库即可.

4.1.6、时间点恢复

起始除了使用 Pos 点恢复以外, 还可以根据时间来进行恢复, 前提你能精准的把控删除发生的时间以及备份的时间:

1
2
# 按时间点生成重做 SQL
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000007 -vv --start-datetime="2024-01-10 18:23:16" --stop-datetime="2024-01-10 18:38:00" > ~/redo.sql

总体来说按照时间点恢复可能会有一些误差, 比如同一时刻发生很多修改; 但是如果你能完全确定删除发生时间也不失为一个简单方法.

4.2、多 Binlog 恢复

多 Binlog 恢复流程中假设在非理想情况下, T1 时刻进行备份, T2 时刻更改数据后主库 Binlog 发生了滚动, 然后在 T3 时刻数据被删除; 整个流程中复杂的点为 在备份时间(T1)到数据删除时间(T3)之间可能发生多次 Binlog 滚动, 这时我们必须联合多个 Binlog 文件来生成重做 SQL.

4.2.1、找到起始 Pos 点

备份恢复步骤与 4.1 部分相同, 这里暂且省略; 查找起始 Pos 点采用同样的方法, 直接查看备份文件:

1
2
root@bintest3 ~ # ❯❯❯ cat full_20240111152100/xtrabackup_binlog_info
mysql-bin.000007        157

4.2.2、找到结束 Pos 点

查找结束 Pos 点大致方法相同, 但是需要注意的是 Binlog 已经发生过滚动, 所以我们在过滤时需要联合多个 Binlog 进行查找:

首先查看当前主库的 Binlog 与备份 Binlog, 通过对比确定中间还有哪些 Binlog

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |     1263 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

可以看到, 主库 Binlog 已经滚动到 mysql-bin.000010, 而备份时的 Binlog 为 mysql-bin.000007, 这意味着我们需要搜索 7、8、9、10 四个 Binlog 来确定删除到底发生在哪里, 从而得到结束 Pos 点:

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
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.0000{07,08,09,10} -vv | grep -A 20 -B 20 'DELETE FROM `bintest`.`userinfo`'
# at 954
#240111 15:25:54 server id 11251  end_log_pos 1032 CRC32 0x85eabcc8     Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1704957954/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;
# at 1032
#240111 15:25:54 server id 11251  end_log_pos 1103 CRC32 0x08a5a623     Table_map: `bintest`.`userinfo` mapped to number 89
# has_generated_invisible_primary_key=0
# at 1103  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 删除前 Pos 点
#240111 15:25:54 server id 11251  end_log_pos 1232 CRC32 0x95e1f664     Delete_rows: table id 89 flags: STMT_END_F

BINLOG '
ApifZRPzKwAARwAAAE8EAAAAAFkAAAAAAAEAB2JpbnRlc3QACHVzZXJpbmZvAAUIDw8PEgf8A/wD
/AMAAAEBAAIB4COmpQg=
ApifZSDzKwAAgQAAANAEAAAAAFkAAAAAAAEAAgAF/wAIAAAAAAAAAAYA5ZC05LicBwAxMTExMTEx
PQDpmZXopb/nnIHmuK3ljZfluILmsaHlspXot68xMjg45Y+36a+t6JWE5bCP5Yy6MTDljZXlhYMx
MjUy5a6kmbJK3Udk9uGV
'/*!*/;
### DELETE FROM `bintest`.`userinfo`
### WHERE
###   @1=8 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='吴东' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @3='1111111' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @4='陕西省渭南市污岕路1288号鯭蕄小区10单元1252室' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @5='2024-01-05 13:53:07' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
# at 1232
#240111 15:25:54 server id 11251  end_log_pos 1263 CRC32 0x25708781     Xid = 359
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从生成的 SQL 中可以看到, 结束的 Pos 点为 1103.

4.2.3、生成重做 SQL

在涉及到多个 Binlog 文件, 通过起始 Pos 点生成重做 SQL 时需要注意一点: Pos 点不是一直自增的, 它是在每个 Binlog 中自增, 所以如果直接联合所有 Binlog 使用起始 Pos 点(157~1103) 来生成重做 SQL 是有问题的:

1
2
# 错误示例
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.0000{07,08,09,10} -vv --start-position=157 --stop-position=1103 > ~/redo.sql

因为在 mysql-bin.000009 中 Pos 点可能增长到 1500, 然后在 mysql-bin.000010 中重新从 157 开始; 直接这样生成的重做 SQL 会丢失一部分数据, 正确做法是为每个中间 Binlog 生成完整重做 SQL, 然后再以起始 Pos 点为边界为两端 Binlog 生成重做 SQL:

1
2
3
4
5
6
7
8
9
# 起始 Binlog + 起始 Pos 点
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000007 -vv --start-position=157 > ~/redo.sql

# 中间 Binlog 全量
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000008 -vv >> ~/redo.sql
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000009 -vv >> ~/redo.sql

# 结束 Binlog + 结束 Pos 点
root@bintest1 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000010 -vv --stop-position=1103 >> ~/redo.sql

4.2.4、恢复数据到删除前

恢复数据这一步跟上面的操作相同, 直接导入备份数据库然后查看被删除数据, 最后导出恢复到主库即可:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@bintest1 ~ # ❯❯❯ scp redo.sql 172.16.11.253:~
root@bintest3 ~ # ❯❯❯ mysql < redo.sql

mysql> select * from userinfo;
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
| id | name         | idno               | address                                                                           | create_time         |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
|  1 | 李瑞芳       | 636920199802136451 | 山西省临汾市顺靯路5032号疞嶥小区14单元2141室                                      | 2024-01-05 13:53:07 |
|  2 | 方方竣       | 146324198806075248 | 甘肃省天水市婄煄路3816号没卾小区17单元229室                                       | 2024-01-05 13:53:07 |
|  3 | 常明珠       | 441439198304217281 | 湖南省湘西土家族苗族自治州僄籓路3511号贪堕小区7单元2074室                         | 2024-01-05 13:53:07 |
|  4 | 陆奕然       | 629162201311010724 | 广西壮族自治区玉林市秈瑨路704号鮗捤小区11单元2409室                               | 2024-01-05 13:53:07 |
|  5 | 郝博雅       | 138088201811117959 | 河南省驻马店市紎筦路1380号攃摍小区13单元2363室                                    | 2024-01-05 13:53:07 |
|  6 | 夏婉君       | 122839198907076789 | 山西省忻州市聜鳁路24号詿臜小区12单元1961室                                        | 2024-01-05 13:53:07 |
|  7 | 邱新宜       | 129192199311154795 | 辽宁省葫芦岛市攣盗路7356号嚓檧小区14单元1326室                                    | 2024-01-05 13:53:07 |
|  8 | 吴东         | 1111111            | 陕西省渭南市污岕路1288号鯭蕄小区10单元1252室                                      | 2024-01-05 13:53:07 |
|  9 | 尉迟妙己     | 719031198308109317 | 陕西省宝鸡市逳彄路1441号骘鼽小区11单元1128室                                      | 2024-01-05 13:53:07 |
| 10 | 公孙昱晔     | 372078200010227375 | 福建省莆田市顪夹路4222号蝣宣小区2单元2046室                                       | 2024-01-05 13:53:07 |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
10 rows in set (0.00 sec)

4.3、从库恢复

在大多数生产环境, 我们都不会选择直接从主库生成备份, 因为直接从主库生成备份时会产生较大磁盘 IO, 备份文件传输时又会造成网络占用; 大多数情况下我们都会在从库备份, 所以在本流程中假设:

  • 1、T1 时刻在从库产生了备份
  • 2、T2 时刻主库产生了数据修改, 且同步到了从库
  • 3、T3 时刻主库发生了误删除, 我们只有从库的备份

在这种复杂环境中, 需要明确一点: 备份只有从库的, 所以一切要以从库为基准, 包括 Binlog Pos 点查找还原等.

4.3.1、找到起始 Pos 点

在这种带有从库的环境中, 如果备份是从从库备份的, 那么 Binlog 恢复时仍然应该选择以从库为主进行操作; 对于起始 Pos 点, 仍然需要查看从库的备份文件:

1
2
3
# 这里略从库备份复制到测试库, 以及测试库恢复过程
root@bintest3 ~ # ❯❯❯ cat full_20240111162313/xtrabackup_binlog_info
mysql-bin.000009        157

4.3.2、找到结束 Pos 点

对于结束 Pos 点来说, 首先要确认数据删除时主库的删除动作成功同步到从库, 然后在从库上根据 Binlog 查询删除动作, 获取结束 Pos 点:

查询删除后从库的 Pos 点

1
2
3
4
5
6
7
8
# 从库 252 执行
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |     1043 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

对比从库备份可得知 Binlog 未发生滚动(如果发生滚动参考 4.2 部分), 接下来查询结束 Pos 点:

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
root@bintest2 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000009 -vv | grep -A 20 -B 20 'DELETE FROM `bintest`.`userinfo`'
/*!80014 SET @@session.immediate_server_version=80035*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 739
#240111 16:33:40 server id 11251  end_log_pos 812 CRC32 0x35941d40      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1704962020/*!*/;
BEGIN
/*!*/;
# at 812
#240111 16:33:40 server id 11251  end_log_pos 883 CRC32 0xcfe618de      Table_map: `bintest`.`userinfo` mapped to number 99
# has_generated_invisible_primary_key=0
# at 883  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 删除前 Pos 点
#240111 16:33:40 server id 11251  end_log_pos 1012 CRC32 0xd03d3ff0     Delete_rows: table id 99 flags: STMT_END_F

BINLOG '
5KefZRPzKwAARwAAAHMDAAAAAGMAAAAAAAEAB2JpbnRlc3QACHVzZXJpbmZvAAUIDw8PEgf8A/wD
/AMAAAEBAAIB4N4Y5s8=
5KefZSDzKwAAgQAAAPQDAAAAAGMAAAAAAAEAAgAF/wAIAAAAAAAAAAYA5ZC05LicBwAxMTExMTEx
PQDpmZXopb/nnIHmuK3ljZfluILmsaHlspXot68xMjg45Y+36a+t6JWE5bCP5Yy6MTDljZXlhYMx
MjUy5a6kmbJK3UfwPz3Q
'/*!*/;
### DELETE FROM `bintest`.`userinfo`
### WHERE
###   @1=8 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='吴东' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @3='1111111' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @4='陕西省渭南市污岕路1288号鯭蕄小区10单元1252室' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @5='2024-01-05 13:53:07' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
# at 1012
#240111 16:33:40 server id 11251  end_log_pos 1043 CRC32 0x084b4d2e     Xid = 88
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从查询结果中可以看到, 删除发生前的结束 Pos 点为 883.

4.3.3、生成重做 SQL

有了两个 Pos 点以后, 同样老办法在从库生成重做 SQL:

1
root@bintest2 ~ # ❯❯❯ mysqlbinlog --no-defaults /data/mysql/binlogs/mysql-bin.000009 -vv --start-position=157 --stop-position=883 > ~/redo.sql

4.3.4、恢复数据到删除前

同样有了重做 SQL, 只需要在测试库还原从库备份, 然后在从库备份上应用重做 SQL, 将数据还原到被删除前, 最后导出恢复到主库即可:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 复制重做 SQL 文件
root@bintest2 ~ # ❯❯❯ scp ~/redo.sql 172.16.11.253:~

# 应用重做 SQL
root@bintest3 ~ # ❯❯❯ mysql < redo.sql

# 确认数据状态
root@bintest3 ~ # ❯❯❯ mysql
mysql> select * from userinfo;
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
| id | name         | idno               | address                                                                           | create_time         |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
|  1 | 李瑞芳       | 636920199802136451 | 山西省临汾市顺靯路5032号疞嶥小区14单元2141室                                      | 2024-01-05 13:53:07 |
|  2 | 方方竣       | 146324198806075248 | 甘肃省天水市婄煄路3816号没卾小区17单元229室                                       | 2024-01-05 13:53:07 |
|  3 | 常明珠       | 441439198304217281 | 湖南省湘西土家族苗族自治州僄籓路3511号贪堕小区7单元2074室                         | 2024-01-05 13:53:07 |
|  4 | 陆奕然       | 629162201311010724 | 广西壮族自治区玉林市秈瑨路704号鮗捤小区11单元2409室                               | 2024-01-05 13:53:07 |
|  5 | 郝博雅       | 138088201811117959 | 河南省驻马店市紎筦路1380号攃摍小区13单元2363室                                    | 2024-01-05 13:53:07 |
|  6 | 夏婉君       | 122839198907076789 | 山西省忻州市聜鳁路24号詿臜小区12单元1961室                                        | 2024-01-05 13:53:07 |
|  7 | 邱新宜       | 129192199311154795 | 辽宁省葫芦岛市攣盗路7356号嚓檧小区14单元1326室                                    | 2024-01-05 13:53:07 |
|  8 | 吴东         | 1111111            | 陕西省渭南市污岕路1288号鯭蕄小区10单元1252室                                      | 2024-01-05 13:53:07 |
|  9 | 尉迟妙己     | 719031198308109317 | 陕西省宝鸡市逳彄路1441号骘鼽小区11单元1128室                                      | 2024-01-05 13:53:07 |
| 10 | 公孙昱晔     | 372078200010227375 | 福建省莆田市顪夹路4222号蝣宣小区2单元2046室                                       | 2024-01-05 13:53:07 |
+----+--------------+--------------------+-----------------------------------------------------------------------------------+---------------------+
10 rows in set (0.00 sec)

五、其他工具

5.1、bytebase

强烈推荐有能力的在内网部署 Bytebase 工具, 这个工具应该是腾讯出的, 基础特性开源同时也有商用版本; 简单的说可以把你的数据库修改变为标准的代码协作模式; 比如提交 PR、Review 合并等, 同时可以针对修改的 SQL 直接生成回滚 SQL, 出问题可以立即回滚:

5.2、canal2sql

这是一个 Java 编写的工具, 支持在线解析 Binlog, 同时直接生成回滚 SQL, 我这里没有实际尝试, 具体请参考项目 GitHub 页面.

六、总结

备份永远说数据恢复的首选! 不管是 Slave 还是 Binlog, 多留点备份.

Database > MySQL
#MySQL #Database #Binlog
MySQL Binlog 数据恢复
https://mritd.com/2024/01/11/mysql-binlog-restore/
作者
Kovacs
发布于
2024年1月11日
许可协议