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

5.1.2.1 DM 分库分表安装部署实战

本实战模拟企业生产环境阿里云 DRDS 中间件对业务表进行分库分表后，这边使用 DM 工具将线上分库分表数据同步至 TiDB 中：

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

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

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

1. 环境说明

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

主机 IP 操作系统 应用部署 说明 帐号密码
192.168.128.131 centos7.3 x86_64 MySQL5.7 3306 端口 root/password
192.168.128.131 centos7.3 x86_64 MySQL5.7 3307 端口 root/password
192.168.128.131 centos7.3 x86_64 MySQL5.7 3308 端口 root/password
192.168.128.132 centos7.3 x86_64 dm-master/dmctl 中控机 root/password
192.168.128.133 centos7.3 x86_64 dm-worker dm-worker root/password
192.168.206.28 centos7.3 x86_64 TiDB 库 4000 端口 root/password

2. 准备工作

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

  1. [tidb@dmmaster ~]# yum -y install epel-release git curl sshpass 
  2. [tidb@dmmaster ~]# yum -y install python-pip

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

1、创建 tidb 用户

  1. [tidb@dmmaster ~]# useradd -m -d /home/tidb tidb

2、为 tidb 用户设置密码

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

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

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

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

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

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

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

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

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

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

  1. [tidb@dmmaster dm-ansible]$ cat hosts.ini 
  2. [servers]
  3. 192.168.128.132
  4. 192.168.128.133
  5. [all:vars]
  6. username = tidb
  7. ansible_ssh_port = 22
  8. ntp_server = ntp.aliyun.com

dm-worker 主机建立 tidb 用户并完成互信，此处输入远程机器的 root 密码 password

  1. [tidb@dmmaster dm-ansible]$ ansible-playbook -i hosts.ini create_users.yml -u root -k
  2. SSH password: 
  3. PLAY [all] 
  4. ********************************************************************
  5. TASK [create user] 
  6. ********************************************************************
  7. changed: [192.168.128.133]
  8. TASK [set authorized key] 
  9. ********************************************************************
  10. changed: [192.168.128.133]
  11. TASK [update sudoers file] ********************************************************************
  12. changed: [192.168.128.133]
  13. PLAY RECAP
  14. ********************************************************************
  15. 192.168.128.133    : ok=3    changed=3    unreachable=0    failed=0

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

  1. [tidb@dmmaster dm-ansible]$ ansible-playbook local_prepare.yml
  2. PLAY [do local preparation] ********************************************************************
  3. TASK [download : Stop if ansible version is too low, make sure that the Ansible version is Ansible 2.5.0 or later, otherwise a compatibility issue occurs.] 
  4. ********************************************************************
  5. ok: [localhost] => {
  6.     "changed": false, 
  7.     "msg": "All assertions passed"
  8. }
  9. 此处打印日志省略
  10. localhost  : ok=13   changed=5    unreachable=0    failed=0

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

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

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

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

3. 部署 dm-worker

第一步：编写 inventory.ini 文件

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

  1. [tidb@dmmaster dm-ansible]$ cat inventory.ini
  2. ## DM modules
  3. [dm_master_servers]
  4. dm_master ansible_host=192.168.128.132
  5. [dm_worker_servers]
  6. 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
  7. 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
  8. 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
  9. [dm_portal_servers]
  10. dm_portal ansible_host=192.168.128.132
  11. ## Monitoring modules
  12. [prometheus_servers]
  13. prometheus ansible_host=192.168.128.132
  14. [grafana_servers]
  15. grafana ansible_host=192.168.128.132
  16. [alertmanager_servers]
  17. alertmanager ansible_host=192.168.128.132
  18. ## Global variables
  19. [all:vars]
  20. cluster_name = dm-cluster
  21. ansible_user = tidb
  22. ansible_port = 5622
  23. dm_version = v1.0.2
  24. deploy_dir = /home/tidb/deploy
  25. grafana_admin_user = "admin"
  26. grafana_admin_password = "admin"
  1. inventory.ini 文件参数说明
  2. [dm_master_servers]  dm-master 选项，用于定义哪台主机是中控 dm-master
  3. [dm_worker_servers]  dm-worker 选项，用于定义 dm-worker 服务
  4. ----dm_worker3306    dm服务全局唯一标签，配合ansible-playbook -l 参数使用
  5. ----ansible_host     指定 dm-worker 部署在哪台主机
  6. ----dm_worker_port   指定 dm-worker 启动服务端口号
  7. ----deploy_dir       指定 dm-worker 部署安装目录
  8. ----source_id        指定 dm-worker 的 source-id
  9. ----mysql_host       上游MySQL主机地址
  10. ----mysql_user       上游MySQL登录用户   
  11. ----mysql_port       上游MySQL服务端口
  12. ----mysql_password   上游MySQL登录密码(必须dmctl加密后的值,参考2.2章第八步)

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

