使用MySQL SQL线程回放Binlog实现恢复

目录

• 1. 需求部分
  • 1.1 基于MySQL复制同步特性，尝试使用Replication的SQL线程来回放binlog，可基于以下逻辑模拟场景
  • 1.2 基于题目1.1，考虑是否可以做到以下场景的恢复
• 2.操作部分
  • 2.1 环境准备及故障模拟
  • 2.2 数据恢复
  • 2.3 只恢复单个库的数据
  • 2.4 只恢复单个表的数据
  • 2.5 恢复到指定的GTID或position点
  • 2.6 提升恢复效率的参数优化
  • 2.7 使用复制线程与使用mysqlbinlog恢复的效率对比
  • 2.8 总结
  • 2.9 恢复优化

1. 需求部分

1.1 基于MySQL复制同步特性，尝试使用Replication的SQL线程来回放binlog，可基于以下逻辑模拟场景

1. 做全量xtrabackup备份模拟日常备份
2. 执行sysbench压测4张表，20个线程，压测10分钟，模拟大量binlog
3. 删除实例模拟数据库被误删除或硬件故障(binlog需要保留)
4. 使用xtrabackup恢复全量备份
5. 使用MySQL Replication SQL线程回放binlog(提示:恢复前需要将relay_log_recocery参数设置为0)

1.2 基于题目1.1，考虑是否可以做到以下场景的恢复

1. 只恢复单个库的数据
2. 只恢复单个表的数据
3. 将数据恢复到指定的GTID或者position点(如恢复到误操作drop之前的GTID)
4. 是否可以通过参数调整提升回放效率，列出提升效率的参数并实际测试用SQL线程回放binlog和直接使用mysqlbinlog回放binlog的性能对比

2.操作部分

2.1 环境准备及故障模拟

1. 使用mysqlbinlog将binlog文件实时转储到备份目录(当前环境备份目录配置在/data/mysql/backup)

[root@10-186-61-162 ~]# mkdir -p /data/mysql/backup/binlog
[root@10-186-61-162 ~]# mysqlbinlog --raw --read-from-remote-server -h10.186.61.162 -urepl -pxxx -P3306 --result-file=/data/mysql/backup/binlog/ mysql-bin.000001 --stop-never --stop-never-slave-server-id=100 &

生成sysbench测试数据,创建4张表，每张各100W数据库并压测一分钟模拟数据变化

