使用DTLE对MySQL大表做分库分表
使用DTLE对MySQL大表做分库分表
保持热爱奔赴山海
发布于 2020-03-28 19:38:30
发布于 2020-03-28 19:38:30
DTLE相关的文档：
https://actiontech.github.io/dtle-docs-cn/3/3.0_function_scenario_mapping.html
https://actiontech.github.io/dtle-docs-cn/1/1.0_mysql_replication.html
我们这里演示的是： 通过DTLE，将1个大的实例中某个大表，拆到2个独立的实例里面，做分库分表（分库分表后，还可以结合爱可生的DBLE玩出更多花样，本次就不涉及）。
image.png
原始库：
# 演示用的账号密码都是 dts
192.168.2.4:3306
mysql -udts -pdts -h 192.168.2.4 --port 5725 testdb2个分库：
# 演示用的账号密码都是dts
192.168.2.4:5725
192.168.2.4:19226
mysql -udts -pdts -h 192.168.2.4 --port 5725
mysql -udts -pdts -h 192.168.2.4 --port 19226原表：
create database testdb;
use testdb;
CREATE TABLE `dtle_t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
`s_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
PRIMARY KEY (`id`),
KEY `idx_uid` (`user_id`) USING BTREE
) COMMENT = '测试表' ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;需要按照user_id 做hash拆分，拆分到2个库里面
造些测试用的数据：
for i in {1..10000} ; do
mysql -hdts -pdts -h 192.168.2.4 -e "insert into testdb.dtle_t1 (user_id,s_status) values (\"$RANDOM\",'0');"
done大致这样：
mysql -udts -pdts -h 192.168.2.4 --port 5725 testdb
[test] > select count(*) from dtle_t1 ;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.007 sec)
[testdb] > select (user_id%2) as hash_id,count(*) FROM dtle_t1 group by (user_id%2);
+---------+----------+
| hash_id | count(*) |
+---------+----------+
| 0 | 5008 |
| 1 | 4992 |
+---------+----------+
2 rows in set (0.009 sec)在2个分库上， 都执行上面的建表操作（貌似DTLE能自动创建，但是我们这还是人工创建下吧）：
create database testdb;
use testdb;
CREATE TABLE `dtle_t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
`s_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
PRIMARY KEY (`id`),
KEY `idx_uid` (`user_id`) USING BTREE
) COMMENT = '测试表' ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;启动DTLE进程：
cd /opt/dtle/
mkdir data/
./bin/dtle server -config=./etc/dtle/dtle.conf另开一个窗口，执行 查看 dtle 状态的命令，效果如下：
curl -XGET "127.0.0.1:8190/v1/nodes" -s | jq .
[
{
"CreateIndex": 4,
"Datacenter": "dc1",
"HTTPAddr": "127.0.0.1:8190",
"ID": "821e9f34-a3c3-f981-df41-8fbdc580708a",
"ModifyIndex": 22,
"Name": "dba-test-node-01",
"Status": "ready",
"StatusDescription": ""
}
]准备的 shard1.json 内容如下：
{
"Name":"dtle-shard1",
"Tasks":[
{
"Type":"Src",
"Config":{
"ReplicateDoDb":[
{
"TableSchema":"testdb",
"Tables":[
{
"TableName":"dtle_t1",
"Where":"user_id%2=0"
}
]
}
],
"ConnectionConfig":{
"Host":"192.168.2.4",
"Port":"3306",
"User":"dts",
"Password":"dts"
}
}
},
{
"Type":"Dest",
"Config":{
"ConnectionConfig":{
"Host":"192.168.2.4",
"Port":"5725",
"User":"dts",
"Password":"dts"
}
}
}
]
}准备的 shard2.json 内容如下：
{
"Name":"dtle-shard2",
"Tasks":[
{
"Type":"Src",
"Config":{
"ReplicateDoDb":[
{
"TableSchema":"testdb",
"Tables":[
{
"TableName":"dtle_t1",
"Where":"user_id%2=2"
}
]
}
],
"ConnectionConfig":{
"Host":"192.168.2.4",
"Port":"3306",
"User":"dts",
"Password":"dts"
}
}
},
{
"Type":"Dest",
"Config":{
"ConnectionConfig":{
"Host":"192.168.2.4",
"Port":"19226",
"User":"dts",
"Password":"dts"
}
}
}
]
}提交任务到DTLE：
curl -H "Accept:application/json" -XPOST "http://127.0.0.1:8190/v1/jobs" -d @shard1.json -s | jq .
curl -H "Accept:application/json" -XPOST "http://127.0.0.1:8190/v1/jobs" -d @shard2.json -s | jq .结果类似如下：
{
"Index": 56,
"KnownLeader": false,
"LastContact": 0,
"Success": true
}
稍等片刻，等数据同步，这时候可以看到老的主库上面的连接，可以看到有2个GTID dump线程在运行：
[testdb] > show full processlist;
+-------+------+-------------------+--------+------------------+------+---------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-------------------+--------+------------------+------+---------------------------------------------------------------+-----------------------+
| 43810 | root | localhost | testdb | Query | 0 | starting | show full processlist |
| 43829 | root | localhost | testdb | Sleep | 25 | | NULL |
| 43830 | dts | 192.168.2.4:38040 | NULL | Sleep | 293 | | NULL |
| 43831 | dts | 192.168.2.4:38048 | NULL | Sleep | 293 | | NULL |
| 43834 | dts | 192.168.2.4:38056 | NULL | Binlog Dump GTID | 292 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 43835 | dts | 192.168.2.4:38060 | NULL | Sleep | 290 | | NULL |
| 43836 | dts | 192.168.2.4:38068 | NULL | Sleep | 290 | | NULL |
| 43839 | dts | 192.168.2.4:38076 | NULL | Binlog Dump GTID | 289 | Master has sent all binlog to slave; waiting for more updates | NULL |
+-------+------+-------------------+--------+------------------+------+---------------------------------------------------------------+-----------------------+
8 rows in set (0.000 sec)然后，查看下分库的数据：
[root@dba-test-node-01 /opt/dtle ] # mysql -udts -pdts -h 192.168.2.4 --port 5725 testdb -e "select count(*) from dtle_t1;"
+----------+
| count(*) |
+----------+
| 5008 |
+----------+
[root@dba-test-node-01 /opt/dtle ] # mysql -udts -pdts -h 192.168.2.4 --port 19226 testdb -e "select count(*) from dtle_t1;"
+----------+
| count(*) |
+----------+
| 4992 |
+----------+我们这里也可以再老的主库，人工再插入几条测试数据看看是否会自动分流到后端不同的分片上面，我这里就不继续演示了。
列出当前的job：
curl -XGET "127.0.0.1:8190/v1/jobs" | jq .列出某作业的所有任务执行
curl -XGET "127.0.0.1:8190/v1/job/8e928a6b-e2be-c7b7-0d4a-745163c87282/allocations" | jq .
curl -XGET "127.0.0.1:8190/v1/job/e8780526-9464-9df9-61f2-48c70a991024/allocations" | jq .删除一个作业：
curl -H "Accept:application/json" -XDELETE "127.0.0.1:8190/v1/job/4079eaba-bcec-08b1-5ac2-78aa4a21a49b"相关文档： https://actiontech.github.io/dtle-docs-cn/4/4.4_http_api.html
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2020-03-26 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读