一、延时从库的介绍
而延迟复制就可以很好地解决这个问题。例如,可以设定某一个从库和主库的更新延迟1个小时,这样当主库数据出现问题以后,1个小时以内即可发现,可以对这个从库进行无害恢复处理,使之依然是正确的完整的数据,这样就省去了数据恢复占用的时间,用户体验也会有所提高。
为什么要有延时从?
数据库故障?
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏
二、配置延时从库
SQL线程延时:数据已经写入从库的relaylog中了,从库的SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
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
三、延时从库应用
延时从库主要用于主库误删除数据,从库可以在一段时间内恢复主库删除的数据。
四、延时从库的实战案例
1、延时从库恢复的思路
1主1从,从库延时5分钟,主库误删除1个库
1. 5分钟之内 侦测到误删除操作
2. 停从库SQL线程
3. 截取relaylog
起点 :停止SQL线程时,relay最后应用位置
查看延时从库的relay-log.info获取
终点:误删除之前的position(GTID)
在从库中show relaylog events in 'vm01-relay-bin.000002'来获取
4. 恢复截取的日志到从库
5. 从库身份解除,替代主库工作
2、实验环境
OS: CentOS Linux release 7.6.1810 (Core)
mysql:mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
mysql master: 3307
mysql slave:3308
主从部署省略
3、配置延时从库3308
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p
Enter password:
[(none)]>stop slave;
[(none)]>change master to master_delay=300;
[(none)]>start slave;
[(none)]>show slave status\G
......
SQL_Delay: 300
SQL_Remaining_Delay: NULL
......
4、故障模拟及恢复
在主库3307上模拟故障
[root@vm01 ~]# mysql -S /data/3307/mysql.sock -p
Enter password:
[(none)]>create database relay charset utf8;
[(none)]>use relay;
[relay]>create table t1 (id int);
[relay]>insert into t1 values(1);
[relay]>commit;
[relay]>select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
[relay]>drop database relay;
[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1806 | | | |
+------------------+----------+--------------+------------------+-------------------+
5、在从库上恢复
5.1停止从库SQL线程
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p
Enter password:
[(none)]>show slave status\G
。。。。。。
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1806
SQL_Delay: 300
SQL_Remaining_Delay: 123
。。。。。。
#可以发现主从库的binlog已经同步,但是从库的sql还没有执行
#停止从库的sql线程
[(none)]>stop slave sql_thread;
5.2. 找3308从库relaylog的截取起点和终点
relaylog的起点
方法一:
[root@vm01 ~]# cat /data/3308/data/relay-log.info
./vm01-relay-bin.000002
320
方法二:
[(none)]>show slave status\G
*************************** 1. row ***************************
。。。。。。
Relay_Log_File: vm01-relay-bin.000002
Relay_Log_Pos: 320
。。。。。。
relaylog的终点
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p
Enter password:
[(none)]>show relaylog events in 'vm01-relay-bin.000002';
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| vm01-relay-bin.000002 | 4 | Format_desc | 3308 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| vm01-relay-bin.000002 | 123 | Previous_gtids | 3308 | 154 | |
| vm01-relay-bin.000002 | 154 | Rotate | 3307 | 0 | mysql-bin.000001;pos=1051 |
| vm01-relay-bin.000002 | 201 | Format_desc | 3307 | 0 | Server ver: 5.7.20-log, Binlog ver: 4 |
| vm01-relay-bin.000002 | 320 | Anonymous_Gtid | 3307 | 1116 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| vm01-relay-bin.000002 | 385 | Query | 3307 | 1226 | create database relay charset utf8 |
| vm01-relay-bin.000002 | 495 | Anonymous_Gtid | 3307 | 1291 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| vm01-relay-bin.000002 | 560 | Query | 3307 | 1391 | use `relay`; create table t1 (id int) |
| vm01-relay-bin.000002 | 660 | Anonymous_Gtid | 3307 | 1456 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| vm01-relay-bin.000002 | 725 | Query | 3307 | 1529 | BEGIN |
| vm01-relay-bin.000002 | 798 | Table_map | 3307 | 1575 | table_id: 281 (relay.t1) |
| vm01-relay-bin.000002 | 844 | Write_rows | 3307 | 1615 | table_id: 281 flags: STMT_END_F |
| vm01-relay-bin.000002 | 884 | Xid | 3307 | 1646 | COMMIT /* xid=927 */ |
| vm01-relay-bin.000002 | 915 | Anonymous_Gtid | 3307 | 1711 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| vm01-relay-bin.000002 | 980 | Query | 3307 | 1806 | drop database relay |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
#pos为relaylog的起始位子
#End_log_pos为binlog的结束位子
#只需要看pos值即可
#看到relay的终点为pos=980
截取relaylog
mysqlbinlog --start-position=320 --stop-position=980 /data/3308/data/vm01-relay-bin.000002>/tmp/relay.sql
5.3恢复从库的relay
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p
Enter password:
[relay]>source /tmp/relay.sql;
[relay]>select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
#从库数据恢复成功
5.4从库身份解除
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p
Enter password:
[relay]>stop slave;
[relay]>reset slave all;
5.5恢复业务
1、主从库配置一样,直接把从库更改为主库,主库更改为从库并重做主从
2、主从库配置不一样,从库导出数据给主库恢复,重做主从
I have a dream so I study hard!!!