mysql主从复制延迟问题

标签：主从复制 slave log SCAN flush mysql 主键延迟

背景

线上mysql主从复制一直处于延迟状态，查看主从状态显示如下：

...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 81609
...

可以看到日志IO_Running和SQL_Runng都处理正常状态，但是延迟时间巨长达到8万多秒，肯定不正常了。

尝试解决办法

对比时间

mysql> select now();

发现时间也差不多

检查/etc/my.cnf

发现两个配置文件内容差不多

从关闭定时任务，减少io

一开始我以为是从库上面带了定时备份任务，定时备份的时候把磁盘io跑高，影响主从复制了，把定时备份给移到每天只备份一次，结果还是一样。

使用xtrabackup重新同步

使用xtrabackup重新同步了一次，没过多久延迟时间还是往上走

开启并行复制

网友说有可能是从的性能比较差，主写入的数据比较多，从为单线程复制，跟不上主的写入速度，于是配置从为多线程复制

stop slave sql_thread;

set global slave_parallel_type='logical_clock';

set global slave_parallel_workers=4;

start slave sql_thread;

配置完发现也没什么效果

修改参数slave_rows_search_algorithms

mysql> show global variables like 'slave_rows_search_algorithms';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| slave_rows_search_algorithms | INDEX_SCAN,HASH_SCAN |
+------------------------------+----------------------+
1 row in set (0.01 sec)

说明： 针对无主键表的复制MySQL5.6以后有个参数来调整从库定位数据的方法：slave_rows_search_algorithms 这个参数是从三个可能选项中选出至少两项 1.TABLE_SCAN(表扫描)，2.INDEX_SCAN(索引扫描),3.HASH_SCAN(hash定位) 默认是：INDEX_SCAN,TABLE_SCAN 一般建议在存在无主键表的复制下，设置为：INDEX_SCAN,HASH_SCAN 通常可以加快从库的同步速度以上查询也是默认的配置不需要修改

修改参数innodb_flush_log_at_trx_commit和sync_binlog

说明： innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.
innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。 sync_binlog sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。当sync_binlog =N (N>0) ，MySQL 在每写 N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

mysql> set global sync_binlog=20;

mysql> set global innodb_flush_log_at_trx_commit=2;

按以上参数设置后仍然没有什么效果

设置跳过错误的事务

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

设置跳过复制事务，启动时会报错。报这个binlog日志文件里面的位置和启动的位置不对。

最终办法

以上设置全部走一遍后，都没有效果，show processlit发现始有一个表终停留在更新的状态，怀疑这一是否是不是没有主键，导致从库每次更新的时候都要全库扫描，从而影响主从延迟查看这个是否有主键

SELECT cu.Column_Name
FROM  INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` cu 
WHERE CONSTRAINT_NAME = 'PRIMARY' AND cu.Table_Name = 'test_table' AND CONSTRAINT_SCHEMA='testdb';

发现确实没有主键，叫开发把这个表的主键加上去，我在从库也把这个主键加上去，加上后再show slave status \G;发现Seconds_Behind_Master这个延迟的值越来越少了。

后续事情

由于我是直接在从加上加主键，主加了主键要等8万多秒后才能同步，所以我直接在从加了主键，从加完主键延迟越来越少，但在Seconds_Behind_Master还剩5000多秒的时候报错主键重复

 Last_Error: Error 'Multiple primary key defined' on query. Default database: 'testdb'. Query: 'ALTER TABLE `testdb`.`test_table` 
MODIFY COLUMN `SID_` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `TS_`,
ADD PRIMARY KEY (`SID_`)'

解决办法：先在从里面把原先加的主键给删除了再重启同步进程

mysql> Alter table testdb.test_table drop primary key;
mysql> stop slave;
mysql> start slave

过了一会后Seconds_Behind_Master的值终于变为0了.

参考链接：https://www.modb.pro/db/546108 https://www.modb.pro/db/80340

标签：主从复制,slave,log,SCAN,flush,mysql,主键,延迟
From： https://blog.51cto.com/riverxyz/6012037

背景