最近在项目中搭建了MySQL主从复制，用来做读写分离和数据备份，提高数据库的可用性和性能。整个过程踩了不少坑，走了不少弯路，从环境搭建、配置优化、到故障排查、性能调优，遇到了各种各样的问题，花了不少时间才解决。

今天就来总结一下MySQL主从复制的踩坑经验和实战经验，包括主从复制的原理、搭建步骤、常见问题、性能优化、高可用方案等，希望能给正在做或者准备做MySQL主从复制的朋友一些参考，少踩坑，少走弯路。

一、为什么要做主从复制

先说说为什么要做主从复制吧。我们的项目，用户量越来越大，数据库的压力也越来越大，单台MySQL服务器已经扛不住了，主要有几个问题：

  1. 读压力大：我们的应用是读多写少，大部分都是查询请求，单台数据库的读压力很大，查询越来越慢，影响用户体验。
  2. 单点故障：只有一台数据库服务器，如果这台服务器挂了，整个应用就不可用了，没有高可用，风险很大。
  3. 数据备份：只有一台数据库，数据备份很麻烦，而且如果服务器硬盘坏了，数据可能就丢了，没有数据冗余。

为了解决这些问题，我们决定搭建MySQL主从复制，用主库负责写，从库负责读，做读写分离，减轻主库的读压力；同时，主从复制也能做数据备份，提高数据的安全性；而且，以后还可以在主从复制的基础上，做高可用方案，主库挂了，从库可以提升为主库，继续提供服务。

MySQL主从复制是MySQL最常用的高可用和性能扩展方案，技术很成熟，社区资料也很多，所以我们选择了这个方案。

二、MySQL主从复制的原理

在说搭建步骤和踩坑经验之前，先简单说一下MySQL主从复制的原理，理解了原理，遇到问题才能更好地排查。

MySQL主从复制的原理，简单来说，就是主库把数据变更记录到二进制日志（binlog）里，从库读取主库的binlog，然后在自己身上重放这些变更，从而实现数据同步。

具体来说，主从复制分为三个步骤：

  1. 主库记录binlog：主库在执行事务提交之前，会把数据变更（DDL、DML等）记录到二进制日志（binlog）里。binlog有三种格式：STATEMENT（记录SQL语句）、ROW（记录行的变更）、MIXED（混合模式），不同的格式有不同的优缺点，后面会详细说。
  1. 从库IO线程读取binlog：从库有一个IO线程，会连接到主库，请求主库的binlog，主库会有一个binlog dump线程，把binlog发送给从库。从库的IO线程收到binlog之后，会把它写入到自己的中继日志（relay log）里。
  1. 从库SQL线程重放relay log：从库还有一个SQL线程，会读取中继日志（relay log）里的内容，然后在从库上重放这些变更，执行对应的SQL，从而实现数据同步。

这就是MySQL主从复制的基本原理，是异步复制的，主库不需要等从库同步完成，就可以提交事务，所以主库的性能不会受到影响，但是可能会有主从延迟，从库的数据可能不是最新的。

除了异步复制，MySQL还有半同步复制，就是主库提交事务的时候，至少要等一个从库收到binlog并写入relay log之后，才返回提交成功，这样能保证数据的安全性，不会因为主库挂了而丢数据，但是会影响主库的性能，因为要等从库的响应。

理解了主从复制的原理，我们就能更好地理解后面的配置和问题了。

三、搭建步骤和踩坑经验

现在来说说搭建步骤，以及我在搭建过程中踩过的坑。

1. 环境准备

首先是环境准备，主库和从库的MySQL版本最好一致，或者从库的版本比主库高，不要从库的版本比主库低，不然可能会有兼容性问题。我们用的是MySQL 5.7，主库和从库都是5.7，版本一致。

服务器的配置，主库的配置要高一些，因为负责写，从库的配置可以稍低一些，但是也不能太低，不然同步会很慢。我们主库是8核16G，从库是4核8G，够用。

操作系统，我们用的是CentOS 7，MySQL用的是官方的rpm包安装的，安装过程很简单，这里就不多说了。

踩坑1：server-id重复

这是最常见的坑，主库和从库的server-id必须不一样，如果一样，就会复制失败。server-id是MySQL实例的唯一标识，在同一个复制集群里，每个实例的server-id都必须唯一。