安装 dm-worker:

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

启动 dm-worker:

  1. [tidb@dmmaster dm-ansible]$ ansible-playbook start.yml 
  2. PLAY RECAP **********************************************************************
  3. alertmanager   : ok=10   changed=1    unreachable=0    failed=0   
  4. dm_master      : ok=10   changed=1    unreachable=0    failed=0   
  5. dm_portal      : ok=9    changed=1    unreachable=0    failed=0   
  6. dm_worker3306  : ok=11   changed=1    unreachable=0    failed=0   
  7. dm_worker3307  : ok=11   changed=1    unreachable=0    failed=0   
  8. dm_worker3308  : ok=11   changed=1    unreachable=0    failed=0   
  9. grafana        : ok=13   changed=1    unreachable=0    failed=0   
  10. localhost      : ok=4    changed=0    unreachable=0    failed=0   
  11. prometheus     : ok=13   changed=4    unreachable=0    failed=0 
  12. #出现以上信息表示 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

上游数据库准备

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

合库合表 task 的 yaml 文件

  1. [tidb@tidb-dm-4-0-95 task]$ cat shardmysql_to_tidb.yaml
  2. name: "shard_to_tidb"  #task 名称，必须全局唯一
  3. is-sharding: true      #上游是不是进行了分库分表库
  4. task-mode: "all"       #迁移同步方式 full-全量、incremental-增量、all-全量+增加
  5. meta-schema: "tidb_meta"   #定义下游保留迁移点位信息库名称
  6. remove-meta: false
  7. target-database:                                   
  8.   host: "192.168.206.28" #下游 TiDB 主机 IP
  9.   port: 4000             #TiDB 访问端口
  10.   user: "root"           #TiDB 登录用户
  11.   password: "vLnqQt44rNFHSxA"    #使用 dmctl 加密的登录密码
  12. mysql-instances:
  13. -
  14.   source-id: "mysql3306"   #必须与 inventory.ini 中对应的 source-id 一致
  15.   route-rules: ["rt000","rt001"]      #库表合并规则
  16.   filter-rules: ["ymdd-filter-rule"]  #过滤规则
  17.   mydumper-config-name: "global"
  18.   loader-config-name: "global"
  19.   syncer-config-name: "global"
  20.   black-white-list: "br01"       #白名单列表
  21.   column-mapping-rules: ["cm001"]   #自增主键重计算规则
  22. -
  23.   source-id: "mysql3307"
  24.   route-rules: ["rt000","rt001"]
  25.   filter-rules: ["ymdd-filter-rule"]
  26.   mydumper-config-name: "global"
  27.   loader-config-name: "global"
  28.   syncer-config-name: "global"
  29.   black-white-list: "br01"
  30.   column-mapping-rules: ["cm002"]
  31. -
  32.   source-id: "mysql3308"
  33.   route-rules: ["rt000","rt001"]
  34.   filter-rules: ["ymdd-filter-rule"]
  35.   mydumper-config-name: "global"
  36.   loader-config-name: "global"
  37.   syncer-config-name: "global"
  38.   black-white-list: "br01"
  39.   column-mapping-rules: ["cm003"]
  40. filters:
  41.   ymdd-filter-rule:
  42.     schema-pattern: "shard_db *"
  43.     #以下 2 行定义忽略的 binlog 事件
  44.     events: ["truncate table","delete","drop table","drop database"]
  45.     action: Ignore  
  47. routes:
  48.   rt000:
  49.     #将上游所有 shard_db*匹配的库合并至 merge_db 库
  50.     schema-pattern: "shard_db*"
  51.     target-schema: "merge_db"
  52.   rt001:
  53.     #将上游所有 shard_tb*匹配的分表合并至 merge_db 库的 merge_tb 表中
  54.     schema-pattern: "shard_db*"
  55.     table-pattern: "shard_tb??"
  56.     target-schema: "merge_db"
  57.     target-table:  "merge_tb"
  59. #由于上游数据库使用了自增主键，此处我们需要定义下游主键重算处理，该功能需要谨慎使用
  60. #特别注意上游的自增主键不能有任何业务关系
  61. column-mappings:
  62.   cm001:
  63.     schema-pattern: "shard_db*"
  64.     table-pattern: "shard_tb??"
  65.     expression: "partition id"
  66.     source-column: "id"
  67.     target-column: "id"
  68.     arguments: ["1","shard_db","shard_tb"]
  69.   cm002:
  70.     schema-pattern: "shard_db*"
  71.     table-pattern: "shard_tb??"
  72.     expression: "partition id"
  73.     source-column: "id"
  74.     target-column: "id"
  75.     arguments: ["2","shard_db","shard_tb"]
  76.   cm003:
  77.     schema-pattern: "shard_db*"
  78.     table-pattern: "shard_tb??"
  79.     expression: "partition id"
  80.     source-column: "id"
  81.     target-column: "id"
  82.     arguments: ["3","shard_db","shard_tb"]
  83. #黑白名单定义
  84. black-white-list:
  85.   br01:
  86.     do-dbs: ["~shard_db*"]  #需要同步的库
  87.     #需要忽略同步的库
  88.     ignore-dbs: ["mysql","performance_schema","information_schema"]
  89.     #需要忽略同步的哪个库的哪张表
  90.     ignore-tables:
  91.     - db-name: "~shard_db*"
  92.       tbl-name: "~txc_undo_log*"
  93. #以下默认即可 
  94. mydumpers:
  95.   global:
  96.     threads:
  97.     chunk-filesize: 64
  98.     skip-tz-utc: true
  99.     extra-args: " --no-locks "
  100. loaders:
  101.   global:
  102.     pool-size: 64
  103.     dir: "./dumped_data"
  104. syncers:
  105.   global:
  106.     worker-count: 6
  107.     batch: 1000

