MySQL 主从复制的问题及解决方案

MySQL 主从复制的问题及解决方案

复制功能是构建 MySQL 的大规模、高性能的基础，也就是所谓的 “水平扩展” 架构。我们可以通过为服务器配置一个或多个备库。同时，复制也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。MySQL主从基本原理，主要形式以及主从同步延迟原理 (读写分离)导致主库从库数据不一致问题的及解决方案。

一、复制概述

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过复制来扩展写操作。当使用一主多备架构时，可能会造成一些浪费，因为本质上它会复制大量不必要的重复数据。 【MySQL 支持两种复制方式】：基于行的复制和基于语句的复制（逻辑复制）。这两种方式都是基于在主库上记录二进制日志、在备库上重放日志的方式来实现异步的数据复制。这就导致在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致产生几秒、几分钟甚至几小时的延迟。

MySQL 大部分是向后兼容的，新版本的服务器可以作为老版本的服务器的备库，但是反过来是不可行的，因为它可能无法解析新版本所采用的新的特性或语法，另外所使用的二进制文件的格式也可能不相同。

复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。 除此之外，每个备库也会对主库增加一些 I/O开销，尤其当备库请求从主库读取旧的二进制日志文件时。另外锁竞争也可能阻塞事务的提交。

二、复制工作机制【异步复制】

复制分为以下三个步骤：【1】在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。MySQL 会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。 【2】备库将主库上的二进制日志复制到自己的中继日志（Relay Log）中。【更多细节】备库会启动一个工作线程，称为 I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlog dump）线程，这个二进制转储线程会读取主库上二进制日志事件。如果该线程追赶上主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库 I/O 线程会将接收到的事件记录到中继日志中。 【3】备库读取中继日志中的事件，将其重放到备库数据之上。

这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说 I/O 线程能够独立于 SQL 线程之外的工作。最重要的问题是主库上并发运行的查询在备库只能串行化执行，因为只有一个 SQL 线程来重放中继日志的事件。

三、复制的用途和条件

【1】主从复制的用途：①、实时灾备，用于故障切换；②、读写分离，提供查询服务；③、备份，避免影响业务； 【2】主从部署必要条件：①、主库开启binlog日志（设置log-bin参数）；②、主从server-id不同；③、从库服务器能连通主库；

四、复制的问题和解决方案

【1】MySQL 数据库从库同步的延迟问题：复制延迟是一个很普遍的问题，最好在设计应用程序时能够让其容忍备库出现延迟。如果系统在备库出现延迟时就无法很好地工作，那么应用程序也许就不应该用到复制。但也有办法可以让备库跟上主库。

MySQL 单线程复制的设计导致备库通常只会有效地使用一个 CPU和磁盘，效率相当低下。而事实上，备库通常都会和主库使用相同配置的机器。同时备库上的锁也是问题，运行的查询可能会阻塞复制线程。因为复制是单线程的，复制线程在等待时将会无法做别的事。

复制一般有两种产生延迟的方式：突然产生延迟然后再跟上，或者稳定的延迟增长。前一种通常是由于一条运行很长时间的查询导致，而后者即使在没有长时间运行的查询时也会出现。当备库无法跟上时，可以记录备库上的查询并使用一个日志分析工具找出哪里慢了。最好的分析办法是暂时在备库上打开慢查询日志记录，然后使用 pt-query-digest 工具来分析。如果打开了 log_slow_slave_statements 选项，在标准的 MySQL 慢查询日志能够记录 MySQL5.1 及更新的版本中复制线程执行的语句，这样就可以找到在复制时那些语句执行慢了。

【解决方案一】：除了购买更快的磁盘和CPU（固态磁盘能够提供极大的帮组）备库没有太多的调优空间。大部分选项都是禁止某些额外的工作以减少备库的负载。一个简单的办法是配置 InnoDB，使其不要频繁地刷新磁盘，这样事务会提交得更快些。如下：

--默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。
--特别是使用电池供电缓存（Battery backed up cache）时。
--设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。
--日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。
--设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。
--而值2只会在整个操作系统挂了时才可能丢数据。
innodb_flush_log_at_try_commit=2

