十八、Mysql之延时从库
MySQL从5.6版本开始就支持主从延迟复制,这个功能主要解决的问题是,当主库有逻辑的数据删除或者错误更新时,所有的从库都会进行错误的更新,从而导致数据库的所有数据都异常,即使有定时的备份数据可以用于数据恢复,特别是数据库的数据量很大时,恢复时间也会很长,在恢复期间,数据库数据被删除或者出现错误数据都会影响正常的访问体验。 而延迟复制就可以很好地解决这个问题。例如,可以设定某一个从库和主库的更新延迟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. 从库身份解除,替代主库工作
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 主从部署省略
[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 ......
在主库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.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;
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
mysqlbinlog --start-position=320 --stop-position=980 /data/3308/data/vm01-relay-bin.000002>/tmp/relay.sql
[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) #从库数据恢复成功
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p Enter password: [relay]>stop slave; [relay]>reset slave all;
1、主从库配置一样,直接把从库更改为主库,主库更改为从库并重做主从 2、主从库配置不一样,从库导出数据给主库恢复,重做主从
I have a dream so I study hard!!!