MySQL使用binlog做Point-in-time recovery

发表于： 2023-08-14 09:54:54
分类：网络资源
浏览：浏览(42)

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

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 
为方便演示，先创建两个测试数据库并建表插入一些测试数据
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

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论