MySQL数据库主从复制原理及常见故障介绍

厍雯轩

民航气象中心 北京 100015

引言

民航气象中心采用MySQL数据库存储值班人员信息、值班日志信息、机场配置及业务系统配置等相关信息，并采用一主两从的MySQL集群来增加数据存储的响应速度和数据安全性，为更好地保证数据存储的完整性及安全性，民航气象中心使用主从复制功能进行MySQL主从数据库之间的数据同步。此功能可以有效提升服务器读写性能，主库出现异常时，可以快速切换至从库，减少对业务运行的影响。民航气象中心在使用此功能的过程中，出现了几次MySQL主从数据库复制延迟的现象，对值班人员使用造成了一定的影响。本文主要介绍MySQL数据库的主从复制原理，主从延迟的排查方法、常见的MySQL主从复制故障原因、现象及处理方法。旨在未来出现MySQL主从复制故障时，可以快速进行故障判断，及时进行故障处理，减少故障对业务运行带来的影响。

1 MySQL数据库简介

MySQL是一个开源小型关联式数据库管理系统，具有体积小、速度快、成本低的特点，使用最常用的数据库管理语言结构化查询语句（sql）进行数据库管理[1]。对于使用者来说，MySQL涵盖了常用的数据库功能，且操作简单便捷。对于开发人员来说，其开源的特性可由开发者根据自身或客户的需求进行定制化处理。MySQL是以客户机/服务器结构的实现，由一个服务器守护程序MySQL和很多不同的客户程序与库组成，能够快速、安全且有效地处理大量的数据。

随着业务及数据存储量的不断增加，单台MySQL服务器所提供的服务能力往往不能满足业务的实际需求，为提升MySQL存取数据的响应速度及安全性，大型企业通常会搭建一个能够同时实现高并发和负载均衡的MySQL集群服务器。为提升数据读写效率，需要对数据进行读写分离；为确保安全，需要对数据进行热备份。为实现上述功能，MySQL提供了一个称为“主从复制”的功能来实现多台服务器之间的数据自动备份。

2 MySQL主从复制

2.1 主从复制原理

一般将提供增删改服务的服务器称作主用服务器，将提供数据查询服务的一个或多个服务器称作备用服务器。MySQL主从复制是指将MySQL的某一台主机（Master）的数据复制到其他主机（Slave）上，并重新执行一遍来实现。复制过程中一个服务器充当主服务器，而一个或多个其他服务器充当从服务器[2]。MySQL主从复制是基于主服务器在二进制日志跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。

MySQL的主从复制遵从以下过程：

2.1.1 主用服务器（Master）进行数据更新操作后将相应的操作记录到二进制日志（Binary Log）中。

2.1.2 备用服务器（Slave）将主用服务器（Master）的二进制日志复制到它的中继日志（Relay Log）中。

2.1.3 备用服务器（Slave）重做中继日志中的事件，进行备用服务器的数据更新。

2.2 主从复制优点

使用主从复制，即对数据做了冗余，数据不会因为单个服务器宕机而导致丢失，使数据存储更加安全。一主多从的服务器结构，可设定不同用户从不同的服务器读取数据，可提升服务器读取性能。当业务量增加时，可以增加从服务器的数量，减少业务增加对系统使用的影响。

综上所述，使用MySQL的主从复制功能，可以提高数据安全性、提升服务器性能、提高服务器扩展性。

3 常见MySQL主从复制延迟原因

3.1 从服务器过多，导致主从同步延迟

一般选用一主两从或者一主三从的集群模式进行数据存储。若备用服务器数量过多，要复制的从节点数量过多，复制延迟的概率越高。

3.2 从库读取数据压力大

进行读写分离后，用户使用指定的服务器进行读数据操作，若设置为主写从读，在sql查询语句不合理、查询数据量过大，从库执行该sql语句时间过长，会导致主从复制延迟。

3.3 服务器性能不足

服务器的硬件设备对MySQL数据库的读取有所影响。对于MySQL数据库服务器，CPU、内存、磁盘等的性能都会对MySQL数据库读写造成影响。

3.4 数据表过大

大数据表特点是记录行数巨大，单表超千万；表数据文件巨大，超过10个G。对于大数据表，很难在短时间内过滤出需要的数据，在大数据表中筛选数据会产生大量的IO，影响磁盘性能；需占用更长的时间建立索引表，导致主从复制延迟；修改表结构时需要长时间锁表，也会造成长时间的主从复制延迟。

3.5 事务运行时间长操作多

运行时间长，操作数据比较多的事务被称作大事务；大事务会导致锁定的数据多，回滚时间长，执行时间长。锁定太多数据，造成大量阻塞和锁超时；回滚时所需时间比较长，且数据仍然会处于锁定；如果执行时间长，只有当主服务器全部执行完写入日志时，从服务器才会开始进行同步，因此会造成主从复制延迟。

3.6 锁等待

对于 SQL 单线程来说，当遇到阻塞时就会一直等待，直到执行成功才会继续进行。如果某一时刻从库因为查询产生了锁等待的情况，此时只有当前的操作执行完成后才会进行下面的操作，同理也就产生了主从延迟的情况。

3.7 从库服务器异常

