乐趣区

关于mysql:MySQL-数据传输参数设置对数据一致性的影响

jiezi

作者通过全面零碎的测试，揭秘 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 的状况。

环境筹备

  1. 部署 DTLE 4.23.04.2
  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**<br/>
CREATE TABLE ACTION_DB.A(id int(11)) <br/>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\` (<br/>\`id\` int(11) DEFAULT NULL, <br/>\`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 <br/>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 <br/>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 作业存续期间更改此配置。

总结

  1. 原则上 DTLE 还是倡议源端和指标端设置雷同。
  2. 当源端 MySQL @@lctn=0 且指标端 MySQL @@lctn=1 时，须要留神源端仅大小写不同的同名库表在指标端会汇聚到同一个表中的问题。
  3. DTLE 作业存续期间，MySQL 上的 lower_case_table_names 配置不可扭转。

对于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，反对多场景审核，反对标准化上线流程，原生反对 MySQL 审核且数据库类型可扩大的 SQL 审核工具。

SQLE 获取

类型 地址
版本库 https://github.com/actiontech/sqle
文档 https://actiontech.github.io/sqle-docs/
公布信息 https://github.com/actiontech/sqle/releases
数据审核插件开发文档 https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit…
退出移动版