前阵子把一套跑了两年的单机MySQL终于拆成了主从结构。起因其实挺朴素——报表查询和业务写入挤在同一台库上每到月底导出经营数据的时候线上接口的响应时间就肉眼可见地涨上去慢查询日志里全是统计类SQL。当时的目标很明确把只读流量摘到另一台实例上顺手给数据库留一份热备。整个过程跌跌撞撞光是在从库上就栽过两个跟头其中一个问题如果不注意会把数据悄悄弄偏。这篇文章把我完整做过的MySQL主从配置过程、原理理解、排错经验一次讲清楚给正准备做读写分离或异地容灾的朋友当一份可以照着操作的参考。先说一个基本判断主从复制不是银弹但它确实是成本最低、适用面最广的扩展和容灾手段。我下面这些内容基于一套比较典型的一主一从拓扑主库负责读写从库接报表和备份任务也会把主库宕机后的手动切换流程一并讲掉。1. 什么场景下值得做主从复制1.1 我当时的真实痛点那套系统本身业务量不算夸张高峰期QPS也就在几百的水平。真正让它变慢的是读取链路一直和写入链路搅在一起。业务表的数据量过了千万级之后联表查询和统计聚合会占用大量buffer pool和磁盘IO一旦某个报表SQL写得比较粗暴整个实例的线程池都会受影响连正常的订单写入也跟着遭殃。所以主从复制解决的第一类问题就是读写互相干扰。把查询类流量全部指到从库主库专心处理写操作和实时性要求高的读操作两侧的资源利用就分开了。这个收益在单机顶不住又读又写的时候尤其明显。第二类问题是容灾。单机实例磁盘坏了、机房出了状况在没有备份恢复手段的情况下就是一场灾难。有了从库之后虽然传统异步复制不能保证零丢失但至少大部分数据在从库有副本恢复成本大幅降低。1.2 主从能做什么、不能做什么先讲能做的主从复制比较擅长四件事读写分离把查询压力分摊到多个只读实例热备份节点在从库上跑备份任务不影响主库性能故障切换的基础条件至少让你在主机故障时有节点可切作为某些复杂升级的演练场比如先在从库上升版本、验数据。再讲不能做的事。第一传统异步主从不提供自动故障转移千万别以为搭完主从就高可用了主库宕机后从库不会自动顶上需要外部组件或者人肉介入去切换。第二它不解决写扩展问题写入单点仍然存在。第三它不保证强一致异步复制下从库数据永远是略微滞后于主库的极端情况下会有少量丢失。1.3 哪些场景不要上主从如果系统的瓶颈是写入能力比如频繁的insert/update把单库打满这时候上主从没有意义因为从库同样处理不了同一份写压力应该考虑分库分表。如果业务对数据一致性要求极高主库提交后从库必须立即可见传统复制方案也很难满足得看半同步复制或其它分布式事务方案。另外如果数据量已经到了几十T级别逻辑备份和全量同步都很吃力就应该优先规划数据分片而不是靠主从硬撑。我个人是这么判断的当一台实例的CPU、IO、连接数没有明显水位压力但各种维护操作备份、报表、异步任务已经开始挤占生产资源时主从就是性价比很高的选择。2. 主从复制的内部链路两个日志和三条线程2.1 一条SQL是如何从主库走到从库的理解主从复制最关键的是理解它的传递链路。主库上有一个二进制日志binlog所有让数据发生变化的操作都会按顺序写进去。从库想拿到这些变更靠的是三条线程协同工作。主库端有一个dump线程负责把binlog内容按位置推送给从库从库端有一个IO线程负责接收这些内容并写入自己的本地日志relay log从库端还有一个SQL线程负责读取relay log并逐条回放把变更落到自己的数据文件里。整个过程大致是这样业务在主库执行一条UPDATE主库把变更记录进binlogdump线程读取新的binlog事件通过网络推给从库从库IO线程把事件追加到relay log从库SQL线程顺序读取relay log在从库实例上重新执行。打个比方主库像是记日记的人binlog就是日记本dump线程是负责送信的邮差IO线程是收信人relay log是收信箱SQL线程则是那个把来信内容抄到从库本地笔记本上的秘书。三条线程中任何一条卡住复制都会停下来。2.2 binlog格式ROW、STATEMENT、MIXED怎么选binlog里记录变更的格式有三种选错格式会在从库复现出完全不同的结果这里要认真对待。STATEMENT格式记录的是原始SQL语句。优点是日志量小缺点是同一句SQL在主从两端执行结果不一定一致例如用到UUID()、NOW()或者不走索引的UPDATE在从库上可能产生不同数据。ROW格式记录的是每一行变更前后的值安全性最高能忠实还原主库的数据变化缺点是binlog体积会变大。MIXED格式则让MySQL自己判断该用哪种。我在这套环境里用的是ROW。原因很简单对一致性要求高的业务ROW在复制链路上的语义最稳定。尤其是执行面积很大的UPDATE或者DELETE在ROW格式下复制到从库时不会因为索引差异导致影响行数不同。现代MySQL的默认binlog格式也是ROW直接用默认值基本没错。2.3 relay log和位置信息复制为什么是异步的主从复制的同步性依据是主库提交事务后是否需要等待从库确认。传统模式中主库提交事务后立刻返回成功从库什么时候拉取binlog、什么时候回放完主库一概不关心。这就是异步复制。异步复制的好处是主库性能损耗极小缺点是存在一个时间窗口在这个窗口里如果主库突然宕机尚未传送到从库的binlog事件就会永久丢失。读取SHOW SLAVE STATUS会看到各种位置信息比如Master_Log_File表示我已经读到主库哪个binlogRead_Master_Log_Pos表示我读到文件中的哪个位置Exec_Master_Log_Pos表示我实际回放到哪个位置。两个位置之间如果有差距则说明延迟正在发生。理解了这一点再看后面要配置的CHANGE MASTER TO就会明白为什么需要指定binlog文件名和位置点——它本质上是一句从主库的哪个日志位置开始接力的指令。3. 正式动手前的规划与准备3.1 版本和拓扑选型版本上我给的建议很简单主从两端大版本必须一致小版本尽量接近。比如主库是5.7.25从库最少也应该是5.7系列最好同小版本甚至更高一点。跨大版本从库配置也可以实现但会遇到字符集、默认认证插件、系统表结构差异等一堆隐性坑没必要为了省事给自己挖坑。我当时用的拓扑是一台物理机主库加一台物理机从库两台机器在同一内网带宽充足延迟在1ms以内。这是一个比较理想的复制环境。如果你的主从跨机房中间带宽或延迟就会直接影响复制进度需要重点评估链路质量。另外要把server-id规划好。同一套复制拓扑里每个实例的server-id必须全局唯一这个值会写进binlog事件里用于标识事件来源。很多初学者两台机器都沿用默认的server-id1结果从库启动后各种报错半天找不到原因。3.2 主从两端的关键参数配置主库的my.cnf中我使用过一套比较标准的配置[mysqld] server-id 10 log-bin /data/mysql/mysql-bin binlog_format ROW expire_logs_days 7 max_binlog_size 1G sync_binlog 1 innodb_flush_log_at_trx_commit 1解释一下为什么这么配。log-bin开启了二进制日志这是主从复制的前提没有binlog的实例无法作为主库。binlog_format选ROW理由上面说过。expire_logs_days控制binlog保留天数太短会导致从库临时断开后找不到需要拉取的文件太长会占满磁盘7天是一个常见起步值。sync_binlog1让每次事务提交都同步刷入binlog文件innodb_flush_log_at_trx_commit1让redo日志也同步刷入这两项配合起来能最大程度保证binlog和存储引擎的数据一致性。如果你用的是MySQL 8.0expire_logs_days这个参数已经被binlog_expire_logs_seconds替代配置时要注意版本差异否则参数会被忽略。从库的配置略有不同[mysqld] server-id 20 log-bin /data/mysql/mysql-bin relay-log /data/mysql/mysql-relay-bin read_only 1 relay_log_recovery 1从库也开启log-bin有两种考量一种是为了把从库继续当作更下层从库的主库也就是级联复制另一种是为了让从库具备独立备份或者被抓取binlog的能力。实际运维中打开没有坏处只要注意server-id不同即可。read_only1能防止应用误连从库并写入脏数据这是一个很值得养成的习惯但要注意它对拥有super权限的管理员账号无效。relay_log_recovery这个参数会在从库启动时自动丢弃损坏或未完成的relay log并重新向主库请求缺失的部分。它在实例异常重启后特别有用可以减少很多手工恢复工作。3.3 准备一份时间点一致的初始数据从库在一开始必须有一份在某个时间点与主库一致的数据。什么叫一致就是从库这份数据恰好对应主库binlog的某个具体位置之后从这个位置开始追主库的增量。最常见的方式是用mysqldump做逻辑备份再导入从库。这里要强调两个参数--single-transaction配合InnoDB引擎可以做到不加表锁的一致性快照--master-data2会在备份文件头部写一行注释记录当时的binlog文件名和位置点这行信息后面配置CHANGE MASTER TO时会用到。mysqldump -uroot -p \ --single-transaction \ --master-data2 \ --routines --triggers --events \ --all-databases \ --default-character-setutf8mb4 \ master_backup.sql加--routines、--triggers、--events的原因很直接存储过程、触发器、事件这些对象不会默认包含在逻辑备份里如果漏了复制过程中一旦在从库执行到依赖这些对象的事务就会直接报错。字符集显式指定为utf8mb4避免备份文件里的中文和表情符号在导入时变成乱码。如果主库数据量特别大逻辑备份时间太长可以考虑xtrabackup这类物理备份工具它备份更快也支持增量。但逻辑备份胜在简单、不依赖额外工具对大多数中小型系统是够用的。4. 一步一步完成主从配置4.1 在主库创建复制账号并授权复制账号只需要REPLICATION SLAVE权限这是最小权限原则。再给一个REPLICATION CLIENT权限让从库可以执行SHOW MASTER STATUS之类的检查命令方便排查。CREATE USER repl192.168.1.% IDENTIFIED BY 这里写一个强密码; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;host部分建议限定从库所在网段不要用%对全网络开放。尤其注意如果你用的是MySQL 8.0默认认证插件是caching_sha2_password旧版本MySQL客户端或驱动可能无法直接建立复制连接。遇到从库IO线程报认证失败时可能是这个原因后面排查章节还会再提。4.2 用mysqldump导数据到从库导出命令和前面准备一节里一样。我实际执行时会先登录主库看一眼当前状态然后马上执行备份FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;加全局读锁可以确保备份期间主库没有写入从而得到一个绝对一致的快照点。但生产环境上加锁会影响写入所以如果你的表都是InnoDB完全可以只靠--single-transaction做一致性快照不阻塞业务备份文件头部记录的位置点同样是可用的。我把FLUSH TABLES WITH READ LOCK当成了一套双保险先短暂锁表获取精确位置再立刻开始备份备份完成后马上UNLOCK TABLES。备份完成后把文件传到从库机器然后导入mysql -uroot -p master_backup.sql导入过程中如果从库已经开启了binlog导入操作也会被记录进从库自己的binlog。这本索引里如果不希望导入动作继续传播可以在导入前临时关闭它导入再恢复。单一层级的主从一般无所谓涉及级联复制时就要注意。4.3 在从库执行CHANGE MASTER TO先找到备份文件头部的位置点grep CHANGE MASTER TO master_backup.sql输出大致是-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000012, MASTER_LOG_POS154;这行信息来自mysqldump执行时的主库位置把它抄下来然后登录从库执行CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORD这里填强密码, MASTER_LOG_FILEmysql-bin.000012, MASTER_LOG_POS154, MASTER_CONNECT_RETRY10, MASTER_RETRY_COUNT86400;MASTER_CONNECT_RETRY和MASTER_RETRY_COUNT定义网络抖动或主库重启时从库的重试策略默认值也够用但把retry次数调大一些确实能减少因为短暂网络异常导致从库长期停摆的情况。这里要多说一句CHANGE MASTER TO本质上是把从库的指针指到主库的某个历史位置所以它不要求从库数据当时就是最新的只要求这个位置与从库数据是同一时刻的一致性快照。如果你的导出是在10:00做的位置点也是10:00的位置导入在10:30才完成接下来从库就会从10:00的位置开始追主库追到与当前主库数据对齐为止这个过程耗时取决于中间的差距大小。4.4 启动复制线程并检查状态启动之前先检查一下配置有没有问题START SLAVE; SHOW SLAVE STATUS\G重点关注以下几行Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Last_IO_Errno: 0 Last_SQL_Errno: 0Slave_IO_Running为Yes表示从库能正常连上主库并持续拉取binlogSlave_SQL_Running为Yes表示relay log在正常回放。这两个字段都必须是Yes缺一不可。验证数据是否真的在同步我习惯在主库建一张临时表插入几行数据几秒后再到从库上查询这张表-- 主库执行 CREATE TABLE test_db.repl_check (id INT PRIMARY KEY, ts DATETIME); INSERT INTO test_db.repl_check VALUES (1, NOW());-- 从库执行 SELECT * FROM test_db.repl_check;如果能在从库查到这条记录说明复制链路是通的。验证结束后把临时表删掉即可删表操作同样会同步到从库。5. 上线后我踩过的坑与排错经验5.1 Slave_IO_Running始终为No从网络到认证的排查链路第一次配置主从最容易卡住的就是IO线程连不上主库。我当时遇到的现象是SHOW SLAVE STATUS里Last_IO_Error报错内容大概是无法连接到主库。排查顺序很重要不要一上来就改配置。先看网络层从库上直接ping主库IP再测试端口连通性telnet 192.168.1.10 3306端口不通时优先查防火墙和安全组规则。很多云服务器的3306端口默认只对本机开放主库上执行SHOW VARIABLES LIKE bind_address如果值是127.0.0.1说明MySQL自己只监听了回环地址外部根本无法连接改成0.0.0.0并重启实例。网络通了再查账号权限。复制账号的Host是否包含了从库的IP用从库IP在主库上测试登录mysql -h192.168.1.10 -urepl -p如果这一步能登录但复制还是报错多了一个身份认证层面的嫌疑。MySQL 8.0默认的caching_sha2_password认证在复制场景下如果从库没有提前完成一次SSL/TLS握手交换公钥就会一直报访问被拒绝。解决办法是在创建账号时显式指定使用兼容的认证方式CREATE USER repl192.168.1.% IDENTIFIED WITH mysql_native_password BY 这里写强密码; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.1.%;如果不想改账号也可以在CHANGE MASTER TO里追加GET_MASTER_PUBLIC_KEY1让从库主动获取主库公钥。两选一即可。5.2 Slave_SQL_Running为No和1062错误不能盲目跳过SQL线程报错是最让人紧张的因为它代表从库本地已经停下来了。最常见的报错是1062主键冲突。比如Last_SQL_Error: Duplicate entry 1001 for key PRIMARY出现这个错误大概率是从库上已经存在某行主键重复的数据。来源可能有几种从库之前被应用直接写过数据或者从库的初始数据与主库不一致或者复制链路中断期间有人修改了从库。总之从库出现了一条主库没有对应操作的额外数据当主库把这条数据的变更传到从库时从库因为已有主键而拒绝执行。处理这种问题原则是先对齐再跳过不盲跳。我先在主从两端分别查询报错涉及的表对比数据差异。如果确认只是从库多了一些垃圾数据先把多余数据清理掉然后执行一次跳过让复制线程继续STOP SLAVE; SET GLOBAL sql_slave_skip_counter 1; START SLAVE;sql_slave_skip_counter1会跳过排在队列最前面的一个事务。注意它跳过的是整个事务在不同事务大小的场景下影响范围不同。千万不要在错误根因不明确的情况下反复跳过尤其是报错是DROP、DDL或者依赖某种对象缺失时盲目跳过会让从库数据偏差越来越大。我在一次演练中就是因为跳过了一个数据定义类错误后面连续出现一堆连锁报错最后只能重建从库。如果遇到的错误无法通过清理数据解决更稳妥的方案是重新准备从库数据再配置一遍把恢复时间控制在可接受范围内。5.3 Seconds_Behind_Master为0但数据还是慢半拍复制状态看起来正常不代表主从之间没有差距。我在实际使用中发现Seconds_Behind_Master这个字段有时候会骗人。比如主库写入压力很大从库IO线程长时间没有读取新binlog时这个值可能会在异常情况下显示为NULL又比如主从服务器系统时间不同步这个字段也会失真。更可靠的方法是直接对比位置信息。看SHOW SLAVE STATUS里的Relay_Master_Log_File、Exec_Master_Log_Pos再登录主库执行SHOW MASTER STATUS比较从库已经回放到哪个binlog位置与主库当前写到了哪个binlog位置是否一致。两者的差距越大真正的延迟越高。延迟的常见原因无非这几种从库SQL线程单线程回放主库并发写入很快从库来不及执行主库某个大事务比如一次删除百万行在binlog里生成大量事件从库要花很久才回放完从库本身执行着重量级查询占用了大量IO从库磁盘性能比主库差。针对单线程回放的瓶颈MySQL 5.7以上的多线程复制可以显著缓解。我在从库上启用过这样的参数slave_parallel_type LOGICAL_CLOCK slave_parallel_workers 4LOGICAL_CLOCK模式允许SQL线程并行回放同一提交组内的多个事务4个worker是比较稳妥的起步值太小效果一般太大可能增加死锁风险。修改后需要重启从库实例才能生效然后观察Seconds_Behind_Master是否明显下降。5.4 主库挂了怎么手动把流量切到从库传统异步复制不会自动切换主库宕机后切换步骤是必须人工掌握的。我当时的操作步骤可以作为参考登到从库查看SHOW SLAVE STATUS确认SQL线程已经把relay log尽量回放完记下当前Exec_Master_Log_Pos停止复制线程清掉复制指向让从库脱离主库管理STOP SLAVE; RESET SLAVE ALL;如果从库开启了read_only先关闭它SET GLOBAL read_only OFF;在从库上执行一次数据校验比如检查关键表的行数和最大时间戳判断数据是否接收完整把应用的数据库连接地址切到这个实例业务流量就由从库顶上了。这个过程中最纠结的一点是从库到底丢失了多少数据。因为异步复制下主库最后那些已提交但尚未传出的binlog事件已经找不回来了。如果业务对这部分丢失无法接受方案就不是传统异步复制而应该升级为半同步复制让主库在每个事务提交时至少等待一个从库ack后再返回成功。我在另一套系统上启用了半同步对性能的影响在可接受范围内但数据安全边界提高了很多。另外RESET SLAVE ALL会清理复制状态执行后想重新回到从库角色就需要重新执行CHANGE MASTER TO。所以切换前务必保存好故障前的主库地址和位置信息。我在实际做完这套主从配置后最大的感受是配置本身只需要二十分钟真正有价值的是对复制细节的理解和在异常情况下的处理能力。哪怕你的环境再简单也建议先在本地用两台虚拟机完整演练几遍把SHOW SLAVE STATUS里的每个关键字段都盯着看一遍模拟一次主库宕机再手动把流量切回来。最后分享一个小习惯每次主从配置完成后我会在从库上开启一段时间通用日志做短期观察一两个小时后关掉。这个方法能快速发现从库上有没有业务写入、查询是否真的走了新链路比起各种监控面板要直观得多。等日志确认干净了再返回正常运维状态。这套流程熟练之后你会越来越有底气。