1. 项目概述在数据库运维领域MySQL主从复制是最基础也最关键的架构设计之一。传统的主从架构通常会将主库和从库部署在不同的服务器上但实际业务场景中我们经常会遇到需要在同一台服务器上同时部署主从库的需求。这种部署方式看似违反常规但在特定场景下却有着不可替代的优势。我最近在为一个客户部署测试环境时就采用了单服务器主从架构。客户需要快速验证业务逻辑但云服务器资源有限。通过在单台4核8G的ECS上部署主从两个MySQL实例不仅节省了60%的云资源成本还完美满足了开发团队的联调测试需求。当然这种架构在生产环境使用时需要更加谨慎。2. 环境准备与规划2.1 硬件资源评估单服务器部署主从库的首要考量是硬件资源是否充足。根据我的经验至少需要满足以下条件CPU建议4核以上主从库的SQL线程和IO线程都是CPU密集型内存每实例建议4GB以上计算公式为(innodb_buffer_pool_size key_buffer_size 其他缓存) × 实例数磁盘推荐SSDIOPS建议在3000以上。特别注意二进制日志和从库relay log的写入压力重要提示务必监控磁盘空间使用率主从库的二进制日志会占用大量空间。我曾经遇到过因为binlog爆满导致服务器宕机的生产事故。2.2 软件版本选择MySQL版本选择直接影响复制功能的稳定性和性能。以下是版本选择的建议MySQL版本复制特性推荐场景5.7GTID复制稳定优先的老系统8.0.23增强型半同步复制新项目首选8.0.26组复制优化高可用集群我强烈建议使用MySQL 8.0最新稳定版它在并行复制和故障恢复方面有显著改进。曾经在5.7版本上遇到的复制延迟问题在8.0上通过设置slave_parallel_workers参数得到了完美解决。2.3 目录结构规划合理的文件目录结构是避免冲突的关键。这是我常用的目录布局/mysql ├── master │ ├── data # 主库数据目录 │ ├── logs # 主库日志目录 │ └── conf # 主库配置文件 └── slave ├── data # 从库数据目录 ├── logs # 从库日志目录 └── conf # 从库配置文件这种结构清晰隔离了两个实例的所有文件避免了配置文件、数据文件和日志文件的冲突。记得为每个目录设置正确的权限chown -R mysql:mysql /mysql chmod 750 /mysql/*/data3. MySQL实例部署3.1 主库安装与配置首先安装MySQL服务器以Ubuntu为例sudo apt update sudo apt install mysql-server-8.0主库的核心配置文件/mysql/master/conf/my.cnf需要特别注意以下参数[mysqld] server-id 1 log_bin /mysql/master/logs/mysql-bin binlog_format ROW binlog_row_image FULL expire_logs_days 7 max_binlog_size 100M binlog_group_commit_sync_delay 100 binlog_group_commit_sync_no_delay_count 10 datadir /mysql/master/data socket /mysql/master/mysql.sock port 3306 # 性能相关 innodb_buffer_pool_size 2G innodb_log_file_size 256M初始化主库数据目录mysqld --initialize --usermysql --datadir/mysql/master/data启动主库服务mysqld_safe --defaults-file/mysql/master/conf/my.cnf 3.2 从库安装与配置从库的安装过程与主库类似但配置有显著差异/mysql/slave/conf/my.cnf[mysqld] server-id 2 relay_log /mysql/slave/logs/relay-bin log_slave_updates ON read_only ON super_read_only ON datadir /mysql/slave/data socket /mysql/slave/mysql.sock port 3307 # 复制性能优化 slave_parallel_workers 4 slave_parallel_type LOGICAL_CLOCK特别注意server-id必须与主库不同使用不同的端口如3307和socket文件启用read_only防止误操作初始化从库数据目录mysqld --initialize --usermysql --datadir/mysql/slave/data启动从库服务mysqld_safe --defaults-file/mysql/slave/conf/my.cnf 4. 主从复制配置4.1 主库用户创建在主库上创建复制专用账户CREATE USER repl% IDENTIFIED WITH mysql_native_password BY SecurePass123!; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;安全提示不要使用弱密码曾经有客户因为使用简单密码导致数据库被入侵。建议密码包含大小写字母、数字和特殊字符长度至少16位。4.2 数据同步有多种方式初始化从库数据我推荐使用mysqldump# 主库备份 mysqldump --single-transaction --master-data2 --triggers --routines --all-databases -uroot -p /tmp/full_dump.sql # 从库恢复 mysql -uroot -p --port3307 --socket/mysql/slave/mysql.sock /tmp/full_dump.sql对于大型数据库可以考虑使用物理备份工具如Percona XtraBackup它能显著减少停机时间。4.3 启动复制在从库上配置复制源CHANGE MASTER TO MASTER_HOST127.0.0.1, MASTER_USERrepl, MASTER_PASSWORDSecurePass123!, MASTER_PORT3306, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154, MASTER_CONNECT_RETRY10;启动复制进程START SLAVE;验证复制状态SHOW SLAVE STATUS\G关键指标检查Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0或很小的值5. 监控与维护5.1 监控指标建立完善的监控体系至关重要。以下是我必监控的关键指标复制延迟SHOW SLAVE STATUS\G线程状态SHOW PROCESSLIST;性能指标SELECT * FROM performance_schema.replication_applier_status_by_worker;5.2 日常维护定期维护任务清单日志清理PURGE BINARY LOGS BEFORE 2023-08-01 00:00:00;表一致性检查pt-table-checksum --replicatetest.checksums h127.0.0.1,uroot,ppassword,P3306定期优化表ANALYZE TABLE important_table;6. 常见问题与解决方案6.1 复制中断处理错误示例Last_Error: Could not execute Write_rows event on table test.t1; Duplicate entry 1 for key PRIMARY, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY解决方案STOP SLAVE; SET GLOBAL sql_slave_skip_counter 1; START SLAVE;更安全的做法是使用GTIDSTOP SLAVE; SET SESSION.GTID_NEXT aaa-bbb-ccc-ddd:12345; BEGIN; COMMIT; SET SESSION GTID_NEXT AUTOMATIC; START SLAVE;6.2 性能优化如果发现复制延迟可以尝试增加并行复制线程STOP SLAVE; SET GLOBAL slave_parallel_workers 8; START SLAVE;调整以下参数slave_parallel_type LOGICAL_CLOCK slave_preserve_commit_order ON优化主库binlog写入sync_binlog 1000 binlog_group_commit_sync_delay 1007. 高级配置技巧7.1 半同步复制提高数据安全性的配置主库INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; SET GLOBAL rpl_semi_sync_master_enabled 1; SET GLOBAL rpl_semi_sync_master_timeout 10000; # 10秒从库INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_slave_enabled 1;7.2 过滤复制只需要复制特定库或表时CHANGE REPLICATION FILTER REPLICATE_DO_DB (important_db), REPLICATE_IGNORE_DB (mysql, sys, performance_schema);7.3 多源复制单从库可以同时复制多个主库CHANGE MASTER TO MASTER_HOSTmaster1 ... FOR CHANNEL master1; CHANGE MASTER TO MASTER_HOSTmaster2 ... FOR CHANNEL master2;8. 生产环境注意事项资源隔离使用cgroups或Docker容器隔离主从实例资源监控告警设置复制延迟超过5分钟触发告警定期演练每季度执行主从切换演练备份策略即使有从库也要坚持定期全量备份安全加固配置适当的防火墙规则和访问控制我曾经遇到过一个典型案例客户的生产系统因为磁盘IO瓶颈导致主从延迟高达2小时。通过分析发现是主库和从库的binlog写入产生了IO竞争。解决方案是将主库的binlog和从库的relay log分别放在不同的物理磁盘上同时调整了sync_binlog参数最终将延迟控制在10秒以内。