使用DM将mysql具体表同步到tidb

 

1.创建数据源
将 MySQL-1 的相关信息写入到 /tmp/source1.yaml 中

 

# MySQL1 Configuration.

source-id: "mysql-replica-01"

# DM-worker 是否使用全局事务标识符 (GTID) 拉取 binlog。使用前提是在上游 MySQL 已开启 GTID 模式。
enable-gtid: false

from:
  host: "192.168.1.134"
  user: "tidb_sync"
  password: "mysql"
  port: 13306

密码可以使用加密的密码,执行如下命令生成:
tiup dmctl -encrypt 'mysql'
AdznFiJ14C3cY/4DTszMP0C/0gmDDg==

 

2.在终端中执行下面的命令，使用 tiup dmctl 将 MySQL-1 的数据源配置加载到 DM 集群中
tiup dmctl --master-addr 192.168.1.118:8261 operate-source create /tmp/source1.yaml

这里192.168.1.118:8261 是其中一个dm-master的地址

[root@localhost tidb-data]# tiup dmctl --master-addr 192.168.1.118:8261 operate-source create /tmp/source1.yaml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl --master-addr 192.168.1.118:8261 operate-source create /tmp/source1.yaml
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql-replica-01",
            "worker": "dm-192.168.1.118-8262"
        }
    ]
}

 

3.配置任务
编辑任务配置文件 /tmp/task.yaml
假设需要将 MySQL-1 和 MySQL-2 实例的 test_db 库的 test_table 表以全量+增量的模式迁移到下游 TiDB 的 test_db 库的 test_table 表。

# 任务名,多个同时运行的任务不能重名.
name: "test"
# 全量+增量 (all) 迁移模式。
task-mode: "all"
# 下游 TiDB 配置信息。
target-database:
  host: "192.168.1.118"
  port: 4000
  user: "root"
  password: "mysql"

# 当前数据迁移任务需要的全部上游 MySQL 实例配置。
mysql-instances:
-
  # 上游实例或者复制组 ID，参考 `inventory.ini` 的 `source_id` 或者 `dm-master.toml` 的 `source-id 配置`。
  source-id: "mysql-replica-01"
  # 需要迁移的库名或表名的黑白名单的配置项名称，用于引用全局的黑白名单配置，全局配置见下面的 `block-allow-list` 的配置。
  block-allow-list: "global"          # 如果 DM 版本早于 v2.0.0-beta.2 则使用 black-white-list。
  # dump 处理单元的配置项名称，用于引用全局的 dump 处理单元配置。
  mydumper-config-name: "global"

# 黑白名单全局配置，各实例通过配置项名引用。
block-allow-list:                     # 如果 DM 版本早于 v2.0.0-beta.2 则使用 black-white-list。
  global:
    do-tables:                        # 需要迁移的上游表的白名单。
    - db-name: "db_dm"              # 需要迁移的表的库名。
      tbl-name: "tb_t1"          # 需要迁移的表的名称。

# dump 处理单元全局配置，各实例通过配置项名引用。
mydumpers:
  global:
    extra-args: ""

 

说明:
这里目的端的tidb不需提前创建库和表

 

4.预检查

[root@localhost tidb-data]# tiup dmctl --master-addr 192.168.1.118:8261 check-task /tmp/task.yaml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl --master-addr 192.168.1.118:8261 check-task /tmp/task.yaml
{
    "result": true,
    "msg": "pre-check is passed. "
}

 

5:启动任务

[root@localhost tidb-data]# tiup dmctl --master-addr 192.168.1.118:8261 start-task /tmp/task.yaml
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl --master-addr 192.168.1.118:8261 start-task /tmp/task.yaml
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql-replica-01",
            "worker": "dm-192.168.1.118-8262"
        }
    ],
    "checkResult": "pre-check is passed. "

 

6.查询任务

[root@localhost tidb-data]# tiup dmctl --master-addr 192.168.1.118:8261 query-status
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl --master-addr 192.168.1.118:8261 query-status
{
    "result": true,
    "msg": "",
    "tasks": [
        {
            "taskName": "test",
            "taskStatus": "Running",
            "sources": [
                "mysql-replica-01"
            ]
        }
    ]
}

 

7.停止任务

[root@localhost tidb-data]# tiup dmctl --master-addr 192.168.1.118:8261 stop-task test
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl /root/.tiup/components/dmctl/v6.0.0/dmctl/dmctl --master-addr 192.168.1.118:8261 stop-task test
{
    "op": "Delete",
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql-replica-01",
            "worker": "dm-192.168.1.118-8262"
        }
    ]
}

 

8.验证

登陆到tidb查看数据是否同步过来

[root@localhost opt]# /opt/mysql5729/bin/mysql -h 192.168.1.118 -uroot -pmysql -P4000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 585
Server version: 5.7.25-TiDB-v6.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| db_cdc             |
| db_dm              |
| db_test            |
| db_tidb_sync       |
| dm_meta            |
| mysql              |
| test               |
+--------------------+
10 rows in set (0.00 sec)

mysql> use db_dm;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_db_dm |
+-----------------+
| tb_t1           |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from tb_t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
+----+-------+
3 rows in set (0.07 sec)