两台服务器MySQL怎么设置同步，如何配置主从复制？

两台服务器的MySQL同步，最稳妥的方案就是配置主从复制，简单说就是让主库记录binlog日志，从库读取日志并重放，从而实现数据准实时一致。这个方案成熟稳定，是绝大多数中小团队的首选，下面我会从方案选型、具体配置、排错和常见坑位，一步步带你搞定。

为什么多数场景选主从复制而不是主主复制

两台服务器同步数据,看似用主主复制两边都能写很方便，但在实际生产环境中，这个方案往往会把事情搞复杂，行业共识认为，主主复制在数据一致性、自增主键冲突、DDL操作同步上都有不小的隐患，一旦出现冲突，排查起来相当折磨人。

加载中

mysql主从热备同步配置实战演示

运

运维凯哥-面试陪跑

7409--

原视频地址

主从复制的逻辑就清晰得多：一台主库（Master）负责写，一台从库（Slave）负责读，从库通过IO线程拉取主库的binlog日志，写入自己的中继日志（relay log），再由SQL线程执行，完成数据同步。

如果你的场景是两台服务器mysql主从复制配置用于网站读写分离，或者单纯为了热备，那主从就是最优解。

主从同步的核心配置全流程

这里我以MySQL 8.0为例，系统环境是CentOS 7/Ubuntu 20.04，路径略有差异，原理完全相同，你需要准备两台服务器，一台做主库（假设IP是192.168.1.10），一台做从库（IP假设是192.168.1.20）。

第一步：修改主库配置文件

登录主库服务器,编辑MySQL配置文件，一般路径在 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，在 [mysqld] 段落下加入以下内容：

• server-id=1（主库要唯一，不能和从库重复）
• log-bin=mysql-bin（开启二进制日志，文件名前缀）
• binlog-format=ROW（行级复制，比STATEMENT更安全，避免函数、存储过程同步出错）
• expire_logs_days=7（日志保留7天，防止磁盘爆掉）
• max_binlog_size=100M（单个binlog文件大小）

修改完后,重启MySQL服务让配置生效：

systemctl restart mysqld

第二步：创建专用的复制账号

主从同步不能直接用root账户,权限太大不安全，主库上执行以下SQL，创建一个只用于复制的账号：

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY '你的强密码';
GRANT REPLICATION SLAVE ON . TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

这里把IP段限定在局域网内,比直接放通所有IP要安全得多。

第三步：锁定主库并查看位置点

这一步是关键,也是新手最容易漏的地方，给主库的所有表加上只读锁，防止在备份过程中有新数据写入，导致binlog位置点错乱：

FLUSH TABLES WITH READ LOCK;

然后查看当前binlog的文件名和位置点,这部分要记牢，后面从库配置要用：

SHOW MASTER STATUS;

返回结果类似这样：

记下 File 值是 mysql-bin.000003，Position 值是 834，这串信息就是从库开始复制的起点。

第四步：备份主库数据

锁表之后,用mysqldump把主库当前的数据导出来，假设你的库名是 myapp：

mysqldump -uroot -p --single-transaction --master-data=2 myapp > myapp.sql

注意这里 –single-transaction 参数只对InnoDB表有效，如果你有MyISAM表，锁表期间导出的数据才是一致的，备份完成后，解除主库的锁：

UNLOCK TABLES;

确定了配置步骤之后,我来梳理一下mysql主从同步配置步骤中从库侧的核心操作和验证方式。

第五步：修改从库配置文件

登录从库服务器,同样编辑 my.cnf，在 [mysqld] 段落下加：

• server-id=2（注意必须和主库不同）
• relay-log=mysql-relay-bin（中继日志，从库自己生成的）
• log_bin=mysql-bin（从库也建议开启binlog，方便以后从库再当主库，或者做数据恢复）
• read_only=1（强制从库只读，防止有人或程序误写数据）

重启从库MySQL：

systemctl restart mysqld

第六步：导入备份数据

把刚才在主库导出的 myapp.sql 传到从库服务器上，然后导入：

mysql -uroot -p myapp < myapp.sql

第七步：配置复制链路关系

在从库上执行 CHANGE MASTER TO 语句，告诉从库主库是谁、从哪个binlog位置开始同步：

CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='你的强密码',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=834;

这里三个坑要注意：

• MASTER_LOG_FILE 和 MASTER_LOG_POS 必须和刚才 SHOW MASTER STATUS 查出来的一致，填错一个字都同步不上。
• 密码如果包含特殊字符,需要用引号处理好。
• 如果这一步执行报错,可以执行 RESET SLAVE; 清掉配置再重新来。

第八步：启动复制线程并验证

执行以下命令启动复制：

START SLAVE;

然后查状态：

SHOW SLAVE STATUS\G

重点看这两行：

