MySQL 环境准备与数据库配置
数据集成提供了 MySQL 的读取和写入能力,本文为您介绍使用 MySQL 进行实时数据同步的前置环境配置以及当前能力支持情况。
支持版本
目前数据集成已支持 MySQL 单表及整库级实时读取,使用实时读取能力需遵循以下版本限制:
类型 | 版本 | Driver |
MySQL | 5.6,5.7,8.0.x | JDBC Driver:8.0.21 |
| RDS MySQL | 5.6,5.7, 8.0.x |
| PolarDB MySQL | 5.6,5.7,8.0.x |
| Aurora MySQL | 5.6,5.7,8.0.x |
| MariaDB | 10.x |
| PolarDB X | 2.0.1 |
使用限制
需要开启 Binlog 日志,仅支持同步 MySQL 服务器 Binlog 配置格式为 ROW。
无主键的表由于无法保证 exactly once 可能会有数据重复,因此实时同步任务最好保证有主键。
不支持 XA ROLLBACK,实时同步的任务不会针对 XA PREPARE 的数据进行回滚的操作,若要处理 XA ROLLBACK 场景,需要手动将 XA ROLLBACK 的表从实时同步任务中移除,再添加表后重新进行同步。
设置 MySQL 会话超时:
当为大型数据库制作初始一致快照时,您建立的连接可能会在读取表时超时。您可以通过在 MySQL 配置文件中配置 interactive_timeout 和 wait_timeout 来防止这种行为。
interactive_timeout:服务器在关闭交互式连接之前等待其活动的秒数。请参阅 MySQL :: MySQL 8.0 Reference Manual :: 7.1.8 Server System Variables。
wait_timeout:服务器在关闭非交互式连接之前等待其活动的秒数。请参阅 MySQL :: MySQL 8.0 Reference Manual :: 7.1.8 Server System Variables。
数据库环境准备
确认 MySQL 版本
数据集成对 MySQL 版本有要求,查看当前待同步的 MySQL 是否符合版本要求。您可以在 MySQL 数据库通过如下语句查看当前 MySQL 数据库版本。
select version();
设置 MySQL 服务器权限
您必须定义一个对 Debezium MySQL 连接器监控的所有数据库具有适当权限的 MySQL 用户。
1. 创建 MySQL 用户(可选):
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
2. 向用户授予所需的权限:
在实时数据同步的情况下,该账号必须拥有数据库的 SELECT、REPLICATION SLAVE 和 REPLICATION CLIENT 权限。执行命令可以参考下面:
mysql> GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user' IDENTIFIED BY 'password';
注意:
启用 scan.incremental.snapshot.enabled 时不再需要 RELOAD 权限(默认启用)。
3. 刷新用户的权限:
mysql>FLUSH PRIVILEGES;
开启 MySQL Binlog
1. 检查 binlog 是否开启
show variables like "log_bin"
返回结果为 ON 时,表示已经开启 Binlog, 如果为备库,使用如下语句:
show variables like "log_slave_updates";
如果返回为 ON 时,表示已经开启 Binlog,如果已经开启 Binlog,可跳过下面流程。
2. 开启 Binlog
如果确认没有开启 Binlog,则需要进行以下操作:
对于腾讯云实例 MySQL / TDSQL-C MySQL,默认开启了 binlog。
对于开源 MySQL,参考官方文档开启 binlog。
3. 修改 Binlog 格式为 Row
实时同步仅支持同步 MySQL 服务器 Binlog 配置格式为 ROW,使用如下语句查询 Binlog 的使用格式。
show variables like "binlog_format";
如果返回非 ROW 请修改 Binlog Format。
对于开源 MySQL,参考官方文档:
对于腾讯云实例 MySQL / TDSQL-C MySQL :
登录腾讯云 MySQL / TDSQL-C MySQL 控制台,找到要开启 Binlog 的实例,点击进入该实例的详细信息页面。
在上面选项卡中选择数据库管理,找到参数设置选项卡。
在参数设置选项卡中,找到 binlog_format 参数,将其设置为 “ROW”。
4. binlog_row_image
实时同步仅支持同步 MySQL 服务器 binlog_row_image 配置格式为 FULL or full。
使用如下语句查询 binlog_row_image 的使用格式。
show variables like "binlog_row_image";
如果返回非 FULL/full 请修改 binlog_row_image:
对于开源 MySQ, 参考官方文档:
对于腾讯云实例 MySQL / TDSQL-C MySQL :
登录腾讯云 MySQL / TDSQL-C MySQL 控制台,找到要开启 Binlog 的实例,点击进入该实例的详细信息页面。
在上面选项卡中选择数据库管理,找到参数设置选项卡。
在参数设置选项卡中,找到binlog_row_image参数,将其设置为“FULL”。
开启 GTIDs(可选)
GTID(Global Transaction Identifier, 全局事务标识),用于在 binlog 中唯一标识一个事务,使用 GTID 可以避免事务重复执行导致数据混乱或者主从不一致。
开启流程
1. 检查是否开启了 GTID
show global variables like '%GTID%';
返回结果类似如下,证明已经开启 GTID。
+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| enforce_gtid_consistency | ON || gtid_mode | ON |+--------------------------+-------+
2. 开启 GTID
对于开源 MySQL,参考官方文档 MySQL :: MySQL 8.0 Reference Manual :: 17.1.4.2 Enabling GTID Transactions Online。
对于腾讯云实例 MySQL / TDSQL-C MySQL,默认为开启,不支持关闭。
数据源配置
进入配置数据源界面,MySQL 数据源支持云实例和连接串两种连接方式。
单击 项目管理 > 数据源管理 > 新建数据源 > 选择 MySQL 数据源。
通过连接串创建数据源。
参数 | 说明 |
数据源名称 | 新建的数据源的名称,由用户自定义且不可为空。命名以字母开头,可包含字母、数字、下划线。长度在20字符以内。 |
描述 | 选填,对本数据源的描述。 |
数据源权限 | 项目共享表示当前数据源项目所有成员均可使用 ,仅个人和管理员表示改数据源仅创建人和项目管理员可用。 |
部署方式 | 支持自建实例、公网实例两种部署方式,其中自建实例为在腾讯云服务器上部署的数据源实例,公网实例为在客户本地IDC或其他云上资源实例,支持通过公网进行访问连接。 |
区域与网络 | 当选择自建实例时,需要选择数据源实例所在地域与 vpcID。 |
JDBC URL | 用于连接 MySQL 数据源实例的连接串信息,包含 host ip、port、数据库名称等信息。 |
数据库名称 | 需要连接的数据库名称。 |
用户名 | 连接数据库的用户名称。 |
密码 | 连接数据库的密码。 |
数据连通性 | 测试是否能够连通所配置的数据库。 说明: 若连通性测试不通过,数据源仍可保存。连通性测试未通过而保存但数据源不可使用。 如果连通性测试不通过,可能是因为 WeData 被数据库所在网络防火墙禁止,请参见 添加腾讯云 MySQL 数据库安全组。 |
MySQL 实时单表读取节点配置
1. 在数据集成页面左侧目录栏单击实时同步。
2. 在实时同步页面上方选择单表同步新建(可选择表单和画布模式)并进入配置页面。
3. 单击选择 MySQL 节点并配置节点信息。
4. 参数信息:
参数 | 描述 |
数据来源 | 选择该项目可用的 MySQL 数据源。 |
库 | 支持选择、或者手动输入需读取的库名称。 默认将数据源绑定的数据库作为默认库,其他数据库需手动输入库名称。 当数据源网络不联通导致无法直接拉取库信息时,可手动输入数据库名称。在数据集成网络连通的情况下,仍可进行数据同步。 |
表 | 支持选择、或者手动输入需读取的表名称。 分表情况下,可在 MySQL 源端支持选择或输入多个表名称,多个表需保证结构一致。 分表情况下,支持配置表序号区间。例如'table_[0-99]'表示读取'table_0'、'table_1'、'table_2'直到'table_99' ; 如果您的表数字后缀的长度一致,例如'table_000'、'table_001'、'table_002'直到'table_999',您可以配置为'"table": ["table_00[0-9]", "table_0[10-99]", "table_[100-999]"]' 。 当数据源网络不联通导致无法直接拉取表信息时,可手动输入表名称。在数据集成网络连通的情况下,仍可进行数据同步。 |
添加分库分表 | 适用于分库场景,点击后可配置多个数据源、库及表信息。分库分表场景下需保证所有表结构一致,任务配置将默认展示并使用第一个表结构进行数据获取。 |
分片列 | 分片列用于将表分为多个分片进行同步。有主键的表建议优先选择表主键作为分片列;无主键的表建议选择有索引的列作为分片列,且保证分片列不存在数据的更新操作,否则只能保证At-Least-Once语义。 |
读取模式 | 支持全量+增量和仅增量两种模式。 |
一致性语义 | Exactly-once At-least-once 注意:仅代表读取端的一致性语义。当前版本两种模式状态不兼容,任务提交后如果修改模式,不支持带状态重启。 |
过滤操作 | 设置后将不同步指定操作类型的数据,支持插入、更新和删除。 |
时区 | 设置日志时间所属时区,默认上海。 |
高级设置(选填) | 可根据业务需求配置参数。 |
5. 预览数据字段,单击保存。
MySQL 实时单表写入节点配置
创建 MySQL 节点
1. 在数据集成页面左侧目录栏单击实时同步。
2. 在实时同步页面上方选择单表同步新建(可选择表单和画布模式)并进入配置页面。
3. 单击选择 MySQL 节点并配置节点信息。
4. 参数信息:
参数 | 描述 |
数据去向 | 选择该项目可用的 MySQL 数据源。 |
库 | 选择需要同步的表所在数据库。 |
表 | 支持选择多个表,请保证多表 schema 一致。 |
高级设置 | 根据业务需要选择填写。 |
5. 预览数据字段,单击保存。
注意事项
1. 为每个 Reader 设置一个不同的 SERVER ID。
每一个读取 Binlog 的 MySQL 数据库客户端都应该有一个唯一的 ID,称为 SERVER ID。 MySQL 服务器将使用此 ID 来维护网络连接和 Binlog 位置。因此,如果不同的作业共享相同的服务器 ID,可能会导致从错误的 Binlog 位置读取。 因此,建议通过 SQL Hints ,例如假设源并行度为4,那么我们可以使用
SELECT * FROM source_table /*+ OPTIONS('server-id'='5401-5404') */
; 为 4个 Source Reader 中的每一个分配唯一的服务器 ID。2. 设置 MySQL 会话超时。
当为大型数据库制作初始一致快照时,您建立的连接可能会在读取表时超时。您可以通过在 MySQL 配置文件中配置 interactive_timeout 和 wait_timeout 来防止这种行为。
interactive_timeout:服务器在关闭交互式连接之前等待其活动的秒数。请参阅 MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables。
wait_timeout:服务器在关闭非交互式连接之前等待其活动的秒数。请参阅 MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables。
MySQL 实时整库来源配置
1. 数据集成 > 实时同步 > 整库迁移页面,源端选择 MySQL 类型。
参数 | 说明 |
数据源 | 选择需要同步的 MYSQL/TDSQL-C MySQL 数据源。 |
来源表 | 所有库表:监控数据源下所有库。任务运行期间新增库、表默认将同步至目标端。 指定表:此选项下需指定到具体表名称,设置后任务仅同步指定表;若需要新增同步表需停止并重启任务。 指定库:此选项下需指定具体库名、以表名正则表达式。设置后,任务运行期间符合表名表达式的新增表默认将同步至目标端。 |
读取模式 | 全量 + 增量:数据同步分为全量和增量同步阶段,全量阶段完成后任务进入增量阶段。全量阶段将同步库内历史数据,增量阶段从任务启动后 binlog cdc 的位点开始同步。 增量:仅从任务启动后的 binlog cdc 位点开始同步数据。 |
过滤操作 | 支持插入、更新和删除三种操作,设置后将不同步指定操作类型的数据。 |
锁表 | 开启后系统将在启动和全量同步期间锁定来源表,请确保当前数据库账户已具备锁表权限。 |
时区 | 设置日志时间所属时区,默认上海。 |
高级设置(可选) | 可根据业务需求配置参数。 |
MySQL 整库同步过程中,任务使用主键或指定主键(无主键的情况下,可以在高级设置中进行指定)进行切分,当前支持的主键类型包括:
主键支持范围类型:
TINYINT、TINYINT_UNSIGNED、SMALLINT、SMALLINT_UNSIGNED、INT、MEDIUMINT、INT_UNSIGNED、MEDIUMINT_UNSIGNED、BIGINT、BIGINT_UNSIGNED、FLOAT、DOUBLE、DECIMAL、TIME、DATE、DATETIME、TIMESTAMP、CHAR、VARCHAR、TEXT、BINARY、VARBINARY、BLOB.
MySQL 日志采集写入节点
1. 进入数据集成 > 实时同步 > 日志采集页面,目标端选择 MySQL 类型。
参数 | 说明 |
数据去向 | 选择当前项目中可用的 MySQL 数据源。 |
库 | 选择该数据源中对应的库。 |
表 | 选择该数据源中对应的表。 |
高级设置(可选) | 可根据业务需求配置参数。 |
MySQL 读取/写入数据类型转换支持
读取
MySQL 读取支持的数据类型及转换对应关系如下(在处理 MySQL 时,会先将 MySQL 数据源的数据类型和数据处理引擎的数据类型做映射)
字段类型 | 是否支持 | 内部映射字段 | 备注 |
TINYINT | 是 | TINYINT | TINYINT(1) 映射到 BOOLEAN 需要增加选项支持 TINYINT(1) 可以映射到 bool 或者 tinyint |
SMALLINT | 是 | SMALLINT | - |
TINYINT_UNSIGNED | 是 | SMALLINT | - |
TINYINT_UNSIGNED_ZEROFILL | 是 | SMALLINT | - |
INT | 是 | INT | - |
INTEGER | 是 | INT | - |
YEAR | 是 | INT | - |
MEDIUMINT | 是 | INT | - |
SMALLINT_UNSIGNED | 是 | INT | - |
SMALLINT_UNSIGNED_ZEROFILL | 是 | INT | - |
BIGINT | 是 | LONG | - |
INT_UNSIGNED | 是 | LONG | - |
MEDIUMINT_UNSIGNED | 是 | LONG | - |
MEDIUMINT_UNSIGNED_ZEROFILL | 是 | LONG | - |
INT_UNSIGNED_ZEROFILL | 是 | LONG | - |
BIGINT_UNSIGNED | 是 | DECIMAL | DECIMAL(20,0) |
BIGINT_UNSIGNED_ZEROFILL | 是 | DECIMAL | DECIMAL(20,0) |
SERIAL | 是 | DECIMAL | DECIMAL(20,0) |
FLOAT | 是 | FLOAT | - |
FLOAT_UNSIGNED | 是 | FLOAT | - |
FLOAT_UNSIGNED_ZEROFILL | 是 | FLOAT | - |
DOUBLE | 是 | DOUBLE | - |
DOUBLE_UNSIGNED | 是 | DOUBLE | - |
DOUBLE_UNSIGNED_ZEROFILL | 是 | DOUBLE | - |
DOUBLE_PRECISION | 是 | DOUBLE | - |
DOUBLE_PRECISION_UNSIGNED | 是 | DOUBLE | - |
ZEROFILL | 是 | DOUBLE | - |
REAL | 是 | DOUBLE | - |
REAL_UNSIGNED | 是 | DOUBLE | - |
REAL_UNSIGNED_ZEROFILL | 是 | DOUBLE | - |
NUMERIC | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
NUMERIC_UNSIGNED | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
NUMERIC_UNSIGNED_ZEROFILL | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
DECIMAL | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
DECIMAL_UNSIGNED | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
DECIMAL_UNSIGNED_ZEROFILL | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
FIXED | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
FIXED_UNSIGNED | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
FIXED_UNSIGNED_ZEROFILL | 是 | DECIMAL | 采用用户数据库实际的精度 p<=38 映射到 DECIMAL 38 < p <= 65 时映射到 String |
BOOLEAN | 是 | BOOLEAN | - |
DATE | 是 | DATE | - |
TIME | 是 | TIME | - |
DATETIME | 是 | TIMESTAMP | - |
TIMESTAMP | 是 | TIMESTAMP | - |
CHAR | 是 | STRING | - |
JSON | 是 | STRING | - |
BIT | 是 | STRING | BIT(1) 映射到 BOOLEAN |
VARCHAR | 是 | STRING | - |
TEXT | 是 | STRING | - |
BLOB | 是 | STRING | - |
TINYBLOB | 是 | STRING | - |
TINYTEXT | 是 | STRING | - |
MEDIUMBLOB | 是 | STRING | - |
MEDIUMTEXT | 是 | STRING | - |
LONGBLOB | 是 | STRING | - |
LONGTEXT | 是 | STRING | - |
VARBINARY | 是 | STRING | - |
GEOMETRY | 是 | STRING | - |
POINT | 是 | STRING | - |
LINESTRING | 是 | STRING | - |
POLYGON | 是 | STRING | - |
MULTIPOINT | 是 | STRING | - |
MULTILINESTRING | 是 | STRING | - |
MULTIPOLYGON | 是 | STRING | - |
GEOMETRYCOLLECTION | 是 | STRING | - |
ENUM | 是 | STRING | - |
BINARY | 是 | BINARY | BINARY(1) |
SET | 否 | | - |
写入
MySQL 写入支持的数据类型及转换对应关系如下:
内部类型 | MySQL 类型 |
TINYINT | TINYINT |
SMALLINT | SMALLINT,TINYINT UNSIGNED |
INT | INT,MEDIUMINT,SMALLINT UNSIGNED |
BIGINT | BIGINT,INT UNSIGNED |
DECIMAL(20, 0) | BIGINT UNSIGNED |
FLOAT | FLOAT |
DOUBLE | DOUBLE,DOUBLE PRECISION |
DECIMAL(p, s) | NUMERIC(p, s),DECIMAL(p, s) |
BOOLEAN | BOOLEAN,TINYINT(1) |
DATE | DATE |
TIME [(p)][WITHOUT TIMEZONE] | TIME [(p)] |
TIMESTAMP [(p)][WITHOUT TIMEZONE] | DATETIME [(p)] |
STRING | CHAR(n),VARCHAR(n),TEXT |
BYTES | BINARY,VARBINARY,BLOB |
ARRAY | - |
常见问题
1. MySql serverid 冲突
错误信息:
com.github.shyiko.mysql.binlog.network.ServerException: A slave with the same server_uuid/server_id as this slave has connected to the master。
解决办法:目前已经优化增加随机生成 serverid,之前的任务中如果在 mysql 高级参数中显示指定了 server-id 建议删除,因为可能多个任务使用了相同的数据源,并且 server-id 设置的相同导致冲突。
2. 报 binlog 文件找不到错误信息:
错误信息:
Caused by: org.apache.kafka.connect.errors.ConnectException: The connector is trying to read binlog starting at GTIDs xxx and binlog file 'binlog.xxx', pos=xxx, skipping 4 events plus 1 rows, but this is no longer available on the server. Reconfigure the connector to use a snapshot when needed。
错误原因:
作业正在读取的 binlog 文件在 MySQL 服务器已经被清理时,会产生报错。导致 Binlog 清理的原因较多,可能是 Binlog 保留时间设置的过短;或者作业处理的速度追不上 Binlog 产生的速度,超过了 MySQL Binlog 文件的最大保留时间,MySQL 服务器上的 Binlog 文件被清理,导致正在读的 Binlog 位点变得无效。
解决办法:如果作业处理速度无法追上 Binlog 产生速度,可以考虑增加 Binlog 的保留时间也可以优化作业减轻反压来加速 source 消费。如果作业状态没有异常,可能是数据库发生了其他操作导致 Binlog 被清理,从而无法访问,需要结合 MySQL 数据库侧的信息来确定 Binlog 被清理的原因。
3. MySQL 报连接被重置
错误信息:
EventDataDeserializationException: Failed to deserialize data of EventHeaderV4 .... Caused by: java.net.SocketException: Connection reset。
错误原因:
1. 网络问题。
2. 作业存在反压,导致 source 无法读取数据,binlog client 空闲,如果 binlog 连接在超时后仍然空闲 mysql 服务器会断开空闲的连接。
解决方法:
1. 如果是网络问题,可以调大 mysql 网络参数 set global slave_net_timeout = 120; (默认30s) set global thread_pool_idle_timeout = 120。
2. 如果是作业反压导致,可以通过调节作业减轻反压,例如增加并行度,提升写入速度,提升 taskmanager 内存减少 gc。
4. Mysql2dlc 任务 JobManager Oom
错误信息:
错误原因和解决方法:
1. 用户数据量比较大,可以调大 jobmanager CU 数,使用 mysql 高级参数 scan.incremental.snapshot.chunk.size 调大 chunk size 大小,默认是8096。
2. 用户数据量不大,但是主键最大值-最小值的差值却很大,导致使用均分 chunk 的策略时划分很多 chunk,修改分布因子,让用户数据走非均匀的数据切分逻辑,split-key.even-distribution.factor.upper-bound=5.0d,默认值分布因子已经修改为10.0d。
5. 用户的 binlog 数据格式不对,导致 debezium 解析异常
错误信息:
ERROR io.debezium.connector.mysql.MySqlStreamingChangeEventSource [] - Error during binlog processing. Last offset stored = null, binlog reader near position = mysql-bin.000044/211839464.2023-02-20 21:37:28.480 [blc-172.17.48.3:3306] ERROR io.debezium.pipeline.ErrorHandler [] - Producer failureio.debezium.DebeziumException: Error processing binlog event.
解决方法:
修改 binlog_row_image=full 后需要重启数据库。
6. 是否支持 gh-ost?
支持,不会迁移 Online DDL 变更产生的临时表数据,只迁移源库使用 gh-ost 执行的原始 DDL 数据,同时您可以使用默认的或者自行配置 gh-ost 影子表和无用表的正则表达式。