mysql主从 冲突_mysql主从同步异常问题整理

本文为主从同步问题整理，由于个人能力有限文中可能有错误的地方，希望发出来大家相互探讨、指正。主键重复

场景说明：

在主从数据不一致情况下，slave已经有id为2数据，在master上没有id为2数据，往主库上写入id=2的数据。

主库t1数据：

mysql> select * from t1;

+----+------+

| id | name |

+----+------+

| 1 | 1 |

+----+------+

从库t1数据：

mysql> select * from t1;

+----+------+

| id | name |

+----+------+

| 1 | 1 |

| 2 | 2 |

+----+------+

从库中断原因：Last_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t1(id,name)value(2,2)'

解决方法：

stop slave;

delete from t1 where id=2;

start slave;

查看"show slave status\G"，同步情况:

Slave_IO_Running: Yes

Slave_SQL_Running: Yesslave 中继日志relay-log损坏

什么是中继日志？

说明： relay-log存放在从服务器上，从服务器将主服务器的二进制日志文件拷贝到自己的主机上放在中继日志中，然后调用SQL线程按照拷中继日志文件中的二进制日志文件执行以便就可达到数据的同步 。

如何避免：

在mysql 5.6的my.cnf文件中开启relay_log_recover=1即可。slave连接超时且重新连接频繁

若有多少slave，且没有设置server_id或两个slave设置相同的server_id，将有可能会出现服务器的ID冲突。这种情况下，其中一台slave可能会频繁超时或丢失后重新连接序列。

解决方法：

每台slave及master在my.cnf中都设置不一样的server_id。slave 运行过慢不能与master同步

slave滞后即slave不能快速执行来自于master的所有事件，从而不能避免更新slave数据延迟。mysql的master-slave架构中master仅做写入、更新、删除操作，slave做select操作。造成slave滞后的原因有很多。

可能原因说明：

可能是slave的I/O线程推迟读取日志中的事件信息。最常见原因是slave是在单线程 中执行所有事务，而master有很多线程可以并行执行事务。其他原因还有带来低效连接的长查询、磁盘读取的I/O限制、锁竞争和innodb线程同步启动等。

如何查看延迟：

1. 可以通过比对master、slave上的日志位置

2. 通过"show slave status"查看Seconds_Behind_Master的值，值越大说明延迟越严重。值为0为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。

3. 使用percona-toolkit的pt-hearbeat工作进行查看。

减少延迟方案：

1. 减少锁竞争

如果查询导致大量的表锁定，需要考虑重构查询语句，尽量避免过多的锁。

2.负载均衡

搭建多少slave，并且使用lvs或nginx进行查询负载均衡，可以减少每个slave执行查询的次数和时间，从而将更多的时间用于去处理主从同步。

3. 更新更好的硬件。没有定义server_id或没有设置唯一server_id

如果在my.cnf里定义的服务器ID ,可以通过change master 语句来设置主库，但是却没有办法启动从库同步线程。mysql> start slave;

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

若没有设置唯一的server_id,则有可能相同的server_id的从库同步会出现异常，如下所示：Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

解决方法：

在my.cnf中设定唯一的server_id值主库与从库使用不同的存储引擎

binlog采用基于语句复制的情况 下，如果从库使用不同存储引擎，可能造成数据不同步的现象。

解决方法：

主库与从库对应的表采用相同的存储引擎。从库同步时，提示表不存在

错误：Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'

解决方法：在从库重建这张表。

9. 从库数据发生变化

mysql 的复制有基于语句复制(statement)、行复制(row)、混合复制(mixed)，使用statement进行复制前提是确保主库与从库相同的数据，故不许对从库数据进行任何修改，需要对从库进行设置read_only。read_only对super权限用户不超作用。如：开放一个测试用户，有select、insert、update权限

grant select,update,insert on *.* to 'test1'@'%' identified by '123456';

使用这个测试用户进行登录，执行update操作

为了从库的不写入数据，可以在my.cnf中加入read-only选项临时表问题

mysql 临时表默认为myisam表引擎，当表大小超到tmp_table_size值时，数据会自动转换基于磁盘的表，若没有超过大小则保留在内存中。临时表只能对其中当前连接可见，随着连接关闭而消失。如果从库宕机或mysql服务关闭都有可能丢失临时表。若表结构设计不当，在执行含有order by 或group by语句时也可能产生临时表，所以在主库中不做查询操作、不创建临时表。。另外,mysql 5.6中开启gtid同步，则临时表不同步，设置sql_log_bin为0

有时我们希望在主库上执行语句不被同步到从库中，执行“set sql_log_bin=0”后所有记录将不被写入到二制日志中。

相关操作如下：

二进制日志写入情况：

没有记录id为18的记录。max_allowed_packet设置过小导致slave报错

max_allowed_packet默认是16M，主从库的max_allowed_packet值和备库上的不匹配。在这情况下，主库可能会记录一个备库认为过大的包。当备库获取到该二进制日志事件时，可能会碰到各种问题，如无限报错和重试、中继日志损坏等。在master上删除一条记录时出现的故障

在master上删除一条记录后，slave上因找不到这条记录而报错。

解决方法：由于主库上已经对这条语句进行了删除操作，故可以跳过。在这种情况下，说明主从同步可能数据会有不一致的情况发生，所以需要使用pt-table-checksum进行数据库一致性比对。在master更新一条记录，而slave找不到

主从数据不致时，master有某条记录，但在salve上没有这条记录，若在master上进行更新这条记录，则在slave中可能报错。

解决方法：

1. 根据从库发生异常的位置，查主库上的二进制日志。

2. 根据主库二进制日志信息，找到更新后的整条记录。

3. 在从库上执行在主库上找到的记录信息，进行insert操作。

4. 跳过这条语句，再同步slave。

5. 使用pt-table-checksum查看主从库表数据否一致。带宽受限问题

如果使用受限的带宽进行复制，可以开启从库的slave_compressed_protocol选项。当从库连接主库时，会请求一个被压缩的连接即与mysql客户端使用的压缩连接一样。使用压缩引擎zlib，它能将文本类型的数据压缩到大约原始大小的三分之一。其代价是需要额外的cpu时间，包括在主库上压缩数据和从库中解压数据。若主库与从库连接是慢速连接，可能需要将分发的主库和从库分布在同一个机房。附

测试相关表结构

create table t1

(

id int not null ,

name char(4) ,

primary key(id)

)engine=innodb default charset=utf8;