MySQL 主从不一致情形与解决方法
一、MySQL 主从不同步情况
1.1 网络的延迟
由于 mysql 主从复制是基于 binlog 的一种异步复制
通过网络传送 binlog 文件，理所当然网络延迟是主从不同步的绝大多数的原因，特别是跨机房的数据同步出现这种几率非常的大，所以做读写分离，注意从业务层进行前期设计。
1.2 主从两台机器的负载不一致
由于 mysql 主从复制是主数据库上面启动 1 个 io 线程，而从上面启动 1 个 sql 线程和 1 个 io 线程，当中任何一台机器的负载很高，忙不过来，导致其中的任何一个线程出现资源不足，都将出现主从不一致的情况。
1.3 max_allowed_packet 设置不一致
主数据库上面设置的 max_allowed_packet 比从数据库大，当一个大的 sql 语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。
1.4 自增键不一致
key 自增键开始的键值跟自增步长设置不一致引起的主从不一致。
1.5 同步参数设置问题
mysql 异常宕机情况下，如果未设置 sync_binlog=1 或者 innodb_flush_log_at_trx_commit=1 很有可能出现 binlog 或者 relaylog 文件出现损坏，导致主从不一致。
1.6 自身 bug
mysql 本身的 bug 引起的主从不同步
1.7 版本不一致
特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能。
1.8 主从不一致优化配置
基于以上情况，先保证 max_allowed_packet，自增键开始点和增长点设置一致
再者牺牲部分性能在主上面开启 sync_binlog，对于采用 innodb 的库，推荐配置下面的内容
innodb_flush_logs_at_trx_commit = 1
innodb-support_xa = 1 # Mysql 5.0 以上
innodb_safe_binlog # Mysql 4.0
同时在从上面推荐加入下面两个参数
skip_slave_start
read_only
二、解决主从不同步的方法
2.1 主从不同步场景描述
今天发现 Mysql 的主从数据库没有同步
先上 Master 库：
mysql>show processlist;
查看下进程是否 Sleep 太多。发现很正常。
show master status;
查看主库状态也正常。
mysql> show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBmysqld-bin.0000013260mysql,test,information_schema
1 row in set (0.00 sec)
复制代码再到 Slave 上查看
mysql> show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: No
复制代码由此可见是 Slave 不同步
2.2 解决方法一：忽略错误后，继续同步
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
解决：
stop slave;
复制代码
表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
复制代码
之后再用 mysql> show slave statusG 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
复制代码 ok，现在主从同步状态正常了。。。
2.3 方式二：重新做主从，完全同步
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
解决步骤如下：
1.先进入主库，进行锁表，防止数据写入
使用命令：
mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大小写
2.进行数据备份
把数据备份到 mysql.bak.sql 文件
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用 shell 脚本或者 python 脚本，都比较方便，确保数据万无一失
3.查看 master 状态
mysql> show master status;
+——————-+———-+————–+——————————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————————-+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+——————-+———-+————–+——————————-+
1 row in set (0.00 sec)
复制代码
4.把 mysql 备份文件传到从库机器，进行数据恢复
使用 scp 命令
[root@server01 mysql]# scp mysql.bak.sql root@192.168.1.206:/tmp/
5.停止从库的状态
mysql> stop slave;
6.然后到从库执行 mysql 命令，导入数据备份
mysql> source /tmp/mysql.bak.sql
7.设置从库同步，注意该处的同步点，就是主库 show master status 信息里的| File| Position 两项
change master to master_host = ‘192.168.1.206’, master_user = ‘rsync’, master_port=3306, master_password=”, master_log_file = ‘mysqld-bin.000001’, master_log_pos=3260;
8.重新开启从同步
mysql> start slave;
9.查看同步状态
mysql> show slave statusG 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
好了，同步完成啦
三、如何监控 mysql 主从之间的延迟
3.1 前言：
日常工作中，对于 MYSQL 主从复制的检查有两方面
保证复制的整体结构是否完整；
需要检查数据是否一致；
对于前者我们可以通过监控复制线程是否工作正常以及主从延时是否在容忍范围内，对于后者则可以通过分别校验主从表中数据的 md5 码是否一致，来保证数据一致，可以使用 Maatkit 工具包中的 mk-table-checksum 工具去检查。
本文档介绍下关于如何检查主从延迟的问题。
主从延迟判断的方法，通常有两种方法：Seconds_Behind_Master 和 mk-heartbeat
3.2 方法 1.
通过监控 show slave statusG 命令输出的 Seconds_Behind_Master 参数的值来判断，是否有发生主从延时。
mysql> show slave statusG;
1. row **
Replicate_Wild_Ignore_Table:
Master_SSL_Verify_Server_Cert: No
Replicate_Ignore_Server_Ids:
1 row in set (0.00 sec)
复制代码以上是 show slave statusG 的输出结果，这些结构给我们的监控提供了很多有意义的参数。
Slave_IO_Running
该参数可作为 io_thread 的监控项，Yes 表示 io_thread 的和主库连接正常并能实施复制工作，No 则说明与主库通讯异常，多数情况是由主从间网络引起的问题；
Slave_SQL_Running
该参数代表 sql_thread 是否正常，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。
Seconds_Behind_Master
是通过比较 sql_thread 执行的 event 的 timestamp 和 io_thread 复制好的 event 的 timestamp(简写为 ts)进行比较，而得到的这么一个差值；NULL—表示 io_thread 或是 sql_thread 有任何一个发生故障，也就是该线程的 Running 状态是 No，而非 Yes。0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为 lag 不存在。
正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。负值 — 几乎很少见，我只是听一些资深的 DBA 说见过，其实，这是一个 BUG 值，该参数是不支持负值的，也就是不应该出现。
备注 Seconds_Behind_Master 的计算方式可能带来的问题
我们都知道的 relay-log 和主库的 bin-log 里面的内容完全一样，在记录 sql 语句的同时会被记录上当时的 ts，所以比较参考的值来自于 binlog，其实主从没有必要与 NTP 进行同步，也就是说无需保证主从时钟的一致。你也会发现，其实比较真正是发生在 io_thread 与 sql_thread 之间，而 io_thread 才真正与主库有关联，于是，问题就出来了，
当主库 I/O 负载很大或是网络阻塞
io_thread 不能及时复制 binlog（没有中断，也在复制），而 sql_thread 一直都能跟上 io_thread 的脚本，这时 Seconds_Behind_Master 的值是 0，
也就是我们认为的无延时，但是，实际上不是，你懂得。
这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，
如果当 io_thread 与 master 网络很好的情况下，那么该值也是很有价值的。’‘之前，提到 Seconds_Behind_Master 这个参数会有负值出现，我们已经知道该值是 io_thread 的最近跟新的 ts 与 sql_thread 执行到的 ts 差值，
前者始终是大于后者的，唯一的肯能就是某个 event 的 ts 发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。
3.2 方法 2.
mk-heartbeat：Maatkit 万能工具包中的一个工具，被认为可以准确判断复制延时的方法。
mk-heartbeat 的实现也是借助 timestmp 的比较实现的，它首先需要保证主从服务器必须要保持一致，通过与相同的一个 NTP server 同步时钟。它需要在主库上创建一个 heartbeat 的表，里面至少有 id 与 ts 两个字段，id 为 server_id，ts 就是当前的时间戳 now()，该结构也会被复制到从库上，表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这个周期默认为 1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期去比较，复制过来记录的 ts 值与主库上的同一条 ts 值，差值为 0 表示无延时，差值越大表示延时的秒数越多。我们都知道复制是异步的 ts 不肯完全一致，所以该工具允许半秒的差距，在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复制，巧妙的借用 timestamp 来检查延时。
看完三件事❤️
如果你觉得这篇内容对你还蛮有帮助，我想邀请你帮我三个小忙：
点赞，转发，有你们的 『点赞和评论』，才是我创造的动力。
关注公众号 『 java 烂猪皮 』，不定期分享原创知识。
同时可以期待后续文章 ing🚀
关注后回复【666】扫码即可获取学习资料包
注：本文章来源于网络，非作者原创
分享后端技术干货。公众号【 Java烂猪皮】 2019.06.30 加入
一名默默无闻的扫地僧！
评论