我们一开始配置的时候，从库的my.cnf里忘记改server-id了，和主库一样，结果复制一直失败，报错"Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids"，找了半天才发现是server-id重复了，改了之后就好了。

所以，搭建主从复制的时候，第一件事就是确保主库和从库的server-id不一样，主库可以设为1，从库设为2、3、4等，不要重复。

2. 主库配置

主库的配置，主要是在my.cnf里配置以下几个参数：

[mysqld]
# 服务器唯一ID
server-id = 1
# 开启binlog
log-bin = mysql-bin
# binlog格式，推荐ROW
binlog_format = ROW
# 同步的数据库，不配置的话同步所有数据库
# binlog-do-db = dbname
# 不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
# 中继日志
relay-log = relay-bin
# 从库也记录binlog，用来做级联复制或者从库提升为主库
log-slave-updates = 1
# 自增ID的步长和偏移量，用来做主主复制，避免ID冲突
# auto_increment_increment = 2
# auto_increment_offset = 1

配置完之后，重启MySQL，然后创建一个复制专用的账号，给从库用来连接主库：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

然后，锁表，备份主库的数据，记录主库的binlog文件名和位置：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录下File和Position的值，比如File是mysql-bin.000001，Position是154，后面从库配置的时候要用。

然后，备份主库的数据，可以用mysqldump，或者直接拷贝数据文件（只适用于MyISAM，InnoDB不建议直接拷贝）。我们用的是mysqldump：

mysqldump -u root -p --all-databases --master-data=2 --single-transaction > all.sql

备份完成之后，解锁表：

UNLOCK TABLES;

踩坑2：binlog格式用STATEMENT导致的数据不一致

binlog有三种格式：STATEMENT、ROW、MIXED。STATEMENT是记录SQL语句，优点是binlog文件小，缺点是有些函数（比如UUID()、NOW()、USER()）在主库和从库执行的结果可能不一样，导致数据不一致；ROW是记录行的变更，优点是数据一致性好，缺点是binlog文件大，特别是批量更新的时候；MIXED是混合模式，一般情况下用STATEMENT，遇到可能导致不一致的函数时自动切换到ROW。

我们一开始图省事，用了默认的STATEMENT格式，结果发现主从数据不一致，排查了很久，发现是因为有一个存储过程里用了UUID()函数，主库生成的UUID和从库生成的不一样，导致数据不一致。

后来，我们把binlog格式改成了ROW，就再也没有出现过数据不一致的问题。虽然ROW格式的binlog文件大一些，但是数据一致性有保障，推荐大家用ROW格式，特别是有存储过程、触发器、函数的场景，一定要用ROW格式，不然很容易出现数据不一致。

踩坑3：备份的时候没有加--master-data，导致不知道binlog位置

我们第一次备份的时候，用mysqldump没有加--master-data参数，结果备份出来的SQL文件里没有记录主库的binlog文件名和位置，从库导入数据之后，不知道从哪里开始复制，只能重新备份，很麻烦。

后来，我们备份的时候都加上了--master-data=2参数，这样备份出来的SQL文件里会包含CHANGE MASTER TO语句，记录了主库的binlog文件名和位置，从库导入之后，直接就能用，很方便。--master-data=1是直接执行CHANGE MASTER TO，--master-data=2是注释掉，需要的时候手动打开，推荐用2，更灵活。

还有，备份InnoDB数据库的时候，一定要加--single-transaction参数，这样可以在不锁表的情况下进行一致性备份，不会影响业务，不然用FLUSH TABLES WITH READ LOCK锁表，会影响业务。

3. 从库配置

从库的配置，也是在my.cnf里配置：

[mysqld]
# 服务器唯一ID，不能和主库一样
server-id = 2
# 中继日志
relay-log = relay-bin
# 从库也记录binlog
log-slave-updates = 1
# 只读，从库设为只读，避免误写
read_only = 1
# 超级用户也只读，5.7.8之后支持
# super_read_only = 1
# 同步的数据库
# replicate-do-db = dbname
# 不同步的数据库
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema

配置完之后，重启MySQL，然后导入主库的备份数据：

mysql -u root -p < all.sql

