mysql主从~延时复制

主从延时原因

1）主库方面原因：binlog写入不及时，sync_binlog=1
2）默认情况下dump_t是串行传输binlog,在并发事务量大时或者大事物情况下，由于dump_thread是串行工作的，导致日志传输较慢。
3）如果从库是单sql线程导致，不管日志有多少，只能一次执行一个事务

解决方案gtid使用group commit方式，可以支持dump_thread并行

4）慢语句
5）从库的个数，每个从库都要对应一个dump_thread
6）网络延时

从库方面原因

1）5.6有了gtid，可以实现多sql_thread，但是只能基于不同的库的事务进行并发回放（database）
2）5.7有了增强gtid，增加了seq_no,增加了新型的并发sql线程模式（logical_clock），Mts技术
3）主从硬件差异太大
4）从库和主库的索引不一致
5) 版本差异
6)SQL线程导致的主从延时
6.1：从库默认只有一个sql线程，只能串行回放事务
6.2：如果主库发生了大事务，会阻塞后续所有的事务的运行

2.3.4主从延时监控

从库抓取的binlog：

Master_Log_File: logbin.000005
Read_Master_Log_Pos: 154
从库回放的realylog
Relay_Log_File: mysql_s1-relay-bin.000004
Relay_Log_Pos: 361
Exec_Master_Log_Pos: 154
Relay_Log_Space: 778
查看从库和主库的日志对应关系
mysql> show relaylog events in ‘mysql_s1-relay-bin.000004’;

group_commit
mysql> show variables like “%group_commit%”;

binlog_group_commit_sync_delay: 等待多少微妙后才进行组提交
binlog_group_commit_sync_no_delay_count: 如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置，直接开始刷盘

延时复制

在传统复制的基础上
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_delay=60;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;

延时从库应用场景

1）延时从库能处理逻辑故障

##延时从库的恢复思路

1）监控到数据库的逻辑故障
2）停止从库sql线程，记录已经回放的位置点（截取日志七点）
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

截取reaylog,起点

Show slave status\G;
Relay_Log_File, Relay_Log_Pos
终点：错误之前的位置点
Show relaylog events in ‘’

进行截取

4）模拟sql线程回访日志
从库source
恢复业务：
一． 就一个从库的话，从库替代主库
二． 导出故障库，还原到主库

演练

从库：
mysql> change master to master_delay=3600;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
主库：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create database delay;
Query OK, 1 row affected (0.00 sec)

mysql> use delay;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> drop database delay;
Query OK, 1 row affected (0.05 sec)
从库：
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

找到relay的截取终点
mysql> show relaylog events in ‘mysql_s1-relay-bin.000002’;

[root@mysql_s1 data]# mysqlbinlog --start-position=317 --stop-position=968 mysql_s1-relay-bin.000002 > /tmp/relay.sql
从库：
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)

mysql> source /tmp/relay.sql
mysql> set sql_log_bin=1;

数据回来了