MySQL如何实现读写分离_配置主从复制与binlog日志同步-SQL-PHP中文网

主库必须开启binlog并配置非零唯一server-id；从库需用replication slave权限账号连接，change master to参数须与show master status完全一致；start slave后须检查io和sql线程状态及seconds_behind_master。

mysql如何实现读写分离_配置主从复制与binlog日志同步

主库必须开启 binlog 且设置正确 server-id

MySQL 读写分离依赖主从复制，而复制的基础是主库生成 binlog。如果 binlog 没开，或者 server-id 为 0 或未配置，从库连连接请求都会被拒绝，更别说同步了。

常见错误现象：SHOW SLAVE STATUS\G 中看到 IO_Running: No，Last_IO_Error 提示 “Could not find first log file name in binary log index file” 或直接报 “Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation”（其实是 binlog 关闭导致权限检查绕过失败）。

确认已设置 log-bin = mysql-bin（路径可省略，默认在 datadir 下）
server-id 必须是非 0 整数，主从不能相同；建议主库设为 1，从库依次为 2、3 等
重启前检查 my.cnf 是否在生效路径（如 /etc/my.cnf 或 /etc/mysql/my.cnf），Docker 容器里常因挂载错位导致配置没加载
运行 SELECT @@log_bin, @@server_id; 实时验证，两个值都应为 1

从库执行 CHANGE MASTER TO 时账号权限和连接参数要严格匹配

从库靠一个专用复制账号拉取主库 binlog，这个账号不是随便建个用户就行——它必须有 REPLICATION SLAVE 权限，且 CHANGE MASTER TO 里填的 MASTER_LOG_FILE 和 MASTER_LOG_POS 必须和主库当前 SHOW MASTER STATUS 输出完全一致，差一个字节都会同步失败。

使用场景：主库已有数据，需要从某个时间点开始同步；或主从断连后手动重置同步位置。

主库创建账号： CREATE USER 'repl'@'%' IDENTIFIED BY 'strong_pass'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
查主库位点：SHOW MASTER STATUS; 记下 File 和 Position 字段值（比如 mysql-bin.000003, 154）
从库执行：CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='repl', MASTER_PASSWORD='strong_pass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
别漏掉 MASTER_PORT（默认 3306，但显式写上更稳），也别把 MASTER_LOG_POS 写成字符串（必须是数字）

START SLAVE 后立刻检查 IO 和 SQL 线程状态

执行 START SLAVE 不等于复制就跑起来了。MySQL 用两个独立线程工作：IO 线程负责连主库、拉日志；SQL 线程负责解析并执行 relay log。任一线程卡住，同步就停摆，但 Slave_IO_Running 和 Slave_SQL_Running 可能一真一假，容易误判。

性能影响：如果 SQL 线程长期延迟（Seconds_Behind_Master > 0），说明从库写入跟不上，可能是从库磁盘慢、大事务未拆分、或开启了 sync_binlog=1 + innodb_flush_log_at_trx_commit=1 双高可靠性配置。

检查命令固定用：SHOW SLAVE STATUS\G（注意末尾 \G 是关键，否则字段挤成一行看不清）
重点盯：Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0
若 Seconds_Behind_Master 持续增长，先看 Relay_Log_Space 是否暴涨（说明日志堆积）、再查 Last_SQL_Error（常见于从库有唯一键冲突或删了不该删的表）
不要依赖 SHOW PROCESSLIST 里 “System user” 进程是否存在来判断——它可能挂着但实际卡死

应用层读写分离不能只靠注释或中间件自动识别

MySQL 本身不区分读写语句，SELECT 可能带 FOR UPDATE，INSERT ... SELECT 也是读+写混合。所谓“读走从库、写走主库”，必须由应用代码、代理层（如 ProxySQL、MaxScale）或 ORM 显式控制，不存在 MySQL 自动分流这回事。

容易踩的坑：用 /*FORCE_MASTER*/ 注释想强制走主库，但 JDBC 驱动默认不解析注释；或以为用了 ShardingSphere 就万事大吉，结果配置里没打开 sql-comment-parse=true，注释全被忽略。

最稳妥的方式是应用里维护两个数据源：一个指向主库地址，一个指向从库地址，业务逻辑中显式选择
如果用代理，确认其负载策略是否支持事务粘性（即一个事务内所有语句必须走同一节点），否则 BEGIN; SELECT ...; INSERT ...; COMMIT; 可能被拆到不同库，引发数据不一致
监控项不能只看 Threads_connected，要分别采集主库的 Com_insert/Com_update 和从库的 Com_select，才能验证分流是否真实生效

binlog 格式选 ROW 而不是 STATEMENT，不然函数、临时表、非确定性语句会导致从库执行结果不一致——这点很多人配完就忘，等上线跑两周才发现数据对不上。