国产数据库|教你快速上手TiDB DM数据迁移工具并实现MySQL数据迁移同步

TiDB Data Migration (DM) 是一体化的数据迁移任务管理工具，支持从与 MySQL 协议兼容的数据库（MySQL、MariaDB、Aurora MySQL）到 TiDB 的数据迁移，支持全量的数据载入和增量的数据传输，同时可以进行表与操作的过滤，并且可以进行分库分表的合并迁移，有利于简化数据迁移过程，降低数据迁移运维成本。

2、原理架构

DM 主要包括三个组件：DM-master，DM-worker 和 dmctl。

DM-master 负责管理和调度数据迁移任务的各项操作。

DM-worker 负责执行具体的数据迁移任务。

dmctl 是用来控制 DM 集群的命令行工具。

3、适用场景

从兼容 MySQL 的单一实例中全量和增量迁移数据到 TiDB
将小数据量（小于 1 TB）分库分表 MySQL 合并迁移数据到 TiDB
在业务数据中台、业务数据实时汇聚等数据中枢场景中，作为数据同步中间件来使用

4、快速上手操作

第1步部署DM集群

（1）安装 TiUP 工具并通过 TiUP 快速部署 dmctl，

安装tiup
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
安装dmctl
tiup install dm dmctl
查看版本
tiup dmctl --version
tiup dmctl:7.5.1 --version

（2）生成 DM 集群最小拓扑文件，即配置文件模板。

tiup dm template

（3）复制输出的配置信息，修改 IP 地址后保存为 topology.yaml 文件，使用 TiUP 部署 DM 集群。

tiup dm deploy dm-test 7.5.1 topology.yaml -p --指定版本为7.5.1

# 修改后的 topology.yaml 文件内容如下：
# The topology template is used deploy a minimal DM cluster, which suitable
# for scenarios with only three machinescontains. The minimal cluster contains
# - 3 master nodes
# - 3 worker nodes
# You can change the hosts according your environment
---
global:
  user: "tidb"
  # systemd_mode: "system"
  ssh_port: 22
  deploy_dir: "/home/tidb/dm/deploy"
  data_dir: "/home/tidb/dm/data"
  # arch: "amd64"


master_servers:
  - host: 192.168.198.133


worker_servers:
  - host: 192.168.198.133


monitoring_servers:
  - host: 192.168.198.133


grafana_servers:
  - host: 192.168.198.133


alertmanager_servers:
  - host: 192.168.198.133

tiup dm list

tiup dm start dm-test

tiup dm display dm-test

第2步准备数据源

（1）使用一个或多个 MySQL 实例作为上游数据源。为每一个数据源编写如下mysql-01.yaml 配置文件：

[root@tidb ~]# cat mysql-01.yaml 
source-id: "mysql-01"


from:
  host: "127.0.0.1"
  user: "root"
  password: "V6MZRIs3HM2JwEdL+nRqd0Cj0LHz+Q=="  # 使用 tiup dmctl --encrypt "123456" 加密。
  port: 3306
  
 # 加密命令，要写上版本号
 tiup dmctl:v7.5.1 encrypt '123456'

（2）使用如下命令将数据源增加至 DM 集群。其中，mysql-01.yaml 是上一步编写的配置文件。

tiup dmctl --master-addr=192.168.198.133:8261 operate-source create mysql-01.yaml 
# --master-addr 填写 master_servers 其中之一。

如果没有部署MySQL环境，可以通过以下步骤快速部署

--在mysql官方网站下载5.7版本安装包
--卸载mariadb软件包
--新建配置文件
[root@tidb ~]# cat etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server-id=1001
user=mysql
port=3306
log-bin=/usr/local/mysql/data/mysql-bin


--安装配置MySQL
$> groupadd mysql
$> useradd -r -g mysql -s bin/false mysql
$> cd usr/local
$> tar zxvf mysql-5.7.37-el7-x86_64.tar.gz
$> ln -s mysql-5.7.37-el7-x86_64 mysql
$> cd mysql
$> mkdir mysql,-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
$> bin/mysqld --initialize --user=mysql
$> bin/mysql_ssl_rsa_setup
$> bin/mysqld_safe --user=mysql &
# Next command is optional
$> cp support-files/mysql.server etc/init.d/mysql.server


--杀掉MySQL进程，通过服务启动
systemctl  status mysql.server
systemctl  start mysql.server

第 3 步：准备下游数据库

可以选择已存在的 TiDB 集群作为数据同步目标。

TiDB集群安装步骤参考《国产数据库|TiDB 7.5 实验测试环境搭建及小插曲处理》

