mysql主从同步问题梳理
mysql主从同步问题梳理
前⾔：
  MySQL主从复制故障机延迟原因有很多，之前详细介绍了Mysql主从复制的原理和部署过程，在mysql同步过程中会出现很多问题，导致数据同步异常。以下梳理了⼏种主从同步中可能存在的问题:
1）slave运⾏过慢不能与master同步，也就是MySQL数据库主从同步延迟
MySQL数据库slave服务器延迟的现象是⾮常普遍的，MySQL复制允许从机进⾏SELECT操作，但是在实际线上环境下，由于从机延迟的关系，很难将读取操作转向到从机。这就导致了有了以下⼀些潜规则：“实时性要求不⾼的读取操作可以放到slave服务器，实时性要求⾼的读取操作放到master 服务器”，“从机仅能做前⼀天的统计类查询”。
slave滞后即slave不能快速执⾏来⾃于master的所有事件，从⽽不能避免更新slave数据延迟。
mysql的master-slave架构中master仅做写⼊、更新、删除操作，slave做select操作。造成slave滞后的原因有很多。
slave同步延迟的原理
MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产⽣的⽇志写进binlog，由于binlog是顺序写，所以效率很⾼。
Slave的IO Thread线程从主库中bin log中读取取⽇志。
Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本⾼很多。
由于SQL Thread也是单线程的，如果slave上的其他查询产⽣lock争⽤，⼜或者⼀个DML语句（⼤事务、⼤查询）执⾏了⼏分钟卡住了，那么所有之后的DML会等待这个DML执⾏完才会继续执⾏，这就导致了延时。也许有⼈会质疑：主库上那个相同的DDL也会执⾏⼏分钟，为什么slave会延时？原因是master可以并发执⾏，⽽Slave_SQL_Running线程却不可以。
slave同步延迟的可能原因
1--slave的I/O线程推迟读取⽇志中的事件信息；最常见原因是slave是在单线程中执⾏所有事务，⽽master有很多线程可以并⾏执⾏事务。
2--带来低效连接的长查询、磁盘读取的I/O限制、锁竞争和innodb线程同步启动等。
3--Master负载；Slave负载
4--⽹络延迟
5--机器配置（cpu、内存、硬盘）
（主从同步延迟怎么产⽣的？）总之，当主库的TPS并发较⾼时，产⽣的DDL数量超过slave⼀个sql线程所能处理的承受范围时，主从同步就会产⽣延时；或者当slave中有⼤型query语句产⽣了锁等待也会产⽣延时。
如何查看同步延迟
1--可以通过⽐对master、slave上的⽇志位置
2--通过"show slave status"查看Seconds_Behind_Master的值，这个值代表主从同步延迟的时间，值越⼤说明延迟越严重。值为0为正常情况，正值表⽰已经出现延迟，数字越⼤从库落后主库越多。
3--使⽤percona-toolkit的pt-hearbeat⼯具进⾏查看。
减少同步延迟的操作⽅案
1--减少锁竞争
如果查询导致⼤量的表锁定，需要考虑重构查询语句，尽量避免过多的锁。
2--负载均衡
搭建多少slave，并且使⽤lvs或nginx进⾏查询负载均衡，可以减少每个slave执⾏查询的次数和时间，从⽽将更多的时间⽤于去处理主从同步。
电脑如何截图
3--salve较⾼的机器配置
4--Slave调整参数
为了保障较⾼的数据安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit=1等设置。⽽Slave可以关闭
binlog，innodb_flush_log_at_trx_commit也可以设置为0来提⾼sql的执⾏效率（这两个参数很管⽤）
5--并⾏复制
即有单线程的复制改成多线程复制。
从库有两个线程与复制相关：io_thread 负责从主库拿binlog并写到relaylog， sql_thread 负责读relayl
死苍蝇og并执⾏。
多线程的思路就是把sql_thread 变成分发线程，然后由⼀组worker_thread来负责执⾏。
⼏乎所有的并⾏复制都是这个思路，有不同的，便是sql_thread 的分发策略。
MySQL5.7的真正并⾏复制enhanced multi-threaded slave（MTS）很好的解决了主从同步复制的延迟问题。
2）slave同步状态中出现Slave_IO_Running: NO （⼀般是下⾯的原因2造成的）
报错：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
原因1：清理数据导致主从库不同步（前提是主库的binlog⽇志没有被暴⼒删除或错误删除，即要确保正在使⽤的那个最新binlog⽂件在master主库机器上存在）。
解决办法：
1）先进⼊slave中执⾏:"slave stop;"来停⽌从库同步；
2）再去master中执⾏:"flush logs;"来清空⽇志；
3）然后在master中执⾏:"show master status;"查看下主库的状态，主要是⽇志的⽂件和position；
4）然后回到slave中，执⾏:"CHANGE MASTER TO ......执⾏同步指令
原因2：该错误发⽣在从库的io进程从主库拉取⽇志时，发现主库的mysql_bin.index⽂件中第⼀个⽂件不存在。出现此类报错可能是由于你的slave 由于某种原因停⽌了好长⼀段
时间，当你重启slave 复制的时候，在主库上不到相应的binlog ，会报此类错误。或者是由于某些设置主库上的binlog被删除了，导致从库获取不到对应的binglog file。
解决办法：
1）为了避免数据丢失，需要重新进⾏slave同步操作。
2）注意主库binlog的清理策略，选择基于时间过期的删除⽅式还是基于空间利⽤率的删除⽅式。
3）记住最好不要使⽤"rm -rf"命令删除binlog file，这样不会同步修改mysql_bin.index 记录的binlog 条⽬。在删除binlog的时候确保主库保留了从库"show slave status"
的Relay_Master_Log_File对应的binlog file。任何时候都不能删除正在使⽤的那个最新binlog⽂件；最好把bin-log⽂件不要删除，最好给备份出来。
原因2的情况下，使⽤原因1的处理⽅案显然是解决不了的！此时的解决⽅案：
在从库上执⾏：
mysql> stop slave;
mysql> reset slave;
mysql> start slave;
mysql> show slave status \G;
3）slave同步状态中出现Slave_IO_Running: Connecting
导致这个错误的原因⼀般是：
1--⽹络不通
中国墙布十大名牌排行榜2--权限问题（连接master的⽤户名和密码跟master授权不⼀致）收到女儿的礼物怎么发朋友圈
3--连接时⽤的log file和pos节点跟"show master status"的结果不⼀致
4）slave同步状态中出现Slave_SQL_Running: No ，即slave不同步！
解决办法：
第⼀种⽅法：忽略错误后，继续同步。
该⽅法适⽤于主从库数据相差不⼤，或者要求数据可以不完全统⼀的情况，数据要求不严格的情况（下⾯均为在slave机器上的操作）
mysql> stop slave;
mysql> set global sql_slave_skip_counter =1; //表⽰跳过⼀步错误，后⾯的数字可变；或者在myf⾥添加slave-skip-errors = all（上⾯已在配置中添加）
mysql> start slave;
mysql> show slave status\G //查看：
第⼆种⽅法：重新做主从，完全同步
该⽅法适⽤于主从库数据相差较⼤，或者要求数据完全统⼀的情况
1--master主库上操作
mysql> flush tables with read lock; //进⾏锁表，防⽌数据写⼊。注意该处是锁定为只读状态，语句不区分⼤⼩写
#mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql //主库完全备份(如果是指定库同步，就备份指定库)，注意数据库备份⼀定要定期进⾏，确保数据万⽆⼀失
mysql> show master status; //查看master状态，注意log file和pos节点，slave同步会⽤到
# scp mysql.bak.sql root@192.168.1.102:/tmp/ //把备份⽂件传到slave从库机器，进⾏数据恢复
2--slave从库操作
mysql> stop slave;
mysql> source /tmp/mysql.bak.sql
mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_
...；
mysql> start slave;
mysql> show slave status\G
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此种⽅法中最为关键主要有两步：
①主服务器上锁表做完全备份，并滚动⽇志；
姓氏的由来②从服务器上进⾏半道恢复.
5）slave中继⽇志relay-log损坏？
什么是中继⽇志？
relay-log存放在从服务器上，从服务器将主服务器的⼆进制⽇志⽂件拷贝到⾃⼰的主机上放在中继⽇志中，然后调⽤SQL线程按照拷中继⽇志⽂件中的⼆进制⽇志⽂件执⾏以便就可达到数据的同步。
如何中继⽇志避免：
mysql 5.6版本后，在myf⽂件中开启relay_log_recover=1即可避免。
6）slave连接超时且重新连接频繁
若有多少slave，且没有设置server_id或两个slave设置相同的server_id，将有可能会出现服务器的ID冲突。这种情况下，其中⼀台slave可能会频繁超时或丢失后重新连接序列。
所以⼀定要确保每台slave及master在myf中都要设置不⼀样的server_id。
7）主库与从库使⽤不同的存储引擎造成不同步
8）从库同步时，提⽰表不存在
错误：Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'
解决⽅法：在从库重建这张表。
9）max_allowed_packet设置过⼩导致slave报错
max_allowed_packet默认是16M，主从库的max_allowed_packet值和备库上的不匹配。
在这情况下，主库可能会记录⼀个备库认为过⼤的包。当备库获取到该⼆进制⽇志事件时，可能会碰到各种问题，如⽆限报错和重试、中继⽇志损坏等。
具体表现：
从库的Slave_IO_Thread死掉了，查看后，出现以下错误提⽰：
Got a packet bigger than 'max_allowed_packet' bytes
很明显是由于max_allowed_packet的设置太⼩导致的，然后查检主从库上的设置，主库的设置⼤于从库，因为max_allowed_packet是动态参数，先调整从库上的max_allowed_packet 与主库相同，重新单独启动I/O线程就正常了。
原理说明：binlog的事件以RBR格式记录，且当前的事件长度⼤于了从库的max_allowed_packet, 导致⽆法Slave IO不能正常读取master binlog event. 10）在master上删除⼀条记录时出现的故障
在master上删除⼀条记录后，slave上因不到这条记录⽽报错。
解决⽅法：
由于主库上已经对这条语句进⾏了删除操作，故可以跳过。
在这种情况下，说明主从同步可能数据会有不⼀致的情况发⽣，所以需要使⽤pt-table-checksum进⾏数据库⼀致性⽐对。
反倾销是什么意思
（参考：mysql主从同步(3)-percona-toolkit⼯具（数据⼀致性监测、延迟监控）使⽤梳理）
11）在master更新⼀条记录，⽽slave却不到。
主从数据不致时，master有某条记录，但在salve上没有这条记录，若在master上进⾏更新这条记录，则在slave中可能报错。
解决⽅法：
1--根据从库发⽣异常的位置，查主库上的⼆进制⽇志。
2--根据主库⼆进制⽇志信息，到更新后的整条记录。
3--在从库上执⾏在主库上到的记录信息，进⾏insert操作。
4--跳过这条语句，再同步slave。
5--使⽤pt-table-checksum查看主从库表数据否⼀致。
12）删除登录mysql后的操作记录
[root@mysql ~]# ll /home/mysql/.mysql_history
-rw------- 1 mysql mysql 1255 Jun 15 18:18 /home/mysql/.mysql_history
[root@mysql ~]# >/home/mysql/.mysql_history
13）同步时出现报错"Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"
在从机mysql上查看slave状态，报错如下：
Mysql> show slave status\G;
......
Slave_IO_Running: No
Slave_SQL_Running: Yes
......
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解决办法：
在主机mysql上执⾏：
Mysql> FLUSH TABLES WITH READ LOCK;
Mysql> flush logs;
Mysql> show master status;
记下File, Position。
然后在从机mysql上执⾏同步操作（根据上⾯记住的File和Position）
Mysql> stop slave;
Mysql> change master to master_host='主机mysqlip',master_user='授权同步的⽤户',master_password='授权同步密码',master_log_file='记下的File',master_log_pos=记下的Position;
Mysql> start slave;
Mysql> show slave status\G;
......
Slave_IO_Running: No
Slave_SQL_Running: Yes
然后在主机mysql上进⾏解锁
mysql> unlock tables;
14）服务器重启或mysql服务重启后，如何恢复mysql主从同步关系
当mysql主从同步顺利完成后，如果重启服务器或mysql服务重启后，恢复mysql主从同步关系的正确步骤：
1）主机mysql和从机mysql都要启动mysql服务
# /etc/init.d/mysql start
2) 在从机mysql上进⾏slave恢复操作
mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.
....
mysql主从同步(跨机房情况)不⼀致问题
1) ⽹络的延迟
由于mysql主从复制是基于binlog的⼀种异步复制，通过⽹络传送binlog⽂件，理所当然⽹络延迟是主从不同步的绝⼤多数的原因，特别是跨机房的数据同步出现这种⼏率⾮常的⼤，所以做读写分离，注意从业务层进⾏前期设计。
2) 主从两台机器的负载不⼀致
由于mysql主从复制是主数据库上⾯启动1个io线程，⽽从上⾯启动1个sql线程和1个io线程，当中任何⼀台机器的负载很⾼，忙不过来，导致其中的任何⼀个线程出现资源不⾜，都将出现主从不⼀致的情况。
3) max_allowed_packet设置不⼀致
主数据库上⾯设置的max_allowed_packet⽐从数据库⼤，当⼀个⼤的sql语句，能在主数据库上⾯执⾏完毕，从数据库上⾯设置过⼩，⽆法执⾏，导致的主从不⼀致。
4) key⾃增键开始的键值跟⾃增步长设置不⼀致引起的主从不⼀致。
5) mysql异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog⽂件出现损坏，导致主从不⼀致。
6) mysql本⾝的bug引起的主从不同步。
7) 版本不⼀致，特别是⾼版本是主，低版本为从的情况下，主数据库上⾯⽀持的功能，从数据库上⾯不⽀持该功能。
以上就是常见的⼀些主从不同步的情况。基于以上情况，先保证max_allowed_packet、⾃增键开始点和增长点设置⼀致，再者牺牲部分性能在主上⾯开启sync_binlog。
对于采⽤innodb的库，推荐在配置⽂件中添加下⾯内容:
innodb_flush_logs_at_trx_commit = 1
innodb-support_xa = 1
同时在从数据库的配置⽂件中再多添加下⾯两个参数:
skip_slave_start
read_only

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。