聊到MySQL高可用和读写分离我估计不少人都被问过这么一句话“你先搞个主从复制把读流量分流一下。” 这句话背后藏着MySQL最经典、也最绕不开的一个技术底座——基于binlog的传统主从复制。这篇文章我就把这个老牌方案从头到尾拆一遍从binlog为什么能当复制媒介到主库从库参数怎么配、数据怎么对齐、复制线程怎么跑再到我实际运维中踩过的几个典型坑一次性说透。适合刚接手数据库的运维同学、准备搭读写分离的后端工程师以及想搞懂复制原理再去追GTID、半同步这些新玩意的朋友。这个系列我打算分几篇写今天先聊最基础的传统异步复制。它虽然“老”但现在大量生产环境还在跑而且后面的GTID、并行复制、半同步都是在这套机制上长出来的。地基不打牢后面很容易糊。1. 为什么先聊基于binlog的传统主从复制1.1 主从复制到底解决了什么问题主从复制的核心就一句话把主库的变更日志同步到从库让从库帮你分担读压力或者当灾备。实际业务中它解决的痛点很典型第一个是读多写少的场景。某次我在一个业务系统里看到主库CPU动不动冲到80%一看慢查询日志全是报表查询和后台管理页面的统计语句真正写数据的接口反而没多少负载。这种时候把查询挪到从库主库就能专心处理写事务效果立竿见影。第二个是备份与容灾。做物理备份或者逻辑备份的时候直接在从库上跑能避免备份任务把主库IO打满。同时如果主库机器硬件故障从库可以快速切换顶上来虽然传统异步复制做不到零丢失但至少能把业务中断时间从“等修复”缩短到“分钟级切换”。第三个是读取扩展。业务量涨了一台从库不够就再加一台扩容成本比业务层做复杂缓存方案要低得多。但有一点必须说清楚传统主从复制解决不了写瓶颈。所有写操作还是集中在主库上它也不是自动故障转移方案切换需要人工介入或者依赖外部高可用组件。理解这个边界你就不会对它有不切实际的期待。1.2 复制方案对比传统binlog复制、GTID、半同步、组复制MySQL这些年衍生出好几个复制形态我简单列一下它们的关系传统异步复制基于binlog的文件名和偏移位点定位主库不等待从库确认性能最好但主库宕机时可能丢事务。半同步复制主库提交事务后至少要等一个从库确认收到binlog才返回客户端减少了数据丢失窗口但会增加写入延迟。组复制MGR基于Paxso类共识协议的多主或单主复制能自动选主但对网络和版本要求更高运维复杂度也上来了。GTID复制用全局事务ID代替文件名位点定位事务更简单故障切换时不用再费劲找位点。我为什么建议大家先学传统异步复制因为它虽然机制简单却是理解其他所有方案的基础。binlog怎么产生、IO线程怎么拉日志、SQL线程怎么回放这套链路在所有复制方案里都是通用的。你把传统方案彻底吃透再看GTID其实就是给每个事务打了个唯一标号半同步就是在提交确认环节加了个等待理解成本会低很多。而且现实环境里老系统、第三方同步工具、某些云数据库实例很多还是传统复制模式。你去排查别人留下的环境不懂这个是真的寸步难行。1.3 这个方案适合什么样的业务阶段如果业务刚起步、数据量不大、访问量也不高其实没必要上主从。单库扛得住的时候多一套复制就是多一份运维负担。但一旦出现下面这些信号就可以考虑上了读流量已经明显让主库CPU或IO出现周期性峰值报表、统计分析类的只读SQL开始影响在线交易业务需要有一个尽量接近实时的备份副本用来恢复误操作数据团队准备做高可用切换演练需要先具备一主一从的基础架构。传统异步复制在这些场景里是最稳妥的起步方案。它不强制依赖额外插件MySQL原生就能配置排错思路也清晰适合作为团队第一套高可用架构的起点。2. binlog复制的数据底座2.1 binlog是怎么记录数据的binlog是MySQL的二进制日志所有会改变数据的操作比如INSERT、UPDATE、DELETE、CREATE TABLE、ALTER TABLE都会以“事件”的形式按顺序写进binlog文件。SELECT和普通的SET这类不改变数据内容的操作不会记。复制能成立靠的就是binlog这个“数据底座”。主库把变更写进binlog从库通过网络把binlog拉到本地然后一条一条重新执行一遍最终从库就变成了主库的数据副本。整个过程跟出版社印报纸很像主库是编辑部binlog是排版好的底稿从库是印刷厂拿着底稿一份份印出来。binlog是按顺序追加的每个文件写到一定大小就切换下一个文件名通常是 mysql-bin.000001、mysql-bin.000002 这样的序号。复制位点就是“哪个文件的哪个偏移量”这个偏移量在复制协议里用Position表示。2.2 三种记录格式STATEMENT、ROW、MIXEDbinlog有三种格式选哪个直接影响复制的一致性和日志体积。我把它们放在一起对比格式记录内容优点缺点适用场景STATEMENT记录原始SQL语句日志体积小可读性好使用now()、rand()等函数时主从执行结果可能不一致早期版本默认现在不建议生产使用ROW记录每一行变更前后的数据复制一致性最强误操作可用binlog反向恢复日志体积大批量更新可能产生大量事件生产环境首选MySQL 8.0默认格式MIXED混合模式SQL确定安全时用STATEMENT否则自动切ROW兼顾日志体积和一致性格式切换逻辑相对复杂定位问题时要两种都懂某些特殊兼容场景我在生产环境一直推荐直接用ROW。尤其是MySQL 5.7以后ROW格式配合binlog_row_imageFULL能把变更前和变更后的完整镜像记录下来。真遇到误删数据你可以用binlog解析工具把删除前的行捞回来这是STATEMENT格式做不到的。虽然日志文件变大了但磁盘现在便宜一致性带来的安心感比省那点空间值钱得多。有个细节要注意ROW格式下一条UPDATE如果影响了十万行binlog里会产生十万条行事件同步到从库也要逐行应用这也是大事务导致主从延迟的常见原因之一。2.3 和复制强相关的几个关键参数配置主从复制不是只开个log_bin就行。下面这些参数直接影响复制的稳定性我整理成表参数名默认值作用与注意事项server_id1每个实例必须唯一主从都不能重复否则复制会报错log_binOFF是否开启binlog主库必须开启建议从库也开binlog_formatROW8.0默认ROW5.7默认STATEMENT生产建议显式设为ROWsync_binlog1每写几个binlog事件做一次磁盘同步设为1可减少宕机丢binlog风险expire_logs_days0binlog过期天数5.7常用8.0建议用binlog_expire_logs_secondsmax_binlog_size1073741824binlog单文件大小上限默认1G超过后自动轮转relay_log空从库中继日志文件名建议显式设置方便管理和恢复read_onlyOFF从库建议开启防止应用程序误写从库造成数据不一致super_read_onlyOFF比read_only更严格连super权限用户也不能写从库建议开启这些参数里面server_id是必须给的而且主从一定要不同。我之前遇到过一兄弟直接把主库的配置文件拷贝到从库忘了改server_id结果IO线程怎么都连不上报错信息还是“server id ... conflicts”排查半天才发现是配置克隆的锅。binlog_expire_logs_seconds这个参数值得多说一句。很多从库报错“Got fatal error 1236”就是因为主库binlog已经被purge清理掉了从库需要的位点找不回来了。保留时间建议结合全量备份周期来设至少要比备份周期长最稳妥是备份完成后才purge旧的binlog。3. 传统主从复制的完整配置实操3.1 场景假设和准备工作为了把步骤讲清楚我虚构一个标准环境主库IP是192.168.56.10从库IP是192.168.56.20操作系统是LinuxMySQL版本用8.0.x。如果你用的是5.7命令上有些小差异我会在关键位置单独标注。开始之前先确认两件事两台机器端口3306都能互通防火墙或者安全组规则放行两套MySQL实例都已完成初始化root或具备SUPER权限的账户可用。我建议在动手前先停掉业务写入至少不要在配置过程中执行大批量DML。如果是生产环境最好选业务低峰期来操作并且先在测试环境完整演练一遍。3.2 主库配置开启binlog并设置server_id编辑主库的my.cnf或my.iniWindows环境在[mysqld]段下加下面这些配置[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW sync_binlog 1server_id保证实例唯一log_bin指定binlog文件前缀名binlog_format显式使用ROW格式sync_binlog1让每个binlog事件都同步到磁盘降低宕机丢日志风险。改完后重启MySQL服务。重启后登录MySQL执行SHOW MASTER STATUS;正常情况下会看到类似这样的输出File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set mysql-bin.000001 | 157 | | |这里的File就是当前正在写的binlog文件名Position是当前写入偏移量。这两个值就是后面配置从库复制位点时要用到的关键数据。3.3 创建专用的复制账号主库需要一个从库用来连接它的账号。复制账号权限不用给太大只需要REPLICATION SLAVE权限就够了。千万不要图省事直接用root当复制账号那是给自己埋雷。在主库执行CREATE USER repl192.168.56.% IDENTIFIED BY your_strong_password; GRANT REPLICATION SLAVE ON *.* TO repl192.168.56.%; FLUSH PRIVILEGES;注意授权对象里的网段。192.168.56.%表示允许这个网段内的从库来连接更严格可以写成具体的从库IP。密码建议用强密码复制账号泄露就等于数据库数据泄露因为通过binlog能读取到几乎所有的数据变更内容。3.4 从库配置独立的server_id和relay log从库同样要改my.cnf关键是不能跟主库用同一个server_id[mysqld] server_id 2 log_bin mysql-bin relay_log relay-bin read_only ON super_read_only ON从库开log_bin不是必须的但建议开。因为从库可能作为更高一级从库的主库或者未来要做主从切换提前开好binlog能省不少事。read_only和super_read_only一起开能防止应用程序和误操作写从库。注意read_only对主从复制本身没有影响SQL线程应用relay log时不受read_only限制这是机制上就设计好的。relay_log参数建议显式设置。如果不设置某些版本在主机名变化时可能导致relay log文件名混乱排查问题的时候特别头疼。改完配置记得重启从库实例。3.5 数据初始化和复制位点对齐最容易翻车的一步这一步是整个搭建过程里最容易被忽略、也最容易出问题的环节。如果主库和从库的初始数据不一致复制一开始就会报错或者产生静默的数据差异。分两种情况看第一种主库是刚初始化的空库比如新装的测试环境没有任何业务数据。这种情况最简单直接把从库的change master位点设为主库当前SHOW MASTER STATUS的位置就行。第二种主库已有存量数据这是生产环境最常见的情况。做法是先做主库备份再恢复到从库同时拿到备份那一刻的binlog位点。推荐用mysqldumpmysqldump --single-transaction --master-data2 -h192.168.56.10 -uroot -p --all-databases master_full.sql这个命令里有两个关键点--single-transaction利用InnoDB的MVCC机制获取一致性快照不锁表对在线业务影响小--master-data2会在备份文件的头部注释里自动记录当时的binlog文件名和Position。打开备份文件头部能看到这样两行-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS157;这两个值就是之后从库启动复制的起点。把备份文件导入从库mysql -h192.168.56.20 -uroot -p master_full.sql导入后从库的数据就跟主库备份那一刻完全一致了。剩下的就是告诉从库从哪个位点开始继续同步。这里我要特别强调如果你跳过备份对齐这一步直接按当前SHOW MASTER STATUS的位点配从库而主库的binlog里某些事务并没有全量包含你缺的那部分数据复制虽然能起来但主从数据会差一截。更糟的是这差的一截很难被发现等到你真正需要从库切换或者查询时才会暴露。3.6 在从库执行change master并启动复制数据就位后在从库执行复制配置。MySQL 8.0推荐用新的CHANGE REPLICATION SOURCE TO语法5.7及更早版本用CHANGE MASTER TO。下面以8.0为例CHANGE REPLICATION SOURCE TO SOURCE_HOST192.168.56.10, SOURCE_PORT3306, SOURCE_USERrepl, SOURCE_PASSWORDyour_strong_password, SOURCE_LOG_FILEmysql-bin.000003, SOURCE_LOG_POS157;如果你的备份文件是从主库mysqldump出来的SOURCE_LOG_FILE和SOURCE_LOG_POS一定要用文件头部注释里记录的位点而不是你现在SHOW MASTER STATUS查到的值。这两个值必须精确匹配备份时刻的binlog位置差一个字节都同步不起来。如果你用的是5.7执行的语句是CHANGE MASTER TO MASTER_HOST192.168.56.10, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDyour_strong_password, MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS157;配置完成后启动复制START REPLICA; -- 8.0 -- 5.7 使用 START SLAVE;然后我建议立刻查看复制状态SHOW REPLICA STATUS\G -- 8.0 -- 5.7 使用 SHOW SLAVE STATUS\G有两点重要提示如果从库之前配置过其他主库执行CHANGE REPLICATION SOURCE TO之前最好先STOP REPLICA再RESET REPLICA ALL清掉旧的复制上下文避免残留位点造成干扰。ALTER USER和CHANGE语句执行后不会马上生效STOP和START交替操作时注意顺序。复制启动后通常几十秒内两个线程就会进入Yes状态。如果一直显示Connecting大概率是网络不通、账号权限不对或者server_id有冲突可以看Last_IO_Error具体报错。4. 复制跑起来之后怎么确认它真的健康4.1 SHOW REPLICA STATUS核心指标解读复制启动后最直观的判断方法就是看SHOW REPLICA STATUS的输出。输出字段非常多我平时只看这几个关键项字段期望值含义说明Replica_IO_RunningYesIO线程是否正常从主库拉取binlogReplica_SQL_RunningYesSQL线程是否正常回放relay logSeconds_Behind_Master0或很小从库落后主库的秒数Last_IO_Error空IO线程最近一次报错信息Last_SQL_Error空SQL线程最近一次报错信息Master_Log_File与主库当前文件一致并推进IO线程正在读取的主库binlog文件Relay_Master_Log_File与主库推进同步SQL线程正在回放的binlog文件两个Running只要有一个是No复制就是中断状态。Seconds_Behind_Master这个值我提醒一下它只在SQL线程空闲时才重新计算如果SQL线程一直忙这个值可能不准确甚至不更新。所以不能只看它来断定延迟要结合Master_Log_File和Relay_Master_Log_File的差距一起看。4.2 写数据验证从主到从的完整链路配置完成后别急着收工做一次端到端验证。在主库执行CREATE DATABASE testdb; USE testdb; CREATE TABLE t_user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t_user(name) VALUES (zhangsan), (lisi), (wangwu);然后到从库查询USE testdb; SELECT * FROM t_user;能看到三行数据就说明复制链路基本通了。再分别在主从执行SHOW MASTER STATUS和SHOW REPLICA STATUS确认binlog文件名和Position都在推进说明数据是持续流动的。顺手验证一下从库read_only是否生效在从库尝试写入一条INSERT如果报错“The MySQL server is running with the --read-only option”说明只读保护已经起作用了。这也是防止未来误写从库的第一道防线。4.3 日常监控需要盯哪些指标复制搭好只是开始日常运维重点是监控几个信号Replica_IO_Running和Replica_SQL_Running的状态变化。这两个只要变No就得立刻告警。Seconds_Behind_Master超过阈值。通常设个30秒或60秒的告警阈值比较合理但要根据业务对数据新鲜度的要求调整。主库binlog增长速度和磁盘空间。ROW格式下日志增长可能非常快磁盘打满会让整个数据库hang住。从库relay log的增长。如果SQL线程追不上IO线程relay log会一直累积也可能塞满从库磁盘。监控方式可以用脚本定时抓SHOW REPLICA STATUS输出也可以接入你现有的监控系统。如果是小型环境一个简单的crond脚本加短信通知就够用。5. 传统主从复制常见故障与排查实战5.1 Slave_IO_Running显示Connecting这是一个高频问题。IO线程连不上主库通常原因有几种网络不通、复制账号密码错、权限不对、server_id冲突。排查顺序我建议是这样的先看Last_IO_Error的具体内容。如果报错是error connecting to master repl192.168.56.10:3306 - retry-time: 60 retries: 1先测试网络和端口telnet 192.168.56.10 3306能通的话再用复制账号手动登录一下主库验证账号和密码是否正确mysql -h192.168.56.10 -urepl -p登录成功后查权限SHOW GRANTS FOR repl192.168.56.%;确认至少有REPLICATION SLAVE权限。如果都正常检查从库和主库的server_id是否不同。这个坑我到现在都记得有次搭建三台实例的集群用了同一份配置模板结果三台server_id全是1复制全部失败。5.2 Slave_SQL_Running显示NoSQL线程停住常见原因是从库数据与主库不一致SQL回放时报错。比如从库已经有了主键冲突记录或者某张表被意外修改过。处理前一定要搞清楚原因执行SHOW REPLICA STATUS\G重点看Last_SQL_Error字段里面会明确告诉你哪条语句执行失败、在哪个库哪张表上。最稳妥的恢复方案是重新做一次全量数据对齐在主库重新mysqldump导入从库重置复制位点。我见过不少人在SQL线程报错后习惯性用sql_slave_skip_counter 跳过一个事务但跳过的是事务内容而不是错误本身很可能让数据差异越来越大。除非你明确知道报错事务是无效数据、跳过它无损否则别跳。如果确实要临时跳过MySQL 8.0可以这样STOP REPLICA; SET GLOBAL SQL_SLave_SKIP_COUNTER 1; START REPLICA;这个操作要极其谨慎我的建议是万不得已不用。5.3 UUID冲突导致的复制报错从库IO线程报错信息里如果出现Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs说明两台实例的server-uuid一样。这种情况大多是复制了数据目录或者克隆虚拟机造成的因为uuid存在数据目录下的auto.cnf文件里。解决办法很简单停掉从库实例删除或修改auto.cnf中的uuid然后重启。比如systemctl stop mysqld rm -f /var/lib/mysql/auto.cnf systemctl start mysqld重启后MySQL会自动生成一个新的uuid复制就能正常继续。注意在这之前要确认从库数据没有被意外写入因为重启不会重置数据。这个坑在虚拟机模板克隆场景特别常见。以后做相关操作第一反应就检查auto.cnf能省很多时间。5.4 其他高频问题速查表现象可能原因快速处理建议Last_IO_Error: Got fatal error 1236主库binlog已清理从库位点太旧延长binlog过期时间重新对齐数据从库relay log撑爆磁盘SQL线程追不上IO线程中继日志堆积开relay_log_recovery拆大事务升级并行复制主从数据不一致从库被误写或初始化未对齐定期做数据校验从库开read_only主库binlog磁盘满磁盘规划不足调短过期参数或迁移binlog到独立磁盘大事务导致延迟单事务影响行数过多SQL线程单线程回放拆分大事务后续配置并行复制复制账号时好时坏授权网段不匹配、密码过期检查账号host与实例IP重置密码并授权5.5 我的一些避坑心得搭过几套主从之后我总结出几条原则从库越简单越好所有应用连接和写操作一律禁止在从库执行。read_only加上super_read_only都打开哪怕暂时没有应用程序连接从库也要先养成好习惯。数据对齐永远不要省。备份、校验、恢复、位点确认一步一步来。我见过很多线上事故起因都是“我先粗略配一下后面再补数据”。后面永远不会补。不要迷信Seconds_Behind_Master等于0。有时候主库写入频率低从库早就追上了它当然是0一旦有大的批量任务延迟瞬间飙升。平时要把binlog位点和延迟结合起来看。还有一个容易被忽略的问题如果主库是大内存实例binlog缓存相关的参数比如binlog_cache_size如果设置不当大事务写binlog时可能频繁发生磁盘临时写入影响主库性能。这个参数可以后续再调优但至少要心里有数。6. 写在最后当年搭第一套主从复制踩过的坑回想我第一次在生产环境搭主从因为没改server_idIO线程反复重试连接我还以为是主库防火墙问题折腾了大半夜。后来发现主库binlog被清理从库报1236错误被迫全量重做了一次。这些教训说白了都是“基础概念不牢被现实教育”。传统主从复制原理看起来简单但真正跑起来涉及位点、日志格式、账号权限、数据一致性每一环都容不得马虎。我个人现在搭这类环境流程已经固化了先确认版本和参数再设计账号和网段然后低峰期做一致性备份恢复后核对位点最后启动复制并连续观察几个小时的延迟曲线。每一步不图快反而从来不返工。先把这个方案玩熟后面聊GTID、半同步复制、并行复制的时候你会发现很多概念都是相通的。下一篇我打算专门写GTID复制它解决了传统复制最让人头疼的位点定位问题也是现在新建集群时更推荐的方式。到时候我们再接着拆。