MySQL分表方案介绍

原创

雨夜v1

发布于 2022-03-18 23:00:14

1.8K0

发布于 2022-03-18 23:00:14

文章被收录于专栏：雨夜-Elasticsearch成长专栏雨夜-Elasticsearch成长专栏

1.业务背景

xx银行某外呼系统，每天任务分配表数据已经达到2000w以上，由于任务的SQL较为复杂。DBA除了优化SQL外，也同步需要对大表进行分表改造。本次分表主要依据任务类型对任务分配表进行水平分表，而本文主要介绍了两种水平分表的方案，希望能给大家一些帮助。

2.环境介绍

操作系统:Linux 7.9

数据库信息:MGR 单主模式版本 MySQL 5.7.26

3.大表拆分方案介绍

以下所有表均为测试表

方案一：

表结构：

 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

4.总结

其实整个迁移方案比较简单，但是整个过程需要跟上下游业务做好沟通，我们这个数据库其实做了数据分层治理，也就是将部分数据通过canal采集到ES，所以这部分采集也需要考虑。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

登录后参与评论

0 条评论

热度