MySQL主从复制是MySQL最常用也是最重要的高可用和高性能方案之一。
对于后端开发和运维来说MySQL主从复制已经成为了必备技能。无论是数据备份还是读写分离还是高可用还是数据分析MySQL主从复制都能发挥重要作用。可以说不会配置MySQL主从复制的后端和运维在2015年已经开始落伍了。
我自己从2013年开始接触MySQL主从复制到现在已经用了两年多了。从最初的只会简单的主从配置到后来的一主多从、双主、级联复制、半同步复制、GTID复制一路走来踩了不少坑也总结了不少经验。我见过太多的团队因为没有主从复制数据库挂了数据丢了服务停了几个小时甚至几天;也见过太多的团队因为主从复制配置不当导致主从不一致数据错乱引发各种奇怪的bug。
2015年MySQL主从复制已经成为了互联网公司的标准配置。无论是淘宝、腾讯、百度还是中小创业公司几乎都在使用MySQL主从复制。MySQL 5.6在2013年发布带来了GTID(全局事务ID)、并行复制、半同步复制增强等重要特性让主从复制更加可靠更加高效。MySQL 5.7在2015年发布了RC版本进一步增强了主从复制的功能和性能。
今天分享MySQL主从复制从原理到配置到常见问题到最佳实践全方位讲解MySQL主从复制的使用方法和技巧帮你掌握这个重要的数据库技术。
什么是MySQL主从复制
MySQL主从复制是指将一台MySQL服务器(主服务器Master)的数据复制到另一台或多台MySQL服务器(从服务器Slave)的过程。
主从复制是异步的。主服务器把数据变更记录到二进制日志(Binary Log)中;从服务器读取主服务器的二进制日志然后在自己身上重放这些日志从而实现数据的同步。
主从复制的基本过程:
- 主服务器把数据变更记录到二进制日志(Binary Log)中。
- 从服务器的IO线程连接到主服务器请求读取二进制日志。
- 主服务器把二进制日志发送给从服务器。
- 从服务器的IO线程把接收到的二进制日志写入到中继日志(Relay Log)中。
- 从服务器的SQL线程读取中继日志重放其中的事件从而实现数据的同步。
主从复制的特点:
- 异步复制:主服务器不需要等待从服务器确认接收就可以继续处理请求。性能好但可能会有数据延迟。
- 单向复制:默认情况下数据只能从主服务器复制到从服务器不能反向复制。
- 支持多从:一个主服务器可以有多个从服务器。
- 支持级联:从服务器也可以作为其他从服务器的主服务器形成级联复制。
为什么需要主从复制
MySQL主从复制主要有以下几个用途:
1. 数据备份
主从复制可以实现数据的实时备份。主服务器的数据会实时复制到从服务器。如果主服务器出现故障数据损坏可以从从服务器恢复数据。
相比传统的定时备份(如每天mysqldump),主从复制的优势是实时性好数据丢失少。定时备份最多只能恢复到上次备份的时间点可能会丢失一天的数据;而主从复制数据延迟通常只有几秒钟甚至更少。
2. 读写分离
主从复制可以实现读写分离。写操作在主服务器上执行;读操作在从服务器上执行。这样可以减轻主服务器的读压力提高整个系统的并发能力。
对于大多数Web应用来说读操作远多于写操作(通常读:写 = 9:1甚至更高)。通过读写分离把读操作分散到多个从服务器上可以大大提高系统的读并发能力。
读写分离通常需要在应用层或者通过中间件(如MySQL Proxy、Atlas、MyCat等),来实现。应用层需要判断SQL是读还是写然后路由到对应的服务器。
3. 高可用
主从复制可以实现MySQL的高可用。如果主服务器出现故障宕机了可以把一个从服务器提升为新的主服务器继续提供服务。这样可以大大减少服务的停机时间。
高可用方案通常需要配合心跳检测、自动故障转移等机制。常见的高可用方案,有:
- MHA(Master High Availability):Perl脚本实现MySQL主从的自动故障转移。
- Keepalived + MySQL:通过Keepalived实现虚拟IP的漂移配合主从复制实现高可用。
- Heartbeat + MySQL:类似Keepalived。
- MySQL Fabric:MySQL官方的高可用和分片方案。
4. 数据分析
主从复制可以把从服务器用于数据分析、报表生成、数据挖掘等耗时的读操作。这样不会影响主服务器的在线业务。
比如每天需要生成各种统计报表需要执行大量的复杂查询。如果在主服务器上执行可能会影响在线业务的性能。而在从服务器上执行就不会影响主服务器。
5. 远程分布
主从复制可以把数据复制到远程的从服务器。这样可以让远程的用户访问就近的从服务器提高访问速度。
比如公司在北京有主服务器在上海、广州有从服务器。上海的用户访问上海的从服务器;广州的用户访问广州的从服务器。这样可以减少网络延迟提高用户体验。
MySQL主从复制的原理
MySQL主从复制基于二进制日志(Binary Log)。下面详细讲解主从复制的原理。
1. 二进制日志(Binary Log)
二进制日志是MySQL记录数据变更的日志。它记录了所有可能导致数据变更的SQL语句(或行变更事件),包括INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等。
二进制日志有两种格式:
- STATEMENT格式:记录原始的SQL语句。优点是日志量小;缺点是某些函数(如UUID()、NOW()),在主从上执行结果可能不一致导致主从不一致。
- ROW格式:记录行的变更。优点是不会有函数不一致的问题主从一致性好;缺点是日志量大(特别是批量UPDATE时)。
- MIXED格式:混合格式。默认使用STATEMENT格式;当遇到可能导致不一致的函数时自动切换到ROW格式。这是推荐的格式。
二进制日志相关的配置参数:
log_bin:开启二进制日志指定日志文件名前缀。binlog_format:二进制日志格式STATEMENT/ROW/MIXED。expirelogsdays:二进制日志自动删除的天数。maxbinlogsize:单个二进制日志文件的最大大小。binlogdodb:只记录指定数据库的二进制日志。binlogignoredb:忽略指定数据库的二进制日志。
2. 中继日志(Relay Log)
中继日志是从服务器用来存储从主服务器接收到的二进制日志的。从服务器的IO线程把从主服务器接收到的二进制日志写入到中继日志中;然后SQL线程从中继日志中读取事件重放。
中继日志的格式和二进制日志一样。相关的配置参数:
relay_log:指定中继日志文件名前缀。relayloginfo_file:指定中继日志信息文件的路径。relaylogpurge:是否自动删除不再需要的中继日志。
3. 复制线程
MySQL主从复制涉及到三个线程:
- 主服务器的Binlog Dump线程:当从服务器连接到主服务器时主服务器会创建一个Binlog Dump线程负责把二进制日志发送给从服务器。
- 从服务器的IO线程:负责连接到主服务器请求二进制日志然后把接收到的二进制日志写入到中继日志中。
- 从服务器的SQL线程:负责读取中继日志重放其中的事件从而实现数据的同步。
可以通过,SHOW PROCESSLIST;,查看这些线程的状态。
4. 复制的基本过程
主从复制的完整过程:
- 主服务器执行数据变更操作记录到二进制日志中。
- 从服务器的IO线程连接到主服务器请求从指定位置开始的二进制日志。
- 主服务器创建Binlog Dump线程读取二进制日志发送给从服务器。
- 从服务器的IO线程接收到二进制日志写入到中继日志中。
- 从服务器的SQL线程读取中继日志重放事件数据变更应用到从服务器的数据库中。
- 重复以上过程实现持续的数据同步。
5. 复制的类型
MySQL主从复制有以下几种类型:
- 异步复制(Asynchronous Replication):默认方式。主服务器执行完事务立即返回给客户端不需要等待从服务器确认接收。性能好但主服务器宕机时可能会丢失部分数据。
- 半同步复制(Semi-synchronous Replication):主服务器执行完事务后需要等待至少一个从服务器确认接收到了事务(写入中继日志),才返回给客户端。这样可以保证至少有一个从服务器有完整的数据。性能比异步复制稍差但数据安全性更高。MySQL 5.5开始支持半同步复制;MySQL 5.6增强了半同步复制。
- 同步复制(Synchronous Replication):主服务器执行完事务后需要等待所有从服务器都执行完事务才返回给客户端。性能最差但数据一致性最好。MySQL原生不支持同步复制但可以通过Galera Cluster、Percona XtraDB Cluster等实现同步复制。
6. GTID复制
GTID(Global Transaction Identifier全局事务ID),是MySQL 5.6引入的新特性。它为每个事务分配一个全局唯一的ID。
GTID的格式:sourceid:transactionid,其中sourceid通常是主服务器的serveruuid;transaction_id是事务序号从1开始递增。
GTID复制的优势:
- 故障转移更简单:从服务器不需要知道主服务器的二进制日志文件名和位置只需要知道自己已经执行到哪个GTID就可以自动从新的主服务器同步数据。
- 数据一致性更好:GTID保证每个事务只执行一次不会重复执行也不会遗漏。
- 复制拓扑更灵活:可以更方便地搭建复杂的复制拓扑如双主、多源复制等。
GTID复制相关的配置参数:
gtid_mode:开启GTID模式ON/OFF。enforcegtidconsistency:强制GTID一致性ON/OFF。
MySQL主从复制的配置
下面以MySQL 5.6为例讲解MySQL主从复制的配置步骤。
1. 环境准备
假设我们有两台服务器:
- 主服务器(Master):IP 192.168.1.101,hostname master
- 从服务器(Slave):IP 192.168.1.102,hostname slave
两台服务器都安装了MySQL 5.6并且可以互相访问(防火墙开放3306端口)。
2. 配置主服务器
编辑主服务器的MySQL配置文件my.cnf(或my.ini):
[mysqld]
# 服务器ID,必须唯一
server-id = 1
# 开启二进制日志
log-bin = mysql-bin
# 二进制日志格式,推荐MIXED
binlog_format = MIXED
# 二进制日志自动删除天数
expire_logs_days = 7
# 单个二进制日志文件最大大小
max_binlog_size = 100M
# 只复制指定数据库(可选)
# binlog_do_db = dbname
# 忽略指定数据库(可选)
# binlog_ignore_db = mysql配置完成后重启MySQL:
service mysql restart然后登录MySQL创建用于复制的用户:
-- 创建复制用户
CREATE USER 'repl'@'192.168.1.102' IDENTIFIED BY 'repl_password';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.102';
-- 刷新权限
FLUSH PRIVILEGES;然后查看主服务器的二进制日志状态:
SHOW MASTER STATUS;记录下File(二进制日志文件名)和Position(位置),后面配置从服务器时需要用到。
3. 配置从服务器
编辑从服务器的MySQL配置文件my.cnf:
[mysqld]
# 服务器ID,必须唯一,不能和主服务器一样
server-id = 2
# 开启中继日志(可选,默认会开启)
relay-log = relay-bin
# 只读(可选,建议开启,防止误写)
read_only = 1
# 中继日志信息文件
relay-log-info-file = relay-log.info注意:readonly = 1,只对普通用户生效;SUPER权限的用户仍然可以写。如果想完全禁止写入可以使用superread_only = 1(MySQL 5.7+支持)。
配置完成后重启MySQL:
service mysql restart然后登录MySQL配置主从复制:
-- 配置主服务器信息
CHANGE MASTER TO
MASTER_HOST='192.168.1.101',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
-- 启动复制
START SLAVE;其中MASTERLOGFILE和MASTERLOGPOS就是之前在主服务器上,SHOW MASTER STATUS;,得到的File和Position。
然后查看从服务器的复制状态:
SHOW SLAVE STATUS\G重点查看以下几个字段:
SlaveIORunning:IO线程是否运行应该是Yes。SlaveSQLRunning:SQL线程是否运行应该是Yes。SecondsBehindMaster:从服务器落后主服务器的秒数。0表示同步正常。LastIOError:IO线程的最后错误。LastSQLError:SQL线程的最后错误。
如果SlaveIORunning和SlaveSQLRunning都是Yes并且没有错误那么主从复制配置成功了!
4. 测试主从复制
可以在主服务器上创建一个数据库或者表插入一些数据然后查看从服务器是否同步了。
-- 在主服务器上执行
CREATE DATABASE test_repl;
USE test_repl;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
-- 在从服务器上执行,查看是否同步
SHOW DATABASES;
USE test_repl;
SELECT * FROM users;如果从服务器上能看到主服务器上创建的数据库、表、数据那么主从复制工作正常!
5. 已有数据的主从配置
如果主服务器已经有大量数据了那么需要先把主服务器的数据导入到从服务器然后再配置主从复制。
步骤:
- 在主服务器上锁表防止数据变更:
FLUSH TABLES WITH READ LOCK;- 查看主服务器的二进制日志状态:
SHOW MASTER STATUS;记录下File和Position。
- 在另一个终端导出主服务器的数据:
mysqldump -u root -p --all-databases --master-data=2 --single-transaction --routines --triggers > all_databases.sql- 解锁主服务器的表:
UNLOCK TABLES;- 把导出的数据文件复制到从服务器然后导入:
mysql -u root -p < all_databases.sql- 在从服务器上配置主从复制(CHANGE MASTER TO ...),然后START SLAVE。
这样就可以在已有数据的情况下配置主从复制了。
GTID主从复制的配置
MySQL 5.6+,支持GTID复制。GTID复制配置更简单故障转移更方便。
1. 配置主服务器
编辑主服务器的my.cnf:
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = MIXED
# 开启GTID
gtid_mode = ON
enforce_gtid_consistency = ON
# 推荐开启,崩溃后,自动恢复
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON重启MySQL。
2. 配置从服务器
编辑从服务器的my.cnf:
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
# 开启GTID
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON重启MySQL。
3. 配置复制
在从服务器上执行:
CHANGE MASTER TO
MASTER_HOST='192.168.1.101',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_AUTO_POSITION = 1;
START SLAVE;注意:GTID复制不需要指定MASTERLOGFILE和MASTERLOGPOS只需要设置MASTERAUTOPOSITION = 1,从服务器会自动根据GTID找到需要同步的位置。
然后,SHOW SLAVE STATUS\G,查看复制状态。
半同步复制的配置
半同步复制可以提高数据的安全性。MySQL 5.5+,支持半同步复制通过插件实现。
1. 安装插件
在主服务器和从服务器上都安装半同步复制插件:
-- 主服务器,安装主插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 从服务器,安装从插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';2. 开启半同步复制
-- 主服务器,开启半同步
SET GLOBAL rpl_semi_sync_master_enabled = 1;
-- 从服务器,开启半同步
SET GLOBAL rpl_semi_sync_slave_enabled = 1;如果想永久生效可以在my.cnf中配置:
# 主服务器
plugin-load = rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
# 从服务器
plugin-load = rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled = 1rplsemisyncmastertimeout:主服务器等待从服务器确认的超时时间单位毫秒。默认10000(10秒)。如果超时主服务器会自动切换回异步复制。
3. 重启从服务器的IO线程
配置完成后需要重启从服务器的IO线程:
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;4. 查看半同步状态
-- 主服务器
SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
-- 从服务器
SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';如果值是ON说明半同步复制已经开启。
常见问题与解决方法
1. 主从延迟(SecondsBehindMaster很大)
原因:
- 主服务器写操作太频繁从服务器SQL线程单线程重放慢(MySQL 5.6之前SQL线程是单线程的;MySQL 5.6+,支持并行复制)。
- 从服务器性能差硬件不如主服务器。
- 从服务器有慢查询阻塞了SQL线程。
- 网络延迟大IO线程接收慢。
- 大事务主服务器执行了一个很大的事务从服务器重放需要很长时间。
解决方法:
- 升级从服务器的硬件提高性能。
- 使用MySQL 5.6+的并行复制(
slaveparallelworkers)。 - 优化慢查询减少大事务。
- 优化网络减少延迟。
- 使用半同步复制减少数据不一致的风险。
2. 主从不一致
原因:
- 从服务器被误写了数据(没有开启read_only)。
- 二进制日志格式是STATEMENT使用了不确定的函数(如UUID()、NOW()、RAND())。
- 主服务器崩溃导致部分事务没有同步到从服务器。
- 复制过程中出现错误SQL线程停止了。
解决方法:
- 开启从服务器的read_only防止误写。
- 使用MIXED或ROW格式的二进制日志。
- 使用半同步复制提高数据安全性。
- 如果已经主从不一致可以重新配置主从复制(重新导出导入数据)。
- 使用pt-table-checksum和pt-table-sync(Percona Toolkit),检查和修复主从不一致。
3. IO线程错误
常见错误:
error connecting to master:无法连接到主服务器。可能是网络不通防火墙端口没开用户名密码错误权限不够。Got fatal error 1236 from master when reading data from binary log:读取二进制日志时出错。可能是主服务器的二进制日志被删除了或者MASTERLOGFILE/MASTERLOGPOS指定错误。
解决方法:
- 检查网络防火墙端口。
- 检查复制用户的用户名密码权限。
- 检查主服务器的二进制日志是否存在。如果被删除了需要重新配置主从复制。
4. SQL线程错误
常见错误:
Error 'Table 'xxx' doesn't exist':表不存在。可能是从服务器上表被误删了或者主从不一致。Error 'Duplicate entry 'xxx' for key 'PRIMARY'':主键冲突。可能是从服务器上已经有了这条数据或者主从不一致。Error 'Can't create database 'xxx'; database exists':数据库已存在。
解决方法:
- 如果是偶尔的错误可以跳过这个事务继续复制:
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;- 如果错误很多主从不一致建议重新配置主从复制。
- 使用GTID复制可以更方便地跳过错误事务:
STOP SLAVE;
SET GTID_NEXT='xxx:xxx';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;5. 主服务器宕机,如何切换
如果主服务器宕机了需要把一个从服务器提升为新的主服务器。
步骤:
- 确保所有从服务器都已经同步完了中继日志。
SHOW SLAVE STATUS\G确保,RelayMasterLogFile和ExecMasterLogPos,都一致了。
- 在要提升为新主的从服务器上停止复制:
STOP SLAVE;
RESET MASTER;- 修改新主服务器的配置开启二进制日志关闭read_only。
- 在其他从服务器上指向新的主服务器:
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='新主IP', ...;
START SLAVE;- 修改应用程序的数据库连接指向新的主服务器。
如果使用了MHA、Keepalived等高可用方案这些步骤会自动完成。
最佳实践
1. 配置方面
- server-id必须唯一:所有MySQL服务器的server-id都不能相同。
- 使用MIXED或ROW格式:不要使用STATEMENT格式避免主从不一致。
- 开启readonly:从服务器开启readonly防止误写。
- 设置expirelogsdays:自动删除旧的二进制日志避免磁盘空间被占满。
- 使用GTID:MySQL 5.6+,推荐使用GTID复制配置更简单故障转移更方便。
- 使用半同步复制:对数据安全性要求高的场景使用半同步复制。
- 使用并行复制:MySQL 5.6+,开启并行复制(
slaveparallelworkers),减少主从延迟。
2. 监控方面
- 监控复制状态:定期检查,
SHOW SLAVE STATUS,确保IO线程和SQL线程都在运行没有错误。 - 监控主从延迟:监控,
SecondsBehindMaster,如果延迟过大及时排查原因。 - 监控磁盘空间:监控二进制日志和中继日志占用的磁盘空间避免磁盘满了。
- 使用监控工具:使用Nagios、Zabbix、Prometheus等监控工具自动监控告警。
3. 备份方面
- 定期备份:即使有主从复制也要定期全量备份。主从复制不能替代备份。
- 从服务器备份:可以在从服务器上做备份不影响主服务器。
- 测试备份:定期测试备份是否可以正常恢复。
4. 安全方面
- 复制用户最小权限:复制用户只授予REPLICATION SLAVE权限不要授予更多权限。
- 复制用户限制IP:复制用户只允许从指定的IP登录。
- 使用SSL:如果主从服务器在不同的机房通过公网传输建议使用SSL加密防止数据被窃听。
- 防火墙:只允许从服务器的IP访问主服务器的3306端口。
5. 性能方面
- 从服务器硬件不要太差:从服务器的硬件应该和主服务器相当或者更好避免因为性能差导致主从延迟。
- 优化SQL:优化慢查询减少大事务避免阻塞SQL线程。
- 使用并行复制:MySQL 5.6+,开启并行复制提高SQL线程的重放速度。
- 读写分离:通过读写分离减轻主服务器的读压力。
学习资源
推荐书籍
- 《高性能MySQL》(High Performance MySQL):MySQL领域的经典书籍第3版详细讲解了MySQL的性能优化包括主从复制。
- 《MySQL技术内幕:InnoDB存储引擎》:姜承尧写的深入讲解了InnoDB存储引擎的原理。
- 《MySQL排错指南》:专门讲解MySQL的故障排查。
在线文档
- MySQL官方文档:https://dev.mysql.com/doc/ ,最权威、最全面的MySQL文档。
- MySQL主从复制文档:https://dev.mysql.com/doc/refman/5.6/en/replication.html
- MySQL GTID文档:https://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html
- MySQL半同步复制文档:https://dev.mysql.com/doc/refman/5.6/en/replication-semisync.html
工具
- Percona Toolkit:https://www.percona.com/software/database-tools/percona-toolkit ,包含pt-table-checksum(检查主从一致性)、pt-table-sync(修复主从不一致)等实用工具。
- MHA:https://code.google.com/p/mysql-master-ha/ ,MySQL主从高可用自动故障转移工具。
- MyCLI:MySQL的命令行客户端支持自动补全语法高亮。
写在最后
MySQL主从复制是MySQL最常用也是最重要的高可用和高性能方案之一。对于后端开发和运维来说掌握MySQL主从复制是必备技能。
学习MySQL主从复制不要只停留在会配置的层面还要深入理解它的原理常见问题最佳实践。只有理解了原理才能在遇到问题时快速定位解决。
另外MySQL主从复制只是MySQL高可用的基础。要实现真正的高可用还需要配合心跳检测、自动故障转移、读写分离中间件等。在实际生产环境中需要根据业务需求选择合适的方案。
2015年MySQL 5.6已经非常成熟GTID、并行复制、半同步复制等特性让主从复制更加可靠更加高效。MySQL 5.7也即将发布会进一步增强主从复制的功能和性能。未来MySQL主从复制会越来越强大越来越易用。
"工欲善其事必先利其器。"希望这篇文章能帮你掌握MySQL主从复制这个重要的数据库技术让你的数据库更加可靠更加高效。
最后记住:主从复制不是万能的。它能提高数据的安全性和系统的读性能但不能替代备份也不能解决所有的高可用问题。 要根据业务需求合理使用主从复制配合其他技术构建真正高可用高性能的数据库架构。
祝你在MySQL的世界里游刃有余搭建出稳定高效的数据库系统!
评论(0)
暂无评论,快来抢沙发~
评论功能仅对会员开放,请先登录
登录