第 4 步：准备测试数据

在一个或多个数据源中创建测试表和数据。如果你使用已存在的 MySQL 数据库，且数据库中已有可用数据，可跳过这一步。

drop database if exists `testdm`;
create database `testdm`;
use `testdm`;
create table t1 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
create table t2 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into t1 (id, uid, name) values (1, 10001, 'Gabriel García Márquez'), (2, 10002, 'Cien años de soledad');
insert into t2 (id, uid, name) values (3, 20001, 'José Arcadio Buendía'), (4, 20002, 'Úrsula Iguarán'), (5, 20003, 'José Arcadio');


mysql> select * from t1;
+----+-------+--------------------------+------+
| id | uid   | name                     | info |
+----+-------+--------------------------+------+
|  1 | 10001 | Gabriel García Márquez   | NULL |
|  2 | 10002 | Cien años de soledad     | NULL |
+----+-------+--------------------------+------+
2 rows in set (0.00 sec)


mysql> select * from t2;
+----+-------+------------------------+------+
| id | uid   | name                   | info |
+----+-------+------------------------+------+
|  3 | 20001 | José Arcadio Buendía   | NULL |
|  4 | 20002 | Úrsula Iguarán         | NULL |
|  5 | 20003 | José Arcadio           | NULL |
+----+-------+------------------------+------+
3 rows in set (0.00 sec)

第 5 步：编写数据同步任务

（1）创建任务的配置文件 testdm-task.yaml：

[root@tidb ~]# cat testdm-task.yaml 
name: testdm
task-mode: all


target-database:
  host: "192.168.198.133"
  port: 4000
  user: "root"
  password: "E9F7OhWFcYuzuqMxy/h0c0BLrYiwH5XbB1fS7kCf4kJCymg=" 
# 如果密码不为空，则推荐使用经过 ( tiup dmctl:v7.5.1 encrypt '123456') 加密的密文


# 填写一个或多个所需同步的数据源信息
mysql-instances:
  - source-id: "mysql-01"
    block-allow-list:  "ba-rule1"


block-allow-list:
  ba-rule1:
    do-dbs: ["testdm"]

（2）使用 dmctl 创建任务：

[root@tidb ~]# tiup dmctl --master-addr 192.168.198.133:8261 start-task testdm-task.yaml
Starting component dmctl: /root/.tiup/components/dmctl/v8.0.0/dmctl/dmctl --master-addr 192.168.198.133 start-task testdm-task.yaml
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql-01",
            "worker": "dm-192.168.198.133-8262"
        }
    ],
    "checkResult": "pre-check is passed. "
}

这样就成功创建了一个将 mysql-01 数据源迁移到 TiDB 的任务。

第 6 步：查看迁移任务状态

在创建迁移任务之后，可以用 dmctl query-status 来查看任务的状态。

tiup dmctl --master-addr 192.168.198.133:8261 query-status testdm




[root@tidb ~]# tiup dmctl --master-addr 127.0.0.1:8261 query-status testdm
Starting component dmctl: /root/.tiup/components/dmctl/v8.0.0/dmctl/dmctl --master-addr 127.0.0.1:8261 query-status testdm
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "sourceStatus": {
                "source": "mysql-01",
                "worker": "dm-192.168.198.133-8262",
                "result": null,
                "relayStatus": null
            },
            "subTaskStatus": [
                {
                    "name": "testdm",
                    "stage": "Running",
                    "unit": "Sync",
                    "result": null,
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "0",
                        "totalTps": "0",
                        "recentTps": "0",
                        "masterBinlog": "(mysql-bin.000001, 154)",
                        "masterBinlogGtid": "",
                        "syncerBinlog": "(mysql-bin.000001, 154)",
                        "syncerBinlogGtid": "",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": true,
                        "binlogType": "remote",
                        "secondsBehindMaster": "0",
                        "blockDDLOwner": "",
                        "conflictMsg": "",
                        "totalRows": "0",
                        "totalRps": "0",
                        "recentRps": "0"
                    },
                    "validation": null
                }
            ]
        }
    ]
}

5、常用命令

（1）查看任务状态

tiup dmctl --master-addr 127.0.0.1:8261 query-status testdm

（2）暂停任务

tiup dmctl --master-addr 127.0.0.1:8261 parse-task testdm

（3）重启任务

tiup dmctl --master-addr 127.0.0.1:8261 resume-task testdm

（4）关闭任务

tiup dmctl --master-addr 127.0.0.1:8261 stop-task testdm

（6）查看 TiUP 管理的集群情况

