MySQL 主从复制配置完整记录（含踩坑总结）
📌 一、目标
搭建 MySQL 主从复制，用于：
- 数据备份
- 容灾恢复
- 可扩展读写分离
🧱 二、主库（Master）配置
1️⃣ 修改配置文件（my.ini / my.cnf）
在 [mysqld] 节点下添加（⚠️必须顶格写）：
# 主库唯一标识（必须唯一）
server-id=1
# 开启二进制日志（主从复制核心）
log-bin=mysql-bin
# binlog格式（推荐ROW）
binlog-format=ROW
# 指定需要同步的数据库（可选）
binlog-do-db=test_db
# 忽略系统库（必须）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# binlog保留时间（天）
expire_logs_days=7
# 保证数据一致性
sync-binlog=1
2️⃣ 重启 MySQL
systemctl restart mysqld
3️⃣ 创建从库账号（重要）
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'Slave@123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
FLUSH PRIVILEGES;
⚠️ 坑点 1：认证插件问题（云服务器常见）
检查：
SELECT user, host, plugin FROM mysql.user WHERE user = 'slave_user';
如果是：
caching_sha2_password
执行：
ALTER USER 'slave_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Slave@123456';
FLUSH PRIVILEGES;
4️⃣ 获取主库位点（关键）
SHOW MASTER STATUS;
记录：
File: mysql-bin.000003
Position: 128576
🧱 三、从库（Replica）配置
1️⃣ 修改配置文件
[mysqld]
server-id=2
relay-log=relay-log-bin
2️⃣ 重启 MySQL
systemctl restart mysqld
⚠️ 坑点 2：提前创建数据库
必须保证：
- 数据库存在
- 表结构存在
否则报错：
ERROR 1049 (Unknown database)
🔗 四、建立主从关系
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='111.228.49.96',
SOURCE_USER='slave_user',
SOURCE_PASSWORD='Slave@123456',
SOURCE_LOG_FILE='mysql-bin.000003',
SOURCE_LOG_POS=128576;
⚠️ 坑点 3：位点必须正确
必须来自：
SHOW MASTER STATUS;
否则报错：
Could not find first log file
▶️ 五、启动复制
START REPLICA;
📊 查看状态
SHOW REPLICA STATUS\G;
✅ 成功标志：
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
🛠 六、常用命令
START REPLICA;
STOP REPLICA;
SHOW REPLICA STATUS\G;
RESET REPLICA ALL;
🚨 七、常见坑总结
❌ 坑 1：binlog 不存在
报错：
Could not find first log file
解决：
SHOW MASTER STATUS;
❌ 坑 2：权限问题
报错：
Access denied for user
解决：
CREATE USER 'slave_user'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
❌ 坑 3：认证插件问题
原因：
caching_sha2_password
解决：
ALTER USER ... mysql_native_password
❌ 坑 4：数据库不存在
报错：
ERROR 1049 (Unknown database)
解决：
CREATE DATABASE xxx;
❌ 坑 5：未做全量同步
问题：
- 表不存在
- 数据错乱
建议：
- 先导出主库
- 再导入从库
⏱ 八、同步机制说明
MySQL 主从复制是：
实时同步（非定时）
查看延迟：
SHOW REPLICA STATUS\G;
关注：
Seconds_Behind_Source
⚠️ 九、重要提醒
主从复制 ≠ 安全备份
❗危险操作会同步：
DROP DATABASE xxx;
DELETE FROM table;
🛡 十、推荐生产方案
1️⃣ 主从复制（实时）
2️⃣ 延迟复制（防误删）
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 600;
3️⃣ 定时备份
0 3 * * * mysqldump -uroot -p --all-databases > backup.sql
🎯 总结
-
主从复制是实时机制
-
核心依赖 binlog
-
最大坑：
- 权限
- 位点
- 数据不一致
-
最佳实践：
主从复制 + 延迟复制 + 定时备份
