作者：雷文霆
爱可生华东交付服务部 DBA 成员，次要负责Mysql故障解决及相干技术支持。喜好看书，电影。座右铭，每一个未曾起舞的日子，都是对生命的辜负。
本文起源：原创投稿
*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
一、背景
某客户的业务中有一张约4亿行的表，因为业务扩大，表中open_id varchar(50) 须要扩容到 varchar(500).
变更期间尽量减少对主库的影响(最好是不要有任何影响->最终争取了4个小时的窗口期)。
二、库表信息
环境：Mysql 8.0.22
1主1从 基于Gtid复制
1.第一个问题，这是一张大表吗? 是的，请看
此表的ibd 文件280G + count长时间无返回 + 应用备库看了一下确认行数>4亿
以下语句也能够查看：show table status from dbname like 'tablename'\G # Rows 的值不准，有时误差有2倍SELECT a.table_schema,a.table_name,concat(round(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024),2) ,'MB')total_size,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') AS data_size,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') AS index_size FROM information_schema.TABLES a WHERE a.table_schema = 'dbname' AND a.table_name = 'tablename'; #看下此表的数据量既然是大表，咱们应该应用什么形式做变更呢？
三、计划抉择
下文中的 M 示意主库，S1 为从1 ，S2 为从2
| 形式 | 长处 | 毛病 | 可行性 |
|---|---|---|---|
| OnlineDDL | 原生，应用两头长期表 | ALGORITHM=COPY时，会阻塞DML，举荐版本>MySQL5.7 | 5星 |
| Gh-ost | 应用binlog+回放线程代替触发器 | 第三方工具，依据不同的参数导致执行工夫较长 | 4星 |
| Pt-osc | 版本兼容性好,应用触发器放弃主副表统一 | 第三方工具，且应用限度较多 | 3星 |
| M-S1-S2 | 工夫可预估 | 级联复制，人工操作 | 1星 |
为什么咱们没有抉择前3种计划？
依据理论状况评估，本次业务侧的需要是此表24h都有业务流量，且不承受超过4小时的业务不可用工夫
OnlineDDL的形式，ALGORITHM=COPY时，期间会阻塞DML(只读)，最初主副表rename操作时(不可读写)，直到DDL实现(其中须要的工夫不确定)。
Gh-ost的形式，举荐的模式为连贯从库，在主库转换，此模式对主库影响最小，可通过参数设置流控。致命的毛病是此工具的变更工夫太长，4亿的表，测试环境应用了70个小时。最初咱们还须要下发切换命令及手动删除两头表*_del。如果是1主2从还是比拟举荐这种形式的，因为还有一个从库能够保障数据安全。
Pt-osc 和Gh-ost都属于第三方，Pt-osc 对大表的操作和OnlineDDL有一个独特的毛病就是失败回滚的代价很大。
如果是低版本如MySQL<5.7能够应用，实践上OnlineDDL是在MySQL5.6.7开始反对，刚开始反对的不是很好，可适当取舍。
最初咱们抉择了，DBA最青睐(xin ku)的一种形式，在M-S1-S2级联复制下进行。
四、如何进行操作
- 新建一个S1的从库，构建M-S1-S2级联复制
- 应用OnlineDDL在S2上进行字段扩容 (长处是期间M-S1的主从不受影响)
- 扩容实现后，期待提早同步M-S1-S2 (升高S2与M的数据差别,并进行数据验证)
- 移除S1,建设M-S2的主从关系(使S2持续同步M的数据)
- 备份S2复原S1，建设M-S2-S1级联复制
- 利用停服，期待主从数据统一(长处是差别数据量的同步工夫很短)
- 最终S2成为主库，S1为从库(利用须要批改前端连贯信息)
- 利用进行回归验证
以上内容看上去很简单，实质上就是备份复原。读者可将其做为备选计划。分享一下具体步骤?
环境配备:开启Gtid,留神M,S1 binlog保留时长,磁盘残余空间大于待变更表的2倍show global variables like 'binlog_expire_logs_seconds'; # 默认604800set global binlog_expire_logs_seconds=1209600; # 主库和级联主库都须要设置1.搭建 1主2从的级联复制，M -> S1 -> S2 ，装置MySQL留神本次环境lower_case_table_names = 0 2.在S2 上做字段扩容。 预估 10个小时`参数设置:`set global slave_type_conversions='ALL_NON_LOSSY'; # 避免复制报错SQL_Errno: 13146，属于字段类型长度不统一无奈回放set global interactive_timeout=144000;set global wait_timeout =144000;`磁盘IO参数设置:`set global innodb_buffer_pool_size=32*1024*1024*1024;# 减少buffer_pool 避免Error1206The total number of locks exceeds the lock table size 资源有余set global sync_binlog=20000;set global innodb_flush_log_at_trx_commit=2;set global innodb_io_capacity=600000;set global innodb_io_capacity_max=1200000; # innodb_io_capacity须要设置两次show variables like '%innodb_io%'; # 验证以上设置screen 下执行:time mysql -S /data/mysql/3306/data/mysqld.sock -p'' dbname -NBe "ALTER TABLE tablename MODIFY COLUMN open_id VARCHAR(500) NULL DEFAULT NULL COMMENT 'Id' COLLATE 'utf8mb4_bin';"查看DDL进度:SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;3.扩容实现后，期待提早同步M-S1-S2 数据同步至主从统一,比照主从Gtid4.移除S1,建设M-S2的主从关系S1 (可选)stop slave;reset slave all;systemctl stop mysql_3306S2stop slave;reset slave all;# MASTER_HOST='M主机IP' CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD=', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_CONNECT_RETRY=10;start slave; (flush privileges;# 验证数据可失常同步)5.备份S2复原S1，建设M-S2-S1级联复制物理备份S2,重做S2->S1 级联主从rm -rf binlog/*rm -rf redolog/*xtrabackup --defaults-file=/data/mysql/3306/my.cnf.3306 --move-back --target-dir=/data/actionsky/xtrabackup_recovery/datachown -R mysql. data/chown -R mysql. binlog/*chown -R mysql. redolog/*systemctl start mysql_3306set global gtid_purged='';reset slave all;# MASTER_HOST='S2主机IP' ,已扩容变更完的主机CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_CONNECT_RETRY=10;`MySQL8.0版本须要在下面语句中增加 GET_MASTER_PUBLIC_KEY=1; #避免 Last_IO_Errno: 2061 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.`start slave;6.利用停服，期待主从数据统一主库停服+可设置read_only+flush privileges，比照主从Gtid7.最终S2成为主库，S1为从库利用更改配置连贯新主库。S2上:stop slave;reset slave all;set global read_only=0;set global super_read_only=0;`show master status\G 察看是否有新事务写入`收尾：还原第2步的参数设置。set global interactive_timeout=28800;set global wait_timeout =28800;set global innodb_buffer_pool_size=8*1024*1024*1024;set global slave_type_conversions='';set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;set global innodb_io_capacity=2000;set global innodb_io_capacity_max=4000;补充场景: 基于磁盘IO能力的测试
间接在主库上批改，且无流量的状况下：场景1，磁盘是NVME的物理机，4亿数据大概须要5个小时(磁盘性能1G/s)。场景2，磁盘是机械盘的虚拟机，此数据量大概须要40个小时(磁盘性能100M/s)。五、总结
- 应用级联，对于业务侧来说，工夫老本次要在利用更改连贯和回归验证。如果从库无流量，不须要期待业务低峰。
- OnlineDDL可通过批改参数，提高效率，其中双一参数会影响数据安全，举荐业务低峰期操作。
- Gh-ost 适宜变更工夫拮据的场景，业务低峰期操作，可调整参数加快进度，自定义切换的工夫。
- 以上形式均不举荐多个DDL同时进行，即并行DDL。
- 大表操作和大数据量操作，须要咱们贴合场景找到适合的变更计划，不须要最优，须要适合。
福利工夫：分享一个速查表