Mysql复制常见的问题及处理方法

• 跳过复制错误

在明确知道数据库出现了何种错误时，可以忽略此错误，但不要滥用，跳过错误的命令如下。

STOP SLAVE;

SET GLOBAL sql_slave_skip_counter = 1;

START SLAVE;

• 临时表和复制

必须保证干净地关闭从库，否则复制可能会出错。因为在复制的时候，从库的临时表也在进行同步，如果关闭了从库，再重启的时候，就没有临时表了，那么那些对临时表的更新就不能被复制过来，从而就会复制出错。所以需要干净地关闭临时表，在没有临时表的时候，干净地关闭数据库。

如下步骤可避免复制出错。

1) 运行”STOP SLAVE SQL_THREAD;“命令。

2) 运行SHOW STATUS检查Slave_open_temp_tables的值，具体命令如下：

SHOW STATUS like '%slave%';

3) 如果Slave_open_temp_tables不等于0，那么运行START SLAVE SQL_THREAD，然后重复以上步骤。

4) 如果Slave_open_temp_tables=0，那么，可以关闭数据库的实例了。

临时表的复制问题主要是出在基于语句的复制模式，如果使用row-based复制，那么临时表是不会被复制的，如果你希望一劳永逸，可以考虑使用基于行的复制模式。

• 内存表和复制

如果使用的是内存引擎的表，那么从库重启也可能会导致复制中断。

1) 如果主库重启，那么内存表将会是空的，会写入一个”DELETE“语句到二进制日志，通知从库清空数据，这种情况下一般不会有复制问题。

2) 如果从库重启，那么内存是空的，这会导致和主库的数据不一致，主库复制过来的操作将无法正常运行，复制将会失败。基于行的复制可能会出现错误”Can't find record in 'memory_table'“。

此种情况下的复制中断没有太好的解决方案。如果可能，我们可以用InnoDB来代替内存表。如果你实在需要使用内存表的话，可以考虑设置IDEMPOTENT(这个选项对所有其他表都生效，因此需要谨慎使用)，或者忽略报错的错误号，比如忽略1032错误，或者还可以在从库中忽略要复制的内存表。

另外，基于语句的复制，比如INSERT INTO ... SELECT FROM memory_table可能向主从库中插入不一样的数据。

如果设置了内存表的大小，SET global max_heap_table_size value，那么这个变更是不会被复制到从库的，你需要确保主从都做了变更。

虽然主从的内存表的数据可能会不一致，但是如果应用程序逻辑可以确保内存表只是用作缓存，那么一般是不会有太大的问题。

• 主库宕机重新启动成功，但复制关系中断

主库、从库宕机都可能导致复制关系中断。

一般情况下，生产环境中出现的复制故障主要是主库宕机后，从库找不到同步的主库日志位置信息，需要手动处理。

主库二进制日志并不是实时刷新的，主库宕机后，部分日志丢失了，但是更多的日志已经被传送到了从库，结果从库的数据比主库的还要新，这种情况下往往会导致主键冲突。我们需要进行临时设置让从库忽略主键冲突的错误。

待主从之间的复制稳定之后，建议立即取消忽略的错误号，仍然执行严格的复制检查。

• 主库宕机重启不成功

如果主库宕机重启不成功，则需要选择其中一台从库做主库，具体步骤如下：

1) 通过master_log_file、read_master_log_pos可以判断哪个从库是最新的。

2) 确认该从库已经应用了所有日志，提升该从库为主库。

3) 其他从库自行检查自己最新的日志，判断是哪个时间点中断了，是哪条SQL，然后通过这条SQL去”新主库“查找具体的位置点(positition)，并从这个点开始同步。

由于检索相关SQL比较耗时，对于高并发的业务，可能会难以定位到具体的位置点，如果可以接受部分数据误差，那么我们也可以直接选择故障时刻的大致日志位置点或凭经理决定从哪里开始同步。如果确实难以定位，但对于数据的一致性要求又很高，那么我们可以对新的主库重新制作从库，以保证数据的准确性。

• 多个从库的server-id相同

如果主库的一些从库存在server-id相同的情况，那么从库的MySQL错误日志里将会有大量的重连和断开的错误，但它不会明确告知我们server-id有重复。

配置不同的server-id即可解决此问题。

• 锁定导致的复制延时

如果是基于语句的复制，那么从库上的操作中锁定可能会比较多，从而影响复制的速度，比如INSERT ... SELECT这类语句会锁住所有数据。由于从库上的操作是逐个顺序执行的，因此长时间的查询，可能会导致大的延时。解决方法具体如下：

1) 分割查询，尽早释放资源。

2) 可以考虑采用SELECT INTO OUTFILE，然后LOAD DATA INFILE的方式。

• 对MyISAM引擎的表恢复数据

如果某个MyISAM表的数据有问题，需要恢复到某个时间点的数据，那么我们可以采用如下便捷方法进行恢复：

1) 主库LOCK TABLE table_name READ。

2) 主库FLUSH TABLES。

3) 复制备份的MyISAM数据文件，覆盖掉主库和从库中的这个表。

4) 主库解锁表 UNLOCK TABLES。

以上方法在理论上是可行的，主要是为了确保在将备份文件复制到主库上的时候，主从复制也是正常的。有时我们在误操作表之后，希望能够恢复数据，这时就可以采用这样的办法了。

• 如何彻底清除Slave设置

MySQL5.1并不能干净清除复制信息，比如，我们在本机执行如下命令。

STOP SLAVE;

RESET SLAVE;

以上命令将清除master.info和relay-log.info。

但我们仍然可以在如下命令中看到一些残留信息：

SHOW SLAVE STATUS \G;

理论上清除废旧的文件，然后重启MySQL即可，那么有没有更好的不需要重启的办法呢？可以试试下面这个办法。

CHANGE MASTER TO MASTER_HOST=''

这个时候虽然”SHOW SLAVE STATUS \G;“没有任何Slave相关信息的输出了，介是重新生成了master.info和relay-log.info文件，这样很不友好。

然后我们再运行”RESET SLAVE“；命令，这次就可以清除所有信息了。此时可手动清除残留的*relay*文件。

• 网络异常导致的复制延时

MySQL在网络异常的时候，也可能会延时很久，然后我们并不知道，虽然SHOW SLAVE STATUS \G输出里的 Second_Behind_Master显示为0，但可能已经延时很久了。建议把slave_net_timeout参数设置得小一些，比如小于1分钟。