导入完成之后，配置主从复制，告诉从库主库的地址、账号、密码、binlog文件名和位置：

CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

然后启动从库复制：

START SLAVE;

查看复制状态：

SHOW SLAVE STATUS\G

如果看到SlaveIORunning: Yes和SlaveSQLRunning: Yes，就说明复制正常启动了。

踩坑4：从库没有设为只读，导致误写，复制中断

我们一开始从库没有设为只读，结果有一次，运维同学误操作，在从库上执行了一条写操作，导致从库的数据和主库不一致，复制中断了，报错"Duplicate entry"或者"Can't find record"，排查了很久才发现是从库被误写了。

后来，我们把从库都设为只读（readonly = 1），这样普通用户就不能在从库上写了，只有超级用户（root）还能写。但是，超级用户还是能写，还是有误操作的风险，MySQL 5.7.8之后增加了superread_only参数，设为1之后，超级用户也不能写了，更安全，推荐大家用这个参数。

设为只读之后，从库就只能读不能写了，避免了误操作导致的数据不一致和复制中断，很重要，一定要配置。

踩坑5：主从延迟很大，同步很慢

我们搭建好之后，发现主从延迟很大，主库写入之后，从库要过好几秒甚至几十秒才能同步过来，读写分离的时候，用户刚写完，马上读，读不到数据，体验很差。

排查之后，发现有几个原因：

  1. 从库的配置太低，CPU和内存不够，SQL线程重放relay log很慢。
  2. 从库的硬盘是机械硬盘，IO性能差，写入慢。
  3. 单线程复制，MySQL 5.6之前，从库的SQL线程是单线程的，主库是多线程并发写的，从库单线程重放，肯定跟不上，延迟大。
  4. 有大事务，主库执行了一个很大的事务，比如批量更新几百万行数据，从库重放的时候也需要很长时间，导致延迟。

针对这些问题，我们做了以下优化：

  1. 升级从库的配置，CPU和内存和主库差不多，避免从库性能太差。
  2. 从库的硬盘换成SSD，提高IO性能。
  3. 开启多线程复制，MySQL 5.6支持按库多线程复制，5.7支持按组提交多线程复制（logical_clock），并发度更高，我们用的是5.7，开启了多线程复制：

``ini slaveparalleltype = logicalclock slaveparallel_workers = 8 `` 开启多线程复制之后，同步速度大大提升，延迟基本降到了毫秒级。

  1. 避免大事务，把大事务拆分成小事务，比如批量更新的时候，每次更新1000条，分多次更新，避免一个事务太大，导致从库延迟。

做了这些优化之后，主从延迟基本解决了，正常情况下延迟在1秒以内，用户基本感知不到。

踩坑6：复制中断，报错1032/1062，怎么恢复

主从复制最常见的问题就是复制中断，SlaveSQLRunning变成No，报错，常见的错误有：

  • 1062：Duplicate entry，主键冲突，从库已经有这条记录了，主库又插入了一条相同主键的记录。
  • 1032：Can't find record，主库更新或删除了一条记录，但是从库没有这条记录。

这些错误，一般都是因为从库被误写了，或者主从数据不一致导致的。遇到这种情况，怎么恢复呢？

有几种方法：

  1. 跳过错误：如果只有几条数据不一致，可以跳过这个错误，继续复制。用SET GLOBAL sqlslaveskip_counter = 1; 然后START SLAVE; 跳过一个事务。但是这个方法只适用于错误很少的情况，如果错误很多，或者数据不一致很严重，就不适用了，因为跳过之后数据还是不一致的。
  2. 手动修复数据：根据错误信息，找到不一致的数据，手动在从库上修复，然后继续复制。这个方法适用于数据不一致不多的情况。
  3. 重新搭建从库：如果数据不一致很严重，或者错误很多，最好的方法就是重新搭建从库，重新备份主库的数据，导入从库，重新配置复制。虽然麻烦一点，但是能彻底解决问题，保证数据一致。

我们遇到过几次复制中断，一开始用跳过错误的方法，但是发现跳过之后数据还是不一致，后面还会继续报错，最后还是重新搭建了从库，才彻底解决。所以，如果数据不一致比较严重，不要犹豫，直接重新搭建从库，这是最可靠的方法。

