MySQL使用binlog做Point-in-time recovery

• 为方便演示，先创建两个测试数据库并建表插入一些测试数据

mysql> create database test_recovery_1;
Query OK, 1 row affected (0.01 sec)

mysql> create database test_recovery_2;
Query OK, 1 row affected (0.01 sec)

mysql> create table test_recovery_1.t_test(id int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test_recovery_1.t_test values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table test_recovery_2.t_test like test_recovery_1.t_test;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_recovery_2.t_test select * from test_recovery_1.t_test;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
复制

• 对test_recovery_1 和 test_recovery_2 库做一个逻辑备份

[root@node1 ~]# mysqldump -uroot -p --single-transaction --source-data=2 --set-gtid-purged=OFF  --databases test_recovery_1 test_recovery_2 > /tmp/dump.sql

#备份文件中记录了备份时的二进制文件和偏移量，用于point-in-time recovery
[root@node1 ~]# grep -inr 'LOG_FILE' /tmp/dump.sql 
22:-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000037', MASTER_LOG_POS=3477;

复制

• 分别在两个测试库中创建表并插入测试数据

[root@node1 /]# sysbench oltp_write_only  --table-size=100000 --tables=10 --mysql-db=test_recovery_1 --mysql-port=3306 --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=XXXXXXXXX --threads=20  --report-interval=1 --events=0 prepare[root@node1 /]# sysbench oltp_write_only  --table-size=100000 --tables=10 --mysql-db=test_recovery_2 --mysql-port=3306 --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=XXXXXXXXX --threads=20  --report-interval=1 --events=0 prepare
复制

• 校验导入的数据用于对比

[root@node1 sysbench-master]# for i in `mysql -uroot -pXXXXXXXXX -BNe "select  concat(table_schema,'.',table_name) from  information_schema.tables where table_schema like 'test_recovery%'"` ;do  mysql -uroot -pXXXXXXXXX -BNe "select '$i',count(*) from $i;checksum table $i"; done > /tmp/source_check.log[root@node1 sysbench-master]# cat /tmp/source_check.log test_recovery_1.sbtest1 100000test_recovery_1.sbtest1 2852089070test_recovery_1.sbtest10        100000test_recovery_1.sbtest10        3391387632test_recovery_1.sbtest2 100000test_recovery_1.sbtest2 2609808608test_recovery_1.sbtest3 100000test_recovery_1.sbtest3 29142631test_recovery_1.sbtest4 100000test_recovery_1.sbtest4 3152164255test_recovery_1.sbtest5 100000test_recovery_1.sbtest5 304839387test_recovery_1.sbtest6 100000test_recovery_1.sbtest6 2038890086test_recovery_1.sbtest7 100000test_recovery_1.sbtest7 285107768test_recovery_1.sbtest8 100000test_recovery_1.sbtest8 805309772test_recovery_1.sbtest9 100000test_recovery_1.sbtest9 3323619109test_recovery_1.t_test  5test_recovery_1.t_test  1245381782test_recovery_2.sbtest1 100000test_recovery_2.sbtest1 2852089070test_recovery_2.sbtest10        100000test_recovery_2.sbtest10        3391387632test_recovery_2.sbtest2 100000test_recovery_2.sbtest2 2609808608test_recovery_2.sbtest3 100000test_recovery_2.sbtest3 29142631test_recovery_2.sbtest4 100000test_recovery_2.sbtest4 3152164255test_recovery_2.sbtest5 100000test_recovery_2.sbtest5 304839387test_recovery_2.sbtest6 100000test_recovery_2.sbtest6 2038890086test_recovery_2.sbtest7 100000test_recovery_2.sbtest7 285107768test_recovery_2.sbtest8 100000test_recovery_2.sbtest8 805309772test_recovery_2.sbtest9 100000test_recovery_2.sbtest9 3323619109test_recovery_2.t_test  5test_recovery_2.t_test  1245381782
复制

• 模拟误操作场景，删除test_recovery_1 和 test_recovery_2 库

mysql> drop database test_recovery_1;
Query OK, 11 rows affected (0.44 sec)

mysql> drop database test_recovery_2;
Query OK, 11 rows affected (0.43 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| eops               |
| iat                |
| information_schema |
| mysql              |
| performance_schema |
| query_rewrite      |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)

复制

• 记录当前binlog文件，通过分析binlog文件定位误操作的偏移量。通过最近一次的备份文件活肤，恢复的数据只有备份前创建的t_test表，备份后创建的sbtest[1-10]表及其数据只能通过binlog做基于时间点的恢复

mysql> show master status;
+---------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                     |
+---------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| binlog.000040 | 66866856 |              |                  | 8c537ce5-4a53-11eb-907d-000c298c47fa:1-63,
8c537ce5-4a53-12eb-907d-000c298c47fa:1-936 |
+---------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@node1 sysbench-master]# mysql -uroot -p </tmp/dump.sql 

