搞主从配置的文档满网都是但大多数都是“照官方文档抄一遍能通就行”的水平。我今天写这篇不是又给你贴一遍CHANGE MASTER TO而是想把我实际在生产环境折腾MySQL主从的经验、踩过的坑、还有怎么排查的思路一次性整理出来。尤其是很多教程忽略的binlog格式怎么选、GTID和文件位置复制哪种靠谱、复制中断后怎么优雅恢复、以及“只同步某几张表”这种实际到不能再实际的需求。如果你是刚接触MySQL的运维新手或者被主从复制搞得焦头烂额的开发这篇应该能帮你少走不少弯路。我会按我的实际配置顺序来写你照着一步步来大概率能一次配通。1. 先搞懂主从复制的核心逻辑别急着敲命令很多人一上来就改配置文件结果出问题了不知道从哪排查。我建议先花两分钟理解主从到底在干什么这对后面解决问题帮助极大。1.1 主从复制的本质一个“日志搬运工”的流水线主从复制可以通俗地理解为一条流水线主库Master把所有写操作记录到自己的二进制日志binlog里从库Slave通过网络把主库的binlog拉到本地写入自己的中继日志relay log然后从库再把这些日志在本地“重放”一遍得到和主库一致的数据。这个过程中有三个关键线程在干活主库上的Binlog Dump线程负责把binlog发送给从库。从库上的I/O线程负责接收主库发来的binlog并写入本地的relay log。从库上的SQL线程负责读取relay log并在从库上执行。理解了这个模型你就明白为什么从库会出现延迟也明白为什么主库挂了从库还能继续因为relay log可能还没应用完。这套机制是2000年左右就定型的架构到今天依然是MySQL高可用的基石不管是传统的文件位置复制还是后面讲的GTID复制本质都没有跳出这个框架。1.2 两种复制方式二进制日志位置 vs GTID我推荐你用GTID传统的复制方式是“基于二进制日志位置”从库需要告诉主库“我从这个文件的这个偏移量开始拉日志”。这种方式最大的缺点是如果从库的中继日志丢了或主库的binlog被清理了你很难精确找到断点。而且Failover故障切换的时候去找“位置”是一件非常痛苦的事情。MySQL 5.6开始引入GTID全局事务标识5.7、8.0已经非常成熟。GTID给每个事务分配了一个全局唯一的ID格式是服务器UUID:事务序号。从库不需要关心从哪个文件的哪个位置开始只需要说“我已经执行到事务3E11FA47-71CA-11E1-9E33-C80AA9429562:10你从这个后面继续给我发就行”。这就像你看连续剧不需要记住看到第几集的第几分钟只需要记住“我看到第几集了”就能无缝续播。强烈建议新搭的主从一律用GTID方式。配置里加两个参数就行后面我会提到。2. 环境准备我的主从密码和账号规划在动手之前先把环境说清楚。我这边生产环境是两台Linux服务器CentOS 7.9MySQL版本是8.0.32通过二进制包部署没用系统自带的yum源。Windows上的玩法我后面也会提一句因为不少开发同学的测试机是Windows。角色IPserver-idMySQL版本Master192.168.1.1018.0.32Slave192.168.1.1128.0.32这里有个很容易踩坑的点server-id 必须不同而且不能为0。如果两台机器的server-id一样从库会报主键冲突一样的错误日志里会出现Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs。实战中不少人把两台机器克隆了MySQL安装路径也一样就连server-id也顺手复制了结果折腾半天连不上。2.1 从零装好一套MySQL 8Linux环境实例因为我是用二进制包方式安装的这里简单记录一下关键步骤装过的可以直接跳过# 下载解压版本号按你自己的来 cd /usr/local/ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql # 创建mysql用户和目录 useradd -s /sbin/nologin mysql mkdir -p /data/mysql chown -R mysql:mysql /data/mysql /usr/local/mysql # 初始化数据目录这一步密码是随机生成的在日志里 /usr/local/mysql/bin/mysqld --initialize-insecure --usermysql --basedir/usr/local/mysql --datadir/data/mysql用--initialize-insecure的好处是root初始密码为空登录后直接改就行省得去日志里翻随机密码我就是懒。初始化完成后配置/etc/my.cnf关键的几个参数我单独拎出来下一节细说。配置好后注册systemd服务启动cat /etc/systemd/system/mysqld.service EOF [Unit] DescriptionMySQL Server Afternetwork.target [Service] Typeforking Usermysql Groupmysql ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf ExecStop/usr/local/mysql/bin/mysqladmin -uroot -p shutdown Restarton-failure LimitNOFILE65536 [Install] WantedBymulti-user.target EOF systemctl daemon-reload systemctl enable mysqld --now顺便说一句Windows上装MySQL 8其实更简单就去官网下个zip包解压后自己建一个my.ini以管理员身份打开命令行执行mysqld --initialize-insecure和mysqld --install再net start mysql就能起来。Windows做从库和Linux做从库在配置上没有任何区别只是binlog的路径写法要注意转义比如log-binD:/mysql/logs/binlog用正斜杠或者双反斜杠都行别用单反斜杠。2.2 准备一个专门用于复制的账号登录主库创建一个复制专用账号。我习惯用repl这个名字一看就知道是干嘛的。不要用root账号去拉复制这是大忌。CREATE USER repl192.168.1.% IDENTIFIED BY StrongPass2024; GRANT REPLICATION SLAVE ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;这个权限只需要REPLICATION SLAVE不需要给其他权限。这里有个小坑MySQL 8.0 默认的认证插件是caching_sha2_password如果你的从库版本是5.7或者更早可能连不上主库。解决方案是创建用户时指定mysql_native_password但8.0.32之后的版本又逐步弃用了这个插件建议直接用8.0对8.0别混搭。如果非要混搭从库连接时报错Authentication plugin caching_sha2_password cannot be loaded就得在主库上重新建用户或者改认证方式。3. 主库和从库的my.cnf配置要点配置文件是主从配置的灵魂。我直接把我线上用的配置贴出来并逐条解释为什么这么写。3.1 主库配置binlog是命根子[mysqld] server-id 1 # 开启binlog文件名前缀建议带上主机名方便区分 log-bin /data/mysql/binlog/mysql-bin binlog_format ROW # 高峰期binlog文件大小默认128M我习惯调成512M减少文件轮转频率 max_binlog_size 512M # binlog的过期时间线上建议7天测试环境可以设短一点 binlog_expire_logs_seconds 604800 # 这个参数很重要后面单表同步的时候会靠它 binlog_row_image FULL # 这个决定了binlog的校验方式分布式环境建议设NONE否则有兼容性问题 binlog_checksum NONE gtid_mode ON enforce_gtid_consistency ONbinlog_format ROW是我特别要强调的。早期MySQL默认是STATEMENT也就是记录SQL语句本身主从执行同样的SQL。这在不确定函数UUID、NOW()存在时会造成主从数据不一致。ROW格式记录的是“哪一行数据变成了什么”虽然日志体积会大一些但是最可靠。8.0里其实已经默认ROW了但你要是从5.7老库升级来的还是要确认一下。binlog是主从复制的命根子这句话一点不夸张。如果主库没开binlog或者binlog被purge了从库就断了根只能重建。所以给binlog单独放一个目录是明智的跟数据目录分开避免磁盘满的时候互相挤兑。binlog_checksum NONE可能有人会问为什么不设CRC32。理论上是CRC32更安全但我实际遇到过跨版本5.7到8.0复制时CRC校验不一致导致IO线程反复报错的案例索性就统一用NONE了。如果你所有节点版本一致保持CRC32问题也不大。3.2 从库配置一个参数避免误操作[mysqld] server-id 2 # 从库也建议开binlog因为从库可能作为下一级主库级联复制 log-bin /data/mysql/binlog/mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON log_slave_updates ON # 从库设置为只读防止客户端直接写入 read_only ON # 即使是超级管理员也限制这个参数看需求开了就必须在这个库上执行super权限操作 # super_read_only ON # relay log配置 relay_log /data/mysql/relaylog/relay-bin # 防止SQL线程报错时无限重试设置重试上限 slave_parallel_workers 4 # 8.0默认的并行复制策略0表示数据库级别并行1表示日志级别并行 slave_parallel_type LOGICAL_CLOCK从库加read_only ON是我强烈建议的。因为很多人测试的时候就顺手往从库插入数据结果主从数据不一致又找不到原因。开了read_only后普通账号根本写不进数据能拦住大部分手误。如果你用的是类似ProxySQL这类中间件它也会自动识别read_only状态做读写分离不会把写请求路由到从库。slave_parallel_workers 4和slave_parallel_type LOGICAL_CLOCK是提升复制性能的关键。MySQL 8.0 默认已经是LOGICAL_CLOCK了但如果你是从5.7升上来的binlog里的信息可能不支持建议升级后用mysqlbinlog验证一下保证事务间并行不会出错。3.3 记住这个检查命令验证GTID配置是否生效配置完成后在主库执行SHOW VARIABLES LIKE gtid_mode; SHOW VARIABLES LIKE enforce_gtid_consistency; SELECT server_uuid;如果两个变量都是ONserver_uuid输出一串UUID那么配置就生效了。这一个UUID在后面的GTID复制中会反复出现。4. 配置主从关系从CHANGE MASTER到START SLAVE配置好后就要在从库上“告诉它主库在哪里”。这一步网上教程很多但大部分都没讲清楚怎么处理GTID的问题。4.1 先在主库上锁定数据做一个一致性快照在主库上执行FLUSH TABLES WITH READ LOCK;这个命令的作用是把所有的表锁住让主库暂时不能写数据。这时候在主库上执行mysqldump -uroot -p --all-databases --single-transaction --set-gtid-purgedON --master-data2 /tmp/master_init.sql注意我们是锁了表之后才做的dump。--single-transaction对InnoDB保证的是一致性快照可以不锁表但这里是用了两个保险。--set-gtid-purgedON会在dump文件中带上SET GLOBAL.GTID_PURGEDxxxx:1-100这样的语句告诉从库“这些事务我已经有了你不需要重复执行”。--master-data2则会在文件里注释标注主库当前使用的binlog文件名和位置。dump完成后记得UNLOCK TABLES别锁着不放开影响业务。4.2 把快照导入从库把/tmp/master_init.sql传到从库然后mysql -uroot -p /tmp/master_init.sql这里有个坑如果导入的备份文件里有GTID_PURGED相关语句那么导入后从库的GTID状态已经是“追赶中”的状态了不需要再额外指定MASTER_LOG_FILE和MASTER_LOG_POS。这是GTID复制比传统复制舒服的地方不需要找binlog位置。4.3 执行CHANGE MASTER TO登录从库执行RESET MASTER; -- 注意这是从库不是主库。可能有人误操作在主库执行了那就完了。 CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDStrongPass2024, MASTER_AUTO_POSITION1, MASTER_CONNECT_RETRY10, MASTER_HEARTBEAT_PERIOD10; START SLAVE;注意这里MASTER_AUTO_POSITION1就是GTID方式的关键。如果是传统方式需要用MASTER_LOG_FILEmysql-bin.000023, MASTER_LOG_POS1567这样的形式还要你手动去dump文件里查位置麻烦不说还容易错。关于RESET MASTER我特别提醒这个命令会让从库的binlog和GTID状态清空。如果你是从库刚搭好、里面还没有业务数据执行没问题。但如果你不小心在主库执行了主库的binlog全都清空所有从库都得重新做这个事故我见过不止一次。执行前一定要SELECT server_id确认自己登的是从库。4.4 验证主从状态这两个值你必须会看在从库上执行SHOW SLAVE STATUS\G关注以下字段Slave_IO_Running: 必须是Yes。如果为Connecting说明IO线程连不上主库检查网络、账号密码。Slave_SQL_Running: 必须是Yes。如果为No说明SQL线程执行出错通常是因为中继日志里的语句在从库执行时遇到冲突。Seconds_Behind_Master: 主从延迟秒数。注意这个值如果是NULL说明SQL线程没运行或者还没追上。Last_IO_Error/Last_SQL_Error: 最近一次错误的详细信息报错排查先看这里。还有一个容易忽略的Retrieved_Gtid_Set和Executed_Gtid_Set。这两个值分别表示从库拉取到哪了、执行到哪了。如果它们的差值越来越大说明延迟在累积。刚执行完START SLAVE后等几秒看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes说明主从状态已经正常了。你可以到主库CREATE TABLE、INSERT数据然后在从库查询验证。5. 只同步一张表到本地replicate-wild-do-table的正确打开方式开头提到的热搜词里有“把远程库的这张表同步到本地”这是很多人在做的数据抽取场景不需要整个库同步只要某几张核心表。这里要区分两种情况一种是新搭建时只初始化那几张表另一种是已有的从库上过滤复制。我建议用前者因为后者有复制不一致的天然风险。5.1 只dump需要的表在主库准备数据时用mysqldump指定表名mysqldump -uroot -p dbname table1 table2 --single-transaction --set-gtid-purgedON /tmp/partial_init.sql这里如果表比较少想直接连表结构都带上加--no-data只导结构再按条件导出数据。比如# 先导表结构 mysqldump -uroot -p dbname table1 --no-data /tmp/partial_schema.sql # 再导最近一个月的数据 mysqldump -uroot -p dbname table1 --wherecreate_time 2024-01-01 --no-create-info /tmp/partial_data.sql这部分数据导入从库后从库上暂时是干净的状态。5.2 在从库配置过滤规则在从库的/etc/my.cnf中加入# 只同步db1库的table1和table2其他忽略 replicate-wild-do-table db1.table1 replicate-wild-do-table db1.table2 replicate-wild-do-table db1.table%注意这里支持通配符。但如果你用了通配符实际上就把db1下的所有表都同步了。如果要精准同步某几张表就写完整的表名。规则写完后必须重启MySQL因为replicate-wild-do-table是静态参数改完后要重启从库生效。如果你需要把多个库的某些表同步过来可以写多行。但一定要注意的是replicate-wild-do-table和replicate-do-table这些过滤规则在GTID复制下有个坑GTID会拉取主库的所有事务但只在符合规则的表上执行也就是说拉到的事务仍然在relay log里只是被SQL线程过滤掉了。这意味着如果你过度依赖过滤规则来减小同步数据量relay log的磁盘占用反而可能变大了因为IO线程还是把所有binlog都拉过来了。5.3 一个更优方案用binlog_row_image和binlog过滤来减少网络开销前面配置里提到的binlog_row_image FULL这里再展开一下。ROW格式下每行变更会记录“前镜像后镜像”或者至少是“后镜像”。如果某些表有blob、text字段一张行可能很大日志量暴涨。有几种优化方式binlog_row_image MINIMAL只记录“唯一键字段 变更字段”日志小很多但可能在做回放时某些场景不稳定。如果只是同步几张小表不用改。主库上binlog-do-db只记录指定库的binlog其他库不记录。这样可以减少从库IO线程的网络压力但副作用是如果以后要扩展新的从库那些被过滤掉的库没法搭了。我个人的习惯是如果明确知道要用在哪几个库主库就配binlog-do-db db1从库再配replicate-wild-do-table双重过滤。这是典型的“生产环境取交集”玩法网络开销和磁盘开销都能压下来。5.4 验证主库写入从库只看得到指定表在主库执行USE db1; INSERT INTO table1 (id, name) VALUES (1, sync_test); INSERT INTO table2 (id, name) VALUES (100, sync_test2); USE testdb; INSERT INTO t (id, col) VALUES (999, this_should_not_sync);等几秒后在从库查询SHOW DATABASES; -- 应该只有db1和系统库testdb不会出现 SELECT * FROM db1.table1; -- 应该有刚插入的那条数据 SELECT * FROM db1.table2; -- 应该有数据 USE testdb; -- ERROR 1049: Unknown database注意如果你主从之前的GTID已经同步到某一点之后从库拉取了全部binlog但过滤规则只对某些表执行那么GTID_SET里还是会记录所有事务过滤掉的事务同样会算作“已执行”。这是GTID模式的一个特性也是它“从库可用binlog做进一步分发”的基础。6. 复制卡住、报错、延迟高的解决方法主从配置好只是开始日常维护才是大头。我最常遇到的是三类问题这里直接给排查链路。6.1 IO线程报错Cant connect to MySQL server on 192.168.1.10 (111)优先排查网络然后看账号权限最后看防火墙。111 拒绝连接一般就是网络不通或者端口被屏蔽。用下面三条命令一次定位telnet 192.168.1.10 3306 ping 192.168.1.10 netstat -anp | grep 3306如果是云服务器阿里云/腾讯云的安全组也要放行3306这是很多人忽略的。即使操作系统防火墙关了安全组没加白名单照样连不上。账号权限可以通过GRANT REPLICATION SLAVE后记得FLUSH PRIVILEGES来验证。6.2 SQL线程报错Duplicate entry 1 for key PRIMARY这个错误太典型了从库上已经有主键为1的数据但主库又插入了一条主键为1的数据。或者反过来从库自己写了一条数据主库没这条数据然后主库又执行了相同主键的插入从库重放时冲突。解决思路分三步先确定从库数据是不是“脏”了看看本地自己写入了什么。如果从库数据不值得保留停掉SQL线程然后跳过这个事务最常用STOP SLAVE SQL_THREAD; SET GLOBAL sql_slave_skip_counter 1; -- 传统复制用 START SLAVE SQL_THREAD;但这在GTID模式下已经不可用了MySQL 8.0 下需要用另一种方式见下节。如果脏数据比较多建议直接重建从库不要慢慢跳事务因为有的事务修改了多行跳过会留下更多不一致的隐患。如果偶尔一个事务就是需要跳过GTID模式下可以用“注入空事务”的方式跳过。具体做法是找到报错事务的GTID然后在从库手动执行一个空事务来弥补STOP SLAVE; SET GTID_NEXTxxxx:12345; -- 这里是报错的那个GTID BEGIN; COMMIT; SET GTID_NEXTAUTOMATIC; START SLAVE;这种操作会让从库的GTID集合追上但数据上可能还是缺了那个事务。只适合“明确该事务可忽略”的场景不能滥用。6.3 主从延迟高Seconds_Behind_Master越来越大出现延迟后先别急。检查几条从库CPU负载是否过高有大查询正在跑会拖慢SQL线程。主库是不是有大事务比如DELETE FROM huge_table或ALTER TABLE这类操作在从库上会同步放大可能锁表、占IO。长事务会导致从库的relay log堆积。可以在从库上看SHOW PROCESSLIST;如果一个线程的Info字段是System lock或者Waiting for table metadata lock说明SQL线程被一个大操作卡住了。还有一类隐蔽的场景从库用的是机械盘或者单盘IOPS不足而主库是SSD这时候复制的瓶颈就在从库的磁盘。解决办法是把从库的redo log增大比如innodb_redo_log_capacity 8G8.0.30后默认有该参数让SQL线程在做checkpoint时没有那么频繁的fsync。另外innodb_buffer_pool_size也要给足否则每一个变更都要读写磁盘复制延迟会持续累积。6.4 GTID复制下报错The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION 1, but the master has purged binary logs这句话翻译成人话是从库要求按GTID自动定位但主库的binlog已经把从库需要的事务删掉了。通常发生在从库停机很久或者主库的binlog过期时间设得太短。遇到这个错误没有别的办法只能重建从库。不要想着“我就差几天数据手工补一下就行”这个念头省不了多少时间却会埋下无数数据不一致的雷。直接回到第4节重新mysqldump CHANGE MASTER一条龙重做。7. 主从配置完之后还有三件事必须做主从正常跑起来后很多人就撤了。我建议你多留十五分钟做下面三件事能帮你日后避免很多麻烦。7.1 配置监控秒级告警必不可少写一个简单的脚本定时检查Slave_IO_Running、Slave_SQL_Running和Seconds_Behind_Master。贴上我常用的一个patch#!/bin/bash status$(mysql -uroot -pYourPass -e SHOW SLAVE STATUS\G 2/dev/null | grep -E Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master) echo $status io$(echo $status | grep Slave_IO_Running | awk {print $2}) sql$(echo $status | grep Slave_SQL_Running | awk {print $2}) delay$(echo $status | grep Seconds_Behind_Master | awk {print $2}) if [ $io ! Yes ] || [ $sql ! Yes ] || [ $delay -gt 60 ]; then echo MySQL Replication ERROR: IO$io SQL$sql Delay$delay # 这里可以接自己的告警通道如短信、钉钉群里加一个webhook fi这个脚本放到crontab里每1分钟跑一次。延迟阈值按业务定60秒只是保守值。如果有超过两分钟的延迟线上订单系统就会出问题建议阈值再收紧。7.2 备份策略从库的binlog别浪费既然从库也开了binloglog_slave_updates ON那从库的binlog可以作为本地备份的基础。比如每天凌晨在从库做一次mysqldump这样即使主库整个挂了也能在从库上恢复或者把从库提升为新主库提升前需要先确认从库数据完全追平。还有一个小技巧定期在从库上执行校验和比如用pt-table-checksumPercona Toolkit比对主从的表是否一致。这是我处理过不少“主从数据静默不一致”问题的经验光靠SHOW SLAVE STATUS根本发现不了这种问题。7.3 写个“提主”操作文档一主一从场景下主库挂了最紧急的操作是把从库提为主库。操作链路很简单确认从库已经追上主库最后的binlog。在从库上执行STOP SLAVE; RESET SLAVE ALL;要保留binlog就别RESET SLAVE ALL只 STOP 即可。把从库的read_only改回OFFsuper_read_only也改掉。将业务流量切到新的主库。但实际生产环境可能有多个从库还可能有主从切换的工具MHA、Orchestrator等建议提前演练。第一次切主的时候手忙脚乱、执行错命令的概率极高我自己的经验是把切换命令文档放在从库服务器上标上“降级预案”比临时翻笔记强一万倍。8. 最后说两个容易被忽略的细节补充两个我实际工作中踩过的坑都是配置上不起眼但后果严重的地方。8.1 主库max_allowed_packet必须大于从库MySQL复制本质是传输binlog事件。如果主库有一个大的存储过程或者大的批量插入生成的binlog事件可能非常大。如果从库的max_allowed_packet配置小于主库SQL线程会报Got a packet bigger than max_allowed_packet bytes。建议在主从两边都设置成比较大的值比如64M或128M并且从库不能小于主库。这属于你在配置里顺手加上就能避免的麻烦。8.2 不同版本之间的复制字符集和排序规则如果主库是8.0从库是5.7虽然我不推荐字符集不一致会造成SQL线程报错。特别是如果主库的表用了utf8mb4_0900_ai_ciMySQL 8.0默认排序规则5.7根本不认识这个排序规则同步的那张表在从库上会直接跳过或者建表失败。检查方法SELECT table_name, table_collation FROM information_schema.tables WHERE table_schemadb1;如果排雷后发现不同建议统一改成utf8mb4_general_ci虽然是老排序规则但兼容性最好。在自己负责的数据库里我一般统一指定utf8mb4utf8mb4_general_ci宁可少用一点新特性的排序也不要四处乱碰兼容性问题。把上面这些步骤都走完你的MySQL主从应该能稳定跑起来。这篇文章我从原理讲到配置再讲到单表同步、故障排查和日常维护基本覆盖了我这些年在生产环境折腾主从的核心经验。主从复制本身不是高可用方案的终点它后面还连着读写分离、故障自动切换、数据备份这些话题但底层的东西都在这了。如果你照着配完还碰到什么报错欢迎在评论区把SHOW SLAVE STATUS\G的输出贴出来我帮你一起看。
