使用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，日志檢視