基于DTLE的MySQL单库单表单向复制案例
环境：
主机 角色 IP 安装的软件
node200 DTLE 172.16.1.200 dtle-ce-3.21.08.0-1.x86_64
node202 mysql 172.16.1.202 mysql-community-server-5.7.34-1.el7.x86_64
node203 mysql 172.16.1.203 mysql-community-server-5.7.34-1.el7.x86_64
安装配置单实例DTLE略(http://www.linuxmysql.com/14/2021/1147.htm)
在源端MySQL实例node202创建测试库和测试表，并在表中插入数据
node202
(root@node202_master) [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)
(root@node202_master) [(none)]> use test;
Database changed
(root@node202_master) [test]> create table t1 (id int primary key);
Query OK, 0 rows affected (0.02 sec)
(root@node202_master) [test]> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
(root@node202_master) [test]> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
创建job的json文件
[root@node200 ~]# cat one-way-replication.json
{
"Job": {
"ID":"one-way-replication",
"Datacenters": ["dc1"],
"TaskGroups": [{
"Name": "src",
"Tasks": [{
"Name": "src",
"Driver": "dtle",
"Config": {
"Gtid":"",
"ReplicateDoDb": [{
"TableSchema": "test",
"Tables": [{
"TableName": "t1"
}]
}],
"ConnectionConfig": {
"Host":"172.16.1.202",
"Port": 3306,
"User": "root",
"Password": "123456"
}
}
}]
}, {
"Name": "dest",
"Tasks": [{
"Name": "dest",
"Driver": "dtle",
"Config": {
"ConnectionConfig": {
"Host":"172.16.1.203",
"Port": 3306,
"User": "root",
"Password": "123456"
}
}
}]
}]
}
}
利用curl命令调用nomad客户端的接口，创建这个job
[root@node200 ~]# curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @one-way-replication.json -s | jq
{
"EvalID": "814871cc-0052-bc47-0607-10bf2d18cb54",
"EvalCreateIndex": 1666,
"JobModifyIndex": 1666,
"Warnings": "",
"Index": 1666,
"LastContact": 0,
"KnownLeader": false
}
-d @指定的是job的json文件的路径，我是在当前路径下执行所以不需要指定绝对路径
jq命令是用于提取返回结果的内容，类似与linux命令grep，需要安装才可以使用
查看已创建的JOB:
[root@node200 ~]# nomad job status
ID Type Priority Status Submit Date
one-way-replication service 50 running 2021-10-12T11:13:34+08:00
查看JOB的运行状态：
[root@node200 ~]# curl -XGET "http://127.0.0.1:4646/v1/job/one-way-replication" -s | jq '.Status'
"running"
进入到目标端数据库node203进行查看数据是否已经复制过去
(root@node203_master) [test]> show databases;
+--------------------+
| Database |
+--------------------+
| dtle | #有一张二进制的表。用于记录job的GTID号
| information_schema |
| mysql |
| performance_schema |
| sys |
| test | #同步过来的库
| universe |
+--------------------+
(root@node203_master) [test]> select * from test.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
在源端数据库node202在插入几条数据，再进行测试
mysql> use test;
(root@node202_master) [test]> insert into t1 values(5),(6),(7);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
此时我们在到目标端node203数据库查看数据是否有增量数据进来
(root@node203_master) [test]> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
修改表结构测试：
源数据库端node202：
(root@node202_master) [test]> alter table t1 add column name varchar(20) not null ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
目标数据库端node203:
(root@node203_master) [test]> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
到此，基于DTLE的单向时时数据复制配置完成
