怎样解决MySQL数据库主从复制延迟的问题

在主服务器上建立一个为从服务器进行复制使用的用户。该账户必须授予 REPLICATION SLAVE 权限，由于仅仅是进行复制使用所以不需要再授予任何其它权限。

mysql>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'192.168.0.2' IDENTIFIED BY 'slavepasswd'

mysql>FLUSH PRIVILEGES

3、编辑主服务器的配置文件：/etc/my.cnf的[ mysqld ] 部分：

server-id = 本机数据库 ID 标示，该部分还应有一个server-id=Master_id选项，其中master_id必须为1到232之间的一个正整数值

log-bin = 二进制日志的位置和名称

binlog-do-db = 需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可

binlog-ignore-db = 不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可

主从复制延迟的监测，我以前的做法是通过比较show slave statusG中的两个变量的差值（Read_Master_Log_Pos，Exec_Master_Log_Pos），将差值设置为一个自己认为合理的范围，Seconds_Behind_Master 没有适用过，今天做一次解析:

Seconds_Behind_Master 是通过比较 SQL THREAD 接受 events事件的时间戳（timestamp） 与IO THREAD 执行事件 events时间戳的差值--秒数来确定slave 落后于master多少。如果主从机器的时间不同，该时间的计算也是不会受影响的（如果时间发生异常，则这个秒数的就不怎么可靠啦）

如果slave SQL thread 或者 slave I/O thread 或者没有连接到master，那么该变量的值为NULL.

0：表示master slave 复制没有延迟（大部分情况下是这个样子）。

正值：表示slave落后于master的秒数。

在网络很快的情况下，I/O thread 能够很快的从master上获取binlog到slave的 relay-log。这种情况下， seconds_behind_master的值能真正代表slave落后于master的秒数。在网络很差的情况下，I/O thread 同步很慢，slave收到的二进制日志信息，SQL THREAD能够很快的执行。这个时候 seconds_behind_master 是0，这种情况下 slave落后于master很多。

为了排除网络的干扰，我们可以参考percona 的工具 pt-heartbeat.

该工具可以计算出MySQL复制或者是PostgreSQL,它可以更新master或者监控复制。它还可以从my.cnf 读取配置。它借助timestmp的比较实现的，首先需要保证主从服务器时间必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面的时间戳ts就是当前的时间戳 now()，该结构也会被复制到从库上。表建好以后，会在主库上以后台进程的模式去执行一行更新 *** 作的命令，定期去向表中的插入数据，这 个周期默认为1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期+0.5S（默认0.5S延迟检查）去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示 延时的秒数越多。

采集延时信息的命令：

show slave status

查看 seconds_behind_master的值；

1. 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差（或者备库都放在同一

个机器上，主要是竞争资源很激烈，特别是IO）；

2. 备库上的压力（备库上的查询可能耗费了大量的CPU资源，影响了同步的速度，造成了主

备延时。这种情况可以通过配置一主多从来分担备库的压力）；

3. 大事务（重点）

因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句

执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。

大事务场景：

1）不要一次性地用 delete 语句删除太多数据 。其实，这就是一个典型的 大事务场景 。

    删除数据的时候，要控制每个事务删除的数据量，分成多次删除。

2）大表 DDL

    建议使用 gh-ost 方案

4. 备库的并行复制能力

    MySQL官方在MySQL5.6以后支持sql_thread线程多线程进行复制。

5. 主库DML语句并发大，从库的qps高；

6. 主库和从库的参数配置不一样

7. 从库上在进行备份 *** 作

8. 备库空间不足的情况下

9. 表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_search_algorithms参数适当优化下)

检查锁情况：

show engine innodb status\G

由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。

1. 可靠性优先策略

2. 可用性优先策略

由参数 slave-parallel-type 来控制并行复制策略：

1. 置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；

2. 配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。不过，

    MySQL 5.7 这个策略，针对并行度做了优化。

MySQL 5.7 并行复制策略的思想是：

1. 同时处于 prepare 状态的事务，在备库执行时是可以并行的；

2. 处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

MySQL 5.7.22版本，MySQL新增了基于WRITESET 的并行复制

通过参数 binlog-transaction-dependency-tracking来控制是否开启：

1. COMMIT_ORDER：根据同时进入 prepare 和 commit 来判断是否可以并行的策略。

2. WRITESET

    表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。

    如果两个事务没有 *** 作相同的行，也就是说它们的 writeset 没有交集，就可以并行。

3. WRITESET_SESSION

    是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，

    在备库执行的时候，要保证相同的先后顺序。

这个 hash 值是通过“库名 + 表名 + 索引名 + 值”计算出来的。如果一个表上除了有主键索引

外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个

 hash 值。

优势：

   1.  writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解

        析 binlog 内容（event 里的行数据），节省了很多计算量；

   2. 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；

   3. 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。

---