mysql 5.6 replicate-db-table,MySQL 5.6 create table like 同步异常问题分析与总结

我们都知道，MySQL5.6版本如果设置了enforce-gtid-consistency=true，则mysql禁止执行create table ... select ...语句，原因是这条语句在mysql内部会被分解成一个dml事务和一个ddl事务，但这两个事务用同一个gtid，binlog同步到从库后，第二个dml语句因为相同gtid的事务已经执行过而被丢弃，造成数据不一致(详见MySQL官方说明：http://dev.mysql.com/doc/refman/5.6/en/replication-features-create-select.html)。凑巧，前几天遇到到一个线上环境主从同步失败的问题，当时因为mysql 5.6 有对create table ... select ...语句的限制，怀疑是一位同事之前做了create table ... like ... 操作导致数据同步失败，于是就研究了一下复制参数对于create table ... like ... 的影响，如下：

先说一下结论：

1. binlog 格式为行模式(row)时，ddl在binlog中实际以语句的形式存在。

2. 行模式下，replicate-wild_ignore_table=test.%，对于create table like 语句，只要目标表所在的库或参照表所在的库为test，则salve将忽略这条事务(event)。

3. 行模式下，当前库是否是test库，对slave确定是否忽略本条事务不起作用。

线上对create table ... like ... 有影响的复制参数如下：

replicate_wild_ignore_table=information_schema.%

replicate-wild_ignore_table=performance_schema.%

replicate-wild_ignore_table=test.%

enforce-gtid-consistency=true

说明：上面三条复制过滤规则中，只有replicate-wild_ignore_table=test.%是实际生效的，其余因为实际上是系统视图，没有基表，无效。

设计对照组，分两组，一组为当前库为test库，另一组为当前库为linuxidc库

然后在同一个组内按目标表和参照表所在的库分别进行组合验证，如下：

#当前库为运维库

use linuxidc;

#目标表在test库，参照表在linuxidc库

create table test.test_a like linuxidc.t_check_sync;

#目标表和参照表都在linuxidc库，这个事务将被复制到slave上

create table linuxidc.linuxidc_a like linuxidc.t_check_sync;

#目标表和参照表都在test库

create table test.test_b like test.t_store;

#目标表在运维库，参照表在test库

create table linuxidc.linuxidc_b like test.t_store;

#当前库为test库

use test;

#目标表在test库，参照表在linuxidc库

create table test.test_c like linuxidc.t_check_sync;

#目标表和参照表都在运维库,这个事务将被复制到slave上

create table linuxidc.linuxidc_c like linuxidc.t_check_sync;

#目标表和参照表都在test库

create table test.test_d like test.t_store;

#目标表在linuxidc库，参照表在test库

create table linuxidc.linuxidc_d like test.t_store;

首先在主库上执行上述对照组：

test@20:39:55> use linuxidc;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

linuxidc@20:39:55> create table test.test_a like linuxidc.t_check_sync;

Query OK, 0 rows affected (0.01 sec)

linuxidc@20:39:55> create table linuxidc.linuxidc_a like linuxidc.t_check_sync;

Query OK, 0 rows affected (0.01 sec)

linuxidc@20:39:55> create table test.test_b like test.t_store;

Query OK, 0 rows affected (0.04 sec)

linuxidc@20:39:55> create table linuxidc.linuxidc_b like test.t_store;

Query OK, 0 rows affected (0.04 sec)

linuxidc@20:39:55> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

test@20:39:55> create table test.test_c like linuxidc.t_check_sync;

Query OK, 0 rows affected (0.01 sec)

test@20:39:55> create table linuxidc.linuxidc_c like linuxidc.t_check_sync;

Query OK, 0 rows affected (0.01 sec)

test@20:39:55> create table test.test_d like test.t_store;

Query OK, 0 rows affected (0.04 sec)

test@20:39:55> create table linuxidc.linuxidc_d like test.t_store;

Query OK, 0 rows affected (0.03 sec)

执行完后看主库上的8张表都被成功创建,如下：

test@21:03:35> show tables;

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

| Tables_in_test        |

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

| b_goods_promotion      |

| b_goods_promotion_rela |

| binlog_test            |

| t_store                |

| t_user_merchant        |

| test                  |

| test_a                |

| test_b                |

| test_c                |

| test_d                |

| user_merchant          |

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

11 rows in set (0.00 sec)

test@21:03:54> use linuxidc;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

linuxidc@21:03:56> show tables;

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

| Tables_in_linuxidc |

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

| t_check_sync    |

| linuxidc_a        |

| linuxidc_b        |

| linuxidc_c        |

| linuxidc_d        |

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

5 rows in set (0.00 sec)

linuxidc@21:04:26>

再看从库上实际创建的表的情况，linuxidc库中有两张，test库没有：

linuxidc@20:38:26> show tables;

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

| Tables_in_linuxidc |

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

| t_check_sync    |

| linuxidc_a        |

| linuxidc_c        |

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

3 rows in set (0.00 sec)

linuxidc@20:40:55> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

test@20:40:58> show tables;

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

| Tables_in_test |

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

| tmp_1126      |

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

1 row in set (0.00 sec)

test@20:41:00>

在从库上查看relay-log，说明在主库上创建8张对照表的binlog已经同步过来了，只不过slave在reply的时候过滤掉了，日志内容如下：

[root@localhost logs]# /usr/local/mysql/bin/mysqlbinlog -vv --start-datetime='2016-10-27 20:39:55' --stop-datetime='2016-10-27 20:39:56' relay_log.003689 > /tmp/test.sql

[root@localhost logs]# grep 'test_[a-z]\|linuxidc_[a-z]' /tmp/test.sql

create table test.test_a like linuxidc.t_check_sync

create table linuxidc.linuxidc_a like linuxidc.t_check_sync

create table test.test_b like test.t_store

create table linuxidc.linuxidc_b like test.t_store

create table test.test_c like linuxidc.t_check_sync

create table linuxidc.linuxidc_c like linuxidc.t_check_sync

create table test.test_d like test.t_store

create table linuxidc.linuxidc_d like test.t_store

[root@localhost logs]#