记1次未正确设置replicate-ignore-db参数导致MySQL主从同步异常的问题

 

今天监控报警，从库的Slave_SQL_Running未启动，在处理完故障后，提取一些主要的步骤整理了一下，在自己的测试机上重现了一下当时的故障情况，供大家参考。

当时使用show slave status查看slave复制状态时，IO线程：Yes说明主库binlog能正常传输并记录在备库relay log中；SQL线程：NO说明应该是应用日志时出错，当时第一反应过来的处理方法，不外乎 change master 重置同步 binlog 的位置，或者 set global SQL_SLAVE_SKIP_COUNTER=1; 跳过该错误所在的事务（治标不治本、慎用）。

 

仔细查看error log：

2017-06-30T01:28:10.388335Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-06-30T01:28:10.388425Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-06-30T01:28:10.388459Z 0 [Note] /usr/local/mysql5.7/bin/mysqld (mysqld 5.7.18-log) starting as process 4739 ...
2017-06-30T01:28:10.395127Z 0 [Note] InnoDB: PUNCH HOLE support not available
2017-06-30T01:28:10.395192Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-30T01:28:10.395206Z 0 [Note] InnoDB: Uses event mutexes
2017-06-30T01:28:10.395219Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-06-30T01:28:10.395231Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-06-30T01:28:10.395244Z 0 [Note] InnoDB: Using Linux native AIO
2017-06-30T01:28:10.395684Z 0 [Note] InnoDB: Number of pools: 1
2017-06-30T01:28:10.395845Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-06-30T01:28:10.397849Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-06-30T01:28:10.409230Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-06-30T01:28:10.411797Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-06-30T01:28:10.424851Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-30T01:28:10.442200Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-30T01:28:10.442272Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-30T01:28:10.489966Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-30T01:28:10.491206Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-30T01:28:10.491243Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-30T01:28:10.491732Z 0 [Note] InnoDB: Waiting for purge to start
2017-06-30T01:28:10.542190Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 2872305
2017-06-30T01:28:10.542958Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-06-30T01:28:10.546372Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql3307/data/ib_buffer_pool
2017-06-30T01:28:10.548003Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170630  9:28:10
2017-06-30T01:28:10.554680Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-06-30T01:28:10.554711Z 0 [Note] Server hostname (bind-address): '*'; port: 3307
2017-06-30T01:28:10.554757Z 0 [Note] IPv6 is available.
2017-06-30T01:28:10.554771Z 0 [Note]   - '::' resolves to '::';
2017-06-30T01:28:10.554793Z 0 [Note] Server socket created on IP: '::'.
2017-06-30T01:28:10.569747Z 1 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-06-30T01:28:10.569845Z 1 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000044' at position 107, relay log '/usr/local/mysql3307/data/mysql-relay-bin.000020' position: 296
2017-06-30T01:28:10.570234Z 1 [ERROR] Slave SQL for channel '': Error executing row event: 'Table 'demo.t1' doesn't exist', Error_code: 1146
2017-06-30T01:28:10.570256Z 1 [Warning] Slave: Table 'demo.t1' doesn't exist Error_code: 1146
2017-06-30T01:28:10.570267Z 1 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000044' position 107
2017-06-30T01:28:10.570516Z 2 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-06-30T01:28:10.573962Z 0 [Note] Event Scheduler: Loaded 0 events
2017-06-30T01:28:10.574337Z 2 [Note] Slave I/O thread for channel '': connected to master 'replication_user@127.0.0.1:3306',replication started in log 'mysql-bin.000045' at position 107
2017-06-30T01:28:10.574783Z 2 [Warning] Slave I/O for channel '': Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2017-06-30T01:28:10.574904Z 2 [Warning] Slave I/O for channel '': Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.54-log), even though it is on the slave (version: 5.7.18-log), Error_code: 1193
2017-06-30T01:28:10.576182Z 0 [Note] /usr/local/mysql5.7/bin/mysqld: ready for connections.
Version: '5.7.18-log'  socket: '/usr/local/mysql5.7/run/mysqld.sock'  port: 3307  MySQL Community Server (GPL)
2017-06-30T01:28:10.584938Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2017-06-30T01:28:10.584964Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-06-30T01:28:10.608475Z 0 [Note] End of list of non-natively partitioned tables

 

重点关注加粗的错误日志，应该是主库binlog记录了关于表 'demo.t1'的相关操作，而从库无相关数据库（如下），导致从库的IO线程获取到主库的binlog信息之后，SQL线程无法replay到本地数据库。

主库：

从库：

 

从库的my.cnf文件中加入如下复制过滤参数：

replicate-wild-ignore-table = demo.%

关于复制过滤参数的讲解可参考：

http://blog.chinaunix.net/uid-20639775-id-3254611.html

https://yq.aliyun.com/articles/59268

 

重启从库后，查看复制情况，已恢复正常

 

归其错误原因，是因为当时在搭建主从复制的时候，导主库数据时，未导入测试库demo。而在复制架构搭建完成后，又没有及时在备库中过滤测试库，导致在主库上往测试库更新数据时，记录在binlog中的相关信息在备库中无法成功应用导致。

 

Tips：

1、在MySQL5.5/5.6版本中，由于修改复制过滤参数只能在my.cnf中进行修改，所以需要重启数据库；

2、而最新版5.7版本中，如下三步就可以不用重启服务器就生效了：

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = 'demo.*';

START SLAVE SQL_THREAD;

 

5.7变更复制过滤参数相关参考文档：

https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html

https://www.percona.com/blog/2015/11/04/mysql-5-7-change-replication-filter-online/

http://www.cnblogs.com/gomysql/p/4991197.html