文档：
https://docs.pingcap.com/zh/tidb/stable/quick-start-create-source
实验环境
源(MySQL)：10.255.8.122:3306
目标(TiDB)：172.16.1.10:3306
全量加增量模式，忽略 test 库。
注意：如果有多个源实例，server_id 不能一样。
源端：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| archery |
| ceshi |
| czg |
| db |
| information_schema |
| mysql |
| nacos_config |
| performance_schema |
| sys |
| test | #这个库不会被同步到目标端
| test_archery |
+--------------------+
11 rows in set (0.00 sec)
目标端:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| mysql |
| tidb |
+--------------------+
5 rows in set (0.00 sec)
一、创建数据源配置文件
[root@root dm]# cat source-mysql-01.yaml
source-id: "mysql-01" # 数据源 ID，在数据迁移任务配置和 dmctl 命令行中引用该 source-id 可以关联到对应的数据源
from:
host: "10.255.8.122"
port: 3306
user: "test"
password: "123456" # 推荐使用 dmctl 对上游数据源的用户密码加密之后的密码
二、创建数据源
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 operate-source create ./source-mysql-01.yaml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 operate-source create ./source-mysql-01.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262"
}
]
}
三、查看创建的数据源
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 config source mysql-01
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 config source mysql-01
{
"result": true,
"msg": "",
"cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-01\nfrom:\n host: 10.255.8.122\n port: 3306\n user: test\n password: '******'\n max-allowed-packet: null\n session: {}\n security: null\npurge:\n interval: 3600\n expires: 0\n remain-space: 15\nchecker:\n check-enable: true\n backoff-rollback: 5m0s\n backoff-max: 5m0s\n check-interval: 5s\n backoff-min: 1s\n backoff-jitter: true\n backoff-factor: 2\nserver-id: 429555468\ntracer: {}\ncase-sensitive: false\nfilters: []\n"
}
查看所有数据源列表
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 operate-source show
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 operate-source show
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262"
}
]
}
查看DM-worker绑定关系
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 list-member --worker
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 list-member --worker
{
"result": true,
"msg": "",
"members": [
{
"worker": {
"msg": "",
"workers": [
{
"name": "dm-172.16.1.13-8262",
"addr": "172.16.1.13:8262",
"stage": "bound",
"source": "mysql-01"
}
]
}
}
]
}
四、创建迁移任务配置文件
完整版参考:https://docs.pingcap.com/zh/tidb/stable/dm-task-configuration-guide
[root@root dm]# cat dm_task.yml
name: dm_test # 任务名称，需要全局唯一
shard-mode: "pessimistic" # 默认值为 "" 即无需协调。如果为分库分表合并任务，请设置为悲观协调模式 "pessimistic"。在深入了解乐观协调模式的原理和使用限制后，也可以设置为乐观协调模式 "optimistic"
task-mode: all # 任务模式，可设为 "full" - "只进行全量数据迁移"、"incremental" - "Binlog 实时同步"、"all" - "全量 + Binlog 迁移"
# timezone: "UTC" # 指定数据迁移任务时 SQL Session 使用的时区。DM 默认使用目标库的全局时区配置进行数据迁移，并且自动确保同步数据的正确性。使用自定义时区依然可以确保整个流程的正确性，但一般不需要手动指定。
## ******** 数据源配置 **********
mysql-instances:
- source-id: "mysql-01" # 从 source-id = mysql-01 的数据源迁移数据
block-allow-list: "bw-rule-1" # 黑白名单配置名称，如果 DM 版本早于 v2.0.0-beta.2 则使用 black-white-list
#filter-rules: ["filter-rule-1"] # 过滤数据源特定操作的规则，可以配置多个过滤规则
#route-rules: ["route-rule-1", "route-rule-2"] # 数据源表迁移到目标 TiDB 表的路由规则，可以定义多个规则
## ******** 目标 TiDB 配置 **********
target-database: # 目标 TiDB 配置
host: "172.16.1.10"
port: 4000
user: "root"
password: "" # 如果密码不为空，则推荐使用经过 dmctl 加密的密文
## ******** 功能配置 **********
block-allow-list: # 定义数据源迁移表的过滤规则，可以定义多个规则。如果 DM 版本早于 v2.0.0-beta.2 则使用 black-white-list
bw-rule-1: # 规则名称
# do-dbs: ["test.*", "user"] # 迁移哪些库，支持通配符 "*" 和 "?"，do-dbs 和 ignore-dbs 只需要配置一个，如果两者同时配置只有 do-dbs 会生效
ignore-dbs: ["mysql", "test"] # 忽略哪些库，支持通配符 "*" 和 "?"
[root@root dm]#
五、预检
对源端数据库进行预检
tiup dmctl --master-addr 172.16.1.13:8261 check-task dm_task.yml
六、创建复制任务，默认开始
开始前也会预检一次
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 start-task dm_task.yml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 start-task dm_task.yml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262"
}
],
"checkResult": "fail to check synchronization configuration with type: no errors but some warnings
detail: {
"results": [
{
"id": 2,
"name": "mysql_version",
"desc": "check whether mysql version is satisfied",
"state": "warn",
"errors": [
{
"severity": "warn",
"short_error": "version suggested less than 8.0.0 but got 8.0.23"
}
],
"extra": "address of db instance - 10.255.8.122:3306"
}, ....
启动后会看到目标端活跃会话有许多 REPLACE INTO 语句
| 163 | root | 172.16.1.13:33202 | NULL | Query | 0 | autocommit | REPLACE INTO `ceshi`.`t`(`id`,`name`,`age`,`age1`) VALUES(3286220,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 197 | root | 172.16.1.13:33236 | NULL | Query | 0 | autocommit | REPLACE INTO `ceshi`.`t`(`id`,`name`,`age`,`age1`) VALUES(2153269,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 79 | root | 172.16.1.13:33096 | NULL | Sleep | 226 | autocommit | NULL |
| 203 | root | 172.16.1.13:33242 | NULL | Query | 0 | autocommit | REPLACE INTO `ceshi`.`t`(`id`,`name`,`age`,`age1`) VALUES(544439,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 75 | root | 172.16.1.13:33092 | NULL | Sleep | 226 | autocommit | NULL |
| 173 | root | 172.16.1.13:33212 | NULL | Query | 0 | autocommit | REPLACE INTO `ceshi`.`t`(`id`,`name`,`age`,`age1`) VALUES(348718,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 47 | root | 172.16.1.13:33064 | NULL | Sleep | 226 | autocommit | NULL |
| 175 | root | 172.16.1.13:33214 | NULL | Query | 0 | autocommit | REPLACE INTO `ceshi`.`t`(`id`,`name`,`age`,`age1`) VALUES(151671,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 33 | root | 172.16.1.13:33040 | NULL | Sleep | 227 | autocommit | NULL |
| 45 | root | 172.16.1.13:33062 | NULL | Sleep | 226 | autocommit | NULL |
| 127 | root | 172.16.1.13:33162 | NULL | Query | 0 | autocommit | REPLACE INTO `ceshi`.`t`(`id`,`name`,`age`,`age1`) VALUES(2829268,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 65 | root | 172.16.1.13:33082 | NULL | Sleep | 226 | autocommit | NULL |
| 71 | root | 172.16.1.13:33088 | NULL | Sleep | 226 | autocommit | NULL |
| 51 | root | 172.16.1.13:33068 | NULL | Sleep | 226 | autocommit | NULL |
| 81 | root | 172.16.1.13:33098 | NULL | Sleep | 226 | autocommit | NULL |
+------+------+-------------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+
40 rows in set (0.01 sec)
七、查看任务状态是否正常
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 query-status dm_task.yml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 query-status dm_task.yml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"sourceStatus": {
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262",
"result": null,
"relayStatus": null
},
"subTaskStatus": [
{
"name": "dm_test",
"stage": "Running",
"unit": "Sync",
"result": null,
"unresolvedDDLLockID": "",
"sync": {
"totalEvents": "4",
"totalTps": "0",
"recentTps": "0",
"masterBinlog": "(mysql-bin.000059, 7186)",
"masterBinlogGtid": "3981d540-36f6-11ec-ba40-1a6bc9b11968:1-7663",
"syncerBinlog": "(mysql-bin.000059, 7186)",
"syncerBinlogGtid": "3981d540-36f6-11ec-ba40-1a6bc9b11968:1-7663",
"blockingDDLs": [
],
"unresolvedGroups": [
],
"synced": true,
"binlogType": "remote",
"secondsBehindMaster": "0",
"blockDDLOwner": "",
"conflictMsg": "",
"totalRows": "4",
"totalRps": "0",
"recentRps": "0"
},
"validation": null
}
]
}
]
}
八、检查数据同否同步
源端写入测试数据
mysql> use ceshi
Database changed
mysql> insert into t select null,1,1,1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select max(id) from t;
+---------+
| max(id) |
+---------+
| 4652971 |
+---------+
1 row in set (0.00 sec)
查看目标端库列表及数据,发现没有test库，刚才写入的数据已经正常写入
即使源端创建新库，也会同步到目标端。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| archery |
| ceshi |
| czg |
| db |
| dm_meta |
| mysql |
| nacos_config |
| test_archery |
| tidb |
+--------------------+
12 rows in set (0.01 sec)
mysql> select * from t where id=4652971;
+---------+------+------+------+
| id | name | age | age1 |
+---------+------+------+------+
| 4652971 | 1 | 1 | 1 |
+---------+------+------+------+
1 row in set (0.00 sec)
九、暂停数据复制任务
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 pause-task dm_task.yml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 pause-task dm_task.yml
{
"op": "Pause",
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262"
}
]
}
十、恢复同步任务
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 resume-task dm_task.yml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 resume-task dm_task.yml
{
"op": "Resume",
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262"
}
]
}
其它异常处理：
ceshi2_t1表先在目标端 tidb 创建为了聚族表，不支持为自增
mysql> create table ceshi2_t1(id int primary key);
Query OK, 0 rows affected (0.02 sec)
alter table ceshi2_t1 modify id int primary key auto_incremnet);
查询任务状态报错
"subTaskStatus": [
{
"name": "dm_test",
"stage": "Paused",
"unit": "Sync",
"result": {
"isCanceled": false,
"errors": [
{
"ErrCode": 44006,
"ErrClass": "schema-tracker",
"ErrScope": "internal",
"ErrLevel": "high",
"Message": "startLocation: [position: (mysql-bin.000059, 8645), gtid-set: 3981d540-36f6-11ec-ba40-1a6bc9b11968:1-7669], endLocation: [position: (mysql-bin.000059, 8788), gtid-set: 3981d540-36f6-11ec-ba40-1a6bc9b11968:1-7670], origin SQL: [alter table ceshi2_t1 modify id int auto_increment]: cannot track DDL: ALTER TABLE `ceshi2`.`ceshi2_t1` MODIFY COLUMN `id` INT AUTO_INCREMENT",
"RawCause": "[ddl:8200]Unsupported modify column: can't set auto_increment",
"Workaround": "You can use handle-error to replace or skip this DDL."
}
],
https://github.com/pingcap/docs-dm/blob/master/zh/handle-failed-ddl-statements.md
使用 handle-error 跳过这个错误
[root@root dm]# tiup dmctl --master-addr 172.16.1.13:8261 handle-error dm_test skip
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.4.0/dmctl/dmctl --master-addr 172.16.1.13:8261 handle-error dm_test skip
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-01",
"worker": "dm-172.16.1.13-8262"
}
]
}