在从库复制数据过程中，从库服务器出现异常宕机，可能造成relay log损坏，无法继续进行复制。

4 MySQL主从复制延迟排查方法

4.1 检查主从MySQL数据库状态

排查MySQL主从复制延迟需要使用root用户登录从数据库，输入MySQL命令，进入MySQL。通过监控show slave statusG命令输出的Seconds_Behind_Master参数值来判断。当此值为NULL时，表示io_thread或是sql_thread任意一个发生了故障；当值为0时，表示主从复制正常；当值为正值时，表示主从复制已经出现了延迟，且数字越大，从库延迟越严重。Relay_mastar_log_file以及exec_master_log_pos值可以查看sql线程执行带的relay_log名及在日志中位置。

4.2 查看错误日志

错误日志记录了MySQL主从复制的错误信息、记录复制开始和停止的相关信息。当发现主从复制延迟时，在MySQL中输入show variables like “log_error”，找到error所在位置。查看日志可以看到从库复制的错误信息，可以看到从库停止复制时，IO读取主库的binlog截止位置和线程执行的relay log的截止位置。

4.3 查看二进制日志文件

二进制日志文件包括主库的binlog、从库的relay log、从库的binlog等。其中主库binlog主要记录了主库执行过的事务记录，从库的relay log主要记录了从库接收到的主库binlog日志，从库binlog主要记录从库执行的事务记录。正常状态下，从库正在接受的binlog的文件和位置均应大于主库。

在MySQL中，可以输入“show binlog events in‘文件名’；”查看二进制文件。二进制文件中，我们需要关注当前的binlog之前执行过的所有gtid，用于定位具体gtid；错误发生时间，用于确定异常时执行的语句。

5 MySQL主从复制延迟常见的报错信息及解决方法

5.1 减少延迟的方法

5.1.1 架构方面。对于读写数据库实时性要求高的系统，如业务运行需要的系统，采取读写均在主库的方式，此种读写方式可以提升读写速率，系统读写数据库不受MySQL从库的影响，即使出现MySQL主从复制延迟，也不会对系统运行造成影响。

对于读数据库实时性要求较低的系统，如读取值班信息的系统，采取主从数据库读写分离的方式，即主写从读，此种读写方式可分散主库的压力，减少主从复制延迟问题出现的概率。

5.1.2 硬件方面。服务器的性能越好，处理事务的速度越快，主从复制延迟越小。因此根据业务量，采用性能更好的密集型CPU、更大的内存及具有更好的随机读取性能的固态硬盘可以有效提升服务器读写速率。

5.1.3 使用方面。使用合理的sql语句进行MySQL数据库的增删改查操作；设置生命周期，定时删除超过生命周期的数据，减少大数据表的数量；减少同时执行多条事务等都可以减少MySQL主从复制延迟问题出现[3]。

5.2 常见报错及处理方法

5.2.1 报错1062主键冲突。错误原因：此错误是从库插入数据时，发生唯一性冲突导致的。报此错误表示从库已经有相同主键的数据，如果再插入相同主键值的数据会报1062错误。

排查方法：主库MySQL中输入show slave status G;找到Master_Log_File错误的事务，输入show binlog events in‘master_bin’；查看该二进制文件中end_log_pos，可以看到从库复制停在了哪一条插入语句。

解决方法：删除造成主键冲突的数据，即可解决该问题。

5.2.2 报错1032更改的数据不存在。错误原因：主库中删除了从库中不存在的数据。

排查方法：查看主库的二进制文件Master_Log_File中end_log_pos，可以看到报错的事务项。

解决方法：先停止从库同步主库数据，跳过报错的事务项后，启动从库同步主库数据功能。

5.2.3 报错13114主库binlog丢失。错误原因：主库binlog日志丢失，从库在二进制日志索引文件中找不到第一个日志文件名。

解决方法：清空原主从配置，查到主库当前最旧的binlog日志，找到GTID值，手动设置从库的GTOD_PURGED值，连接主库，重新启动主从复制功能。

问题：由于舍弃了主库丢失的binlog日志，导致部分同步事务丢失，主从数据库中数据可能存在不一致[4]。

5.2.4 报错13121从库日志丢失。错误原因：从relay log日志丢失。

解决方法：根据Relay_Master_Log_File和Exec_Master_Log_Pos、Executed_Gtid_Set的值，找到从库最后完成的事务所对应的binlog文件和位置，情况从库的gtid及从库信息，连接主库，启动主从复制功能。

6 结束语

随着民航气象中心业务的逐步增加，存储的数据种类越来越多，根据数据特点选择了不同的数据库进行存储。MySQL数据库便于查询使用、性能强大、支持多操作系统运行、性价比高等优点，同时具有不支持复杂的查询条件、不能有效的存储大量数据的缺点。民航气象中心的系统配置、人员信息等数据的数据量较为固定、日常改动较低，因此存放在MySQL数据库中，并采用MySQL一主两从、主从复制功能来保证数据存储的完整性。

本文针对MySQL集群主从复制延迟的常见原因、现象及处理方法进行介绍，希望为未来民航气象中心的MySQL集群主从复制延迟故障发生时的及时处理，提供思路与解决办法。