MySQL级联复制下如何进行大表的字段扩容（mysql复制表结构语句）快来看

文章摘要

这篇文章介绍了如何配置MySQL环境，重点围绕级联复制（M-S1-S2）的环境装备和参数设置。主要内容包括： 1. **环境搭建**：搭建1主2从的级联复制，M→S1→S2，注意设置`lower_case_table_names=0`。 2. **参数设置**： - `binlog_expire_logs_seconds`设置为1209600秒，适用于主库和级联主库。 - 防止复制报错，设置`slave_type_conversions='ALL_NON_LOSSY'`和`interactive_timeout=144000`等。 - 配置磁盘IO参数，如`innodb_buffer_pool_size=32G`、`innodb_flush_log_at_trx_commit=2`等。 3. **扩容操作**：在S2上进行字段扩容，预估耗时10小时。 4. **数据同步**：完成扩容后，等待延迟同步，对比主从GTD，移除S1，建立M-S2主从关系。 5. **备份与恢复**：备份S2，恢复S1，重新建立级联复制，并应用停服等待数据一致。 6. **环境恢复**：最终S2成为主库，S1为从库，恢复参数设置。文章详细描述了整个过程中的关键步骤和注意事项，适合MySQL级联复制配置参考。

环境装备:开启Gtid,注意M,S1 binlog保存时长,磁盘剩余空间大于待变更表的2倍
show global variables like ‘binlog_expire_logs_seconds’; # 默认604800
set 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
数据同步至主从一致,对比主从Gtid
4.移除S1,建立M-S2的主从关系
S1 (可选)
stop slave;
reset slave all;
systemctl stop mysql_3306
S2
stop 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/data
chown -R mysql. data/
chown -R mysql. binlog/*
chown -R mysql. redolog/*
systemctl start mysql_3306
set 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，对比主从Gtid
7.最终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;