还有，为了避免复制中断之后数据不一致越来越严重，我们配置了复制监控，用脚本定期检查SHOW SLAVE STATUS的状态，如果发现SlaveIORunning或者SlaveSQLRunning不是Yes，或者SecondsBehindMaster太大，就马上告警，通知运维人员处理，避免问题扩大。

四、性能优化经验

搭建好主从复制之后，我们还做了一些性能优化，这里也分享一下。

1. 主库性能优化

主库负责写，性能很重要，我们做了这些优化：

  • innodbbufferpool_size：设置为物理内存的70%-80%，让更多的数据和索引缓存在内存里，减少磁盘IO。
  • innodblogfile_size：设置大一些，比如1G或者2G，减少checkpoint的频率，提高写入性能。
  • innodbflushlogattrx_commit：设置为2，每次事务提交的时候，把日志写到os buffer，每秒刷到磁盘，性能比1好很多，但是如果服务器宕机，可能会丢1秒的数据，对数据一致性要求不高的场景可以用，要求高的还是用1。
  • sync_binlog：设置为0或者100，不要每次提交都刷binlog，提高性能，但是可能会丢数据，根据业务场景选择。
  • 批量写入：批量插入、批量更新，减少事务次数，提高性能。

2. 从库性能优化

从库负责读，也需要优化：

  • innodbbufferpool_size：同样设置大一些，提高查询性能。
  • 查询缓存：MySQL 5.7还有查询缓存，读多写少的场景可以开启，但是要注意，查询缓存有锁竞争，写多的场景反而会影响性能，8.0已经去掉了查询缓存，所以不建议太依赖。
  • 索引优化：从库的查询很多，要做好索引优化，给常用的查询字段加索引，避免全表扫描。
  • 慢查询日志：开启慢查询日志，定期分析慢查询，优化慢SQL。

3. 读写分离

主从复制搭建好之后，最重要的应用就是读写分离，写请求走主库，读请求走从库，减轻主库的读压力。

读写分离可以在应用层做，也可以用中间件做，比如MyCat、ShardingSphere、ProxySQL等。我们一开始是在应用层做的，用的是Spring的AbstractRoutingDataSource，动态切换数据源，写请求用主库，读请求用从库。但是应用层做读写分离，对代码有侵入，而且事务里的读请求也要走主库，不然会有数据不一致的问题，处理起来比较麻烦。

后来，我们用了ProxySQL做读写分离，ProxySQL是一个MySQL代理，在应用和数据库之间，应用连接ProxySQL，ProxySQL根据SQL语句自动路由，写请求路由到主库，读请求路由到从库，对应用透明，不需要改代码，很方便。而且ProxySQL还支持连接池、查询缓存、故障转移等功能，很强大，推荐大家使用。

读写分离的时候，要注意一个问题，就是主从延迟，用户刚写完，马上读，如果读从库，可能读不到刚写的数据，因为从库还没同步过来。对于这种情况，有几种处理方法：

  1. 强制走主库：对于写完马上要读的场景，强制读请求走主库，不读从库。
  2. 延迟检测：读从库之前，先检测主从延迟，如果延迟太大，就走主库，延迟小就走从库。
  3. 半同步复制：用半同步复制，保证主库提交的时候，从库已经收到了binlog，减少主从延迟。

我们用的是第一种方法，对于写完马上要读的场景，强制走主库，其他读请求走从库，虽然简单，但是很有效，能解决大部分问题。

五、高可用方案

主从复制只能解决读压力和数据备份的问题，不能解决主库单点故障的问题，如果主库挂了，还是不能写，应用还是不可用。所以，在主从复制的基础上，我们还需要做高可用方案，主库挂了，自动把从库提升为主库，继续提供服务。

