《MySQL 复制全家桶实战：从级联复制到 MGR 多主组复制的踩坑全复盘》

MySQL复制全家桶实操复盘级联复制多源复制半同步MGR组复制踩坑全记录今天把MySQL的几种主流复制架构从头到尾实操了一遍从最基础的级联复制到多源复制、半同步复制最后搭完了多主模式的MGR组复制。整个过程不算太顺利踩了好几个经典的细节坑特意把完整步骤、报错现象、排查思路和解决方法都整理出来既是自己的复盘笔记也给大家避避坑。一、环境与前期准备1. 节点规划本次实操一共3台虚拟机MySQL版本统一为8.0.36源码编译安装节点规划如下主机名IP地址初始角色server7192.168.241.137主库Aserver6192.168.241.136中间节点Bserver5192.168.241.135从库C2. 基础环境前置配置正式搭复制之前先把基础的Web环境和MySQL开机自启配好方便后面用phpMyAdmin管理数据库。首先创建phpMyAdmin的软链接到Nginx站点目录修改目录权限ln-s/usr/local/nginx/html/phpMyAdmin-5.2.3-all-languages /usr/local/nginx/html/phpmyadminchown-Rnginx:nginx /usr/local/nginx/html/phpmyadmin然后把Nginx和PHP-FPM加入开机自启同时开启mysqld的系统自启echo/usr/local/nginx/sbin/nginx/etc/rc.d/rc.localchmodx /etc/rc.d/rc.localecho/usr/local/php/sbin/php-fpm/etc/rc.d/rc.local systemctlenablemysqld二、级联复制A→B→C搭建与踩坑级联复制就是主库A同步到中间节点BB再同步到从库C适合跨机房或者从库数量多的场景可以有效减轻主库的同步压力。1. 主库创建复制专用账号首先在主库server7上创建复制用户授予REPLICATION SLAVE权限并且修改认证插件为mysql_native_password兼容同步链路的认证需求CREATEUSERrep1%IDENTIFIEDBYwestos;GRANTREPLICATIONSLAVEON*.*TOrep1%;ALTERUSERrep1%IDENTIFIEDWITHmysql_native_passwordBYwestos;FLUSHPRIVILEGES;创建完成后查看主库的binlog状态记录文件和位置点后续配置从库会用到SHOWMASTERSTATUS;2. 中间节点B核心配置级联复制最关键的一步中间节点必须开启log_slave_updates参数。如果不开中继日志中的同步事件不会写入节点自身的binlog下游从库C就拿不到数据同步会一直卡住。修改server6的/etc/my.cnf配置文件添加相关参数后重启MySQL服务vim/etc/my.cnf systemctl restart mysqld重启后登录MySQL验证参数是否生效SHOWVARIABLESLIKElog_slave_updates;可以看到Value为ON配置生效。3. 从库同步配置踩坑GTID未开启报错一开始我图省事直接用GTID自动定位的方式配置同步结果直接踩了本次实操第一个坑STOP SLAVE;CHANGE MASTERTOMASTER_HOST192.168.241.136,MASTER_PORT3306,MASTER_USERrep1,MASTER_PASSWORDwestos,MASTER_AUTO_POSITION1;执行后直接抛出错误ERROR 1777 (HY000): CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION 1 cannot be executed because GLOBAL.GTID_MODEOFF.当时当场愣了一下才反应过来当前环境还没开启GTID模式自然不能用自动定位。解决方法有两种要么开启GTID要么改用传统的binlog文件位置点方式同步。这里先改用传统方式完成级联基础同步后面做多源复制前再统一开启GTID。4. 级联复制数据验证配置完成后启动从库同步服务在主库插入一条测试数据验证A→B→C整条链路是否通畅INSERTINTOtest.users(username,password)VALUES(new_test,999999);在从库C查询users表可以看到数据已经同步过来加上之前的测试数据一共5条级联复制搭建成功。SELECT*FROMtest.users;三、多源复制搭建实现A→C B→C双源同步级联搭完之后我们给从库C再加一个同步源直接从主库A同步数据最终形成「A→B→C级联 A→C直连」的多源架构C节点可以同时接收两个上游的同步事件。1. 重置从库同步状态先在server5上停止原来的同步重置所有同步配置方便后续配置多通道vim/etc/my.cnf systemctl restart mysqldSTOP SLAVE;RESET SLAVEALL;2. 配置双复制通道MySQL多源复制通过不同的channel名称来区分多个同步源我们分别创建两个通道channel_a直接连接主库Aserver7192.168.241.137channel_b连接中间节点Bserver6192.168.241.136在server5上配置第一个通道channel_aCHANGE MASTERTOMASTER_HOST192.168.241.137,MASTER_PORT3306,MASTER_USERrep,MASTER_PASSWORDwestos,MASTER_AUTO_POSITION1FORCHANNELchannel_a;在server6上配置通道channel_b对应级联上游CHANGE MASTERTOMASTER_HOST192.168.241.136,MASTER_PORT3306,MASTER_USERrepi,MASTER_PASSWORDwestos,MASTER_AUTO_POSITION1FORCHANNELchannel_b;注意这里我们已经提前开启了GTID模式所以可以直接使用MASTER_AUTO_POSITION自动定位不用再手动指定binlog文件和位置。3. 多源复制效果验证配置完成后启动所有通道的同步在主库插入一条测试数据multi_source_okINSERTINTOtest.users(username,password)VALUES(multi_source_ok,888888);在从库C查询可以看到数据已经同步过来总数据量变成6条多源复制配置成功最终实现了A→B→C级联 A→C直连的双源架构。四、半同步复制配置与踩坑默认的MySQL复制是异步的主库提交事务后不用等从库确认就会返回客户端极端情况下主库宕机会丢失最后一部分数据。半同步复制就是要求主库至少收到一个从库的确认后再返回事务提交成功以此提升数据一致性。1. 确认半同步插件状态MySQL 8.0默认已经内置半同步插件先确认插件是否已经正常加载SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMELIKEsemi%;主库侧开启半同步相关参数2. 从库开启半同步的经典坑先在从库执行开启半同步的全局参数SETGLOBALrpl_semi_sync_slave_enabled1;执行完之后我直接查询参数状态结果发现Value还是OFF当时反复确认命令没写错折腾了好一会儿才想起来半同步从库参数生效必须重启IO线程否则不会即时生效。3. 多源场景下的半同步开启因为我们是多源复制有两个独立的复制通道所以需要针对每个通道分别重启IO线程不能只全局重启一次-- 重启channel_a的IO线程STOP SLAVE IO_THREADFORCHANNELchannel_a;STARTSLAVE IO_THREADFORCHANNELchannel_a;-- 重启channel_b的IO线程STOP SLAVE IO_THREADFORCHANNELchannel_b;STARTSLAVE IO_THREADFORCHANNELchannel_b;重启完成后再次查询参数已经成功变成ON。SHOWVARIABLESLIKErpl_semi_sync_slave_enabled;重点提醒多源复制场景下开启半同步一定要逐个通道重启IO线程只执行全局参数设置是不会生效的这个细节非常容易踩坑也是面试的高频考点。4. 主库半同步状态验证在主库查看半同步运行状态可以看到Rpl_semi_sync_master_clients的值为2说明两个从库都已经以半同步模式连接到主库。SHOWSTATUSLIKERpl_semi_sync%;SHOWSTATUSLIKERpl_semi_sync_master_clients;5. 半同步降级机制测试半同步有一个超时降级机制如果主库等待从库确认超过默认的10秒就会自动降级为异步复制避免业务长时间阻塞。我们手动模拟从库IO线程异常然后在主库插入一条数据INSERTINTOtest.users(username,password)VALUES(semi_test,123456);执行后明显卡住掐表等了刚好10秒才返回Query OK。再查看半同步状态Rpl_semi_sync_master_no_tx的值变成了1代表有1个事务没有收到从库确认触发了降级。当时等的时候还以为数据库卡了反应过来是超时降级的时候对这个机制的理解一下就深刻了。6. 延迟复制特性验证顺便测试了一下延迟复制功能配置从库延迟30秒应用日志。在主库插入一条测试数据INSERTINTOtest.users(username,password)VALUES(delay_verify_ok,pass123456);插入后立刻在从库查询是空结果30秒后再次查询数据成功出现完美验证了延迟复制的效果。SELECT*FROMtest.usersWHEREusernamedelay_verify_ok;五、MGR组复制多主模式完整部署前面的都是传统的主从复制架构最后我们来搭MGRMySQL Group Replication组复制采用多主模式三个节点都可以读写基于Paxos协议保证数据一致性是MySQL原生的高可用方案。1. 统一配置文件参数三个节点都需要修改my.cnf配置文件加入复制基础参数和组复制核心参数。注意每个节点的server_id和group_replication_local_address要改成对应自己的IP和端口不能完全照搬。完整配置如下[mysqld] # 基础运行参数 basedir/usr/local/mysql datadir/data/mysql socket/data/mysql/mysql.sock port3306 usermysql character-set-serverutf8mb4 default-storage-engineINNODB default_authentication_pluginmysql_native_password max_connections1000 disabled_storage_enginesMyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY # 复制基础参数 server_id3 gtid_modeON enforce_gtid_consistencyON master_info_repositoryTABLE relay_log_info_repositoryTABLE binlog_checksumNONE log_slave_updatesON log_binbinlog binlog_formatROW # 组复制核心参数 plugin_load_addgroup_replication.so transaction_write_set_extractionXXHASH64 group_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa group_replication_start_on_bootoff group_replication_local_address192.168.241.135:33061 group_replication_group_seeds192.168.241.137:33061,192.168.241.136:33061,192.168.241.135:33061 group_replication_bootstrap_groupoff group_replication_ip_whitelist192.168.241.0/24,127.0.0.1/8 # 多主模式配置 group_replication_single_primary_modeOFF group_replication_enforce_update_everywhere_checksON group_replication_allow_local_disjoint_gtids_join1 [mysql] socket/data/mysql/mysql.sock default-character-setutf8mb4 [client] socket/data/mysql/mysql.sock配置修改完成后三个节点都重启MySQL服务。2. 配置组复制恢复通道MGR组内新节点加入时会通过专门的恢复通道来同步全量和增量数据所以每个节点都需要创建复制用户并配置group_replication_recovery通道。以server7为例CREATEUSERrep%IDENTIFIEDBY123456;GRANTREPLICATIONSLAVEON*.*TOrep1%;FLUSHPRIVILEGES;CHANGE MASTERTOMASTER_USERrep1,MASTER_PASSWORD123456FORCHANNELgroup_replication_recovery;节点配置细节补充server5和server6节点执行相同的操作创建用户并配置恢复通道。3. 引导启动第一个节点组复制的第一个节点需要手动引导组的创建注意bootstrap_group参数只能临时开启启动完成后必须立刻关闭否则后续节点重启会导致组分裂、脑裂。在server7上执行SETGLOBALgroup_replication_bootstrap_groupON;STARTGROUP_REPLICATION;SETGLOBALgroup_replication_bootstrap_groupOFF;启动后查看组成员状态可以看到server7已经在线角色为PRIMARYSELECT*FROMperformance_schema.replication_group_members;4. 其余节点加入组依次在server6和server5上启动组复制STARTGROUP_REPLICATION;刚启动时节点状态为RECOVERING代表正在进行数据恢复三个节点都启动后短暂等待数据同步完成最终三个节点的MEMBER_STATE都变成ONLINE且角色都是PRIMARY代表多主模式的MGR组复制搭建完成。六、踩坑总结与实操心得1. 核心踩坑点汇总GTID与自动定位未开启GTID模式时不能使用MASTER_AUTO_POSITION1配置同步否则报错1777要么开启GTID要么改用传统文件位置方式。级联复制必备参数中间节点必须开启log_slave_updates否则下游从库无法获取binlog事件同步会卡住但不报错排查起来很隐蔽。半同步生效条件从库设置半同步参数后必须重启IO线程才会生效多源复制场景需针对每个通道单独重启。MGR引导参数group_replication_bootstrap_group只能在首个节点启动时临时开启启动后必须设为OFF严禁长期开启。认证插件兼容MySQL 8.0默认认证插件是caching_sha2_password复制用户建议改为mysql_native_password避免兼容性问题。2. 实操心得MySQL的复制体系看起来分支很多从异步、半同步到组复制从一主一从、级联到多源本质上都是围绕binlog的流转和一致性保证做的扩展。很多坑光看文档记不住亲手操作一遍、踩一遍报错理解才会到位。尤其是半同步重启IO线程、级联开log_slave_updates这种细节属于面试高频考点实操过一次就再也不会忘。后面还可以继续测试MGR的故障切换、多主写入冲突等场景慢慢把整个复制体系的知识点都串起来。