• Slave_IO_Running: Yes（IO线程正常，能连上主库拉取binlog）
• Slave_SQL_Running: Yes（SQL线程正常，能执行中继日志中的SQL）

这两个都是 Yes，说明同步链路已经通了，再等等几秒钟，新建一条数据插入主库，去从库查一下，能看到说明全流程没问题。

主从同步延迟怎么解决

主从配置好了不代表万事大吉,mysql主从复制延迟怎么解决是运维中绕不开的话题，从库的数据落后主库几十秒甚至几分钟，在真实业务中相当常见。

先自查这几个常见拖延元凶

• 主库大事务：比如一次性 UPDATE 几十万行，这条SQL在从库重放时耗时很长，期间所有数据都追不上。
• 从库硬件性能弱：主库是8核16G，从库是2核4G，写入速度自然跟不上。
• 单线程SQL线程瓶颈：默认情况下从库SQL线程是单线程执行的，主库并发写入高时，重放速度跟不上。
• 服务器网络带宽方向受限：主库上传带宽小，binlog传输速度慢。

从业内实践来看，缓解手段优先级如下：

• 检查慢日志,把大事务拆成小批次提交，这是最有效的手段。
• 从库磁盘用SSD,性能提升立竿见影。
• MySQL 8.0 默认支持并行复制，确认配置了 slave_parallel_workers=4 到 8。
• 尽量把从库的查询压力分摊到多个从库上,别让一台从库又同步又扛大查询。
• 监控主从延迟时间,核心指标用 Seconds_Behind_Master 字段，超过阈值立刻报警。

常见坑位排查清单

配置同步这件事,很多时候是当时配好了，过几天发现有数据不一致，或者slave起不来，这里列几个高频故障点，你可以直接按图索骥排查。

报错：Got fatal error 1236

原因是主库binlog被清理了,从库读取到了已经被删除的binlog文件，导致断连，解决办法：

• 重新查看主库 SHOW MASTER STATUS; 获取最新位置。
• 从库执行 STOP SLAVE; 再 CHANGE MASTER TO 更新到新位置。
• 如果数据差异比较大,建议直接把主库重新备份，再配置一次，保证数据起点一致。

报错：Duplicate entry for key PRIMARY

说明主从数据在某一刻分叉了,从库已经存在相同主键的数据，重放时插入失败，日常运维中这个多在误操作从库写入或者初始数据不一致时发生，解决办法：

• 确认从库是否开启了 read_only=1，如果没开，先开掉。
• 跳过这个错误继续往后同步：STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE;
• 有多个错误时,建议查看 MySQL 错误日志，定位是哪几张表不一致，严重的话就要考虑重搭从库了。

主库故障切换

主库宕机时,需要手动把从库提升为主库，避免业务停摆，操作步骤：

• 在从库上执行 STOP SLAVE; 取消复制关系。
• 执行 RESET SLAVE ALL; 清掉旧的主库信息。
• 修改从库配置,去掉 read_only=1，或者执行 SET GLOBAL read_only=OFF;
• 在从库上执行 SHOW MASTER STATUS\G; 记下新的文件名字和位置，后续如果有新的从库接入，以这个位置为准。
• 业务连接池的连接串切换IP,然后把原来主库宕机期间的数据缺失部分用备份补齐。

Q&A：关于两台服务器mysql同步的常见疑问

只同步某一个库或者某几张表可以吗？

可以,在主库配置 binlog_do_db=数据库名 指定需要记录的库，在从库配置 replicate-do-db=数据库名 指定需要重放的库，或者用 replicate-wild-do-table=数据库名.表名 精确到表，需要注意，配置了过滤规则会让后续维护变复杂，非必要不建议加。

搭建同步和酷番云/简米云的云数据库MySQL自带的主从是一回事吗？

云数据库厂商提供的主从是托管服务,底层原理同样是基于binlog复制，但监控、切换、备份都由云平台代管，业务无感知，自建服务器搭建的主从则需要自己管理故障切换和复制状态检查，不过两种方式在数据一致性上遵循相同的MySQL复制机制。

服务器MySQL同步对带宽有什么要求？

初期全量备份数据如果比较大有几十GB,传输时尽量走内网，速度快且不占用公网带宽，日常增量同步只传输binlog变更，流量并不大，但服务器带宽方向限制致延迟的情况常见主库的上行带宽如果是小水管，同步延迟就会变大，建议公网架构下至少预留5Mbps以上上行带宽，内网架构不受此限。

两台服务器MySQL同步不是一个做完就完事的任务,你需要关注的是持续的数据一致性，主从复制方案选择得当、配置准确、延迟可控、故障处理有预案，这四个环节全部打通之后，这套架构就能稳定支撑你的业务，配好只是开始，请务必定期检查复制状态，最好用脚本监控Slave_IO_Running和Slave_SQL_Running的值，任何一个是No都要立刻处理。