阮胜昌的技术记录站-LinuxMySQL数据库运维

拓扑环境：
mysql-5.7.18        172.16.1.201
tidb-5.0.0             172.16.1.100
        dm-master: v1.6.1    172.16.1.101
        dm-worker:v1.6.1    172.16.1.102


前题是DM集群已安装配置完成：

1.定义mysql的数据源：
mysql: 172.16.1.201:3306

数据源要开启binlog和server_id等配置：
cat /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server_id=1
show_compatibility_56=on  

创建复制用的帐号和密码：
这边用root来测试

将mysql 示例数据库employees导入到mysql数据库中

创建复制源文件：
[tidb@tidbsrv-mon-node100 ~]$ cat dm-mysql-source-config.yaml
source-id: "mysql-replica-01"
from:
  host: "172.16.1.201"
  user: "root"
  password: "123456"
  port: 3306


创建复制任务文件：
将mysql中的employees全量同步到Tidb中
[tidb@tidbsrv-mon-node100 ~]$ cat dm-task.yaml
name: "dm-task"
task-mode: all
ignore-checking-items: ["auto_increment_ID"]

target-database:
  host: "172.16.1.100"
  port: 4000
  user: "root"
  password: ""
mysql-instances:
  -
    source-id: "mysql-replica-01"
    route-rules: ["instance-user-rule"]
    filter-rules: ["trace-filter-rule"]
    block-allow-list: "log-ignored"
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"
mydumpers:
  global:
    extra-args: "--no-locks"
loaders:
  global:
    pool-size: 16
syncers:
  global:
    worker-count: 16
    batch: 100

routes:
  instance-user-rule:
    schema-pattern: "employees"
    target-schema: "employees"
filters:
  trace-filter-rule:
    schema-pattern: "employees"
    table-pattern: "titles"
    events: ["truncate table","drop table"]
    action: Ignore
block-allow-list:
  log-ignored:
    ignore-dbs: ["log"]   

数据源服务启动：
将数据源配置文件加载到DM中：
tiup dmctl --master-addr=172.16.1.101:8261 operate-source create dm-mysql-source-config.yaml

查看已经加载的数据源：
tiup dmctl --master-addr=172.16.1.101:8261 get-config source mysql-replica-01

删除数据库源：
tiup dmctl --master-addr=172.16.1.101:8261 operate-source stop  dm-mysql-source-config.yaml

查看数据源和DM-WORKER的对应关系：
tiup dmctl --master-addr=172.16.1.101:8261 operate-source show


复制任务的启动:

查看已应用的数据源：
tiup dmctl --master-addr=172.16.1.101:8261 get-config source mysql-replica-01

创建复制任务：
tiup dmctl --master-addr=172.16.1.101:8261 start-task dm-task.yaml

复制相关状态查看：
查看复制状态：
[tidb@tidbsrv-mon-node100 ~]$ tiup dmctl --master-addr=172.16.1.101:8261 query-status  dm-task.yaml
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.7/dmctl/dmctl --master-addr=172.16.1.101:8261 query-status dm-task.yaml
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "sourceStatus": {
                "source": "mysql-replica-01",
                "worker": "dm-172.16.1.102-8262",
                "result": null,
                "relayStatus": null
            },
            "subTaskStatus": [
                {
                    "name": "dm-task",
                    "stage": "Running",
                    "unit": "Load",
                    "result": null,
                    "unresolvedDDLLockID": "",
                    "load": {
                        "finishedBytes": "1000082",
                        "totalBytes": "172294596",
                        "progress": "0.58 %",
                        "metaBinlog": "(mysql-bin.000002, 154)",
                        "metaBinlogGTID": ""
                    }
                }
            ]
        }
    ]
}
查看复制完成后的状态：
[tidb@tidbsrv-mon-node100 ~]$ tiup dmctl --master-addr=172.16.1.101:8261 query-status  dm-task.yaml
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.7/dmctl/dmctl --master-addr=172.16.1.101:8261 query-status dm-task.yaml
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "sourceStatus": {
                "source": "mysql-replica-01",
                "worker": "dm-172.16.1.102-8262",
                "result": null,
                "relayStatus": null
            },
            "subTaskStatus": [
                {
                    "name": "dm-task",
                    "stage": "Running",
                    "unit": "Sync",
                    "result": null,
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "2",
                        "totalTps": "0",
                        "recentTps": "0",
                        "masterBinlog": "(mysql-bin.000002, 154)",
                        "masterBinlogGtid": "",
                        "syncerBinlog": "(mysql-bin.000002, 154)",
                        "syncerBinlogGtid": "",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": true,
                        "binlogType": "remote",
                        "secondsBehindMaster": "0"
                    }
                }
            ]
        }
    ]
}

在mysql上可以查看到dm-worker已连接：
mysql> show processlist;
+----+------+--------------------+------+---------+------+----------+------------------+
| Id | User | Host               | db   | Command | Time | State    | Info             |
+----+------+--------------------+------+---------+------+----------+------------------+
| 19 | root | 172.16.1.102:47404 | NULL | Sleep   |  345 |          | NULL             |
| 20 | root | 172.16.1.102:47406 | NULL | Sleep   |  340 |          | NULL             |
| 23 | root | 172.16.1.102:47456 | NULL | Sleep   |  340 |          | NULL             |
| 60 | root | localhost          | NULL | Query   |    0 | starting | show processlist |
+----+------+--------------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)
如果复制完成后，将显示如下：
mysql> show processlist;
+----+------+--------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host               | db   | Command     | Time | State                                                         | Info             |
+----+------+--------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 19 | root | 172.16.1.102:47404 | NULL | Sleep       |  683 |                                                               | NULL             |
| 20 | root | 172.16.1.102:47406 | NULL | Sleep       |   81 |                                                               | NULL             |
| 23 | root | 172.16.1.102:47456 | NULL | Sleep       |  678 |                                                               | NULL             |
| 60 | root | localhost          | NULL | Query       |    0 | starting                                                      | show processlist |
| 79 | root | 172.16.1.102:48302 | NULL | Binlog Dump |   80 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+--------------------+------+-------------+------+---------------------------------------------------------------+------------------+
5 rows in set (0.00 sec)


在tidb目标数据库可以查看到，employees已同步了：
MySQL [employees]> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| dm_meta            |
| employees          |
| mysql              |
| test               |
+--------------------+
7 rows in set (0.01 sec)


MySQL [employees]> use employees;
Database changed
MySQL [employees]> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)
 
DM的同步是先全量，后再增量的复制，复制的目标(tidb)，最开始可以不用创建数据库，DM会自动创建对应的库和表
因为配置文件中是同步employees整个数据库，所以，如果在mysql中新增表，会自己同步到tidb中，如下：
mysql:
mysql> create table rsc(id int)
    -> ;
Query OK, 0 rows affected (0.52 sec)
mysql> insert into rsc select 1;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0


tidb:
MySQL [employees]> desc employees.rsc;
+-------+---------+------+------+---------+-------+
| Field | Type    | Null | Key  | Default | Extra |
+-------+---------+------+------+---------+-------+
| id    | int(11) | YES  |      | NULL    |       |
+-------+---------+------+------+---------+-------+
1 row in set (0.01 sec)


MySQL [employees]> select * from employees.rsc;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.02 sec)
注意： 通过DM将MySQL数据同步到TiDB是单向同步，不是双向。