一、环境规划角色IP 地址主机名作用主库 Master172.16.1.191db-master可读写处理所有写操作从库 Slave172.16.1.193db-slave只读处理查询操作前提条件两台服务器网络互通能互相 ping 通MySQL 已安装版本尽量一致191服务器mysql有数据防火墙开放 3306 端口二、主库配置Master2.1 修改配置文件# 编辑配置文件# CentOS: /etc/my.cnf# Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnfvim/etc/my.cnf在[mysqld]段添加以下配置[mysqld]port3306basedir/usr/local/mysqldatadir/data/3306/datausermysqlsocket/tmp/mysql.sock# 主从复制核心配置 server-id1# 唯一标识必须为1log-binmysql-bin# 开启二进制日志binlog-formatROW# 行级复制推荐sync-binlog1# 每次事务提交同步到磁盘expire-logs-days7# binlog保留7天# 可选指定要复制的数据库不设置则复制所有# binlog-do-db world# 可选指定不复制的数据库# binlog-ignore-db mysql2.2 重启 MySQLsystemctl restart mysqld2.3 创建复制专用用户-- 登录 MySQLmysql-uroot-p-- 创建复制用户允许从库IP连接CREATEUSERrepl172.16.1.193IDENTIFIEDBY123456;-- 授予复制权限GRANTREPLICATIONSLAVEON*.*TOrepl172.16.1.193;-- 刷新权限FLUSHPRIVILEGES;2.4 获取 binlog 位置-- 锁表防止数据写入导致位置变化FLUSHTABLESWITHREADLOCK;-- 查看当前 binlog 文件和位置SHOWMASTERSTATUS;记录输出结果---------------------------- | File | Position | ---------------------------- | mysql-bin.000001 | 709 | ----------------------------2.5 导出初始数据# 另开一个 SSH 窗口执行导出mysqldump-uroot-p--all-databases --master-data --single-transaction/tmp/master_backup.sql参数含义作用–all-databases备份所有数据库导出全部数据包括系统库mysql、sys等–master-data记录 binlog 位置在备份文件中记录主库的二进制日志坐标–single-transaction单事务模式在不锁表的情况下获得一致性快照2.6 解锁主库-- 导出完成后执行UNLOCKTABLES;2.7 传输备份到从库# 将备份文件复制到从库scp/tmp/master_backup.sql root172.16.1.193:/tmp/三、从库配置Slave3.1 修改配置文件vim/etc/my.cnf[mysqld] port3306 basedir/usr/local/mysql datadir/data/3306/data usermysql socket/tmp/mysql.sock # 主从复制核心配置 server-id 2 # 必须与主库不同 relay-log mysql-relay-bin # 中继日志 read-only 1 # 设置为只读 # 可选指定要复制的数据库 # replicate-do-db world # 可选指定不复制的数据库 # replicate-ignore-db mysql3.2 重启 MySQLsystemctl restart mysqld3.3 导入主库初始数据-- 登录从库 MySQLmysql-uroot-p-- 导入数据source/tmp/master_backup.sql;3.4 配置主从连接-- 停止现有的复制如果有STOP SLAVE;-- 清除旧的配置RESET SLAVEALL;-- 配置主库连接信息CHANGE MASTERTOMASTER_HOST172.16.1.191,-- 主库 IPMASTER_PORT3306,-- 主库端口MASTER_USERrepl,-- 复制用户名MASTER_PASSWORD123456,-- 密码MASTER_LOG_FILEmysql-bin.000001,-- 主库的 FileMASTER_LOG_POS709;-- 主库的 Position-- 启动复制STARTSLAVE;3.5 验证复制状态SHOWSLAVESTATUS\G关键指标检查字段期望值说明Slave_IO_RunningYesIO线程正常Slave_SQL_RunningYesSQL线程正常Seconds_Behind_Master0无延迟Last_IO_Errno0无IO错误Last_SQL_Errno0无SQL错误四、验证主从同步4.1 在主库创建测试数据-- 创建测试数据库CREATEDATABASEtest_replication;-- 使用测试数据库USEtest_replication;-- 创建测试表CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 插入测试数据INSERTINTOusers(name)VALUES(张三);INSERTINTOusers(name)VALUES(李四);INSERTINTOusers(name)VALUES(王五);-- 查看主库数据SELECT*FROMusers;4.2 在从库验证-- 切换到测试数据库USEtest_replication;-- 查询数据应该与主库一致SELECT*FROMusers;4.3 验证从库只读-- 在从库尝试写入应该报错INSERTINTOusers(name)VALUES(测试);预期报错ERROR 1290 (HY000): The MySQL server is running with the --read-only option五、常用维护命令操作命令查看主库状态SHOW MASTER STATUS;查看从库状态SHOW SLAVE STATUS\G停止从库复制STOP SLAVE;启动从库复制START SLAVE;跳过错误慎用STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;六、快速排查指南问题可能原因解决方案Slave_IO_Running: Connecting网络不通/用户密码错误检查防火墙、重新授权Slave_IO_Running: No主库 binlog 文件或位置错误重新CHANGE MASTERSlave_SQL_Running: No数据冲突主键重复等跳过错误或重新同步Seconds_Behind_Master持续增长主库写入量大/网络延迟开启并行复制八、架构图┌─────────────────────────────────────────────────────────────────┐ │ 应用程序 │ │ 写操作 → 主库 / 读操作 → 从库 │ └─────────────┬─────────────────────────────────┬───────────────┘ │ │ ▼ ▼ ┌───────────────┐ ┌───────────────┐ │ 主库 Master │ binlog │ 从库 Slave │ │ 172.16.1.191 │ ────────────────→ │ 172.16.1.193 │ │ (可读写) │ 异步复制 │ (只读) │ └───────────────┘ └───────────────┘ │ │ │ │ ▼ ▼ INSERT/SELECT SELECT 写操作入口 读操作入口
MySQL 运维实战系列（七）mysql 主从配置
发布时间：2026/5/27 1:16:12
一、环境规划角色IP 地址主机名作用主库 Master172.16.1.191db-master可读写处理所有写操作从库 Slave172.16.1.193db-slave只读处理查询操作前提条件两台服务器网络互通能互相 ping 通MySQL 已安装版本尽量一致191服务器mysql有数据防火墙开放 3306 端口二、主库配置Master2.1 修改配置文件# 编辑配置文件# CentOS: /etc/my.cnf# Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnfvim/etc/my.cnf在[mysqld]段添加以下配置[mysqld]port3306basedir/usr/local/mysqldatadir/data/3306/datausermysqlsocket/tmp/mysql.sock# 主从复制核心配置 server-id1# 唯一标识必须为1log-binmysql-bin# 开启二进制日志binlog-formatROW# 行级复制推荐sync-binlog1# 每次事务提交同步到磁盘expire-logs-days7# binlog保留7天# 可选指定要复制的数据库不设置则复制所有# binlog-do-db world# 可选指定不复制的数据库# binlog-ignore-db mysql2.2 重启 MySQLsystemctl restart mysqld2.3 创建复制专用用户-- 登录 MySQLmysql-uroot-p-- 创建复制用户允许从库IP连接CREATEUSERrepl172.16.1.193IDENTIFIEDBY123456;-- 授予复制权限GRANTREPLICATIONSLAVEON*.*TOrepl172.16.1.193;-- 刷新权限FLUSHPRIVILEGES;2.4 获取 binlog 位置-- 锁表防止数据写入导致位置变化FLUSHTABLESWITHREADLOCK;-- 查看当前 binlog 文件和位置SHOWMASTERSTATUS;记录输出结果---------------------------- | File | Position | ---------------------------- | mysql-bin.000001 | 709 | ----------------------------2.5 导出初始数据# 另开一个 SSH 窗口执行导出mysqldump-uroot-p--all-databases --master-data --single-transaction/tmp/master_backup.sql参数含义作用–all-databases备份所有数据库导出全部数据包括系统库mysql、sys等–master-data记录 binlog 位置在备份文件中记录主库的二进制日志坐标–single-transaction单事务模式在不锁表的情况下获得一致性快照2.6 解锁主库-- 导出完成后执行UNLOCKTABLES;2.7 传输备份到从库# 将备份文件复制到从库scp/tmp/master_backup.sql root172.16.1.193:/tmp/三、从库配置Slave3.1 修改配置文件vim/etc/my.cnf[mysqld] port3306 basedir/usr/local/mysql datadir/data/3306/data usermysql socket/tmp/mysql.sock # 主从复制核心配置 server-id 2 # 必须与主库不同 relay-log mysql-relay-bin # 中继日志 read-only 1 # 设置为只读 # 可选指定要复制的数据库 # replicate-do-db world # 可选指定不复制的数据库 # replicate-ignore-db mysql3.2 重启 MySQLsystemctl restart mysqld3.3 导入主库初始数据-- 登录从库 MySQLmysql-uroot-p-- 导入数据source/tmp/master_backup.sql;3.4 配置主从连接-- 停止现有的复制如果有STOP SLAVE;-- 清除旧的配置RESET SLAVEALL;-- 配置主库连接信息CHANGE MASTERTOMASTER_HOST172.16.1.191,-- 主库 IPMASTER_PORT3306,-- 主库端口MASTER_USERrepl,-- 复制用户名MASTER_PASSWORD123456,-- 密码MASTER_LOG_FILEmysql-bin.000001,-- 主库的 FileMASTER_LOG_POS709;-- 主库的 Position-- 启动复制STARTSLAVE;3.5 验证复制状态SHOWSLAVESTATUS\G关键指标检查字段期望值说明Slave_IO_RunningYesIO线程正常Slave_SQL_RunningYesSQL线程正常Seconds_Behind_Master0无延迟Last_IO_Errno0无IO错误Last_SQL_Errno0无SQL错误四、验证主从同步4.1 在主库创建测试数据-- 创建测试数据库CREATEDATABASEtest_replication;-- 使用测试数据库USEtest_replication;-- 创建测试表CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 插入测试数据INSERTINTOusers(name)VALUES(张三);INSERTINTOusers(name)VALUES(李四);INSERTINTOusers(name)VALUES(王五);-- 查看主库数据SELECT*FROMusers;4.2 在从库验证-- 切换到测试数据库USEtest_replication;-- 查询数据应该与主库一致SELECT*FROMusers;4.3 验证从库只读-- 在从库尝试写入应该报错INSERTINTOusers(name)VALUES(测试);预期报错ERROR 1290 (HY000): The MySQL server is running with the --read-only option五、常用维护命令操作命令查看主库状态SHOW MASTER STATUS;查看从库状态SHOW SLAVE STATUS\G停止从库复制STOP SLAVE;启动从库复制START SLAVE;跳过错误慎用STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;六、快速排查指南问题可能原因解决方案Slave_IO_Running: Connecting网络不通/用户密码错误检查防火墙、重新授权Slave_IO_Running: No主库 binlog 文件或位置错误重新CHANGE MASTERSlave_SQL_Running: No数据冲突主键重复等跳过错误或重新同步Seconds_Behind_Master持续增长主库写入量大/网络延迟开启并行复制八、架构图┌─────────────────────────────────────────────────────────────────┐ │ 应用程序 │ │ 写操作 → 主库 / 读操作 → 从库 │ └─────────────┬─────────────────────────────────┬───────────────┘ │ │ ▼ ▼ ┌───────────────┐ ┌───────────────┐ │ 主库 Master │ binlog │ 从库 Slave │ │ 172.16.1.191 │ ────────────────→ │ 172.16.1.193 │ │ (可读写) │ 异步复制 │ (只读) │ └───────────────┘ └───────────────┘ │ │ │ │ ▼ ▼ INSERT/SELECT SELECT 写操作入口 读操作入口
