xx银行某外呼系统,每天任务分配表数据已经达到2000w以上,由于任务的SQL较为复杂。DBA除了优化SQL外,也同步需要对大表进行分表改造。本次分表主要依据任务类型对任务分配表进行水平分表,而本文主要介绍了两种水平分表的方案,希望能给大家一些帮助。
操作系统:Linux 7.9
数据库信息:MGR 单主模式 版本 MySQL 5.7.26
以下所有表均为测试表
表结构:
CREATE TABLE `single_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key1` varchar(100) DEFAULT NULL,
`key2` int(11) DEFAULT NULL,
`key3` varchar(100) DEFAULT NULL,
`key_part1` varchar(100) DEFAULT NULL,
`key_part2` varchar(100) DEFAULT NULL,
`key_part3` varchar(100) DEFAULT NULL,
`common_field` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_key2` (`key2`),
KEY `idx_key1` (`key1`),
KEY `idx_key3` (`key3`),
KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
通过mysql_random_data_load先造100000条数据
mysql_random_data_load -h127.0.0.1 -u'root' -p'xxx' --max-threads=40 test single_test 100000
1.备份全表方式 灾备节点执行 --因为我是有多张表拆分,所以采用的是mydumper进行全表备份
mkdir -p /backup/mysql/zxb
nohup mydumper -u root -p xxx -P 3306 -B test -T single_test -t 4 -o /backup/mysql/zxb/ &
2.锁住原表 主节点执行
lock table test.single_test read;
3.开始迁移single_table 表 将id 值 <10000的迁移到single_table_1表,本文指定对应的字段值,id使用自增字段生成,这样用pt工具回退就不会存在主键冲突的问题。
#创建目标表
create table test.single_table_1 like test.single_table;
开始迁移数据 注意: --columns 后面跟随对应的字段名,不需要带上id,这样生成的id就是自增id值
pt-archiver \
--source h=192.168.248.111,P=3306,u=root,p='xxx',D=test,t=single_table --columns key1,key2,key3,key_part1,key_part2,key_part3,common_field \
--dest h=192.168.248.111,P=3306,u=root,p='xxx',D=test,t=single_table_1 --columns key1,key2,key3,key_part1,key_part2,key_part3,common_field \
--charset=UTF8 --where 'id<10000' --progress 1000 --limit=1000 --txn-size 1000 --bulk-insert --bulk-delete --statistics --no-delete
4.检查数据是否一致
#迁移完成校验两边数据量是否一致
Select count(*) from test.single_table_1;
Select count(*) from test.single_table where id<10000;
当然为了数据的一致性也可以通过mysqldump顺序导出,然后对比md5值,此处我就不列举了。
5.删除原表的数据
unlock tables;
pt-archiver \
--source h=192.168.248.111,P=3306,u=root,p='xxx',,D=test,t=single_table \
--charset=UTF8 --where 'id<10000' --progress 10000 --limit=10000 --txn-size 10000 --bulk-delete --statistics --purge
为什么会有方案二呢?原因是存在两表关联,用pt-archiver进行数据归档解决不了。
方案二的第1步和第2步跟方案一保持一致。所以我从第3步开始讲起
将test.xxxx_task_process_test_exp的数据迁移到
#创建新的目录以及表
mkdir -p /backup/mysql/outfile_20220318
create table test.xxxx_task_process_test_inner_xkx_exp like test.xxxx_task_process_test_exp;
#导出需要迁移的数据 --需要标明所有的字段
select m.key1,m.key2,m.key3,m.key_part1,m.key_part2,m.key_part3,m.common_field from xxxx_task_process_test_exp m left join xxxx_task_process_test e on m.task_id = e.task_id where e.biz_code = 'DX_INNER_TYPE_XKX' INTO OUTFILE "/backup/mysql/outfile_20220318/xxxx_task_process_test_exp.csv" character set utf8 FIELDS TERMINATED BY '|@|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
#split拆分csv文件
cd /backup/mysql/outfile_20220318
split -l 10000 xxxx_task_process_test_exp.csv xxxx_task_process_test_exp_
#将文件数据导入新表
cd /backup/mysql/outfile_20220318/
for file in `ls xxxx_task_process_test_exp_*`;do echo " LOAD DATA INFILE '/backup/mysql/zxb/${file}' INTO TABLE test.xxxx_task_process_test_inner_xkx_exp FIELDS TERMINATED BY '|@|' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY \"\r\n\" (key1,key2,key3,key_part1,key_part2,key_part3,common_field); select sleep(2);" >> loader_20220318.sql ;done
#将数据导入新表
mysql -uroot -p"paic1234A?" -D test < loader_20220318.sql
#校验两边的数据是否一致
select count(*) from xxxx_task_process_test_exp m left join xxxx_task_process_test_inner_xkx_exp e on m.key2 = e.key2 where e.key3 = 'xxxx';
select count(*) from xxxx_task_process_test_inner_xkx_exp_inner_xkx_exp;
select * from xxxx_task_process_test_inner_xkx_exp_inner_xkx_exp limit 10; ---检查是否有字符集问题
注意 还需要校验两边的数据,特别是null值。
4.数据迁移完毕后,升级锁并并清理原表数据
升级锁等待
lock table xxxx_task_process_test_exp write ; ---此时所有应用涉及这张表均查写
delete m.* from xxxx_task_process_test_exp m left join xxxx_task_process_test e on m.key2 = e.key2 where e.key3 = 'xxxx' limit 10000;
可以用shell的方式实现
mkdir -p /backup/mysql/test_20220318/delete
for i in$(1 100);do echo "delete m.* from test.xxxx_task_process_test_exp m left join test.xxxx_task_process_test e on m.key2 = e.key2 where e.key3 = 'xxxx' limit 10000; select sleep(1);" >>/backup/mysql/test_20220318/delete/xxxx_task_process_test_exp_delete.sql ;done
然后在上面的事务中执行sql
source /backup/mysql/test_20220318/delete/xxxx_task_process_test_exp_delete.sql
…
循环删除一直到以下结果为0
select count(*) from xxxx_task_process_test_exp m left join xxxx_task_process_test e on m.key2 = e.key2 where e.key3 = 'xxxx' limit 10000;
5.解锁所有的表
unlock tables;
直接将所有迁移后的数据全部回迁回去即可,但是不要指定主键,否则可能导致主键冲突
1.锁定single_table和single_table_exp表
lock table test.single_table read;
2.将single_table_1全表数据回迁到single_table
pt-archiver \
--source h=21.106.106.76,P=3306,u=deploy,p='xxx',D=test,t=single_table_1 --columns key1,key2,key3,key_part1,key_part2,key_part3,common_field \
--dest h=21.106.106.76,P=3306,u=deploy,p='xxx',D=test,t=single_table --columns key1,key2,key3,key_part1,key_part2,key_part3,common_field \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --no-delete
其实整个迁移方案比较简单,但是整个过程需要跟上下游业务做好沟通,我们这个数据库其实做了数据分层治理,也就是将部分数据通过canal采集到ES,所以这部分采集也需要考虑。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。