本小节的实践目标是将 单机 MySQL 实例数据同步到 TiDB 集群。
主要包括以下8个小节的内容，所有提到的参数配置及说明基于 DM 1.0.2版本
全量&增量同步数据
DM 以一个集群为单位运行，包括以下5个组成部分：
| 组件 | 主机 | 端口号 |
|---|---|---|
| DM-Master | 172.16.10.71 | 8261 |
| DM-worker | 172.16.10.72 | 8262 |
| 上游MariaDB | 172.16.10.81 | 3306 |
| 下游TiDB的计算节点 | 172.16.10.83 | 4000 |
[dm_worker_servers]
dm-worker1 ansible_host=1.1.1.1 source_id="mariadb-01" server_id=101 mysql_host=172.16.10.81 mysql_user=tidbdm mysql_password="encryptpwd" mysql_port=3306
下游TiDB 集群部署及读写访问授权
task 文件决定 DM-Worker 按照怎样的规格同步数据，主要有以下9个区域：
name: "taskname" # 全局唯一的 task 名称
task-mode: all # 同步模式，这里选全量
meta-schema: "dm_meta" # checkpoint 信息存储在下游的数据库名
remove-meta: false # 是否在任务同步开始前移除该任务名对应的 checkpoint 信息，删除会重新开始同步，不删除会从上次停止的位置开始同步
target-database，下游 TiDB 集群地址用户密码，密码与 DM-Worker 配置里的密码相同
mysql-instances，上游 MySQL 实例 source-id 及同步规则模块名称
source-id: "mariadb-01" # dm-worker 定义的 source-id 对应
route-rules: ["book-route-rules-schema", "book-route-rules"] # 需要同步的对应的库表配置名称
filter-rules: ["book-filter-1"] # 需要过滤的 binlog event 配置名称
black-white-list: "bookblack" # 需要过滤的库表配置名称
mydumper-config-name: "global" # mydumper 配置名称
mydumper-thread: 4
loader-config-name: "global" # loader 配置名称
loader-thread: 8
syncer-config-name: "global" # syncer 配置名称
syncer-thread: 9
book-route-rules-schema: # mysql-instances 部分定义的配置名称
schema-pattern: "book"
target-schema: "book"
book-route-rules:
schema-pattern: "book"
table-pattern: "session"
target-schema: "book"
target-table: "session"
book-filter-1:
schema-pattern: "book"
table-pattern: "session"
events: ["truncate table", "drop table"]
action: Ignore
bookblack:
do-dbs: ["~^book.*"]
ignore-dbs: ["mysql", "performance_schema", "percona", "information_schema"]
ignore-tables:
- db-name: "book.*"
tbl-name: "draft"
global:
mydumper-path: "./bin/mydumper"
threads: 4
chunk-filesize: 64
skip-tz-utc: true
global:
pool-size: 8
dir: "./dumped_data"
global:
worker-count: 8
batch: 100
task 配置完成，通过 dmctl 工具检查执行同步
./dmctl -master-addr 172.16.10.71:8261
» check-task task-path
{
"result": true,
"msg": "check pass!!!"
}
» start-task task-path
{
"result": true,
"msg": "",
"workers": [
{
"result": true,
"worker": "172.16.10.72:8262",
"msg": ""
},
]
}
query-status taskname
query-error taskname
start-task taskname
举个例子
"msg": "[code=44003:class=schema-tracker:scope=downstream:level=high] current pos (mysql-bin.000010, 814332497): failed to create table for `db_1`.`tb_1` in schema tracker: [types:1067]Invalid default value for 'expire_time'
expire_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
TiDB> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+