MySQL生僻字改造项目排坑大法

雨夜v1 • 2023-01-02 • 云技术社区 • 280 阅读

1.背景

中信银行的某个大佬在一个会议中提及中信银行将成为中国第一个在技术上支持生僻字的商业银行。由于历史原因，我们的MySQL部署规范提供的字符集为utf8字符集，需要将数据库的字符集集体转换为utf8mb4。

2.难点分析

众所周知，中信银行核心数据库用的是Goldendb,是支持utf8mb4的，所以并不需要花很长的时间去改造。本次讲的这个生僻字改造主要是针对中信银行贷前的一个核心数据库，该库是采用mgr 3+1的架构部署的部署架构图如下:

MGR 3+1跨机房部署

难点一: 相关贷前的MGR集群涉及多个业务库，每个业务库数据量大约在500GB（共四个业务库），所以单个集群的业务库数据为2TB，数据量大，涉及的改造关联表多，改造难度大;

难点二：改造的业务库涉及上下游采数，所以需要提前评估改造上下游采数影响;

难点三：整体改造时间短，需要在四个小时内完成改造，需要充分评估好回退方案;

难点四：业务库通过canal采集MySQL的binlog,然后通过kafka消费到ES，实现了业务数据的分层治理，本次字符编码的改造必然会造成大量binlog的写入，导致kafka消费延迟，影响业务查询，所以如何解决kafka消费延迟也是本次的苦难之一；

3.方案设计

3.1 方案一：分批改造法

3.1.1 配置文件修改

