# **练习-2 : 创建 `MySQL` 数据库到 `TiDB` 数据库的同步任务，并检查任务状态** ## 概述 + 在本练习中，将为源端(上游) MySQL 数据库到目标端(下游) TiDB 数据库创建同步任务，开启数据同步，并检查任务状态｡ ## 实验环境要求 + 1. 注意，您的实验环境（包括IP，端口号，用户名，密码和目录）可能和手册不同。 + 2. 可以连接到实验用虚拟机。 + 3. 部署好 1 个 TiBD 数据库集群，集群名为 tidb-test，并清理掉之前的实验数据。 + 4. 部署好 2 个 MySQL 数据库实例，端口号为 3306 和 3307 ，2 个 MySQL 的 binlog 均开启，并且拥有不同的 servier id，2 个 MySQL 实例的参数 show_compatibility_56 必须为：ON， 清理掉之前的实验数据。 + 5. 需要完成练习-1。 ## 拓扑图 ## 同步规则为: + 规则一:MySQL 数据库实例 3306 中的 user 库中所有的表同步到 TiDB 数据库的 user_north 中去, 3307 中的 user 库中所有的表同步到 TiDB 数据库的 user_east 中去｡ + 规则二:MySQL 数据库实例 3306 和 3307 中的 store 库中的表原样同步到 TiDB 数据库中的 store 库中的表,但是 3307 中的 store 库中的表 store_sz 会同步到 TIDB 的 store_suzhou 表中｡ + 规则三:MySQL 数据库实例 3306 和 3307 中的 salesdb 库中的表 sales 做了分表,它们会同步到 TiDB 中的salesdb 库的 sales 表中｡(分表分库规则) + 规则四:MySQL 数据库实例 3306 和 3307 中的 user 库不会复制删除操作,user 库中的 trace 表不会复制 truncate ,drop 和 delete 操作,store 库不会复制删除操作,store 库的表不会复制 truncate ,drop 和 delete 操作｡ + 规则五:MySQL 数据库实例 3306 和 3307 中的 log 库不会参与复制。 ## 步骤 ----------------------------------------------- #### 1. 准备源端（上游）MySQL 数据库和目标端（下游）TiDB 数据库 + **为 MySQL 数据库开通用户权限，如下:** 分别连接到端口号 3306 和 3307 的 MySQL 数据库,创建'root'@'172.31.0.232'，'root'@'172.31.0.233' 和 'root'@'172.31.0.175'，并赋予 all privileges 权限(更详细权限请参考文档)，这 3 个用户用于 dm-worker 连接 MySQL 数据库进行全量和增量数据的读取。 ``` # mysql -uroot -pmysql -h127.0.0.1 -P3306 ``` ```sql mysql> create user 'root'@'172.31.0.232' identified by 'mysql'; Query OK, 0 rows affected (0.00 sec) mysql> create user 'root'@'172.31.0.233' identified by 'mysql'; Query OK, 0 rows affected (0.00 sec) mysql> create user 'root'@'172.31.0.175' identified by 'mysql'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'root'@'172.31.0.232'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'root'@'172.31.0.233'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'root'@'172.31.0.175'; Query OK, 0 rows affected (0.00 sec) ``` 注意：在 3307 端口的 MySQL 实例需要做同样操作。 + **分别连接到端口号 3306 和 3307 的 MySQL 数据库,导入数据库 user,store,log,salesdb，如下:** ``` # mysql -uroot -pmysql -h127.0.0.1 -P3306 < 3306db.sql ``` ``` # mysql -uroot -pmysql -h127.0.0.1 -P3307 < 3307db.sql ``` + **为 TiDB 数据库开通用户权限，如下:** 在已有 TiDB 数据库中,创建'root'@'172.31.0.232'，'root'@'172.31.0.233' 和 'root'@'172.31.0.175'，并赋予 all privileges 权限(更详细权限请参考文档)，这 3 个用户用于 dm-worker 连接 TiDB 数据库进行全量和增量数据的导入。 ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> create user 'root'@'172.31.0.232' identified by 'tidb'; Query OK, 0 rows affected (0.02 sec) mysql> create user 'root'@'172.31.0.233' identified by 'tidb'; Query OK, 0 rows affected (0.02 sec) mysql> create user 'root'@'172.31.0.175' identified by 'tidb'; Query OK, 0 rows affected (0.01 sec) mysql> grant all privileges on *.* to 'root'@'172.31.0.232'; Query OK, 0 rows affected (0.02 sec) mysql> grant all privileges on *.* to 'root'@'172.31.0.233'; Query OK, 0 rows affected (0.02 sec) mysql> grant all privileges on *.* to 'root'@'172.31.0.175'; Query OK, 0 rows affected (0.03 sec) ``` + **为 TiDB 数据库创建 database 和数据表，创建数据库 user_north,user_east,store 和 salesdb，创建相关表 user_north.information，user_north.trace，user_east.information，user_east.trace，store.store_bj，store.store_tj，store.store_sh，store.tore_suzhou 和 salesdb.sales，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` + **注意:TiDB 数据库中的数据库和表也可以不预先创建，在 DM 全量同步数据之前会创建这些数据库和表。** ----------------------------------------------- #### 2.编辑数据源（上游）MySQL 数据库配置文件 + **对数据源（上游）MySQL 数据库的密码进行加密操作，如下:** ``` # tiup dmctl --encrypt 'mysql' ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --encrypt mysql DMJtTSNCJbEoxUn1RVUZCUD9D3L4Fw== ``` + **为端口号为 3306 的 MySQL 实例编辑数据源配置文件，如下:** ``` # vi mysql-source-conf1.yaml ``` ``` source-id: "mysql-replica-01" from: host: "172.31.0.135" port: 3306 user: "root" password: "DMJtTSNCJbEoxUn1RVUZCUD9D3L4Fw==" ``` + **为端口号为 3307 的 MySQL 实例编辑数据源配置文件，如下:** ``` # vi mysql-source-conf2.yaml ``` ``` source-id: "mysql-replica-02" from: host: "172.31.0.135" port: 3307 user: "root" password: "DMJtTSNCJbEoxUn1RVUZCUD9D3L4Fw==" ``` ----------------------------------------------- #### 3.将数据源（上游）MySQL 数据库配置文件加载到 DM 中。 + **加载数据源（上游） MySQL 数据库的 3306 端口配置文件，如下:** ``` # tiup dmctl --master-addr=172.31.0.232:8261 operate-source create mysql-source-conf1.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 operate-source create mysql-source-conf1.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" } ] } ``` + **加载数据源（上游） MySQL 数据库的 3307 端口配置文件，如下:** ``` # tiup dmctl --master-addr=172.31.0.232:8261 operate-source create mysql-source-conf2.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 operate-source create mysql-source-conf2.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ] } ``` + **查看已经加载的数据源 mysql-replica-01 和 mysql-replica-02，如下:** ``` # tiup dmctl --master-addr=172.31.0.232:8261 get-config source mysql-replica-01 ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 get-config source mysql-replica-01 { "result": true, "msg": "", "cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-01\nfrom:\n host: 172.31.0.135\n port: 3306\n user: root\n password: '******'\n max-allowed-packet: null\n session: {}\n security: null\npurge:\n interval: 3600\n expires: 0\n remain-space: 15\nchecker:\n check-enable: true\n backoff-rollback: 5m0s\n backoff-max: 5m0s\n check-interval: 5s\n backoff-min: 1s\n backoff-jitter: true\n backoff-factor: 2\nserver-id: 429542057\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` ``` # tiup dmctl --master-addr=172.31.0.232:8261 get-config source mysql-replica-02 ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 get-config source mysql-replica-02 { "result": true, "msg": "", "cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-02\nfrom:\n host: 172.31.0.135\n port: 3307\n user: root\n password: '******'\n max-allowed-packet: null\n session: {}\n security: null\npurge:\n interval: 3600\n expires: 0\n remain-space: 15\nchecker:\n check-enable: true\n backoff-rollback: 5m0s\n backoff-max: 5m0s\n check-interval: 5s\n backoff-min: 1s\n backoff-jitter: true\n backoff-factor: 2\nserver-id: 429507194\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` + **查看数据源和 dm-worker 的对应关系，如下:** ``` # tiup dmctl --master-addr=172.31.0.232:8261 operate-source show ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 operate-source show { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ] } ``` ----------------------------------------------- #### 4.按照规则,配置 DM 任务配置文件 dm-task。 + **任务信息，如下:** ``` name: "dm-taskX" task-mode: all ignore-checking-items: ["auto_increment_ID"] ``` 任务名:dm-taskX（X 代表任意字符），复制方式:all(全量 + 增量)，ignore-checking-items: ["auto_increment_ID”]:忽略自增主键检测｡ + **目标数据库（下游） TiDB 数据库配置信息，如下:** ``` target-database: host: "172.31.0.135" port: 4000 user: "root" password: "tidb" ``` 数据库地址:172.31.0.135:4000，用户名:root，密码:tidb + **规则一：MySQL 数据库实例 3306 中的 user 库中所有的表同步到 TiDB 数据库的 user_north 中去，3307 中的 user 库中所有的表同步到 TiDB 数据库的 user_east 中去。我们使用 Table routings 实现，如下:** ``` routes: instance-1-user-rule: schema-pattern: "user" target-schema: "user_north" instance-2-user-rule: schema-pattern: "user" target-schema: "user_east" ``` + **规则二：MySQL 数据库实例 3306 和 3307 中的 store 库中的表原样同步到 TiDB 数据库中的 store 库中的表,但是 3307 中的 store 库中的表 store_sz 会同步到 TIDB 的store_suzhou 表中｡我们使用 Table routings 实现，如下:** ``` instance-2-store-rule: schema-pattern: "store" table-pattern: "store_sz" target-schema: "store" target-table: "store_suzhou" ``` + **规则三：MySQL 数据库实例 3306 和 3307 中的 salesdb 库中的表 sales 做了分表，它们会同步到 TiDB 中的，salesdb 库的 sales 表中｡(分表分库规则)我们使用 Table routings 实现，如下:** ``` sale-route-rule: schema-pattern: "salesdb" target-schema: "salesdb" ``` + **规则四：MySQL 数据库实例 3306 和 3307 中的 user 库不会复制删除操作，user 库中的 trace 表不会复制 truncate，drop 和 delete 操作，store 库不会复制删除操作，store 库的表不会复制 truncate ，drop 和 delete 操作｡我们使用 Binlog event filter 实现，如下:** ``` filters: trace-filter-rule: # user 库中的 trace 表不会复制 truncate ，drop 和 delete 操作 schema-pattern: "user" table-pattern: "trace" events: ["truncate table", "drop table", "delete"] action: Ignore user-filter-rule: # MySQL 数据库实例 3306 和 3307 中的 user 库不会复制删除操作 schema-pattern: "user" events: ["drop database"] action: Ignore store-filter-rule: # store 库不会复制删除操作，store 库的表不会复制 truncate ，drop 和delete 操作 schema-pattern: "store" events: ["drop database", "truncate table", "drop table", "delete"] action: Ignore ``` + **规则五：MySQL 数据库实例 3306 和 3307 中的 log 库不会参与复制｡我们使用 block allow list 实现，如下:** ``` block-allow-list: log-ignored: ignore-dbs: ["log"] ``` + **将 MySQL 数据库实例 3306 和 3307 两个实例关联上述规则，如下:** ``` mysql-instances: - source-id: "mysql-replica-01" route-rules: ["instance-1-user-rule","sale-route-rule"] filter-rules: ["trace-filter-rule", "user-filter-rule" , "store-filter-rule"] block-allow-list: "log-ignored" mydumper-config-name: "global" loader-config-name: "global" syncer-config-name: "global" - source-id: "mysql-replica-02" route-rules: ["instance-2-user-rule", "instance-2-store-rule","sale-route-rule"] filter-rules: ["trace-filter-rule", "user-filter-rule" , "store-filter-rule"] block-allow-list: "log-ignored" mydumper-config-name: "global" loader-config-name: "global" syncer-config-name: "global" ``` + **将上面的配置组合为最终配置，见输出样例:** ----------------------------------------------- #### 5. 对于源端（上游） MySQL 数据库进行检查。 + **对于源端（上游） MySQL 数据库进行检查，如下:** ``` tiup dmctl --master-addr=172.31.0.232:8261 check-task dm-task.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 check-task dm-task.yaml { "result": true, "msg": "pre-check is passed. " } ``` ----------------------------------------------- #### 6. 创建复制任务，并默认开始，随后检查复制任务状态。 + **创建复制任务,并默认开始，如下:** ``` tiup dmctl --master-addr=172.31.0.232:8261 start-task dm-task.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 start-task dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ], "checkResult": "pre-check is passed. " } ``` + **查询任务状态,查看是否正常，如下:** ``` tiup dmctl --master-addr=172.31.0.232:8261 query-status dm-task.yaml ``` ----------------------------------------------- #### 7.检查目标（下游） TiDB 数据库的数据,查看是否全量数据同步正确。 + **检查目标（下游） TiDB 数据库的数据,查看是否全量数据同步正确，请参考输出样例** #### 8.测试分表分库复制。 + **在目标端（下游） TiDB 数据库的 salesdb 中查询，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> use salesdb; Database changed mysql> select * from sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 2 | cup | 200 | +----+-------+------+ 2 rows in set (0.00 sec) ``` + **在源端（上游） MySQL 数据库 3306 端口的 salesdb 中插入数据，如下：** ``` # mysql -h127.0.0.1 -P3306 -uroot -pmysql ``` ```sql mysql> insert into salesdb.sales values(3,'shose',120); Query OK, 1 row affected (0.01 sec) mysql> select * from salesdb.sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 3 | shose | 120 | +----+-------+------+ 2 rows in set (0.00 sec) ``` + **在目标端（下游） TiDB 数据库的 salesdb 中查询，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select * from salesdb.sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 2 | cup | 200 | | 3 | shose | 120 | +----+-------+------+ 3 rows in set (0.00 sec) ``` + **在源端（上游） MySQL 数据库 3307 端口的 salesdb 中插入数据，如下：** ``` # mysql -h127.0.0.1 -P3307 -uroot -pmysql ``` ```sql mysql> insert into salesdb.sales values(4, 'paper',1000); Query OK, 1 row affected (0.01 sec) mysql> select * from salesdb.sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 2 | cup | 200 | | 4 | paper | 1000 | +----+-------+------+ 2 rows in set (0.00 sec) ``` + **在目标端（下游） TiDB 数据库的 salesdb 中查询，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select * from salesdb.sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 2 | cup | 200 | | 3 | shose | 120 | | 4 | paper | 1000 | +----+-------+------+ 4 rows in set (0.01 sec) ``` ----------------------------------------------- #### 9.测试路由规则复制。 + **在源端（上游） MySQL 数据库 3306 端口的 user 库的 information 表中插入数据，如下：** ``` # mysql -h172.31.0.135 -P3306 -uroot -pmysql ``` ```sql mysql> use user; Database changed mysql> show tables; +----------------+ | Tables_in_user | +----------------+ | information | | trace | +----------------+ 2 rows in set (0.00 sec) mysql> select * from information; +----+------+ | id | info | +----+------+ | 1 | tom | | 2 | jack | +----+------+ 2 rows in set (0.00 sec) mysql> insert into information values(3,'frank'); Query OK, 1 row affected (0.00 sec) mysql> select * from information; +----+-------+ | id | info | +----+-------+ | 1 | tom | | 2 | jack | | 3 | frank | +----+-------+ 3 rows in set (0.00 sec) ``` + **在目标端（下游）TiDB 数据库的 user_north 数据库中查询 information 表的数据，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select * from user_north.information; +----+-------+ | id | info | +----+-------+ | 1 | tom | | 2 | jack | | 3 | frank | +----+-------+ 3 rows in set (0.00 sec) ``` ----------------------------------------------- #### 9.对于“规则五: MySQL 数据库实例 3306 和 3307 中的 log 库不会参与复制｡”的测试。 + **源端（上游） MySQL 数据库 3306 端口的 log 库中插入数据，如下：** ``` # mysql -h127.0.0.1 -P3306 -uroot -pmysql ``` ```sql mysql> insert into log.messages values(2,'world'); Query OK, 1 row affected (0.01 sec) mysql> select * from log.messages; +----+-------+ | id | msg | +----+-------+ | 1 | helo | | 2 | world | +----+-------+ 2 rows in set (0.00 sec) ``` + **在目标端（下游）TiDB 数据库的 log 库中查询，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select * from log.messages; Empty set (0.00 sec) ``` **新插入的数据并没有从源端（上游）复制到目标端口（下游）。** ----------------------------------------------- #### 10.对于“规则四中:MySQL 数据库实例 3306 和 3307 中的 user 库中的 trace 表不会复制 truncate ,drop 和 delete 操作｡”的测试 + **源端（上游） MySQL 数据库 3306 端口的 user 库中进行操作，如下：** ``` # mysql -h127.0.0.1 -P3306 -uroot -pmysql ``` ```sql mysql> use user; Database changed mysql> insert into trace values(2,'query'); Query OK, 1 row affected (0.01 sec) mysql> insert into trace values(3,'log out'); Query OK, 1 row affected (0.01 sec) mysql> select * from trace; +----+---------+ | id | content | +----+---------+ | 1 | login | | 2 | query | | 3 | log out | +----+---------+ 3 rows in set (0.00 sec) mysql> delete from trace where id=3; Query OK, 1 row affected (0.01 sec) mysql> truncate table trace; Query OK, 0 rows affected (0.01 sec) mysql> drop table trace; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +----------------+ | Tables_in_user | +----------------+ | information | +----------------+ 1 row in set (0.00 sec) mysql> exit Bye ``` + **在目标端（下游）TiDB 数据库中检查 user_north 库中的 trace 表是否存在，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select * from user_north.trace; +----+---------+ | id | content | +----+---------+ | 1 | login | | 2 | query | | 3 | log out | +----+---------+ 3 rows in set (0.00 sec) ``` ----------------------------------------------- #### 11.测试暂停复制后再次开启复制，源端（上游）数据库的变更是否会同步到目标端（下游）数据库。 + **暂停复制任务，如下：** ``` # tiup dmctl --master-addr=172.31.0.232:8261 pause-task dm-task.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 pause-task dm-task.yaml { "op": "Pause", "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ] } ``` + **在任务暂定的情况下,连接源端（上游） MySQL 数据库 3307 端口的 user 库中进行数据变更操作，如下：** ``` # mysql -h127.0.0.1 -P3307 -uroot -pmysql ``` ```sql mysql> use user; Database changed mysql> show tables; +----------------+ | Tables_in_user | +----------------+ | information | | trace | +----------------+ 2 rows in set (0.00 sec) mysql> select * from information; +----+-------+ | id | info | +----+-------+ | 1 | andy | | 2 | candy | +----+-------+ 2 rows in set (0.00 sec) mysql> insert into information values(3,'joe'); Query OK, 1 row affected (0.00 sec) mysql> select * from information; +----+-------+ | id | info | +----+-------+ | 1 | andy | | 2 | candy | | 3 | joe | +----+-------+ 3 rows in set (0.00 sec) mysql> exit Bye ``` + **在目标端（下游）TiDB 数据库中检查 user_east 库中的 information 表数据是否同步了变更操作，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> use user_east; Database changed mysql> mysql> select * from information; +----+-------+ | id | info | +----+-------+ | 1 | andy | | 2 | candy | +----+-------+ 2 rows in set (0.00 sec) mysql> exit Bye ``` + **由于 DM 集群暂停复制后，并没有操作被复制｡** + **恢复暂停的复制任务，如下：** ``` # tiup dmctl --master-addr=172.31.0.232:8261 resume-task dm-task.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 resume-task dm-task.yaml { "op": "Resume", "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ] } ``` + **再次在目标端（下游）TiDB 数据库中检查 user_east 库中的 information 表数据是否同步了变更操作，如下：** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> use user_east; Database changed mysql> mysql> select * from information; +----+-------+ | id | info | +----+-------+ | 1 | andy | | 2 | candy | | 3 | joe | +----+-------+ 3 rows in set (0.00 sec) mysql> exit Bye ``` ----------------------------------------------- ## 输出样例 ----------------------------------------------- #### 步骤 1-为 TiDB 数据库创建 database 和数据表，创建数据库 user_north,user_east,store 和 salesdb，创建相关表 user_north.information，user_north.trace，user_east.information，user_east.trace，store.store_bj，store.store_tj，store.store_sh，store.store_suzhou 和 salesdb.sales 输出参考： ``` mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ``` mysql> create database user_north; Query OK, 0 rows affected (0.08 sec) mysql> create database user_east; Query OK, 0 rows affected (0.08 sec) mysql> create database store; Query OK, 0 rows affected (0.09 sec) mysql> use user_north; Database changed mysql> create table information(id int primary key, info varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> create table trace(id int primary key, content varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> use user_east Database changed mysql> create table information(id int primary key, info varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> create table trace(id int primary key, content varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> use store; Database changed mysql> create table store_bj(id int primary key, pname varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> create table store_tj(id int primary key, pname varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> create table store_sh(id int primary key, pname varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> create table store_suzhou(id int primary key, pname varchar(64)); Query OK, 0 rows affected (0.09 sec) mysql> show tables; +-----------------+ | Tables_in_store | +-----------------+ | store_bj | | store_sh | | store_suzhou | | store_tj | +-----------------+ 4 rows in set (0.00 sec) mysql> create database salesdb; Query OK, 0 rows affected (0.09 sec) mysql> use salesdb; Database changed mysql> create table sales(id int primary key, pname varchar(20), cnt int); Query OK, 0 rows affected (0.09 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | mysql | | salesdb | | store | | test | | user_east | | user_north | +--------------------+ 9 rows in set (0.00 sec) mysql> create database log; Query OK, 0 rows affected (0.08 sec) mysql> use log; Database changed mysql> create table messages(id int primary key, msg varchar(64)); Query OK, 0 rows affected (0.08 sec) ``` ----------------------------------------------- #### 步骤 1-将上面的配置组合为最终配置输出参考： ``` name: "dm-task2" task-mode: all ignore-checking-items: ["auto_increment_ID"] target-database: host: "172.31.0.135" port: 4000 user: "root" password: "tidb" mysql-instances: - source-id: "mysql-replica-01" route-rules: ["instance-1-user-rule","sale-route-rule"] filter-rules: ["trace-filter-rule", "user-filter-rule" , "store-filter-rule"] block-allow-list: "log-ignored" mydumper-config-name: "global" - source-id: "mysql-replica-02" route-rules: ["instance-2-user-rule", "instance-2-store-rule","sale-route-rule"] filter-rules: ["trace-filter-rule", "user-filter-rule" , "store-filter-rule"] block-allow-list: "log-ignored" mydumper-config-name: "global" routes: instance-1-user-rule: schema-pattern: "user" target-schema: "user_north" instance-2-user-rule: schema-pattern: "user" target-schema: "user_east" instance-2-store-rule: schema-pattern: "store" table-pattern: "store_sz" target-schema: "store" target-table: "store_suzhou" sale-route-rule: schema-pattern: "salesdb" target-schema: "salesdb" filters: trace-filter-rule: schema-pattern: "user" table-pattern: "trace" events: ["truncate table", "drop table", "delete"] action: Ignore user-filter-rule: schema-pattern: "user" events: ["drop database"] action: Ignore store-filter-rule: schema-pattern: "store" events: ["drop database", "truncate table", "drop table", "delete"] action: Ignore block-allow-list: log-ignored: ignore-dbs: ["log"] mydumpers: global: threads: 4 chunk-filesize: 64 ``` ----------------------------------------------- #### 步骤 6-查询任务状态,查看是否正常输出参考： ``` tiup dmctl --master-addr=172.31.0.232:8261 query-status dm-task.yaml ``` ``` tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl --master-addr=172.31.0.232:8261 query-status dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { "name": "dm-task2", "stage": "Running", "unit": "Sync", "result": null, "unresolvedDDLLockID": "", "sync": { "totalEvents": "4", "totalTps": "0", "recentTps": "0", "masterBinlog": "(mysql-bin.000002, 154)", "masterBinlogGtid": "", "syncerBinlog": "(mysql-bin.000002, 154)", "syncerBinlogGtid": "", "blockingDDLs": [ ], "unresolvedGroups": [ ], "synced": true, "binlogType": "remote", "secondsBehindMaster": "0", "blockDDLOwner": "", "conflictMsg": "" } } ] }, { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { "name": "dm-task2", "stage": "Running", "unit": "Sync", "result": null, "unresolvedDDLLockID": "", "sync": { "totalEvents": "4", "totalTps": "0", "recentTps": "0", "masterBinlog": "(mysql-bin.000002, 154)", "masterBinlogGtid": "", "syncerBinlog": "(mysql-bin.000002, 154)", "syncerBinlogGtid": "", "blockingDDLs": [ ], "unresolvedGroups": [ ], "synced": true, "binlogType": "remote", "secondsBehindMaster": "0", "blockDDLOwner": "", "conflictMsg": "" } } ] } ] } ``` ----------------------------------------------- #### 步骤 7-检查目标（下游） TiDB 数据库的数据,查看是否全量数据同步正确输出样例 ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> use user_east Database changed mysql> show tables; +---------------------+ | Tables_in_user_east | +---------------------+ | information | | trace | +---------------------+ 2 rows in set (0.00 sec) mysql> select * from information; +----+-------+ | id | info | +----+-------+ | 1 | andy | | 2 | candy | +----+-------+ 2 rows in set (0.00 sec) mysql> select * from trace; +----+---------+ | id | content | +----+---------+ | 1 | error | +----+---------+ 1 row in set (0.00 sec) mysql> use user_north; Database changed mysql> select * from information; +----+------+ | id | info | +----+------+ | 1 | tom | | 2 | jack | +----+------+ 2 rows in set (0.00 sec) mysql> select * from trace; +----+---------+ | id | content | +----+---------+ | 1 | login | +----+---------+ 1 row in set (0.00 sec) mysql> use store; Database changed mysql> show tables; +-----------------+ | Tables_in_store | +-----------------+ | store_bj | | store_sh | | store_suzhou | | store_tj | +-----------------+ 4 rows in set (0.00 sec) mysql> select * from store_bj; +----+-------+ | id | pname | +----+-------+ | 1 | book | +----+-------+ 1 row in set (0.00 sec) mysql> select * from store_tj; +----+-------+ | id | pname | +----+-------+ | 1 | cup | +----+-------+ 1 row in set (0.00 sec) mysql> select * from store_suzhou; +----+-------+ | id | pname | +----+-------+ | 1 | hat | +----+-------+ 1 row in set (0.00 sec) mysql> select * from store_sh; +----+-------+ | id | pname | +----+-------+ | 1 | bike | +----+-------+ 1 row in set (0.01 sec) mysql> use salesdb; Database changed mysql> select * from sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 2 | cup | 200 | +----+-------+------+ 2 rows in set (0.00 sec) mysql> exit Bye ```