5.1.2 DM 同步分库分表 MySQL 到 TiDB 的实践

解决跨业务跨库的数据查询分析
结合 DBA 管理平台提供数据排错查询减少因人为慢查询引起的线上故障
线上仅保留半年数据，数据归档至 TiDB 保留。

有部分的分库分表使用了自增长主键 ID，使用 DM 的自增长主键重算机制，解决了上游分库分表合并到下游单表时的主键冲突问题。要说明的是这个功能还是有所限制，上游在设计自增主键的时候最好还是使用全局自增服务组件来做比较好。

此外 dm 在 loader 恢复时支持断点操作，支持幂等 binlog 重做，不用担心恢复中意外而前功尽弃。

实验环境宿主机的用户名、密码与数据库的用户名、密码一致。

2. 准备工作

第一步：使用 root 账号登录中控机 192.168.128.132 上并安装依赖包

第二步：在中控机上创建 tidb 用户并生成 SSH 密钥

1、创建 tidb 用户

2、为 tidb 用户设置密码

[tidb@dmmaster ~]# echo "password" | passwd --stdin tidb

3、为 tidb 用户设置免密使用 sudo

[tidb@dmmaster ~]# echo "tidb ALL=(ALL) NOPASSWD: ALL" >>/etc/sudoers

4、切换至 tidb 用户 home 目录并生成 SSH 密钥

[tidb@dmmaster ~]# su - tidb
[tidb@dmmaster ~]$ ssh-keygen -t rsa
一路按回车生成密钥

第三步：使用 tidb 用户在中控机下载 DM-Ansible

[tidb@dmmaster ~]$ wget https://download.pingcap.org/dm-ansible-v1.0.2.tar.gz

第四步：安装 DM-Ansible 及其依赖至中控机

[tidb@dmmaster ~]$ tar -xf dm-ansible-v1.0.2.tar.gz
[tidb@dmmaster ~]$ mv dm-ansible-v1.0.2.tar.gz dm-ansible
[tidb@dmmaster ~]$ cd /home/tidb/dm-ansible
[tidb@dmmaster dm-ansible]$ sudo pip install --upgrade pip
[tidb@dmmaster dm-ansible]$ sudo pip install -r ./requirements.txt

第五步: 在中控机上配置 ssh 互信和 sudo 规则

[tidb@dmmaster dm-ansible]$ cat hosts.ini 
[servers]
192.168.128.132
192.168.128.133
[all:vars]
username = tidb
ansible_ssh_port = 22
ntp_server = ntp.aliyun.com

[tidb@dmmaster dm-ansible]$ ansible-playbook -i hosts.ini create_users.yml -u root -k
SSH password: 
PLAY [all] 
********************************************************************
TASK [create user] 
********************************************************************
changed: [192.168.128.133]
TASK [set authorized key] 
********************************************************************
changed: [192.168.128.133]
TASK [update sudoers file] ********************************************************************
changed: [192.168.128.133]
PLAY RECAP
********************************************************************
192.168.128.133    : ok=3    changed=3    unreachable=0    failed=0

第六步：下载 DM 及监控组件安装包至中控机

第七步：上游 MySQL 数据库建立 TiDB 数据迁移专用帐户

root@localhost >grant Reload,Replication slave, Replication client,select on *.* to tidb@'%' IDENTIFIED by 'tidb@2020';

第八步：使用 dmctl 加密上下游数据库登录密码

[tidb@dmmaster bin]$ dmctl -encrypt tidb@2020
BXTTVvKeWhXgAefaFRNoN0BS4XjZ85uZByE=

第一步：编写 inventory.ini 文件

此处我们主要定义 dm-master 和 dm-worker，本章采取单台部署多台 dm-worker。

