阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL 5.6 create table like 同步异常问题分析与总结

215次阅读
没有评论

共计 4645 个字符,预计需要花费 12 分钟才能阅读完成。

我们都知道,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]#

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-02/141060.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4645字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中