使用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/      

[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/      

## 擷取備份檔案中的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 status\G
 
## 待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
• 繼續同步複制