使用DM迁移MySQL数据到TIDB小测试

作者：luyayao​

一、DM部署简介

DM-Ansible和Binary都可以部署
TIUP部署的方式更加便捷 和ansible方式都需要联网。

TIDB version: TiDB-v4.0.3
MYSQL version: 5.7.26-log MySQL Community Server (GPL)

1， 安装依赖包

yum -y install epel-release git curl sshpass

wget ​​https://bootstrap.pypa.io/pip/2.7/get-pip.py​​

python get-pip.py

wget ​​https://download.pingcap.org/dm-ansible-v2.0.0.tar.gz​​

[tidb@luyy01 ~]$ tar -zxvf dm-ansible-v2.0.0.tar.gz

[tidb@luyy01 ~]$ mv dm-ansible-v2.0.0 dm-ansible

[tidb@luyy01 ~]$sudo pip install -r ./requirements.txt
有版本错误提示的需单独安装或升级包

2，在中控机上配置 SSH 互信和 sudo 规则

cd /home/tidb/dm-ansible

vi hosts.ini

[servers]

192.168.199.62

192.168.199.63

192.168.199.64

[all:vars]

username = tidb

[tidb@luyy01 dm-ansible]$ ansible-playbook -i hosts.ini create_users.yml -u root -k

SSH password:

3，下载 DM 及监控组件安装包至中控机

ansible-playbook local_prepare.yml

4，使用 dmctl 加密上游 MySQL 用户密码

cd /home/tidb/dm-ansible/resources/bin && ./dmctl -encrypt ‘abc!@#123’

5，编辑 ​​inventory.ini​​ 配置文件

### DM modules

[dm_master_servers]

dm_master ansible_host=192.168.199.61 dm_master_port=18261

[dm_worker_servers]

dm-worker1 ansible_host=192.168.199.62 dm_worker_port=18262 source_id="mysql-replica-01" mysql_host=192.168.199.61 mysql_user=admin mysql_password='YUZP1pa88fWbIzudw3bDTEBF6ZUTnw==' mysql_port=3306

[dm_portal_servers]

dm_portal ansible_host=192.168.199.61

### Monitoring modules

[prometheus_servers]

prometheus ansible_host=192.168.199.63

[grafana_servers]

grafana ansible_host=192.168.199.63

[alertmanager_servers]

alertmanager ansible_host=192.168.199.63

### Global variables

[all:vars]

cluster_name = test-cluster

ansible_user = tidb

dm_version = v2.0.0

deploy_dir = /home/tidb/deploy

grafana_admin_user = "admin"

grafana_admin_password = "admin"

6， 修改内核参数，并部署 DM 集群组件和监控组件

ansible-playbook deploy.yml

7，启动DM集群

ansible-playbook start.yml

二、 DM迁移数据

实例	主机	用户名	密码	端口
上游 MySQL	192.168.199.61	admin	admin	3306
下游 TiDB	192.168.199.62/63/64	root		4000

1，dm-master 进程配置

cat /home/tidb/deploy/conf/dm-master.yaml

server-id: 101

source-id: mysql-replica-01

flavor: mysql

enable-relay: false

enable-gtid: false

from:

host: 192.168.199.61

user: admin

password: YUZP1pa88fWbIzudw3bDTEBF6ZUTnw==

port: 3306

2，配置任务

在dm-master上使用dm-portal自动生成task配置文件登录网页​​http://192.168.199.61:8280/​​

也可手动修改配置文件

 vi luyayao.yaml

name: luyayao
task-mode: all
is-sharding: false
clean-dump-file: true
ignore-checking-items: [schema_of_shard_tables]   
 #任务过程中存在表一致性的报错，加上即可忽略

target-database:
  host: 192.168.199.63
  port: 4000
  user: root
  password: KyMi25sGljNJPVL8OV3JrEA=
mysql-instances:
- source-id: mysql-replica-01
  meta:
    binlog-name: mysql-bin.0000001
    binlog-pos: 4
  filter-rules: []
  route-rules:
  - mysql-replica-01.route_rules.1
  - mysql-replica-01.route_rules.2
  black-white-list: mysql-replica-01.bw_list.1
  mydumper-config-name: mysql-replica-01.dump
routes:
  mysql-replica-01.route_rules.1:
    schema-pattern: employees
    table-pattern: ""
    target-schema: employees
    target-table: ""
  mysql-replica-01.route_rules.2:
    schema-pattern: employees
    table-pattern: "*"                   
    target-schema: employees   
 #简单测试  源端所有表用* ，目标表不知道用什么对应就没写  可以正常在TIDB中创建了库和表

filters: {}
black-white-list:
  mysql-replica-01.bw_list.1:
    do-tables:
    - db-name: employees
      tbl-name: "*"
    do-dbs: []
    ignore-tables: []
    ignore-dbs: []
mydumpers:
  mysql-replica-01.dump:
    mydumper-path: bin/mydumper
    threads: 4
    chunk-filesize: 64
    skip-tz-utc: true
    extra-args: "--no-locks"

3，进入交互窗口

./dmctl -master-addr 192.168.199.61:18261

operate-source create /home/tidb/deploy/conf/dm-master.yaml

4，开始任务

start-task -s mysql-replica-01 /home/tidb/deploy/conf/luyayao.yaml

 #查询任务运行状态
» query-status
{
    "result": true,
    "msg": "",
    "tasks": [
        {
            "taskName": "luyayao",
            "taskStatus": "Running",
            "sources": [
                "mysql-replica-01"
            ]
        }
    ]
}

5，日志查看

ps -ef|grep dm 可查看进程中dm-master 和 dm-work中的日志