延时从库

解决数据损坏

  • 物理损坏
    • 误操作rm
    • 磁盘阵列损坏
    • 机房爆炸
  • 逻辑损坏
    • 删库跑路

对于传统的主从复制,比较擅长处理物理损坏。

设计理念

对SQL线程进行设置延迟。

延迟多久合适

企业生产中一般设置延迟3-6个小时

如何设置

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
从库:
mysql> show slave status \G
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 300;
mysql> start slave;

mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL //最近一个事务的SQL剩余延迟
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

主库:
mysql> create database pincheng charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

从库:
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: 227 //倒计时
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

倒计时结束后
show databases;
| pincheng |

如何使用

模拟故障:

1
2
3
4
5
6
7
mysql -S /data/3307/mysql.sock -p
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;

发现问题,处理思路:

  1. 停止从库SQL线程,停止主库业务。
  2. 模拟SQL手工恢复relaylog到drop之前的位置点
  3. 截取relaylog日志,找到起点(relay-log.info)和终点(drop 操作之前)
  4. 恢复截取的日志,验证数据可用性。

开始处理:

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
1.停止从库SQL线程,停止主库业务。
mysql -S /data/3308/mysql.sock -p
mysql> stop slave sql_thread;
mysql> show slave status \G
Slave_SQL_Running: No //SQL线程停止

SQL_Delay: 300 //倒计时结束
SQL_Remaining_Delay: NULL

2.截取relaylog日志,找到起点(relay-log.info)和终点(drop 操作之前)
起点:
mysql> show slave status \G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 507 //已经执行的最后一个事件的Position号
或者查看relay-log.info文件
[root@db01 ~]# cat /data/3308/data/relay-log.info
7
./db01-relay-bin.000002
507 //起点

终点:
mysql> show relaylog events in 'db01-relay-bin.000002';
+-----------------------+------+----------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+------+----------------+-----------+-------------+------------------------------------------+
| db01-relay-bin.000002 | 4 | Format_desc | 8 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000002 | 123 | Previous_gtids | 8 | 154 | |
| db01-relay-bin.000002 | 154 | Rotate | 7 | 0 | mysql-bin.000001;pos=775 |
| db01-relay-bin.000002 | 201 | Format_desc | 7 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000002 | 320 | Anonymous_Gtid | 7 | 840 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 385 | Query | 7 | 962 | create database pincheng charset utf8mb4 |
| db01-relay-bin.000002 | 507 | Anonymous_Gtid | 7 | 1027 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 572 | Query | 7 | 1141 | create database delay charset utf8mb4 |
| db01-relay-bin.000002 | 686 | Anonymous_Gtid | 7 | 1206 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 751 | Query | 7 | 1305 | use `delay`; create table t1(id int) |
| db01-relay-bin.000002 | 850 | Anonymous_Gtid | 7 | 1370 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 915 | Query | 7 | 1443 | BEGIN |
| db01-relay-bin.000002 | 988 | Table_map | 7 | 1489 | table_id: 109 (delay.t1) |
| db01-relay-bin.000002 | 1034 | Write_rows | 7 | 1539 | table_id: 109 flags: STMT_END_F |
| db01-relay-bin.000002 | 1084 | Xid | 7 | 1570 | COMMIT /* xid=74 */ |
| db01-relay-bin.000002 | 1115 | Anonymous_Gtid | 7 | 1635 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 1180 | Query | 7 | 1730 | drop database delay |
+-----------------------+------+----------------+-----------+-------------+------------------------------------------+
注意:relaylog查看只看 Pos列,End_log_pos是从库对应主库的binlog的位置点,可忽略
找到drop的操作,对应的Pos列| db01-relay-bin.000002 | 1180 | drop database delay |

3.截取:
[root@db01 ~]# mysqlbinlog --start-position=507 --stop-position=1180 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
//通过截取relay-log日志,截取到drop操作之前

4.检查日志结尾是否包含drop操作:
[root@db01 ~]# tail /tmp/relay.sql
# at 1115
#200427 13:26:50 server id 7 end_log_pos 1635 CRC32 0x6a2be066 Anonymous_GTID last_committed=8 sequence_number=9 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;

5.在从库中恢复
[root@db01 ~]# mysql -S /data/3308/mysql.sock -p

mysql> set sql_log_bin=0; //临时关闭binlog日志记录

mysql> source /tmp/relay.sql //恢复数据

6.检查数据
mysql> use delay;
Database changed
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+