MySQL 数据传输参数设置对数据一致性的影响

站长

2023年10月01日 22:48 · 阅读数 21

作者通过全面系统的测试，揭秘 lower_case_table_names 设置对数据一致性的影响。

作者：刘安

爱可生测试团队成员，主要负责 DTLE 开源项目相关测试任务，擅长 Python 自动化测试开发。

本文来源：原创投稿

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景

最近有客户询问：源端 MySQL 和目标端 MySQL 的 lower_case_table_names 的配置不一致时，DTLE 是否能正常同步数据？

本文就这个问题测试一下 lower_case_table_names 的设置对 DTLE 同步数据的影响。

为了简化场景这里只讨论 Linux 环境下 lower_case_table_names 配置为 0 或 1 的情况。

环境准备

部署 DTLE 4.23.04.2
两个 MySQL 实例，lower_case_table_names 配置不同

# lower_case_table_names=0
$ dbdeployer deploy single 5.7 --port 3306 --sandbox-directory sandbox --port-as-server-id --remote-access % --bind-address 0.0.0.0 -c skip-name-resolve -c binlog_format=ROW -c binlog_row_image=FULL -c log_slave_updates=ON --gtid -c lower_case_table_names=0

# lower_case_table_names=1
$ dbdeployer deploy single 5.7 --port 3306 --sandbox-directory sandbox --port-as-server-id --remote-access % --bind-address 0.0.0.0 -c skip-name-resolve -c binlog_format=ROW -c binlog_row_image=FULL -c log_slave_updates=ON --gtid -c lower_case_table_names=1

第一种情况

源端 MySQL @@lctn=0
目标端 MySQL @@lctn=1

根据 DTLE 的开发文档可知，此种情况下的 DTLE 行为：

源端按原大小写执行。
目标端收到 BinlogEntry - DML/DDL 按原大小写执行，MySQL 会自动转为小写。

以下是执行一些典型 SQL 的数据同步结果：