[root@tidb ~]# tiup dm list
Starting component dm: /root/.tiup/components/dm/v1.15.1/tiup-dm list
Name     User  Version  Path                                     PrivateKey
----     ----  -------  ----                                     ----------
dm-test  tidb  v7.5.1   /root/.tiup/storage/dm/clusters/dm-test  /root/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa

（7）检查部署的 DM 集群情况

预期输出包括 dm-test 集群中实例 ID、角色、主机、监听端口和状态（由于还未启动，所以状态为 Down/inactive）、目录信息。[root@tidb ~]# tiup dm display dm-test
Starting component dm: /root/.tiup/components/dm/v1.15.1/tiup-dm display dm-test
Cluster type:       dm
Cluster name:       dm-test
Cluster version:    v7.5.1
Deploy user:        tidb
SSH type:           builtin
Grafana URL:        http://192.168.198.133:3000
ID                    Role          Host             Ports      OS/Arch       Status     Data Dir                              Deploy Dir
--                    ----          ----             -----      -------       ------     --------                              ----------
192.168.198.133:9093  alertmanager  192.168.198.133  9093/9094  linux/x86_64  Up         /home/tidb/dm/data/alertmanager-9093  /home/tidb/dm/deploy/alertmanager-9093
192.168.198.133:8261  dm-master     192.168.198.133  8261/8291  linux/x86_64  Healthy|L  /home/tidb/dm/data/dm-master-8261     /home/tidb/dm/deploy/dm-master-8261
192.168.198.133:8262  dm-worker     192.168.198.133  8262       linux/x86_64  Bound      /home/tidb/dm/data/dm-worker-8262     /home/tidb/dm/deploy/dm-worker-8262
192.168.198.133:3000  grafana       192.168.198.133  3000       linux/x86_64  Up         -                                     /home/tidb/dm/deploy/grafana-3000
192.168.198.133:9090  prometheus    192.168.198.133  9090       linux/x86_64  Up         /home/tidb/dm/data/prometheus-9090    /home/tidb/dm/deploy/prometheus-9090

（8）关闭集群

[root@tidb ~]# tiup dm stop dm-test
Starting component dm: /root/.tiup/components/dm/v1.15.1/tiup-dm stop dm-test
Will stop the cluster dm-test with nodes: , roles: .
Do you want to continue? [y/N]:(default=N) y
+ [ Serial ] - SSHKeySet: privateKey=/root/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa, publicKey=/root/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa.pub
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [ Serial ] - StopCluster
Stopping component alertmanager
    Stopping instance 192.168.198.133
    Stop alertmanager 192.168.198.133:9093 success
Stopping component grafana
    Stopping instance 192.168.198.133
    Stop grafana 192.168.198.133:3000 success
Stopping component prometheus
    Stopping instance 192.168.198.133
    Stop prometheus 192.168.198.133:9090 success
Stopping component dm-worker
    Stopping instance 192.168.198.133
    Stop dm-worker 192.168.198.133:8262 success
Stopping component dm-master
    Stopping instance 192.168.198.133
    Stop dm-master 192.168.198.133:8261 success
Stopped cluster `dm-test` successfully

（9）启动集群

[root@tidb ~]# tiup dm start dm-test
Starting component dm: /root/.tiup/components/dm/v1.15.1/tiup-dm start dm-test
Starting cluster dm-test...
+ [ Serial ] - SSHKeySet: privateKey=/root/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa, publicKey=/root/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa.pub
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [Parallel] - UserSSH: user=tidb, host=192.168.198.133
+ [ Serial ] - StartCluster
Starting component dm-master
    Starting instance 192.168.198.133:8261
    Start instance 192.168.198.133:8261 success
Starting component dm-worker
    Starting instance 192.168.198.133:8262
    Start instance 192.168.198.133:8262 success
Starting component prometheus
    Starting instance 192.168.198.133:9090
    Start instance 192.168.198.133:9090 success
Starting component grafana
    Starting instance 192.168.198.133:3000
    Start instance 192.168.198.133:3000 success
Starting component alertmanager
    Starting instance 192.168.198.133:9093
    Start instance 192.168.198.133:9093 success
Started cluster `dm-test` successfully

（10）删除DM集群

tiup dm destroy dm-test

6、登录上游数据库，进行数据插入，并在下游目标库验证数据是否收到。

[root@tidb ~]# mysql -h 192.168.198.133 -P 4000 -u root -p

7、参考资料

https://docs.pingcap.com/zh/tidb/stable/dm-overview

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

国产数据库|教你快速上手TiDB DM数据迁移工具并实现MySQL数据迁移同步

评论