MySQL 主从同步配置（生产无停机：运行1年主库 + 全量+增量同步）
场景是主库已长期运行（1年）、业务不能停机，核心方案是：
1. 全量同步：对主库做热备份（不锁表、不影响业务），导出所有历史数据；
2. 增量同步：基于主库二进制日志（binlog），自动同步备份后产生的所有新数据；
3. 全程无需重启主库、无业务中断（InnoDB引擎首选，99%生产环境适用）。
前置条件
1. 主从库MySQL版本一致（大版本相同，如8.0/5.7）；
2. 主库已开启 binlog（二进制日志，增量同步的核心）；
3. 主从库网络互通，3306端口开放；
4. 主库存储引擎：优先InnoDB（支持热备，无锁表）；
5. 从库为全新初始化的MySQL（无业务数据，避免冲突）。
一、主库配置检查（关键：已运行1年，无需重启）
1. 检查binlog是否开启
登录主库MySQL执行：
| SQL SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'server_id'; |
|---|
• 必须满足：log_bin = ON（增量同步依赖）；
• server_id 为唯一数字（如1，主从不能重复）。
2. 若binlog未开启（极少情况，需低峰期重启主库）
编辑主库配置文件（my.cnf / my.ini）：
| TOML [mysqld] # 主库唯一ID，不能和从库重复 server-id = 1 # 开启binlog，文件名前缀 log-bin = mysql-bin # binlog格式（推荐ROW，同步最准确） binlog-format = ROW # 保留binlog天数（自动清理，避免磁盘满） expire_logs_days = 7 |
|---|
重启主库MySQL（业务低峰期操作），再次验证binlog开启。
二、主库操作：创建复制专用账号
主库创建仅用于主从同步的账号，最小权限原则：
| SQL -- 创建账号（repl为用户名，从库IP替换为你的从库地址，123456为密码） CREATE USER 'repl'@'192.168.1.100' IDENTIFIED BY 'Repl@123456'; -- 授予复制权限（仅需要REPLICATION SLAVE） GRANT REPLICATION SLAVE ON . TO 'repl'@'192.168.1.100'; -- 刷新权限 FLUSH PRIVILEGES; |
|---|
三、主库操作：全量热备份（无锁表、不影响业务）
核心备份命令（InnoDB专用，生产标准方案）
使用 mysqldump 做热备，参数 --single-transaction 实现无锁表，--master-data=2 自动记录binlog位置（增量同步的起点）：
| Bash # 登录主库服务器，执行备份（备份所有数据库） mysqldump -uroot -p \ --all-databases \ --single-transaction \ --master-data=2 \ --flush-logs \ > full_backup_$(date +%Y%m%d).sql |
|---|
参数说明
• --all-databases：备份所有库（可替换为 --databases 库名 备份指定库）；
• --single-transaction：InnoDB热备，不锁表、不影响业务读写；
• --master-data=2：将binlog文件名+位置以注释形式写入备份文件（无需手动查询）；
• --flush-logs：切割主库binlog，增量同步从新日志开始，更干净。
备份完成后
1. 记录备份文件（如 full_backup_20260323.sql）；
2. 无需锁表、无需停机，直接进入下一步。
四、传输备份文件到从库
将主库的全量备份文件，复制到从库服务器（用scp/rzsz/ftp均可）：
| Bash # 主库执行，传输到从库 scp full_backup_20260323.sql root@192.168.1.100:/root/ |
|---|
五、从库配置：基础参数设置
1. 编辑从库配置文件（ my.cnf ）
| TOML [mysqld] # 从库唯一ID（必须≠主库的1） server-id = 2 # 开启中继日志（主从同步核心） relay-log = mysql-relay-bin # 从库只读（防止误写入，超级管理员除外） read_only = 1 # 可选：级联同步用，普通主从无需开启 # log_slave_updates = 1 |
|---|
2. 重启从库MySQL
| Bash systemctl restart mysqld |
|---|
六、从库操作：恢复全量数据
登录从库服务器，导入主库的全量备份：
| Bash mysql -uroot -p < /root/full_backup_20260323.sql |
|---|
等待导入完成（时间根据数据量大小，1年数据耐心等待）。
七、从库操作：配置主从同步（增量同步起点）
1. 获取binlog同步起点（关键）
从备份文件中自动提取主库binlog的文件名和位置（--master-data=2的作用）：
| Bash # 从库执行，查询同步起点 grep "CHANGE MASTER TO" /root/full_backup_20260323.sql |
|---|
返回结果示例（记录这两个值）：
| SQL -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=154; |
|---|
• MASTER_LOG_FILE：binlog日志文件
• MASTER_LOG_POS：日志起始位置
2. 从库执行主从关联命令
登录从库MySQL，执行以下命令（替换为你的主库信息）：
| SQL STOP SLAVE; -- 先停止可能的旧同步 RESET SLAVE; -- 重置同步配置 -- 核心配置：关联主库 CHANGE MASTER TO MASTER_HOST='192.168.1.99', -- 主库IP MASTER_PORT=3306, -- 主库端口 MASTER_USER='repl', -- 主库创建的复制账号 MASTER_PASSWORD='Repl@123456', -- 复制账号密码 MASTER_LOG_FILE='mysql-bin.000123', -- 上一步查到的binlog文件 MASTER_LOG_POS=154; -- 上一步查到的binlog位置 |
|---|
3. 启动从库同步
| SQL -- 启动主从同步 START SLAVE; |
|---|
八、验证主从同步是否成功
从库执行命令，查看同步状态：
| SQL SHOW SLAVE STATUS\G; |
|---|
关键校验项（两个必须为 Yes ）
1. Slave_IO_Running: Yes ✅ （从库IO线程正常，拉取主库binlog）
2. Slave_SQL_Running: Yes ✅ （从库SQL线程正常，执行binlog增量数据）
业务验证
1. 主库创建测试表/插入数据；
2. 从库查询，数据实时同步 → 全量+增量同步配置完成！
九、核心原理：全量+增量如何工作？
1. 全量同步：mysqldump 导出主库备份时刻的所有历史数据，从库导入后，基础数据一致；
2. 增量同步：从库从MASTER_LOG_POS位置开始，自动拉取主库备份后产生的所有binlog，实时回放执行 → 实现数据实时同步。
3. 全程无需手动处理增量日志，MySQL主从机制自动完成。
十、生产注意事项
1. 从库只读：务必配置 read_only=1，防止误写入导致主从数据冲突；
2. binlog清理：主库配置 expire_logs_days=7，自动清理过期binlog，避免磁盘占满；
3. 数据校验：同步完成后，用 pt-table-checksum 工具校验主从数据一致性；
4. 异常处理：若同步失败，查看 Last_Error 字段，优先排查主键冲突、数据不一致问题；
5. MyISAM引擎：若主库是MyISAM，需用 FLUSH TABLES WITH READ LOCK 锁表备份（业务停机），不推荐。
总结
1. 核心：InnoDB热备无停机，--master-data=2 自动记录增量起点；
2. 流程：主库备全量 → 从库导全量 → 配置binlog起点 → 启动同步；
3. 结果：从库自动同步历史全量数据+后续所有增量数据，主从完全一致。