源端 SQL 与数据	目标段数据
CREATE DATABASE ACTION_DB; mysql> SHOW DATABASES\G **** 1. row **Database: ACTION_DB**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE TABLE ACTION_DB.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> SHOW TABLES\G **** 1. row **** Tables_in_ACTION_DB: A	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
INSERT INTO ACTION_DB.A VALUES (1); mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1	mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1
ALTER TABLE ACTION_DB.A ADD D CHAR(20); mysql> SHOW CREATE TABLE ACTION_DB.A\G **** 1. row **** Table: A Create Table: CREATE TABLE `A` ( `id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8	mysql> SHOW CREATE TABLE ACTION_DB.A\G **** 1. row **** Table: A Create Table: CREATE TABLE `a` (`id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE ACTION_DB.A RENAME TO ACTION_DB.B; mysql> SHOW TABLES\G **** 1. row **** Tables_in_ACTION_DB: B	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: b
DROP TABLE ACTION_DB.B; mysql> SHOW TABLES\G Empty set (0.00 sec)	mysql> SHOW TABLES\G Empty set (0.00 sec)

可以看到 DTLE 同步后的数据是符合预期。在源端 MySQL 与 SQL 的大小写一致，在目标端 MySQL 自动转为小写。

接下来看一些极端情况：

源端 SQL 和数据	目标端数据
CREATE DATABASE ACTION_DB; mysql> SHOW DATABASES\G **** 1. row ** Database: ACTION_DB**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE DATABASE action_db; mysql> SHOW DATABASES\G **** 1. row ** Database: ACTION_DB ** 2. row ** Database: action_db**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE TABLE ACTION_DB.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> use ACTION_DB mysql> SHOW TABLES\G **** 1. row **** Tables_in_ACTION_DB: A	mysql> use action_db mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
CREATE TABLE ACTION_DB.a(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> use ACTION_DB mysql> SHOW TABLES\G **** 1. row ** Tables_in_ACTION_DB: A ** 2. row **** Tables_in_ACTION_DB: a	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
CREATE TABLE action_db.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> USE action_db mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: A	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
CREATE TABLE action_db.a(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> USE action_db mysql> SHOW TABLES\G **** 1. row ** Tables_in_action_db: A ** 2. row **** Tables_in_action_db: a	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
INSERT INTO ACTION_DB.A VALUES (1); mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1	mysql> SELECT * FROM action_db.a\G **** 1. row **** id: 1
INSERT INTO ACTION_DB.a VALUES (2); mysql> SELECT * FROM ACTION_DB.a\G **** 1. row **** id: 2	mysql> SELECT * FROM action_db.a\G **** 1. row ** id: 1 ** 2. row **** id: 2
INSERT INTO action_db.A VALUES (3); mysql> SELECT * FROM action_db.A\G **** 1. row **** id: 3	mysql> SELECT * FROM action_db.a\G **** 1. row ** id: 1 ** 2. row ** id: 2 ** 3. row **** id: 3
INSERT INTO action_db.a VALUES (4); mysql> SELECT * FROM action_db.a\G **** 1. row **** id: 4	mysql> SELECT * FROM action_db.a\G **** 1. row ** id: 1 ** 2. row ** id: 2 ** 3. row ** id: 3 ** 4. row **** id: 4

可以看到此时 DTLE 的行为，相当于把 ACTION_DB.A、ACTION_DB.a、action_db.A 和 action_db.a 四个表的数据合并到一张表。

所以为了避免此种情况，可以通过在创建 DTLE 作业的时候，为每个重名的库配置 TableSchemaRename 属性、重名表配置 Table.TableRename 属性的方式来解决。

第二种情况

源端 MySQL @@lctn=1
目标端MySQL @@lctn=0

根据 DTLE 的开发文档里面介绍，此种情况下的 DTLE 行为：

用户填写的复制范围，应转化为小写。
不复制已有的大写 SCHEMA.TABLE。
新增的 Schema.Table，转化为小写后，加入复制范围。
目标端无论 @@lctn=0 或 @@lctn=1，都应该复制源端的效果，即小写。
目标端收到的 BinlogEntry 中，schema.tableName 已为小写。

以下是执行一些典型 SQL 的数据同步结果：

源端 SQL 和数据	目标端数据
CREATE DATABASE ACTION_DB; mysql> SHOW DATABASES\G **** 1. row ** Database: action_db**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE TABLE ACTION_DB.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
INSERT INTO ACTION_DB.A VALUES (1); mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1	mysql> SELECT * FROM action_db.a\G **** 1. row **** id: 1
ALTER TABLE ACTION_DB.A ADD D CHAR(20); mysql> SHOW CREATE TABLE ACTION_DB.A\G **** 1. row **** Table: A Create Table: CREATE TABLE `a` ( `id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8	mysql> SHOW CREATE TABLE action_db.a\G **** 1. row **** Table: a Create Table: CREATE TABLE `a` ( `id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE ACTION_DB.A RENAME TO ACTION_DB.B; mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: b	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: b
DROP TABLE ACTION_DB.B; mysql> SHOW TABLES\G Empty set (0.00 sec)	mysql> SHOW TABLES\G Empty set (0.00 sec)

可以看到 DTLE 同步后的数据是符合预期。在源端 MySQL 自动转为小写，在目标端 MySQL 同步的数据也是小写的。

其他限制

通过观察 general log 可以得知，DTLE 作业是在初始化作业的时候获取源端以及目标端 MySQL 的 lower_case_table_names 配置的，所以在 DTLE 作业存续期间更改 MySQL 的该参数是 DTLE 无法感知并处理的。因此禁止在 DTLE 作业存续期间更改此配置。

总结

原则上 DTLE 还是建议源端和目标端设置相同。
当源端 MySQL @@lctn=0 且目标端 MySQL @@lctn=1 时，需要注意源端仅大小写不同的同名库表在目标端会汇聚到同一个表中的问题。
DTLE 作业存续期间，MySQL 上的 lower_case_table_names 配置不可改变。

源端 SQL 与数据	目标段数据
CREATE DATABASE ACTION_DB; mysql> SHOW DATABASES\G **** 1. row **Database: ACTION_DB**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE TABLE ACTION_DB.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> SHOW TABLES\G **** 1. row **** Tables_in_ACTION_DB: A	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
INSERT INTO ACTION_DB.A VALUES (1); mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1	mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1
ALTER TABLE ACTION_DB.A ADD D CHAR(20); mysql> SHOW CREATE TABLE ACTION_DB.A\G **** 1. row **** Table: A Create Table: CREATE TABLE `A` ( `id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8	mysql> SHOW CREATE TABLE ACTION_DB.A\G **** 1. row **** Table: A Create Table: CREATE TABLE `a` (`id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE ACTION_DB.A RENAME TO ACTION_DB.B; mysql> SHOW TABLES\G **** 1. row **** Tables_in_ACTION_DB: B	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: b
DROP TABLE ACTION_DB.B; mysql> SHOW TABLES\G Empty set (0.00 sec)	mysql> SHOW TABLES\G Empty set (0.00 sec)

源端 SQL 和数据	目标端数据
CREATE DATABASE ACTION_DB; mysql> SHOW DATABASES\G **** 1. row ** Database: ACTION_DB**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE DATABASE action_db; mysql> SHOW DATABASES\G **** 1. row ** Database: ACTION_DB ** 2. row ** Database: action_db**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE TABLE ACTION_DB.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> use ACTION_DB mysql> SHOW TABLES\G **** 1. row **** Tables_in_ACTION_DB: A	mysql> use action_db mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
CREATE TABLE ACTION_DB.a(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> use ACTION_DB mysql> SHOW TABLES\G **** 1. row ** Tables_in_ACTION_DB: A ** 2. row **** Tables_in_ACTION_DB: a	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
CREATE TABLE action_db.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> USE action_db mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: A	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
CREATE TABLE action_db.a(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> USE action_db mysql> SHOW TABLES\G **** 1. row ** Tables_in_action_db: A ** 2. row **** Tables_in_action_db: a	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
INSERT INTO ACTION_DB.A VALUES (1); mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1	mysql> SELECT * FROM action_db.a\G **** 1. row **** id: 1
INSERT INTO ACTION_DB.a VALUES (2); mysql> SELECT * FROM ACTION_DB.a\G **** 1. row **** id: 2	mysql> SELECT * FROM action_db.a\G **** 1. row ** id: 1 ** 2. row **** id: 2
INSERT INTO action_db.A VALUES (3); mysql> SELECT * FROM action_db.A\G **** 1. row **** id: 3	mysql> SELECT * FROM action_db.a\G **** 1. row ** id: 1 ** 2. row ** id: 2 ** 3. row **** id: 3
INSERT INTO action_db.a VALUES (4); mysql> SELECT * FROM action_db.a\G **** 1. row **** id: 4	mysql> SELECT * FROM action_db.a\G **** 1. row ** id: 1 ** 2. row ** id: 2 ** 3. row ** id: 3 ** 4. row **** id: 4

源端 SQL 和数据	目标端数据
CREATE DATABASE ACTION_DB; mysql> SHOW DATABASES\G **** 1. row ** Database: action_db**	mysql> SHOW DATABASES\G **** 1. row ** Database: action_db ** 2. row ** Database: dtle**
CREATE TABLE ACTION_DB.A(id int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: a
INSERT INTO ACTION_DB.A VALUES (1); mysql> SELECT * FROM ACTION_DB.A\G **** 1. row **** id: 1	mysql> SELECT * FROM action_db.a\G **** 1. row **** id: 1
ALTER TABLE ACTION_DB.A ADD D CHAR(20); mysql> SHOW CREATE TABLE ACTION_DB.A\G **** 1. row **** Table: A Create Table: CREATE TABLE `a` ( `id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8	mysql> SHOW CREATE TABLE action_db.a\G **** 1. row **** Table: a Create Table: CREATE TABLE `a` ( `id` int(11) DEFAULT NULL, `D` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE ACTION_DB.A RENAME TO ACTION_DB.B; mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: b	mysql> SHOW TABLES\G **** 1. row **** Tables_in_action_db: b
DROP TABLE ACTION_DB.B; mysql> SHOW TABLES\G Empty set (0.00 sec)	mysql> SHOW TABLES\G Empty set (0.00 sec)