常见的MySQL高可用方案有：

  1. MHA（Master High Availability）：这是最常用的MySQL高可用方案，由日本人开发的，能自动监控主库状态，主库挂了，自动把最新的从库提升为主库，然后把其他从库指向新的主库，切换过程很快，一般在30秒以内。MHA是目前最成熟、应用最广的MySQL高可用方案，推荐大家使用。
  2. Keepalived + 双主复制：用两台MySQL做主主复制（互为主从），然后用Keepalived做VIP漂移，主库挂了，VIP自动漂移到从库，从库变成主库，继续提供服务。这个方案配置简单，但是双主复制可能会有数据冲突的问题，需要配置自增ID的步长和偏移量，避免ID冲突。
  3. Orchestrator：这是一个MySQL高可用和复制管理工具，功能很强大，能自动发现复制拓扑，自动故障转移，还提供Web界面，方便管理。
  4. Galera Cluster / Percona XtraDB Cluster：这是多主同步复制方案，所有节点都是主，都能写，数据同步复制，没有主从延迟，可用性很高，但是对网络要求高，性能有一定损失，适合对可用性要求很高的场景。

我们用的是MHA方案，配置起来不算太复杂，稳定性也很好，用了一年多，没有出过问题。MHA的原理是，有一个MHA Manager节点，监控所有的MySQL节点，主库挂了，Manager会选择一个数据最新的从库，提升为主库，然后把其他从库指向新的主库，整个过程自动完成，不需要人工干预，切换很快，业务基本无感知。

MHA的配置和使用，这里就不详细说了，网上有很多教程，大家可以参考。需要注意的是，MHA需要配置SSH免密登录，Manager节点要能SSH到所有的MySQL节点，MySQL节点之间也要能SSH免密登录，这样MHA才能在切换的时候，在各个节点上执行命令。

还有，高可用方案一定要做故障演练，定期模拟主库故障，看看高可用方案能不能正常切换，不要等到真的出故障了才发现有问题，那就晚了。我们每季度都会做一次故障演练，模拟主库宕机，看看MHA能不能正常切换，应用能不能正常访问，确保高可用方案是有效的。

六、监控和运维

最后，说说监控和运维，主从复制搭建好之后，监控和运维非常重要，不然出了问题都不知道。

我们做了这些监控：

  1. 复制状态监控：定期检查SHOW SLAVE STATUS，监控SlaveIORunning、SlaveSQLRunning、SecondsBehindMaster等指标，有异常马上告警。
  2. 主从延迟监控：监控SecondsBehindMaster，如果延迟超过阈值（比如10秒），就告警，通知运维人员处理。
  3. 数据库性能监控：监控CPU、内存、IO、连接数、QPS、TPS、慢查询等指标，有异常马上告警。
  4. 磁盘空间监控：监控磁盘使用率，特别是binlog和relay log的大小，避免磁盘满了导致数据库挂掉。binlog要设置过期时间，比如expirelogsdays = 7，只保留7天的binlog，避免binlog占满磁盘。

运维方面，要注意：

  1. 定期备份：虽然主从复制能做数据备份，但是还是要定期做全量备份，比如每天用mysqldump或者xtrabackup备份一次，保留最近7天的备份，以防万一。
  2. 定期清理binlog：binlog会越来越大，要设置过期时间，定期清理，避免占满磁盘。
  3. 定期优化表：对于经常更新和删除的表，会有碎片，定期用OPTIMIZE TABLE优化表，回收空间，提高性能。
  4. 版本升级：定期升级MySQL版本，修复bug，提高性能和安全性，但是升级之前一定要做好备份，并且在测试环境测试没问题之后，再升级生产环境。

七、写在最后

MySQL主从复制：踩坑总结与实战经验。

以上就是我在搭建和使用MySQL主从复制过程中的踩坑经验和实战经验，从原理、搭建步骤、常见问题、性能优化、高可用方案，到监控和运维，都做了一个比较全面的总结。

MySQL主从复制是MySQL最常用的高可用和性能扩展方案，技术很成熟，资料也很多，但是实际搭建和使用的时候，还是会遇到各种各样的问题，需要我们耐心地排查和解决。只要理解了原理，做好配置，做好监控和运维，主从复制还是很稳定可靠的，能大大提高数据库的性能和可用性。

希望我的这些经验，能给正在做或者准备做MySQL主从复制的朋友一些参考，少踩坑，少走弯路，顺利地搭建和使用MySQL主从复制。

最后，用一句话结尾："数据库是应用的基石，稳定和可靠是第一位的，性能和扩展是第二位的，做好监控和运维，才能保证数据库的稳定运行。"愿我们都能搭建和运维好自己的数据库，让应用稳定可靠地运行。