启动迁移同步任务

  1. [tidb@dmmaster dmctl]$ dmctl -master-addr 192.168.128.132:8261
  2. » start-task shard_to_tidb.yaml
  3. » query-status
  4. {
  5.     "result": true,
  6.     "msg": "",
  7.     "tasks": [
  8.         {
  9.             "taskName": "shard_to_tidb",
  10.             "taskStatus": "Running",
  11.             "workers": [
  12.                 "192.168.128.133:53306",
  13.                 "192.168.128.133:53307",
  14.                 "192.168.128.133:53308"
  15.             ]
  16.         }
  17.     ]
  18. }
  19. #任务运行正常

上游数据全量导入 TiDB 完成后，我们登录 dm-worker 机器，删除全备并保留表结构文件 （节约磁盘空间成本），操作如下

先看一下备份目录的文件

  1. [root@dmworker dumped_data.shard_to_tidb]# ll |awk '{print $NF}'
  2. metadata
  3. shard_db01-schema-create.sql
  4. shard_db01.shard_tb01-schema.sql
  5. shard_db01.shard_tb01.sql
  6. shard_db02-schema-create.sql
  7. shard_db02.shard_tb02-schema.sql
  8. shard_db02.shard_tb02.sql

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

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

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

图片

5.1.2.2 DM 常用管理命令

1. dm-worker 部署管理

部署命令 deploy.yml

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

dm-worker 启动停止更新命令

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

2. dm-worker task 管理

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

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

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

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

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

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

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

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

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

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

skip_sql 跳过正在执行的 SQL 语句

  1. #查看出错的 binlog 位置(failedBinlogPosition)，确定是否可以路过错误
  2. query-error shard_to_tidb
  3. #跳过当前错误的 binlog
  4. sql-skip --worker=192.168.128.133:53307 --binlog-pos=mysql-bin|000001.000003:737983  shard_to_tidb
  5. #恢复继续任务
  6. resume-task --worker=192.168.128.133:53307  shard_to_tidb
  7. #再次查看错误信息
  8. query-error shard_to_tidb