在数据库高可用架构中单点故障和数据备份是必须解决的难题。当业务量增长读写请求都集中在单一数据库实例上时性能瓶颈和宕机风险会急剧增加。MySQL 主从同步Replication正是解决这些问题的核心方案之一它通过将主库Master的数据变更实时复制到一个或多个从库Slave实现了数据的冗余备份、读写分离和负载均衡。本文将系统性地拆解 MySQL 主从同步的原理并提供从零开始搭建“一主一从”和“一主多从”架构的完整实战教程包含详细的配置步骤、代码示例和排错指南无论是用于学习、测试还是生产环境部署都能直接复用。1. 主从同步核心原理与 MySQL 实现介绍1.1 什么是主从同步主从同步也称为数据库复制是一种数据冗余和高可用性技术。其核心思想是让一个数据库服务器主库的数据变更自动、异步地同步到另一个或多个数据库服务器从库。在 MySQL 中这个过程是单向的数据从主库流向从库。它主要解决以下问题数据备份与灾难恢复从库作为主库的实时热备当主库发生故障时可以快速切换到从库减少服务中断时间。读写分离主库负责处理写操作INSERT, UPDATE, DELETE和部分实时性要求高的读操作而从库可以承担绝大部分的读操作SELECT从而分散主库压力提升整体并发处理能力。数据分析与报表可以在从库上执行耗时的统计、报表查询或数据挖掘任务避免这些操作影响主库的在线事务处理性能。1.2 MySQL 主从同步的工作原理MySQL 默认采用基于二进制日志Binary Log的异步复制机制。理解其流程是后续配置和排错的基础。整个复制过程可以概括为以下三个核心步骤第一步主库记录二进制日志Binary Log当主库上发生数据变更DML、DDL时这些变更会以“事件Event”的形式按照事务提交的顺序被顺序写入本地的二进制日志文件中。二进制日志是复制的数据源。第二步从库拉取日志I/O Thread从库会启动一个I/O 线程连接到主库。主库上有一个特殊的线程Binlog Dump Thread负责响应从库的请求。I/O 线程会读取主库的二进制日志事件并将其写入从库本地的中继日志Relay Log文件中。第三步从库重放日志SQL Thread从库会启动一个SQL 线程读取本地的中继日志并解析、执行其中记录的 SQL 事件从而在从库上重现主库的数据变更最终达到数据一致的状态。简单流程图主库 (Master) | | 1. 数据变更 - 写入 Binlog v Binlog Dump Thread (主库线程) | | 2. 通过网络传输 Binlog Event v 从库 I/O Thread (从库线程) - 写入 Relay Log | v 从库 SQL Thread (从库线程) - 执行 Relay Log 中的 SQL - 从库数据更新关键概念解析异步复制主库提交事务后无需等待从库应用完成即可向客户端返回成功。这是 MySQL 默认方式性能好但存在极小概率的数据延迟或丢失风险。二进制日志 (binlog)记录所有更改数据库数据的语句用于复制和点播恢复。格式有 STATEMENT基于SQL语句、ROW基于行数据变更、MIXED混合模式。中继日志 (relay log)从库的 I/O 线程从主库拉取的二进制日志的副本格式与 binlog 相同供 SQL 线程消费。GTID (Global Transaction Identifier)MySQL 5.6 引入的全局事务ID为每个提交的事务生成一个唯一标识。使用 GTID 可以简化复制故障切换和主从维护无需再依赖传统的binlog file和position。2. 环境准备与版本说明在开始实战前需要准备好实验环境。为了模拟真实场景我们将在同一台机器上部署多个 MySQL 实例通过不同端口区分。生产环境通常部署在不同服务器上。环境要求操作系统本文以 CentOS 7.x / Rocky Linux 8 为例命令也适用于大多数 Linux 发行版。Windows 环境思路类似但配置文件和启动方式不同。MySQL 版本强烈建议使用 5.7 或 8.0 版本。本文示例基于 MySQL 8.0.36但核心配置对 5.7 同样适用。请注意8.0 在默认身份验证插件caching_sha2_password和部分参数上与 5.7 有差异文中会特别说明。服务器规划假设我们有一台服务器IP 为192.168.1.100。一主一从主库端口3306从库端口3307。一主多从主库端口3306从库1端口3307从库2端口3308。重要前提确保服务器之间网络互通如果是单机多实例则本地环回地址127.0.0.1互通。关闭防火墙或开放对应的 MySQL 端口如 3306, 3307, 3308。# 临时关闭防火墙仅用于实验生产环境请配置规则 systemctl stop firewalld systemctl disable firewalld # 或者开放端口 # firewall-cmd --permanent --add-port3306/tcp # firewall-cmd --reload安装 MySQL。如果尚未安装可以参考以下简易步骤# 以 Rocky Linux 8 安装 MySQL 8.0 为例 sudo dnf install mysql:8.0 -y # 启动并设置开机自启 sudo systemctl start mysqld sudo systemctl enable mysqld # 运行安全初始化脚本设置 root 密码等 sudo mysql_secure_installation3. 搭建一主一从同步架构这是最基础也是最常见的复制结构。我们将一步步完成配置。3.1 主库Master配置步骤 1编辑主库配置文件MySQL 的主配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf也可能在/etc/my.cnf.d/目录下。我们需要修改或添加以下配置。# 文件路径/etc/my.cnf (在 [mysqld] 段落下添加) [mysqld] # 服务器唯一ID主从必须不同 server-id 1 # 启用二进制日志并指定日志文件前缀 log-bin mysql-bin # 设置二进制日志格式推荐使用 ROW数据一致性更好 binlog_format ROW # 可选指定需要复制的数据库多个则写多行不配置则默认复制所有库 # binlog-do-db mydb # 可选指定不需要复制的数据库 # binlog-ignore-db mysql # binlog-ignore-db information_schema # binlog-ignore-db performance_schema # binlog-ignore-db sys # 从 MySQL 8.0.20 开始默认开启 binlog 校验和建议保持开启 binlog_checksum CRC32步骤 2重启主库 MySQL 服务sudo systemctl restart mysqld # 检查服务状态 sudo systemctl status mysqld步骤 3登录主库创建用于复制的用户并授权复制用户需要REPLICATION SLAVE权限。-- 登录 MySQL mysql -u root -p -- 创建复制用户repl_user 是用户名YourPassword123! 是密码请修改。 -- % 表示允许从任何主机连接生产环境建议指定从库IP。 CREATE USER repl_user% IDENTIFIED BY YourPassword123!; -- MySQL 8.0 默认使用 caching_sha2_password如果从库是旧版本客户端可能有问题可改用 mysql_native_password -- CREATE USER repl_user% IDENTIFIED WITH mysql_native_password BY YourPassword123!; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO repl_user%; -- 刷新权限 FLUSH PRIVILEGES;步骤 4查看主库状态记录关键信息执行以下命令记录File和Position的值在配置从库时会用到。SHOW MASTER STATUS\G输出示例*************************** 1. row *************************** File: mysql-bin.000001 Position: 157 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set:请务必记下File: mysql-bin.000001和Position: 157。3.2 从库Slave配置步骤 1准备从库数据可选但推荐为了保证主从数据起点一致最好先将主库的数据全量导出并导入到从库。如果从库是全新的可跳过此步。# 在主库服务器上执行导出 mysqldump -u root -p --all-databases --master-data2 --single-transaction --routines --events master_full_backup.sql # 将备份文件拷贝到从库服务器然后导入 mysql -u root -p master_full_backup.sql步骤 2编辑从库配置文件从库需要不同的server-id并且通常不需要开启log-bin除非它本身也要作为其他从库的主库即级联复制。# 文件路径/etc/my.cnf (在 [mysqld] 段落下添加) [mysqld] # 服务器唯一ID必须与主库和其他从库不同 server-id 2 # 可选如果需要从库也记录二进制日志用于备份或级联则开启 # log-bin mysql-bin # 可选设置中继日志文件前缀 relay-log mysql-relay-bin # 可选设置中继日志索引文件 relay-log-index mysql-relay-bin.index # 设置只读模式防止从库被意外写入影响同步 read_only 1 # 可选对于超级用户如root是否也只读1为是 super_read_only 1步骤 3重启从库 MySQL 服务# 注意如果是单机多实例需要启动对应端口的实例这里假设已配置好3307实例并重启 sudo systemctl restart mysqld3307 # 具体服务名根据你的多实例配置而定 # 更通用的方法是指定配置文件启动本文假设你已配置好多实例。步骤 4登录从库配置复制链路使用第 3.1 步中记录的File和Position信息。-- 登录从库 (端口3307) mysql -u root -p -P 3307 -- 停止从库复制线程如果是新库可能已经在停止状态 STOP SLAVE; -- 配置主库连接信息 CHANGE MASTER TO MASTER_HOST192.168.1.100, -- 主库IP单机多实例则为 127.0.0.1 MASTER_PORT3306, -- 主库端口 MASTER_USERrepl_user, -- 第3.1步创建的复制用户 MASTER_PASSWORDYourPassword123!, MASTER_LOG_FILEmysql-bin.000001, -- 第3.1步记录的 File MASTER_LOG_POS157; -- 第3.1步记录的 Position -- 如果是基于 GTID 的复制配置更简单推荐 MySQL 5.7 使用 -- CHANGE MASTER TO -- MASTER_HOST192.168.1.100, -- MASTER_PORT3306, -- MASTER_USERrepl_user, -- MASTER_PASSWORDYourPassword123!, -- MASTER_AUTO_POSITION 1;步骤 5启动从库复制并检查状态-- 启动复制 START SLAVE; -- 查看从库复制状态 SHOW SLAVE STATUS\G3.3 验证同步状态与测试关键状态检查执行SHOW SLAVE STATUS\G后重点关注以下字段Slave_IO_Running:必须为Yes。表示 I/O 线程正常运行正在从主库读取日志。Slave_SQL_Running:必须为Yes。表示 SQL 线程正常运行正在重放中继日志。Seconds_Behind_Master:应为0或一个很小的数字。表示从库落后主库的秒数0代表完全同步。Last_IO_Error/Last_SQL_Error:应为空。如果有错误信息说明同步出现问题。测试数据同步在主库3306上创建一个数据库和表并插入数据。CREATE DATABASE IF NOT EXISTS test_repl; USE test_repl; CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO user (name) VALUES (Alice), (Bob);在从库3307上查询数据。USE test_repl; SELECT * FROM user;如果能看到Alice和Bob两条记录说明主从同步成功4. 扩展至一主多从同步架构一主多从架构是一主一从的自然延伸配置流程几乎相同只是需要重复配置多个从库。每个从库都需要唯一的server-id和独立的复制链路配置。架构规划主库 (Master):192.168.1.100:3306server-id1从库1 (Slave1):192.168.1.100:3307server-id2从库2 (Slave2):192.168.1.100:3308server-id3配置步骤主库配置与3.1 节完全相同无需更改。从库1配置与3.2 节完全相同server-id2,port3307。从库2配置按照3.2 节的步骤操作。修改从库配置文件设置server-id 3和对应的端口如port3308。启动 MySQL 实例端口 3308。登录从库2执行CHANGE MASTER TO ...命令注意MASTER_PORT仍是3306指向主库MASTER_LOG_FILE和MASTER_LOG_POS需要重新从主库获取当前值因为从库1启动后主库的Position可能已经变化。-- 在主库上重新执行 SHOW MASTER STATUS\G -- 记下新的 File 和 Position例如 mysql-bin.000001, 831在从库2上配置时使用这个新的位置信息。CHANGE MASTER TO MASTER_HOST192.168.1.100, MASTER_PORT3306, MASTER_USERrepl_user, MASTER_PASSWORDYourPassword123!, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS831;启动从库2的复制线程START SLAVE;验证分别在两个从库上执行SHOW SLAVE STATUS\G和查询测试数据确保Slave_IO_Running和Slave_SQL_Running都是Yes并且数据一致。使用 GTID 简化多从库配置如果主从库都启用了 GTID则配置从库时无需关心File和Position。只需在从库上执行CHANGE MASTER TO MASTER_HOST192.168.1.100, MASTER_PORT3306, MASTER_USERrepl_user, MASTER_PASSWORDYourPassword123!, MASTER_AUTO_POSITION 1;MySQL 会自动根据 GTID 集合来定位需要开始复制的点位这对于维护多个从库和故障切换非常方便。要启用 GTID需要在主从配置文件中添加[mysqld] gtid_mode ON enforce_gtid_consistency ON5. 常见问题与排查思路在主从同步搭建和维护过程中可能会遇到各种问题。下面是一个快速排查指南。问题现象可能原因排查步骤与解决方案Slave_IO_Running为Connecting或No网络不通、权限错误、主库信息配置错误。1.检查网络ping 主库IPtelnet 主库IP 3306。2.检查复制用户权限在主库用SHOW GRANTS FOR repl_user从库IP;确认。3.检查 CHANGE MASTER 配置确认MASTER_HOST,PORT,USER,PASSWORD,LOG_FILE,LOG_POS全部正确。4.查看错误日志SHOW SLAVE STATUS\G中的Last_IO_Error字段。Slave_SQL_Running为No同步的 SQL 语句在从库执行失败如主从数据不一致、从库有额外写入。1.查看具体错误SHOW SLAVE STATUS\G中的Last_SQL_Error字段。2.常见错误主键冲突、表不存在。可以尝试跳过这个错误谨慎使用STOP SLAVE; SET GLOBAL sql_slave_skip_counter 1; START SLAVE;。或者根据错误信息手动修复从库数据。3.检查read_only确保从库设置为只读防止应用误写。Seconds_Behind_Master值很大从库 SQL 线程应用日志的速度跟不上主库产生日志的速度。1.检查从库服务器负载CPU、内存、磁盘 I/O 是否过高。2.检查是否有长事务或大表操作在主库执行。3.考虑优化从库硬件或调整复制参数如slave_parallel_workers并行复制。4. 对于 ROW 格式的 binlog大事务会产生大量日志尽量避免。主从数据不一致初始数据未同步、从库被直接写入、复制错误被跳过等。1.定期校验使用pt-table-checksum等工具检查数据一致性。2.重新搭建从库最彻底的方法。停止从库从主库做全量备份并恢复到从库重新配置复制点位。3.严格保证从库只读。复制用户连接失败 (caching_sha2_password)MySQL 8.0 默认身份验证插件与旧版本客户端不兼容。1.主从均使用 8.0建议统一版本。2.修改复制用户插件ALTER USER repl_user% IDENTIFIED WITH mysql_native_password BY YourPassword123!;3. 或者在从库配置文件中指定默认认证插件default_authentication_pluginmysql_native_password通用排查命令-- 查看详细的复制错误信息 SHOW SLAVE STATUS\G -- 查看从库的进程列表看 SQL 线程在做什么 SHOW PROCESSLIST; -- 查看主库的 binlog 情况 SHOW MASTER STATUS; SHOW BINARY LOGS; -- 查看从库的中继日志情况 SHOW RELAYLOG EVENTS;6. 生产环境最佳实践与进阶建议将主从同步用于生产环境时除了基础配置还需要考虑更多可靠性、安全性和性能因素。6.1 配置与监控启用 GTID对于 MySQL 5.7 的生产环境强烈建议启用 GTID 复制。它简化了故障转移和主从切换的复杂性。监控关键指标除了Seconds_Behind_Master还应监控Slave_IO_Running、Slave_SQL_Running、Relay_Log_Space中继日志空间等。可以集成到 Zabbix、Prometheus 等监控系统中。定期检查数据一致性使用 Percona Toolkit 中的pt-table-checksum和pt-table-sync工具定期检查和修复主从数据不一致。设置合理的 binlog 过期时间避免磁盘被 binlog 占满。SET GLOBAL binlog_expire_logs_seconds 604800; -- 7天8.0版本参数 -- 5.7 版本使用 expire_logs_days -- SET GLOBAL expire_logs_days 7;6.2 高可用与读写分离读写分离实现在应用层使用中间件如 MyCat、ShardingSphere-Proxy或框架内插件如 Spring AOP来路由读写请求。写请求发往主库读请求发往一个或多个从库。自动故障转移一主多从架构下需要配合 MHAMaster High Availability、Orchestrator 等工具实现主库故障时的自动选主和切换否则需要人工干预。半同步复制如果对数据一致性要求极高可以考虑使用半同步复制Semisynchronous Replication。它要求主库在提交事务时至少有一个从库接收并确认了 binlog 事件主库才会向客户端返回成功。这在一定程度上保证了数据的强一致性但会略微增加写操作的延迟。-- 在主库安装插件并启用 INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; SET GLOBAL rpl_semi_sync_master_enabled 1; -- 在从库安装插件并启用 INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_slave_enabled 1;6.3 安全与权限最小权限原则复制用户只需REPLICATION SLAVE权限不要授予其他权限。限制连接IP创建复制用户时尽量指定从库的具体 IP而不是%。CREATE USER repl_user192.168.1.101 IDENTIFIED BY StrongPassword!; GRANT REPLICATION SLAVE ON *.* TO repl_user192.168.1.101;网络隔离主从库之间的网络应尽量保证低延迟和高带宽并处于安全的内网环境中。从库只读务必设置read_only 1和super_read_only 1MySQL 5.7.8防止运维误操作或程序 bug 导致从库被写入破坏数据一致性。6.4 性能优化并行复制MySQL 5.6 支持基于库的并行复制5.7 支持基于 LOGICAL_CLOCK 的并行复制能显著提升从库应用日志的速度。# 在从库配置文件中 slave_parallel_type LOGICAL_CLOCK # MySQL 5.7 推荐 slave_parallel_workers 4 # 根据CPU核心数调整通常设置为4-8使用 ROW 格式的 binlog虽然 ROW 格式的日志量可能比 STATEMENT 大但它能保证数据变更的绝对正确性特别是在涉及不确定函数如NOW(),RAND()或存储过程时。MIXED 格式是折中方案。硬件均衡从库的硬件配置尤其是磁盘 I/O 和 CPU不应明显低于主库否则容易造成复制延迟。通过以上步骤你不仅能够搭建起可用的 MySQL 主从复制环境更能理解其内部原理并掌握在生产环境中运维和优化这一架构的关键要点。主从复制是构建 MySQL 高可用、高性能集群的基石后续的读写分离、分库分表、高可用方案都离不开它的支持。在实际操作中多查看官方文档勤于测试积累的排错经验会让你在面对复杂数据库架构时更加从容。
MySQL主从同步原理与实战：从零搭建高可用数据库架构
在数据库高可用架构中单点故障和数据备份是必须解决的难题。当业务量增长读写请求都集中在单一数据库实例上时性能瓶颈和宕机风险会急剧增加。MySQL 主从同步Replication正是解决这些问题的核心方案之一它通过将主库Master的数据变更实时复制到一个或多个从库Slave实现了数据的冗余备份、读写分离和负载均衡。本文将系统性地拆解 MySQL 主从同步的原理并提供从零开始搭建“一主一从”和“一主多从”架构的完整实战教程包含详细的配置步骤、代码示例和排错指南无论是用于学习、测试还是生产环境部署都能直接复用。1. 主从同步核心原理与 MySQL 实现介绍1.1 什么是主从同步主从同步也称为数据库复制是一种数据冗余和高可用性技术。其核心思想是让一个数据库服务器主库的数据变更自动、异步地同步到另一个或多个数据库服务器从库。在 MySQL 中这个过程是单向的数据从主库流向从库。它主要解决以下问题数据备份与灾难恢复从库作为主库的实时热备当主库发生故障时可以快速切换到从库减少服务中断时间。读写分离主库负责处理写操作INSERT, UPDATE, DELETE和部分实时性要求高的读操作而从库可以承担绝大部分的读操作SELECT从而分散主库压力提升整体并发处理能力。数据分析与报表可以在从库上执行耗时的统计、报表查询或数据挖掘任务避免这些操作影响主库的在线事务处理性能。1.2 MySQL 主从同步的工作原理MySQL 默认采用基于二进制日志Binary Log的异步复制机制。理解其流程是后续配置和排错的基础。整个复制过程可以概括为以下三个核心步骤第一步主库记录二进制日志Binary Log当主库上发生数据变更DML、DDL时这些变更会以“事件Event”的形式按照事务提交的顺序被顺序写入本地的二进制日志文件中。二进制日志是复制的数据源。第二步从库拉取日志I/O Thread从库会启动一个I/O 线程连接到主库。主库上有一个特殊的线程Binlog Dump Thread负责响应从库的请求。I/O 线程会读取主库的二进制日志事件并将其写入从库本地的中继日志Relay Log文件中。第三步从库重放日志SQL Thread从库会启动一个SQL 线程读取本地的中继日志并解析、执行其中记录的 SQL 事件从而在从库上重现主库的数据变更最终达到数据一致的状态。简单流程图主库 (Master) | | 1. 数据变更 - 写入 Binlog v Binlog Dump Thread (主库线程) | | 2. 通过网络传输 Binlog Event v 从库 I/O Thread (从库线程) - 写入 Relay Log | v 从库 SQL Thread (从库线程) - 执行 Relay Log 中的 SQL - 从库数据更新关键概念解析异步复制主库提交事务后无需等待从库应用完成即可向客户端返回成功。这是 MySQL 默认方式性能好但存在极小概率的数据延迟或丢失风险。二进制日志 (binlog)记录所有更改数据库数据的语句用于复制和点播恢复。格式有 STATEMENT基于SQL语句、ROW基于行数据变更、MIXED混合模式。中继日志 (relay log)从库的 I/O 线程从主库拉取的二进制日志的副本格式与 binlog 相同供 SQL 线程消费。GTID (Global Transaction Identifier)MySQL 5.6 引入的全局事务ID为每个提交的事务生成一个唯一标识。使用 GTID 可以简化复制故障切换和主从维护无需再依赖传统的binlog file和position。2. 环境准备与版本说明在开始实战前需要准备好实验环境。为了模拟真实场景我们将在同一台机器上部署多个 MySQL 实例通过不同端口区分。生产环境通常部署在不同服务器上。环境要求操作系统本文以 CentOS 7.x / Rocky Linux 8 为例命令也适用于大多数 Linux 发行版。Windows 环境思路类似但配置文件和启动方式不同。MySQL 版本强烈建议使用 5.7 或 8.0 版本。本文示例基于 MySQL 8.0.36但核心配置对 5.7 同样适用。请注意8.0 在默认身份验证插件caching_sha2_password和部分参数上与 5.7 有差异文中会特别说明。服务器规划假设我们有一台服务器IP 为192.168.1.100。一主一从主库端口3306从库端口3307。一主多从主库端口3306从库1端口3307从库2端口3308。重要前提确保服务器之间网络互通如果是单机多实例则本地环回地址127.0.0.1互通。关闭防火墙或开放对应的 MySQL 端口如 3306, 3307, 3308。# 临时关闭防火墙仅用于实验生产环境请配置规则 systemctl stop firewalld systemctl disable firewalld # 或者开放端口 # firewall-cmd --permanent --add-port3306/tcp # firewall-cmd --reload安装 MySQL。如果尚未安装可以参考以下简易步骤# 以 Rocky Linux 8 安装 MySQL 8.0 为例 sudo dnf install mysql:8.0 -y # 启动并设置开机自启 sudo systemctl start mysqld sudo systemctl enable mysqld # 运行安全初始化脚本设置 root 密码等 sudo mysql_secure_installation3. 搭建一主一从同步架构这是最基础也是最常见的复制结构。我们将一步步完成配置。3.1 主库Master配置步骤 1编辑主库配置文件MySQL 的主配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf也可能在/etc/my.cnf.d/目录下。我们需要修改或添加以下配置。# 文件路径/etc/my.cnf (在 [mysqld] 段落下添加) [mysqld] # 服务器唯一ID主从必须不同 server-id 1 # 启用二进制日志并指定日志文件前缀 log-bin mysql-bin # 设置二进制日志格式推荐使用 ROW数据一致性更好 binlog_format ROW # 可选指定需要复制的数据库多个则写多行不配置则默认复制所有库 # binlog-do-db mydb # 可选指定不需要复制的数据库 # binlog-ignore-db mysql # binlog-ignore-db information_schema # binlog-ignore-db performance_schema # binlog-ignore-db sys # 从 MySQL 8.0.20 开始默认开启 binlog 校验和建议保持开启 binlog_checksum CRC32步骤 2重启主库 MySQL 服务sudo systemctl restart mysqld # 检查服务状态 sudo systemctl status mysqld步骤 3登录主库创建用于复制的用户并授权复制用户需要REPLICATION SLAVE权限。-- 登录 MySQL mysql -u root -p -- 创建复制用户repl_user 是用户名YourPassword123! 是密码请修改。 -- % 表示允许从任何主机连接生产环境建议指定从库IP。 CREATE USER repl_user% IDENTIFIED BY YourPassword123!; -- MySQL 8.0 默认使用 caching_sha2_password如果从库是旧版本客户端可能有问题可改用 mysql_native_password -- CREATE USER repl_user% IDENTIFIED WITH mysql_native_password BY YourPassword123!; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO repl_user%; -- 刷新权限 FLUSH PRIVILEGES;步骤 4查看主库状态记录关键信息执行以下命令记录File和Position的值在配置从库时会用到。SHOW MASTER STATUS\G输出示例*************************** 1. row *************************** File: mysql-bin.000001 Position: 157 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set:请务必记下File: mysql-bin.000001和Position: 157。3.2 从库Slave配置步骤 1准备从库数据可选但推荐为了保证主从数据起点一致最好先将主库的数据全量导出并导入到从库。如果从库是全新的可跳过此步。# 在主库服务器上执行导出 mysqldump -u root -p --all-databases --master-data2 --single-transaction --routines --events master_full_backup.sql # 将备份文件拷贝到从库服务器然后导入 mysql -u root -p master_full_backup.sql步骤 2编辑从库配置文件从库需要不同的server-id并且通常不需要开启log-bin除非它本身也要作为其他从库的主库即级联复制。# 文件路径/etc/my.cnf (在 [mysqld] 段落下添加) [mysqld] # 服务器唯一ID必须与主库和其他从库不同 server-id 2 # 可选如果需要从库也记录二进制日志用于备份或级联则开启 # log-bin mysql-bin # 可选设置中继日志文件前缀 relay-log mysql-relay-bin # 可选设置中继日志索引文件 relay-log-index mysql-relay-bin.index # 设置只读模式防止从库被意外写入影响同步 read_only 1 # 可选对于超级用户如root是否也只读1为是 super_read_only 1步骤 3重启从库 MySQL 服务# 注意如果是单机多实例需要启动对应端口的实例这里假设已配置好3307实例并重启 sudo systemctl restart mysqld3307 # 具体服务名根据你的多实例配置而定 # 更通用的方法是指定配置文件启动本文假设你已配置好多实例。步骤 4登录从库配置复制链路使用第 3.1 步中记录的File和Position信息。-- 登录从库 (端口3307) mysql -u root -p -P 3307 -- 停止从库复制线程如果是新库可能已经在停止状态 STOP SLAVE; -- 配置主库连接信息 CHANGE MASTER TO MASTER_HOST192.168.1.100, -- 主库IP单机多实例则为 127.0.0.1 MASTER_PORT3306, -- 主库端口 MASTER_USERrepl_user, -- 第3.1步创建的复制用户 MASTER_PASSWORDYourPassword123!, MASTER_LOG_FILEmysql-bin.000001, -- 第3.1步记录的 File MASTER_LOG_POS157; -- 第3.1步记录的 Position -- 如果是基于 GTID 的复制配置更简单推荐 MySQL 5.7 使用 -- CHANGE MASTER TO -- MASTER_HOST192.168.1.100, -- MASTER_PORT3306, -- MASTER_USERrepl_user, -- MASTER_PASSWORDYourPassword123!, -- MASTER_AUTO_POSITION 1;步骤 5启动从库复制并检查状态-- 启动复制 START SLAVE; -- 查看从库复制状态 SHOW SLAVE STATUS\G3.3 验证同步状态与测试关键状态检查执行SHOW SLAVE STATUS\G后重点关注以下字段Slave_IO_Running:必须为Yes。表示 I/O 线程正常运行正在从主库读取日志。Slave_SQL_Running:必须为Yes。表示 SQL 线程正常运行正在重放中继日志。Seconds_Behind_Master:应为0或一个很小的数字。表示从库落后主库的秒数0代表完全同步。Last_IO_Error/Last_SQL_Error:应为空。如果有错误信息说明同步出现问题。测试数据同步在主库3306上创建一个数据库和表并插入数据。CREATE DATABASE IF NOT EXISTS test_repl; USE test_repl; CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO user (name) VALUES (Alice), (Bob);在从库3307上查询数据。USE test_repl; SELECT * FROM user;如果能看到Alice和Bob两条记录说明主从同步成功4. 扩展至一主多从同步架构一主多从架构是一主一从的自然延伸配置流程几乎相同只是需要重复配置多个从库。每个从库都需要唯一的server-id和独立的复制链路配置。架构规划主库 (Master):192.168.1.100:3306server-id1从库1 (Slave1):192.168.1.100:3307server-id2从库2 (Slave2):192.168.1.100:3308server-id3配置步骤主库配置与3.1 节完全相同无需更改。从库1配置与3.2 节完全相同server-id2,port3307。从库2配置按照3.2 节的步骤操作。修改从库配置文件设置server-id 3和对应的端口如port3308。启动 MySQL 实例端口 3308。登录从库2执行CHANGE MASTER TO ...命令注意MASTER_PORT仍是3306指向主库MASTER_LOG_FILE和MASTER_LOG_POS需要重新从主库获取当前值因为从库1启动后主库的Position可能已经变化。-- 在主库上重新执行 SHOW MASTER STATUS\G -- 记下新的 File 和 Position例如 mysql-bin.000001, 831在从库2上配置时使用这个新的位置信息。CHANGE MASTER TO MASTER_HOST192.168.1.100, MASTER_PORT3306, MASTER_USERrepl_user, MASTER_PASSWORDYourPassword123!, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS831;启动从库2的复制线程START SLAVE;验证分别在两个从库上执行SHOW SLAVE STATUS\G和查询测试数据确保Slave_IO_Running和Slave_SQL_Running都是Yes并且数据一致。使用 GTID 简化多从库配置如果主从库都启用了 GTID则配置从库时无需关心File和Position。只需在从库上执行CHANGE MASTER TO MASTER_HOST192.168.1.100, MASTER_PORT3306, MASTER_USERrepl_user, MASTER_PASSWORDYourPassword123!, MASTER_AUTO_POSITION 1;MySQL 会自动根据 GTID 集合来定位需要开始复制的点位这对于维护多个从库和故障切换非常方便。要启用 GTID需要在主从配置文件中添加[mysqld] gtid_mode ON enforce_gtid_consistency ON5. 常见问题与排查思路在主从同步搭建和维护过程中可能会遇到各种问题。下面是一个快速排查指南。问题现象可能原因排查步骤与解决方案Slave_IO_Running为Connecting或No网络不通、权限错误、主库信息配置错误。1.检查网络ping 主库IPtelnet 主库IP 3306。2.检查复制用户权限在主库用SHOW GRANTS FOR repl_user从库IP;确认。3.检查 CHANGE MASTER 配置确认MASTER_HOST,PORT,USER,PASSWORD,LOG_FILE,LOG_POS全部正确。4.查看错误日志SHOW SLAVE STATUS\G中的Last_IO_Error字段。Slave_SQL_Running为No同步的 SQL 语句在从库执行失败如主从数据不一致、从库有额外写入。1.查看具体错误SHOW SLAVE STATUS\G中的Last_SQL_Error字段。2.常见错误主键冲突、表不存在。可以尝试跳过这个错误谨慎使用STOP SLAVE; SET GLOBAL sql_slave_skip_counter 1; START SLAVE;。或者根据错误信息手动修复从库数据。3.检查read_only确保从库设置为只读防止应用误写。Seconds_Behind_Master值很大从库 SQL 线程应用日志的速度跟不上主库产生日志的速度。1.检查从库服务器负载CPU、内存、磁盘 I/O 是否过高。2.检查是否有长事务或大表操作在主库执行。3.考虑优化从库硬件或调整复制参数如slave_parallel_workers并行复制。4. 对于 ROW 格式的 binlog大事务会产生大量日志尽量避免。主从数据不一致初始数据未同步、从库被直接写入、复制错误被跳过等。1.定期校验使用pt-table-checksum等工具检查数据一致性。2.重新搭建从库最彻底的方法。停止从库从主库做全量备份并恢复到从库重新配置复制点位。3.严格保证从库只读。复制用户连接失败 (caching_sha2_password)MySQL 8.0 默认身份验证插件与旧版本客户端不兼容。1.主从均使用 8.0建议统一版本。2.修改复制用户插件ALTER USER repl_user% IDENTIFIED WITH mysql_native_password BY YourPassword123!;3. 或者在从库配置文件中指定默认认证插件default_authentication_pluginmysql_native_password通用排查命令-- 查看详细的复制错误信息 SHOW SLAVE STATUS\G -- 查看从库的进程列表看 SQL 线程在做什么 SHOW PROCESSLIST; -- 查看主库的 binlog 情况 SHOW MASTER STATUS; SHOW BINARY LOGS; -- 查看从库的中继日志情况 SHOW RELAYLOG EVENTS;6. 生产环境最佳实践与进阶建议将主从同步用于生产环境时除了基础配置还需要考虑更多可靠性、安全性和性能因素。6.1 配置与监控启用 GTID对于 MySQL 5.7 的生产环境强烈建议启用 GTID 复制。它简化了故障转移和主从切换的复杂性。监控关键指标除了Seconds_Behind_Master还应监控Slave_IO_Running、Slave_SQL_Running、Relay_Log_Space中继日志空间等。可以集成到 Zabbix、Prometheus 等监控系统中。定期检查数据一致性使用 Percona Toolkit 中的pt-table-checksum和pt-table-sync工具定期检查和修复主从数据不一致。设置合理的 binlog 过期时间避免磁盘被 binlog 占满。SET GLOBAL binlog_expire_logs_seconds 604800; -- 7天8.0版本参数 -- 5.7 版本使用 expire_logs_days -- SET GLOBAL expire_logs_days 7;6.2 高可用与读写分离读写分离实现在应用层使用中间件如 MyCat、ShardingSphere-Proxy或框架内插件如 Spring AOP来路由读写请求。写请求发往主库读请求发往一个或多个从库。自动故障转移一主多从架构下需要配合 MHAMaster High Availability、Orchestrator 等工具实现主库故障时的自动选主和切换否则需要人工干预。半同步复制如果对数据一致性要求极高可以考虑使用半同步复制Semisynchronous Replication。它要求主库在提交事务时至少有一个从库接收并确认了 binlog 事件主库才会向客户端返回成功。这在一定程度上保证了数据的强一致性但会略微增加写操作的延迟。-- 在主库安装插件并启用 INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; SET GLOBAL rpl_semi_sync_master_enabled 1; -- 在从库安装插件并启用 INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_slave_enabled 1;6.3 安全与权限最小权限原则复制用户只需REPLICATION SLAVE权限不要授予其他权限。限制连接IP创建复制用户时尽量指定从库的具体 IP而不是%。CREATE USER repl_user192.168.1.101 IDENTIFIED BY StrongPassword!; GRANT REPLICATION SLAVE ON *.* TO repl_user192.168.1.101;网络隔离主从库之间的网络应尽量保证低延迟和高带宽并处于安全的内网环境中。从库只读务必设置read_only 1和super_read_only 1MySQL 5.7.8防止运维误操作或程序 bug 导致从库被写入破坏数据一致性。6.4 性能优化并行复制MySQL 5.6 支持基于库的并行复制5.7 支持基于 LOGICAL_CLOCK 的并行复制能显著提升从库应用日志的速度。# 在从库配置文件中 slave_parallel_type LOGICAL_CLOCK # MySQL 5.7 推荐 slave_parallel_workers 4 # 根据CPU核心数调整通常设置为4-8使用 ROW 格式的 binlog虽然 ROW 格式的日志量可能比 STATEMENT 大但它能保证数据变更的绝对正确性特别是在涉及不确定函数如NOW(),RAND()或存储过程时。MIXED 格式是折中方案。硬件均衡从库的硬件配置尤其是磁盘 I/O 和 CPU不应明显低于主库否则容易造成复制延迟。通过以上步骤你不仅能够搭建起可用的 MySQL 主从复制环境更能理解其内部原理并掌握在生产环境中运维和优化这一架构的关键要点。主从复制是构建 MySQL 高可用、高性能集群的基石后续的读写分离、分库分表、高可用方案都离不开它的支持。在实际操作中多查看官方文档勤于测试积累的排错经验会让你在面对复杂数据库架构时更加从容。
