论MySQL如何查看复制信息和排查复制问题

从库执行语句与主库冲突

1.错误原因

复制过程中，由于从库需要读取的主库的binlog丢失，从而导致复制发生异常。导致主库binlog丢失的主要原因如下：主库执行reset master命令;主库执行purge binary/master logs命令;主库设置expire_logs_days，自动删除了binlog;主库的binlog被误删除。

2.错误信息

如果发生找不到主机binlog的情况，从库error log会报出如下错误：

2017-04-18T09:01:42.894612Z 17 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from './binlog001.000002' at 154, the last byte read from './binlog001.000002' at 154.', Error_code: 1236
复制

3.原因分析过程

>>查看error log

Error log中显示的详细错误信息如下：

2017-04-18T09:01:42.894612Z 17 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from './binlog001.000002' at 154, the last byte read from './binlog001.000002' at 154.', Error_code: 1236
复制

错误信息显示无法找到对应的binlog文件。

>>查看主库binlog日志

查看主库的binlog日志文件列表，可能会发现主库的binlog变成重新开始记录：

-rw-r-----  1 songlei.wang songlei.wang      449 Apr 18 17:01 binlog001.000001
-rw-r-----  1 songlei.wang songlei.wang       19 Apr 18 17:01 binlog_index001.index
复制

或者需要复制的binlog已经被删除:

-rw-r-----  1 songlei.wang songlei.wang      241 Apr 18 17:12 binlog001.000006
-rw-r-----  1 songlei.wang songlei.wang     1716 Apr 18 17:12 binlog001.000007
-rw-r-----  1 songlei.wang songlei.wang      194 Apr 18 17:12 binlog001.000008
-rw-r-----  1 songlei.wang songlei.wang       57 Apr 18 17:12 binlog_index001.index
复制

如果binlog重新开始记录，通常是由于主库执行了resetmaster命令，导致所有的binlog被删除;如果binlog任然在继续记录，只是从库需要的binlog被删除，通常是由于主库手动执行了purge binary logs命令，或者日志的保留时间超过了expire_logs_days设置的时间。

2017-04-18 20:39:01 682 [ERROR] Slave SQL: Error 'Table 'mydb.mytbl2' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mydb.mytbl4 values (1)', Error_code: 1146
复制

#170418 20:50:47 server id 30061  end_log_pos 199 CRC32 0xaaa00818      GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '1ac5f388-0dea-11e7-b2ec-d4ae52a34783:1'/*!*/;
# at 199
#170418 20:50:47 server id 30061  end_log_pos 319 CRC32 0x06f5789f      Query   thread_id=3     exec_time=0     error_code=0
……
create table if not exists mydb.mytbl4 (A int)
复制

解析从库的binlog日志，查找是否存在建表的事务日志:

#170418 20:49:49 server id 30061  end_log_pos 199 CRC32 0x3051fade      GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '1ac5f388-0dea-11e7-b2ec-d4ae52a34783:1'/*!*/;
# at 199
#170418 20:49:49 server id 30061  end_log_pos 278 CRC32 0x7156a1c9      Query   thread_id=3     exec_time=40    error_code=0
……
BEGIN
/*!*/;
# at 278
#170418 20:49:49 server id 30061  end_log_pos 385 CRC32 0x25560e04      Query   thread_id=3     exec_time=40    error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1492519789/*!*/;
insert into mydb.mytbl values (1)
/*!*/;
……
复制

这时我们发现对于相同的GTID，从库和主机执行的语句是不相同的。通过上述分析，我们推断是从库并没有执行建表语句，从而导致主库数据不一致。

4.说明

Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
复制

这种情况的复制错误只会发生在MySQL-5.6版本，在MySQL-5.7版本会在复制时有更严格的校验，如果主机发送GTID要少于从库的GTID，那么会报告出如下的错误：

Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236
复制

1. 错误原因

主库上执行的操作并不会写入binlog。这里不考虑主库主动关闭binlog的情况。

2. 错误信息

由于主库和从库的数据不一致，从而导致主库执行的操作复制到从库后，发生从库执行失败的情况。如：

创建FEDERATED引擎的表失败:

2017-01-24T08:04:31.682038Z 3 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '887ca716-e202-11e6-8a82-52540086ae59:7' at master log mysql-bin.000004, end_log_pos 906; Error 'server name: 's' doesn't exist!' on query. Default database: 'mydb'. Query: 'CREATE TABLE `mytbl` (
  `idaction` int(10) unsigned NOT NULL AUTO_INCREMENT,
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='s'', Error_code: 143
复制

当变量relay_log_info_repository设置为FILE时，从库的SQL线程每次执行完一个事务后，会把对应的文件和位置信息更新到文件relay_log.info中。用于在从库重启时，SQL能够从正确的位置继续进行复制。

1.错误原因

如果物理机发生宕机或者从库发生意外中断，那么可能发生SQL已经执行过了某一个relay log中的事务，但是这个事务对应文件和位置信息并没有更新到relay_log.info中的情况。在从库发生重启时，会将执行过的事务重新再次执行。

2.错误信息

重复执行的事务包括任何记录到relay log中的事务，可能出现的错误信息包括：

创建库或者表失败：

017-04-18T07:52:40.440723Z 6 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:2' at master log binlog001.000001, end_log_pos 493; Error 'Can't create database 'mydb4'; database exists' on query. Default database: 'mydb4'. Query: 'create database mydb4', Error_code: 1007
复制

插入语句主键冲突:

2017-04-18T07:57:35.975310Z 10 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1' at master log binlog001.000001, end_log_pos 378; Could not execute Write_rows event on table mydb.test; Duplicate entry '10' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 378, Error_code: 1062
复制