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

头像
爱可生开源社区
     上海
    阅读 6 分钟

    作者通过全面系统的测试，揭秘 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...
    mysql
    爱可生开源社区
    426 声望211 粉丝

    成立于 2017 年，以开源高质量的运维工具、日常分享技术干货内容、持续的全国性的社区活动为社区己任；目前开源的产品有：SQL审核工具 SQLE，分布式中间件 DBLE、数据传输组件DTLE。

    « 上一篇
    一个 Kill 不掉的 MySQL 会话
    下一篇 »
    MySQL：我的从库竟是我自己！？

    引用和评论

    推荐阅读
    头像
    如何巧妙解决 Too many connections 报错？

    爱可生开源社区阅读 333

    头像
    Open WebUI：开源AI交互平台的全面解析

    DBLens赞 5阅读 1.8k

    头像
    被 Manus 带火的 MCP 是什么｜一文看懂

    Bytebase赞 3阅读 3.7k

    头像
    MySQL × 向量数据库：大模型时代的黄金组合实战指南

    DBLens赞 5阅读 1.8k

    头像
    百万级群聊的设计实践

    vivo互联网技术赞 1阅读 1.2k

    头像
    分布式数据库解析

    风流倜傥的伤痕赞 1阅读 1.1k

    头像
    MySQL Binlog 深度解析

    DBLens赞 4阅读 731

    0 条评论
    得票最新