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执行完才会继续执行，这就导致了延时。
slave同步延迟的可能原因
    1--slave的I/O线程推迟读取日志中的事件信息；最常见原因是slave是在单线程中执行所有事务，而master有很多线程可以并行执行事务。
    2--带来低效连接的长查询、磁盘读取的I/O限制、锁竞争和innodb线程同步启动等。
    3--Master负载；Slave负载
    4--网络延迟
    5--机器配置（cpu、内存、硬盘）
总之，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当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 负责读relaylog并执行。
多线程的思路就是把sql_thread 变成分发线程，然后由一组worker_thread来负责执行。
几乎所有的并行复制都是这个思路，有不同的，便是sql_thread 的分发策略。
MySQL5.7的真正并行复制enhanced multi-threaded slave（MTS）很好的解决了主从同步复制的延迟问题。

2）slave同步状态中出现Slave_IO_Running: NO
报错：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‘
原因：清理数据导致主从库不同步。
解决措施：http://www.cnblogs.com/kevingrace/p/6256603.html

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; //表示跳过一步错误，后面的数字可变；或者在my.cnf里添加slave-skip-errors = all（上面已在配置中添加）
mysql> start slave;
mysql> show slave status\G 查看：
第二种方法：重新做主从，完全同步
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
    1--master主库上操作
mysql> flush tables with read lock;          //进行锁表，防止数据写入。注意该处是锁定为只读状态，语句不区分大小写
# mysqldump -uroot -p -hlocalhost > mysql.bak.sql     //把数据备份到mysql.bak.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_port=3306.......；
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版本后，在my.cnf文件中开启relay_log_recover=1即可避免。

6）slave连接超时且重新连接频繁
若有多少slave，且没有设置server_id或两个slave设置相同的server_id，将有可能会出现服务器的ID冲突。这种情况下，其中一台slave可能会频繁超时或丢失后重新连接序列。
所以一定要确保每台slave及master在my.cnf中都要设置不一样的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主从同步数据一致性检查工具pt-table-checksum用法梳理）

11）在master更新一条记录，而slave却找不到。
主从数据不致时，master有某条记录，但在salve上没有这条记录，若在master上进行更新这条记录，则在slave中可能报错。
解决方法：
   1--根据从库发生异常的位置，查主库上的二进制日志。
   2--根据主库二进制日志信息，找到更新后的整条记录。
   3--在从库上执行在主库上找到的记录信息，进行insert操作。
   4--跳过这条语句，再同步slave。
   5--使用pt-table-checksum查看主从库表数据否一致。

mysql主从同步中出现的问题梳理

标签：密码   通过   策略   检查   percona   数据安全   重试   设置   多少