vim /etc/my.cnf
[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'

参数解释

  • character-set-server：数据库默认字符集，主流字符集支持一些特殊表情符号（特殊表情符占用 4 个字节）
  • collation-server：数据库字符集对应一些排序等规则，注意要和 character-set-server 对应
  • init_connect：设置 client 连接 mysql 时的字符集，防止乱码 ,如果数据库有其他字符集，请慎用。
  • character-set-client-handshake：要忽略客户信息并使用默认的服务器字符集，如果数据库不完全是utf8mb4字符集，请谨慎使用。

3.1.2 表字符集修改分批处理

第一批次: 小于200w的表，直接采用alter的方式进行字符集修改 (900+张表，需要停止应用)

1.修改数据库的字符集编码
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
2.修改表的字符集编码
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
3.批量修改
select concat("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") from information_schema.tables where TABLE_SCHEMA='test' and TABLE_ROWS<20000000 order by TABLE_ROWS desc;

第二批次：将小于200w的表大于2000w的表采用mydumper的方式导入导出修改字符集 (50+ 张表,需要停止应用)

1.用mydumper导出数据
mydumper -u root-p XXXXX -h 192.168.1.1 -P 3306 -v 3 -t 16 -F 50 -B testdb -T table_1,table_2 -o /backup/mydumper/data &
2.rename表
rename table_1 to table_1_bak_20210718;
3.创建新表并修改字符集;
create table table_1 like table_1_bak_20210718;
ALTER TABLE table_1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
4.导入数据
myloader -u root -p XXXX -S /appdata/mysql/mysql.sock -v 3 -t 16 -e -B testdb -d /backup/mydumper/data &

第三批次，将大于2000w的表直接用pt-osc工具在线修改(9张上亿数据的表)

pt-online-schema-change -u root -h localhost -p "xxx -P 3306 --alter=" CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" D=sysbench,t=sbtest1 --charset=utf8 --no-version-check  --statistics --critical-load="Threads_running:200" --max-load="Threads_running=25" --print --execute

注意事项:

  1. 日志空间需要重点关注
  2. 资源的使用情况，包括内存，io,cpu等
  3. tmp表空间的大小
  4. 字符集修改过程中，关闭mgr的流控

3.2 方案二:单库直接改造法

这个方案简单粗暴，直接停业，主要步骤如下:

  1. 停止业务，停止两个机房的HA，防止流量进入;
  2. 待mgr三个节点数据一致后，停止MGR两个从节点；
  3. 在主节点修改所有表的字符集，如果规定时间内修改完成，则重做MGR的另外两个从节点；如果规定时间内字符集修改未能完成，则停止主节点，启用两个从节点重新组建集群，重做老主即可。

该方案需要优化一下mgr的几个参数:

innodb_io_capacity

如官当，由于磁盘是ssd做的是raid10，所以完全可以把磁盘给打到80%，所以经过一番测试之后我把innodb_io_capacity

调到了3000，将innodb_io_capacity_max改成了5000，尽可能的加快convert的速度。

4.艰难的排坑之路

理想总是丰满的，现实总是残酷的。方案看起来很完美，进退自如。但实际上确实山高路远坑好深。

4.1 坑一：隐式转换问题

方案一按照计划先将第一批小表改造完毕后，发现部分业务点查询，半天没有结果。数据库的cpu也被打满了。这个时候赶紧登录数据库查看异常的SQL有哪些。

pager grep -v Sleep 
show processlist;
xxxxx
xxxxx
200个查询在执行而且每一条执行的都很慢，于是查看完整的异常SQL
 select ID,DB,INFO from information_schema.processlist where STATE <> 'Sleep' and info is not null;

这个时候发现都是字符集为utf8mb4和utf8的表进行关联查询的语句，这个时候问题就很明显了，大小表关联字符集不一致导致的隐式转换问题，导致查询大表的时候都是进行全表扫描（utf8mb4是utf8的超集。所以当两个字段类型的字符串在做比较的时候MySQL是先把utf8字符串转成utf8mb4字符集，再做比较的）。

4.2 坑二：方案二进件回退问题

按照方案二，如果MySQL修改成功了，业务需要通过进件去校验系统是否正常，那这个进件的过程从节点是没有同步数据的。如果最终业务校验有异常，回退则会丢失部分数据。那这个回退到底要如何处理呢？

猜想一: 直接启动两个从节点，跳过convert的事务，然后用utf8mb4的表和utf8的表进行数据同步，下面是测试验证方案的过程:

1.记录三个节点的gtid，并对比是否一致
m1:
sbtest3
Create Table: CREATE TABLE `sbtest3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1871799 DEFAULT CHARSET=utf8
1 row in set (0.00 sec);

Executed_Gtid_Set: 43bfe852-f322-11ea-8c89-005056297a00:1-48,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-532939

m2:
Executed_Gtid_Set: 43bfe852-f322-11ea-8c89-005056297a00:1-48,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-532939

m3:
Executed_Gtid_Set: 43bfe852-f322-11ea-8c89-005056297a00:1-48,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-532939

2.停止m2和m3两个节点
stop group_Replication;

3.修改数据库的字符集
alter database test CHARACTER SET utf8mb4;
ALTER TABLE sbtest3 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4.查看主节点的gtid
Executed_Gtid_Set:
 
5.从节点重置gtid并启动组复制
m2 和m3
reset master;
set global gtid_purged='43bfe852-f322-11ea-8c89-005056297a00:1-48,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-532954';
start group_Replication;

6.sbtest1 进行增删改查操作
insert into sbtest3 values (267404,4506405, '48106784413-43381850026-86198991016-71089559483-56197354911-90536096048-73634963624-07341059004-29497941405-81406099226','89745911914-64263695991-64635842344-65641596363-04208401864');

7.集群出现异常报错为
2021-07-16T14:35:48.637304+08:00 97 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 1 failed executing transaction 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:532954'; Column 2 of table 'test.sbtest3' cannot be converted from type 'char(480(bytes))' to type 'char(360(bytes) utf8)', Error_code: 1677

8.结论
mgr在对表增删改查的时候,主节点是utf8mb4的数据(每个字符有4个字节)，从节点是utf8字符集（每个字符有3个字节），所以不能将4个字节转换成3个字节。

猜想一验证失败，方案否定。

猜想二：业务有白名单的进件，这些白名单的进件可以去除。通过跟业务确认，这个方案可行。

5.总结

技术方案其实就是大胆猜想，小心验证得到的。理解好MySQL内部原理，做到每个操作都胸有成竹，你就赢了。路漫漫其修远兮，我将上下而求索。

本站文章资源均来源自网络，除非特别声明，否则均不代表站方观点，并仅供查阅，不作为任何参考依据！
如有侵权请及时跟我们联系，本站将及时删除！
如遇版权问题，请查看 本站版权声明
THE END
分享
二维码
海报
MySQL生僻字改造项目排坑大法
中信银行的某个大佬在一个会议中提及中信银行将成为中国第一个在技术上支持生僻字的商业银行。由于历史原因，我们的MySQL部署规范提供的字符集为utf8字符集，需要...
<<上一篇
下一篇>>