还可以在备库上禁止二进制日志记录，如下：但这些设置都是牺牲安全获取速度。如果需要将备库提升为主库，记得将这些设置还原成安全的值。

innodb_locks_unsafe_for_binlog=1

【解决方案二】：不要重复写操作中代价较高的部分：重构应用程序或者优化查询通常是最好的保持备库同步的方法。任何主库上昂贵的写操作都会在每一个备库上重放。如果可以把工作转移到备库，那么就只有一个备库需要执行，然后我们可以把写的结果回传到主库，例如，通过执行 LOAD DATA INFILE。举个栗子：

--replace into 主要作用类似insert插入操作。
--主要的区别是replace会根据主键或者唯一索引检查数据是否存在，如果存在就先删除在更新。
REPLACE INTO table_min(col1,col2)
SELECT col1,SUM(col2)
FROM table_max
GROUP BY col1;

如上在主库上执行查询，每个备库将同样需要执行庞大的 GROUP BY 查询。当进行太多这样操作时，备库将被拉开差距。如果将查询转移到一个备库上也许会有帮组。在备库上创建一个特别保留的数据库，用于避免和从主库上复制的数据产生冲突。可以执行如下操作：

REPLACE INTO back.People(col1,col2)
SELECT col1,SUM(col2)
FROM main.People 
GROUP BY col1;

现在可以执行 SELECT INTO OUTFILE，然后执行 LOAD DATA INFILE 将结果集加载到主库中。如果有 N个备库，就节约了 N-1 次庞大的 GROUP BY 操作。该策略的问题是备库中的数据和写入主库的数据很难保持一致。

SELECT * INTO OUTFILE "/data/mysql/e.sql" FROM e;
--load DATA 需要有处理文件的权限， GRANT FILE ON *.* TO USER@host;
--因为我们前面指定的分隔符是 ','，LOAD DATA 时也要指定分隔符，否则也会报错：
LOAD DATA INFILE "/data/mysql/e.sql" INTO TABLE e FIELDS TERMINATED BY ',';

我们还可以通过分离 REPLACE 和 SELECT 部分，把结果返回给应用程序，然后将其插入到主库中。这种方法再次避免了在备库上执行 GROUP BY 部分。将SELECT 与 REPLACE 分离后意味着查询的 SELECT 操作不会在每一个备库上重放。节约了备库上昂贵的写入操作部分。

--先获取需要插入的数据集
SELECT col1,SUM(col2) FROM main.table_max GROUP BY col1;
--在插入数据
REPLACE INTO  main.table_min(col1,col2) VALUES(?,?);

【解决方案三】：在复制之外并行写入：另一种避免备库严重延迟的办法是绕过复制。自己复制数据到另外一台服务器，而不是通过复制。特别是复核的瓶颈通常集中在一些小部分表上。如果能在复制之外单独处理这些，就能够显著地加快复制。 【解决方案四】：并行复制：MySQL 5.7才可称为真正的并行复制，这其中最为主要的原因就是 slave服务器的回放与 master是一致的，即 master服务器上是怎么并行执行的，那么 slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求（基于库的并行复制也没有要求）。【更多细节】 【2】数据损坏或丢失的错误：从服务器崩溃、断电、磁盘损坏、内存或网络错误中恢复。都需要从某个点开始重启复制。大部分由于非正常关机导致的复制问题都是由于没有把数据及时地刷新到磁盘。例如：  ■ 主库意外关闭：如果没有设置主库的 sync_binlog 选项，就可能在崩溃前没有将最后的几个二进制日志事件刷新到磁盘中。备库 I/O 也就一直处于读不到尚未写入磁盘的事件。 【解决方案】：指定备库从下一个二进制日志的开头读日志，但是一些日志事件将永久丢失，建议使用Percona Toolkit 中的 pt-table-checksum 工具来检查主备一致性，以便修复。可以通过在主库开启 sync_binlog 来避免数据丢失。即使开启了 sync_binlog，MyISAM 表的数据仍然可能在崩溃的时候损坏，对于 InnoDB 事务，如果 innodb_flush_log_try_commit 没有设为1，也可能丢失数据（但数据不会损坏）。