mysql> select  table_schema,table_name from  information_schema.tables where table_schema like 'test_recovery%';            
+-----------------+------------+
| TABLE_SCHEMA    | TABLE_NAME |
+-----------------+------------+
| test_recovery_1 | t_test     |
| test_recovery_2 | t_test     |
+-----------------+------------+
2 rows in set (0.01 sec)

复制

• 方法一、使用传统的mysqlbinlog方式恢复。耗时约20分钟

[root@node1 log]# mysqlbinlog --start-position=3477 --skip-gtids binlog.000037 binlog.000038 binlog.000039 | mysql -uroot -p[root@node1 log]# mysqlbinlog --stop-position=66866369 --skip-gtids binlog.000040 | mysql -uroot -p                                     检验恢复后的数据，确认一致性[root@node1 log]# for i in `mysql -uroot -pXXXXXXXXX -BNe "select  concat(table_schema,'.',table_name) from  information_schema.tables where table_schema like 'test_recovery%'"` ;do  mysql -uroot -pXXXXXXXXX -BNe "select '$i',count(*) from $i;checksum table $i"; done > /tmp/recovery_check.log[root@node1 log]# diff /tmp/recovery_check.log /tmp/source_check.log [root@node1 log]# [root@node1 log]# cat /tmp/recovery_check.log test_recovery_1.sbtest1 100000test_recovery_1.sbtest1 2852089070test_recovery_1.sbtest10        100000test_recovery_1.sbtest10        3391387632test_recovery_1.sbtest2 100000test_recovery_1.sbtest2 2609808608test_recovery_1.sbtest3 100000test_recovery_1.sbtest3 29142631test_recovery_1.sbtest4 100000test_recovery_1.sbtest4 3152164255test_recovery_1.sbtest5 100000test_recovery_1.sbtest5 304839387test_recovery_1.sbtest6 100000test_recovery_1.sbtest6 2038890086test_recovery_1.sbtest7 100000test_recovery_1.sbtest7 285107768test_recovery_1.sbtest8 100000test_recovery_1.sbtest8 805309772test_recovery_1.sbtest9 100000test_recovery_1.sbtest9 3323619109test_recovery_1.t_test  5test_recovery_1.t_test  1245381782test_recovery_2.sbtest1 100000test_recovery_2.sbtest1 2852089070test_recovery_2.sbtest10        100000test_recovery_2.sbtest10        3391387632test_recovery_2.sbtest2 100000test_recovery_2.sbtest2 2609808608test_recovery_2.sbtest3 100000test_recovery_2.sbtest3 29142631test_recovery_2.sbtest4 100000test_recovery_2.sbtest4 3152164255test_recovery_2.sbtest5 100000test_recovery_2.sbtest5 304839387test_recovery_2.sbtest6 100000test_recovery_2.sbtest6 2038890086test_recovery_2.sbtest7 100000test_recovery_2.sbtest7 285107768test_recovery_2.sbtest8 100000test_recovery_2.sbtest8 805309772test_recovery_2.sbtest9 100000test_recovery_2.sbtest9 3323619109test_recovery_2.t_test  5test_recovery_2.t_test  1245381782
复制

• 方法二、使用sql thread回放相关的binlog。耗时约10分钟

[root@node1 ~]# mysql -uroot -p </tmp/dump.sql


#备份binlog
for i in `ls binlog.00000[2-6]`;do cp $i bak$i;done

#生成relaylog
[root@node1 log]# rm -f relay.000*
[root@node1 log]# >relay.index 

[root@node1 log]# for i in `ls bakbinlog.00000[2-6]`;do cp $i ${i/bakbinlog/relay};echo "`pwd`/${i/bakbinlog/relay}" >>relay.index ;done

[root@node1 log]# chown mysql.mysql *




mysql> set global server_id=1864117;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CHANGE MASTER TO RELAY_LOG_FILE='/data/mysql/log/relay.000002',RELAY_LOG_POS=41764820, MASTER_HOST='nohost';
Query OK, 0 rows affected, 2 warnings (0.98 sec)

mysql> START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'relay.0000006', RELAY_LOG_POS = 2616821;
Query OK, 0 rows affected, 1 warning (0.07 sec)

#同步完成后重置主从配置

mysql> reset slave all;
Query OK, 0 rows affected, 2 warnings (0.04 sec)


[root@node1 log]# for i in `mysql -uroot -pXXXXXXXXX -BNe "select  concat(table_schema,'.',table_name) from  information_schema.tables where table_schema like 'test_recovery%'"` ;do  mysql -uroot -pXXXXXXXXX -BNe "select '$i',count(*) from $i;checksum table $i"; done > /tmp/recovery_check.log

[root@node1 log]# diff /tmp/source_check.log /tmp/recovery_check.log 


复制