文章摘要
这篇文章详细介绍了如何在MySQL环境中完成级联复制部署及优化，主要内容包括：
1. 搭建1主2从的级联复制环境（M-S1-S2），其中M为主库，S1和S2为从库；
2. 设置磁盘IO参数以解决资源不足问题，具体包括`innodb_buffer_pool_size`、`innodb_io_capacity`等；
3. 配置`binlog_expire_logs_seconds`等全局参数以调整复制时长；
4. 扩容主库表并进行数据同步；
5. 更改复制主从关系并进行数据备份与恢复；
6. 最终将S2升级为主库，S1转为从库，并优化参数设置。
文章重点围绕级联复制环境的搭建、参数配置及数据同步操作展开，适合MySQL administered人员参考。
环境装备:开启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;