## 插入基础数据
[root@10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=xxx --mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=1 --time=600 --threads=20  prepare
 
## 执行sysbench压测模拟数据变化，压测1分钟
[root@10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=xxx --mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=1 --time=60 --threads=20  run

执行物理备份模拟每天的全量备份

[root@10-186-61-162 ~]# innobackupex --host=10.186.61.162 --port=3306 --user=backup --password=xxx  /data/mysql/backup/

继续执行压测，模拟备份后的数据变化，压测10分钟，模拟大量binlog

[root@10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=xxx --mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=1 --time=600 --threads=20  run

停止并删除数据库数据库模拟误操作或硬件故障

[root@10-186-61-162 ~]# systemctl stop mysql_3306
[root@10-186-61-162 ~]# rm -rf /data/mysql/data/*

2.2 数据恢复

还原xtrabackup全量备份

[root@10-186-61-162 ~]# cd /data/mysql/backup
[root@10-186-61-162 backup]# innobackupex --apply-log 2020-03-02_17-39-07/
[root@10-186-61-162 backup]# innobackupex --defaults-file=/etc/my.cnf --copy-back 2020-03-02_17-39-07/

将binlog转换为relay log并拷贝到数据目录以便SQL线程回放数据

[root@10-186-61-162 backup]# cd /data/mysql/backup/binlog/
 
## 将binlog重命名为relay log
[root@10-186-61-162 binlog]# rename mysql-bin mysql-relay mysql-bin*
 
## 创建relay log index文件
[root@10-186-61-162 binlog]# ls ./mysql-relay.0000* >mysql-relay.index
 
## 拷贝relay log到数据目录
[root@10-186-61-162 binlog]# cp mysql-relay.* /data/mysql/data/
 
## 修改数据目录权限
[root@10-186-61-162 binlog]# chown -R mysql:mysql /data/mysql/data/

修改relay_log_recovery参数，设置为0并启动数据库,relay_log_recovery为1时，relay log会在复制线程启动时被清除重新拉

## 获取备份文件中的binlog位点信息及GTID点对应的relay log文件
[root@10-186-61-162 ~]# cd /data/mysql/backup/2020-03-02_17-39-07/
[root@10-186-61-162 2020-03-02_17-39-07]# cat xtrabackup_binlog_info
mysql-bin.000007	80456866	f41abe78-5c62-11ea-abf1-02000aba3da2:1-52013
 
## 启动数据库
[root@10-186-61-162 2020-03-02_17-39-07]# systemctl start mysql_3306
 
## change master指定一个空的主库，创建SQL线程
root@localhost[(none)]> reset master;
 
## 指定备份文件中对应的binlog位点
root@localhost[(none)]> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='mysql-relay.000007',RELAY_LOG_POS=80456866;
 
## 查看指定的位点是否生效
root@localhost[(none)]> select * from mysql.slave_relay_log_info;
+-----------------+----------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
| Number_of_lines | Relay_log_name       | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name |
+-----------------+----------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
|               7 | ./mysql-relay.000007 |      80456866 |                 |              0 |         0 |                 0 |  1 |              |
+-----------------+----------------------+---------------+-----------------+----------------+-----------+-------------------+----+--------------+
 
## 只需要开启SQL线程对指定的relay log开始回放即可
root@localhost[(none)]> START SLAVE SQL_THREAD;
 
## 持续执行可看到binlog数据开始回放
root@localhost[(none)]> show slave statusG
 
## 待binlog恢复完成且数据校验无问题后后可将复制关系重置
root@localhost[(none)]> stop slave;
root@localhost[(none)]> reset slave;
root@localhost[(none)]> reset slave all;

2.3 只恢复单个库的数据

• 由于备份是全量备份，无法单库还原(如果已知表结构可使用可传输表空间方式单库还原)，可先将全量恢复后对只对单库做binlog还原
  • 只需在2.2数据恢复的基础上增加一条以下命令即可实现对sbtest单库的binlog还原
  • CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('sbtest.%');

2.4 只恢复单个表的数据

• 该步骤与单库还原类似，只需将复制过滤属性指定为具体的表，如
  • CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('sbtest.sbtest1','sbtest.sbtest2');
  • 可在开启binlog还原前后对sbtest1-4表做checksum，验证是否只有sbtest1和sbtest2做了binlog还原
  • checksum table sbtest1,sbtest2,sbtest3,sbtest4;

2.5 恢复到指定的GTID或position点

• 该步骤只需在2.2数据恢复的基础上，将START SLAVE SQL_THREAD改为START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS，如只恢复到GTID=499999
  • START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 'f41abe78-5c62-11ea-abf1-02000aba3da2:500000;

2.6 提升恢复效率的参数优化

• set global sync_binlog=0;
• set global innodb_flush_log_at_trx_commit=0;
• set global slave_parallel_type=LOGICAL_CLOCK;
• set global slave_parallel_workers=8;
• 关闭binlog

2.7 使用复制线程与使用mysqlbinlog恢复的效率对比

• mysqlbinlog
  • 恢复耗时 722s
• SQL线程
  • 恢复耗时 151s

2.8 总结

• 使用SQL线程恢复binlog数据相对比mysqlbinlog有以下优势
• 只需配置好复制让数据库自动恢复数据即可
• 可以使用MySQL并行复制特性，提升恢复效率
• 可以精细化的控制恢复的库表
• 可以指定恢复到具体的GTID点以及跳过误操作的GTID

2.9 恢复优化

• 假设一个每天凌晨备份，某天早上8点数据库被误操作drop了业务库，请说明恢复的流程
  • 保持主库当前状态
  • 使用主库的备份恢复一个实例
  • 使用change master to后在启动开启START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS方式让复制同步数据到drop操作之前
  • 跳过drop操作的gtid
  • 继续同步复制