[tidb@dmmaster dm-ansible]$ cat inventory.ini
## DM modules
[dm_master_servers]
dm_master ansible_host=192.168.128.132
[dm_worker_servers]
dm_worker3306 ansible_host=192.168.128.133 deploy_dir=/data/mysql3306 dm_worker_port=13306 source_id="mysql3306" server_id=13306 mysql_host=192.168.128.131 mysql_user=tidb mysql_password=BXTTVvKeWhXgAefaFRNoN0BS4XjZ85uZByE mysql_port=3306
dm_worker3307 ansible_host=192.168.128.133 deploy_dir=/data/mysql3307 dm_worker_port=13307 source_id="mysql3307" server_id=13307 mysql_host=192.168.128.131 mysql_user=tidb mysql_password=BXTTVvKeWhXgAefaFRNoN0BS4XjZ85uZByE mysql_port=3307
dm_worker3308 ansible_host=192.168.128.133 deploy_dir=/data/mysql3308 dm_worker_port=13308 source_id="mysql3308" server_id=13308 mysql_host=192.168.128.131 mysql_user=tidb mysql_password=BXTTVvKeWhXgAefaFRNoN0BS4XjZ85uZByE mysql_port=3308
[dm_portal_servers]
dm_portal ansible_host=192.168.128.132
## Monitoring modules
[prometheus_servers]
prometheus ansible_host=192.168.128.132
[grafana_servers]
grafana ansible_host=192.168.128.132
[alertmanager_servers]
alertmanager ansible_host=192.168.128.132
## Global variables
[all:vars]
cluster_name = dm-cluster
ansible_user = tidb
ansible_port = 5622
dm_version = v1.0.2
deploy_dir = /home/tidb/deploy
grafana_admin_user = "admin"
grafana_admin_password = "admin"

inventory.ini 文件参数说明
[dm_master_servers]  dm-master 选项，用于定义哪台主机是中控 dm-master
[dm_worker_servers]  dm-worker 选项，用于定义 dm-worker 服务
----dm_worker3306    dm服务全局唯一标签，配合ansible-playbook -l 参数使用
----ansible_host     指定 dm-worker 部署在哪台主机
----dm_worker_port   指定 dm-worker 启动服务端口号
----deploy_dir       指定 dm-worker 部署安装目录
----source_id        指定 dm-worker 的 source-id
----mysql_host       上游MySQL主机地址
----mysql_user       上游MySQL登录用户   
----mysql_port       上游MySQL服务端口
----mysql_password   上游MySQL登录密码(必须dmctl加密后的值,参考2.2章第八步)

第二步：执行安装并启动 dm-worker

安装 dm-worker:

[tidb@dmmaster dm-ansible]$ ansible-playbook deploy.yml
PLAY RECAP **********************************************************************
alertmanager    : ok=13   changed=7    unreachable=0    failed=0   
dm_master       : ok=13   changed=8    unreachable=0    failed=0   
dm_portal       : ok=12   changed=5    unreachable=0    failed=0   
dm_worker3306   : ok=14   changed=2    unreachable=0    failed=0   
dm_worker3307   : ok=14   changed=2    unreachable=0    failed=0   
dm_worker3308   : ok=14   changed=2    unreachable=0    failed=0   
grafana         : ok=17   changed=10   unreachable=0    failed=0   
prometheus      : ok=15   changed=13   unreachable=0    failed=0
#出现以上信息表示部署成功

启动 dm-worker:

[tidb@dmmaster dm-ansible]$ ansible-playbook start.yml 
PLAY RECAP **********************************************************************
alertmanager   : ok=10   changed=1    unreachable=0    failed=0   
dm_master      : ok=10   changed=1    unreachable=0    failed=0   
dm_portal      : ok=9    changed=1    unreachable=0    failed=0   
dm_worker3307  : ok=11   changed=1    unreachable=0    failed=0   
dm_worker3308  : ok=11   changed=1    unreachable=0    failed=0   
grafana        : ok=13   changed=1    unreachable=0    failed=0   
localhost      : ok=4    changed=0    unreachable=0    failed=0   
prometheus     : ok=13   changed=4    unreachable=0    failed=0 
#出现以上信息表示 dm 启动成功，此时已经开始同步上游 binlog 至 dm 机器中。

4. 配置 & 启动 task

上游数据库结构合并至下游 TiDB 说明

上游分库	上游分表	下游合并库名	下游合并表名
shard_db01	shard_tb01	merge_db	merge_tb
shard_db02	shard_tb02
shard_db03	shard_tb03
shard_db04	shard_tb04
shard_db05	shard_tb05
shard_db06	shard_tb06

上游数据库准备