MySQL 提供一个 sync_binlog参数来控制数据库的 binlog刷到磁盘上去。默认，sync_binlog=0，表示 MySQL不控制 binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在binlog_cache 中的所有 binlog信息都会被丢失。如果sync_binlog>0，表示每 sync_binlog次事务提交，MySQL 调用文件系统的刷新操作将缓存刷下去。最安全的就是 sync_binlog=1了，表示每次事务提交，MySQL都会把 binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是 binlog虽然是顺序IO，但是设置 sync_binlog=1，多个事务同时提交，同样很大的影响 MySQL和 IO性能。虽然可以通过group commit 的补丁缓解，但是刷新的频率过高对 IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。所以很多 MySQL DBA设置的 sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。 innodb_flush_log_try_commit 值说明： 0：log buffer 将每秒一次地写入log file中，并且 log file的 flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。 1：每次事务提交时 MySQL都会把 log buffer的数据写入 log file，并且 flush(刷到磁盘)中去，该模式为系统默认。 2：每次事务提交时MySQL都会把 log buffer的数据写入 log file，但是 flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

 ■  备库意外关闭：当备库关闭后重启时，会读取 master.info 文件已找到上次停止复制的位置。不幸的是，该文件并没有同步写到磁盘，文件中存储的信息可能也是错误的。备库可能会尝试重新执行一些二进制日志事件，这可能会导致唯一索引错误。唯一的办法就是忽略那些错误。Percona Toolkit 中的 pt-slave-restart 工具可以帮组完成这一点。 如果使用的是 InnoDB 表，可以在重启后观察 MySQL 错误日志。InnoDB 在恢复过程中打印出它的恢复点的二进制日志坐标。可以使用这个值来决定备库指向主库的偏移量。Percona Toolkit 提供了一个新的特性，可以在恢复的过程中自动将这些信息提取出来，并更新 master.info 文件，从根本上使得复制能够协调好备库上的事务。  ■  主库上的二进制日志损坏：除了忽略损坏的位置别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开启一个新的日志文件，然后在将备库指向该文件的开始位置。某些情况下可以通过 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该步骤，知道跳过所有损坏的事件。  ■  备库上的中继日志损坏：如果主库上的日志是完好的，就可以通过 CHANGE MASTER TO 命令丢弃并重新获取损坏的事件。  ■  二进制日志与InnoDB事务日志不同步：当主库崩溃时，InnoDB 可能将一个事务标记为提交，此时该事务可能还没有记录到二进制日志中。除非是某个备库的中继日志已经保存，否则没有任何办法恢复丢失的事务。在 MySQL5.0 版本可以设置 sync_binlog 选项来防止该问题。 【3】不唯一的服务器 ID：如果不小心为两台备库设置了相同的服务器ID，当查看错误日志或者使用 innotop 查看主库，会发现两台备库只有一台连接到主库。在备库的错误日志会发现反复的重连和连接断开信息，但不会提及被错误配置的服务器ID。 【解决方案】：小心设置备库的服务器ID。一个比较好的办法是创建一个主库到备库的服务器ID映射表，这样就可以跟踪到备库的ID 信息。 【4】InnoDB 加锁读引起的锁争用：通常InnoDB 的读操作是非阻塞的，但是某些情况下。特别是在使用基于语句的复制方式时，执行 INSERT ... SELECT 操作会锁定原表上的所有行。MySQL 需要加锁以确保该语句的执行结果在主库和备库上是一致的。实际上，加锁导致主库上的语句串行化，以确保和备库上执行的方式相符。这种设计可能导致锁竞争、阻塞、以及锁等待超时等问题。应当避免让事务开启太久以减少阻塞。 【解决方案】：将大命令拆分成小命令，使其尽可能简短。另一种方法是替换 INSERT...SELECT 在主库上先执行SELECT INTO OUTFILE 再执行 LOAD DATE INFILE 这种方法更快，并且不需要加锁。并在完成之后清理掉文件（通过定时任务）。