CREATE TABLE shard_tb01~06 (
  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT'主键ID',
  uid bigint(20) NOT NULL COMMENT '用户ID',
  uname varchar(10) NOT NULL DEFAULT '' COMMENT '用户名',
  gender tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别 0-男、1-女',
  shard varchar(50) NOT NULL DEFAULT '' COMMENT '分片信息',
  mobile varchar(15) NOT NULL DEFAULT '' COMMENT '联系电话',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分库分表';
INSERT INTO shard_db01.shard_tb01 
(uid,uname,gender,shard,mobile) VALUES
('10001','tb01001','0','shard_db01_tb01','136******17'),('10002','tb01002','1','shard_db01_tb01','136******17');
INSERT INTO shard_db02.shard_tb02 
(uid,uname,gender,shard,mobile) VALUES ('20001','tb02001','1','shard_db02_tb02','136******33'), ('20002','tb02002','0','shard_db02_tb02','139******63');
INSERT INTO shard_db03.shard_tb03 
(uid,uname,gender,shard,mobile) VALUES ('30001','tb03001','0','shard_db03_tb03','135******73'),
('30002','tb03002','0','shard_db03_tb03','139******46');
INSERT INTO shard_db04.shard_tb04 
(uid,uname,gender,shard,mobile) VALUES ('40001','tb04001','0','shard_db04_tb04','137******91'),('40002','tb04002','1','shard_db04_tb04','138******91');
INSERT INTO shard_db05.shard_tb05 
(uid,uname,gender,shard,mobile) VALUES ('50001','tb05001','1','shard_db05_tb05','158******96'),('50002','tb05002','0','shard_db05_tb05','188******92');
INSERT INTO shard_db06.shard_tb06 
(uid,uname,gender,shard,mobile) VALUES ('60001','tb06001','1','shard_db06_tb06','178******98'),('60002','tb06002','1','shard_db06_tb06','175******31');

合库合表 task 的 yaml 文件

[tidb@tidb-dm-4-0-95 task]$ cat shardmysql_to_tidb.yaml
name: "shard_to_tidb"  #task 名称，必须全局唯一
is-sharding: true      #上游是不是进行了分库分表库
task-mode: "all"       #迁移同步方式 full-全量、incremental-增量、all-全量+增加
meta-schema: "tidb_meta"   #定义下游保留迁移点位信息库名称
remove-meta: false
target-database:                                   
  host: "192.168.206.28" #下游 TiDB 主机 IP
  port: 4000             #TiDB 访问端口
  user: "root"           #TiDB 登录用户
  password: "vLnqQt44rNFHSxA"    #使用 dmctl 加密的登录密码
mysql-instances:
-
  source-id: "mysql3306"   #必须与 inventory.ini 中对应的 source-id 一致
  route-rules: ["rt000","rt001"]      #库表合并规则
  filter-rules: ["ymdd-filter-rule"]  #过滤规则
  mydumper-config-name: "global"
  loader-config-name: "global"
  syncer-config-name: "global"
  black-white-list: "br01"       #白名单列表
  column-mapping-rules: ["cm001"]   #自增主键重计算规则
-
  source-id: "mysql3307"
  route-rules: ["rt000","rt001"]
  filter-rules: ["ymdd-filter-rule"]
  mydumper-config-name: "global"
  loader-config-name: "global"
  syncer-config-name: "global"
  black-white-list: "br01"
  column-mapping-rules: ["cm002"]
-
  source-id: "mysql3308"
  route-rules: ["rt000","rt001"]
  filter-rules: ["ymdd-filter-rule"]
  mydumper-config-name: "global"
  loader-config-name: "global"
  syncer-config-name: "global"
  black-white-list: "br01"
  column-mapping-rules: ["cm003"]
filters:
  ymdd-filter-rule:
    schema-pattern: "shard_db *"
    #以下 2 行定义忽略的 binlog 事件
    events: ["truncate table","delete","drop table","drop database"]
    action: Ignore  
routes:
  rt000:
    #将上游所有 shard_db*匹配的库合并至 merge_db 库
    schema-pattern: "shard_db*"
    target-schema: "merge_db"
  rt001:
    #将上游所有 shard_tb*匹配的分表合并至 merge_db 库的 merge_tb 表中
    schema-pattern: "shard_db*"
    table-pattern: "shard_tb??"
    target-schema: "merge_db"
    target-table:  "merge_tb"
#特别注意上游的自增主键不能有任何业务关系
column-mappings:
  cm001:
    table-pattern: "shard_tb??"
    expression: "partition id"
    source-column: "id"
    target-column: "id"
    arguments: ["1","shard_db","shard_tb"]
  cm002:
    schema-pattern: "shard_db*"
    table-pattern: "shard_tb??"
    expression: "partition id"
    source-column: "id"
    target-column: "id"
    arguments: ["2","shard_db","shard_tb"]
  cm003:
    schema-pattern: "shard_db*"
    table-pattern: "shard_tb??"
    expression: "partition id"
    source-column: "id"
    target-column: "id"
    arguments: ["3","shard_db","shard_tb"]
#黑白名单定义
black-white-list:
  br01:
    do-dbs: ["~shard_db*"]  #需要同步的库
    #需要忽略同步的库
    ignore-dbs: ["mysql","performance_schema","information_schema"]
    #需要忽略同步的哪个库的哪张表
    ignore-tables:
    - db-name: "~shard_db*"
      tbl-name: "~txc_undo_log*"
#以下默认即可 
mydumpers:
  global:
    threads:
    chunk-filesize: 64
    skip-tz-utc: true
    extra-args: " --no-locks "
loaders:
  global:
    pool-size: 64
    dir: "./dumped_data"
syncers:
  global:
    worker-count: 6
    batch: 1000

启动迁移同步任务

先看一下备份目录的文件

[root@dmworker dumped_data.shard_to_tidb]# ll |awk '{print $NF}'
metadata
shard_db01-schema-create.sql
shard_db01.shard_tb01-schema.sql
shard_db01.shard_tb01.sql
shard_db02-schema-create.sql
shard_db02.shard_tb02-schema.sql
shard_db02.shard_tb02.sql

删除备份的数据，必须保留表库结构信息否则会出错

[root@dmworker dumped_data.shard_to_tidb]# ls | grep -v schema | xargs rm -f
[root@dmworker dumped_data.shard_to_tidb]# ls
shard_db01.shard_tb01-schema.sql    shard_db02.shard_tb02-schema.sql 
shard_db02-schema-create.sql

查看上游分库分表数据已迁移到下游 merge_db 库 merge_tb 表

5.1.2.2 DM 常用管理命令

部署命令 deploy.yml

#部署所有inventory.ini中所有选项中的主机服务
[tidb@dmmaster dm-ansible]$ ansible-playbook deploy.yml
#使用-l 参数部署指定标签，如部署 mysql3306 标签的 dm-worker 主机服务
[tidb@dmmaster dm-ansible]$ ansible-playbook deploy.yml -l mysql3306
#使得 --tags 部署指定部署 deploy.yml 中的某个标签任务，如仅部署所有的 dm-worker
[tidb@dmmaster dm-ansible]$ ansible-playbook deploy.yml --tags=dm-worker

dm-worker 启动停止更新命令

#启动 dm 集群，开始自动拉取上游 MySQL 的 binlog 日志
#相当于开启了 MySQL 的 Slave_IO_Running 线程
[tidb@dmmaster dm-ansible]$ ansible-playbook start.yml
#停止 dm 集群，停止拉取上游 MySQL 的 binlog日志
[tidb@dmmaster dm-ansible]$ ansible-playbook stop.yml
#滚动更新 dm 集群
[tidb@dmmaster dm-ansible]$ ansible-playbook rolling_update.yml
*此三个yml命令也可以配合-l、--tags一起使用。

2. dm-worker task 管理

管理 task 需要使用 dmctl 连接上中控机，输入 help 查看所有命令信息

#连接中控
[tidb@dmmaster dm-ansible]$ dmctl -master-addr 192.168.128.132:8261
»help

start-task 命令读取 task 文件启动同步任务，相当于开启 MySQL 的 Slave_SQL_Running 线程

[tidb@dmmaster dm-ansible]$ dmctl -master-addr 192.168.128.132:8261
#启动 task.yaml 配置文件中的所有 dm-worker 数据写入下游库任务
»start-task shard_to_tidb.yaml
#启动 shard_to_tidb.yaml 配置文件中的某个 dm-worker 数据写入下游库任务
#启动 shard_to_tidb.yaml 对应的子任务 192.168.128.133:53307，如下:
»start-task -w '192.168.128.133:53307' shard_to_tidb.yaml

stop-task 命令终止同步任务，相当于停止 MySQL 的 Slave_SQL_Running 线程

[tidb@dmmaster dm-ansible]$ dmctl -master-addr 192.168.128.132:8261
#停止 shard_to_tidb.yaml 配置文件中的所有 dm-worker 数据写入下游库任务
#也可使用-w 参数停止某个指定的任务[可选 -w IP:PORT]
»stop-task shard_to_tidb

query-status 命令查看任务状态，默认显示所有任务状态，可指定任务名查看

[tidb@dmmaster dm-ansible]$ dmctl -master-addr 192.168.128.132:8261
#查看 shard_to_tidb 任务当前状态
»query-status shard_to_tidb

query-error 命令查看任务错误信息，默认显示所有任务的错误信息，可指定任务名查看

skip_sql 跳过正在执行的 SQL 语句

#查看出错的 binlog 位置(failedBinlogPosition)，确定是否可以路过错误
query-error shard_to_tidb
#跳过当前错误的 binlog
sql-skip --worker=192.168.128.133:53307 --binlog-pos=mysql-bin|000001.000003:737983  shard_to_tidb
#恢复继续任务
resume-task --worker=192.168.128.133:53307  shard_to_tidb
query-error shard_to_tidb