mysql之复制

Sean Yu 12月 04, 2020

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

MySQL支持两种复制方式：基于行的复制和基于语句的复制。在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。

复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。

除此之外，每个备库也会对主库增加一些负载（例如网络I/O开销），尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。

最后，如果是从一个高吞吐量（例如5000或更高的TPS）的主库上复制到多个备库，唤醒多个复制线程发送事件的开销将会累加。

复制解决的问题

数据分布
负载均衡
备份
高可用和故障切换

复制如何工作

在主库上把数据更改记录到二进制日志（BinaryLog）中（这些记录被称为二进制日志事件）

在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。

备库将主库上的日志复制到自己的中继日志（RelayLog）中

首先，备库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlogdump）线程（该线程没有对应的SQL命令），这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库I/O线程会将接收到的事件记录到中继日志中。

备库读取中继日志中的事件，将其重放到备库数据之上

该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中，它对于我们稍后提到的场景非常有用。

这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作。但这种架构也限制了复制的过程，其中最重要的一点是在主库上并发运行的查询在备库只能串行化执行，因为只有一个SQL线程来重放中继日志中的事件。后面我们将会看到，这是很多工作负载的性能瓶颈所在。虽然有一些针对该问题的解决方案，但大多数用户仍然受制于单线程。

复制的原理

基于语句的复制

在MySQL5.0及之前的版本中只支持基于语句的复制（也称为逻辑复制），这在数据库领域是很少见的。

基于语句的复制模式下，主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍。这种方式既有好处，也有缺点。

最明显的好处是实现相当简单。理论上讲，简单地记录和执行这些语句，能够让主备保持同步。另一个好处是二进制日志里的事件更加紧凑，所以相对而言，基于语句的模式不会使用太多带宽。一条更新好几兆数据的语句在二进制日志里可能只占几十个字节。

但事实上基于语句的方式可能并不如其看起来那么便利。因为主库上的数据更新除了执行的语句外，可能还依赖于其他因素。还存在着一些无法被正确复制的SQL。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。

另外一个问题是更新必须是串行的。这需要更多的锁。

基于行的复制

MySQL5.1开始支持基于行的复制，这种方式会将实际数据记录在二进制日志中，跟其他数据库的实现比较相像。它有其自身的一些优点和缺点。最大的好处是可以正确地复制每一行。一些语句可以被更加有效地复制。

由于无须重放更新主库数据的查询，使用基于行的复制模式能够更高效地复制数据。这主要语句取决于影响的行数。一条语句影响的行数越多，使用基于行的复制开销越大，因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大。并且会给主库上记录日志和复制增加额外的负载，更慢的日志记录则会降低并发度。

对于基于行的复制模式，很难进行时间点恢复。

选择哪种方式

基于语句的复制模式的优点

当主备的模式不同时，逻辑复制能够在多种情况下工作。例如，在主备上的表的定义不同但数据类型相兼容、列的顺序不同等情况。这样就很容易先在备库上修改schema，然后将其提升为主库，减少停机时间。基于语句的复制方式一般允许更灵活的操作。

基于语句的方式执行复制的过程基本上就是执行SQL语句。这意味着所有在服务器上发生的变更都以一种容易理解的方式运行。这样当出现问题时可以很好地去定位。

基于语句的复制模式的缺点

很多情况下通过基于语句的模式无法正确复制，几乎每一个安装的备库都会至少碰到一次。简单地说：如果正在使用触发器或者存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会碰到复制问题。

基于行的复制模式的优点

几乎没有基于行的复制模式无法处理的场景。对于所有的SQL构造、触发器、存储过程等都能正确执行。只是当你试图做一些诸如在备库修改表的schema这样的事情时才可能导致复制失败。

这种方式同样可能减少锁的使用，因为它并不要求这种强串行化是可重复的。

基于行的复制模式会记录数据变更，因此在二进制日志中记录的都是实际上在主库上发生了变化的数据。你不需要查看一条语句去猜测它到底修改了哪些数据。在某种程度上，该模式能够更加清楚地知道服务器上发生了哪些更改，并且有一个更好的数据变更记录。另外在一些情况下基于行的二进制日志还会记录发生改变之前的数据，因此这可能有利于某些数据恢复。

在很多情况下，由于无须像基于语句的复制那样需要为查询建立执行计划并执行查询，因此基于行的复制占用更少的CPU。

最后，在某些情况下，基于行的复制能够帮助更快地找到并解决数据不一致的情况。举个例子，如果是使用基于语句的复制模式，在备库更新一个不存在的记录时不会失败，但在基于行的复制模式下则会报错并停止复制。

基于行的复制模式的缺点

由于语句并没有在日志里记录，因此无法判断执行了哪些SQL，除了需要知道行的变化外，这在很多情况下也很重要（这可能在未来的MySQL版本中被修复）。

使用一种完全不同的方式在备库进行数据变更——而不是执行SQL。事实上，执行基于行的变化的过程就像一个黑盒子，定位问题困难。

基于行的日志无法处理诸如在备库修改表的schema这样的情况，而基于语句的日志可以。

在某些情况下，例如找不到要修改的行时，基于行的复制可能会导致复制停止，而基于语句的复制则不会。

发送复制事件到其他备库

log_slave_updates选项可以让备库变成其他服务器的主库。在设置该选项后，MySQL会将其执行过的事件记录到它自己的二进制日志中。这样它的备库就可以从其日志中检索并执行事件。

复制拓扑

基本原则

一个MySQL备库实例只能有一个主库。
每个备库必须有一个唯一的服务器ID。
一个主库可以有多个备库（或者相应的，一个备库可以有多个兄弟备库）。
如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库。

一主库多备库

上一主多备的结构和基本配置差不多简单，因为备库之间根本没有交互

在有少量写和大量读时，这种配置是非常有用的。可以把读分摊到多个备库上，直到备库给主库造成了太大的负担，或者主备之间的带宽成为瓶颈为止。它非常灵活，能满足多种需求。下面是它的一些用途：

为不同的角色使用不同的备库（例如添加不同的索引或使用不同的存储引擎）。
把一台备库当作待用的主库，除了复制没有其他数据传输。
将一台备库放到远程数据中心，用作灾难恢复。
延迟一个或多个备库，以备灾难恢复。
使用其中一个备库，作为备份、培训、开发或者测试使用服务器。

主动主动模式下的主主复制

主主复制（也叫双主复制或双向复制）包含两台服务器，每一个都被配置成对方的主库和备库，换句话说，它们是一对主库。

主动主动模式下主主复制有一些应用场景，但通常用于特殊的目的。一个可能的应用场景是两个处于不同地理位置的办公室，并且都需要一份可写的数据拷贝。这种配置最大的问题是如何解决冲突，两个可写的互主服务器导致的问题非常多。

主动被动模式下的主主复制

主要区别在于其中的一台服务器是只读的被动服务器

这种方式使得反复切换主动和被动服务器非常方便，因为服务器的配置是对称的。这使得故障转移和故障恢复很容易。它也可以让你在不关闭服务器的情况下执行维护、优化表、升级操作系统（或者应用程序、硬件等）或其他任务。

确保两台服务器上有相同的数据。
启用二进制日志，选择唯一的服务器ID，并创建复制账号。
启用备库更新的日志记录，后面将会看到，这是故障转移和故障恢复的关键。
把被动服务器配置成只读，防止可能与主动服务器上的更新产生冲突，这一点是可选的。
启动每个服务器的MySQL实例。将每个主库设置为对方的备库，使用新创建的二进制日志开始工作。

设置主动被动的主主拓扑结构在某种意义上类似于创建一个热备份，但是可以使用这个“备份”来提高性能，例如，用它来执行读操作、备份、“离线”维护以及升级等。真正的热备份做不了这些事情。然而，你不会获得比单台服务器更好的写性能（稍后会提到）。

拥有备库的主主结构

这种配置的优点是增加了冗余，对于不同地理位置的复制拓扑，能够消除站点单点失效的问题。你也可以像平常一样，将读查询分配到备库上。

环形复制

环形结构可以有三个或更多的主库。每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库。这种结构也称为环形复制（circularreplication）。如果从环中移除一个节点，这个节点发起的事件就会陷入无限循环：它们将永远绕着服务器链循环。因为唯一可以根据服务器ID将其过滤的服务器是创建这个事件的服务器。总地来说，环形结构非常脆弱，应该尽量避免。

复制和容量规划

例如，假设工作负载为20%的写以及80%的读。为了计算简单，假设有以下前提：

读和写查询包含同样的工作量。
所有的服务器是等同的，每秒能进行1000次查询。
备库和主库有同样的性能特征。
可以把所有的读操作转移到备库。

如果当前有一个服务器能支持每秒1000次查询，那么应该增加多少备库才能处理当前两倍的负载，并将所有的读查询分配给备库？

看上去应该增加两个备库并将1600次读操作平分给它们。但是不要忘记，写入负载同样增加到了400次每秒，并且无法在主备服务器之间进行分摊。每个备库每秒必须处理400次写入，这意味着每个备库写入占了40%，只能每秒为600次查询提供服务。因此，需要三台而不是两台备库来处理双倍负载。

如果负载再增加一倍呢？将有每秒800次写入，这时候主库还能处理，但备库的写入同样也提升到80%

这远远不是线性扩展，查询数量增加4倍，却需要增加17倍的服务器。这说明当为单台主库增加备库时，将很快达到投入远高于回报的地步。这仅仅是基于上面的假设，还忽略了一些事情，例如，单线程的基于语句的复制常常导致备库容量小于主库。真实的复制配置比我们的理论计算还要更差。

复制无法扩展写操作，对数据进行分区是唯一可以扩展写入的方法
备库会有复制延迟
分配给每台机器的读负载应该低于50％，否则，如果某台服务器失效，就没有足够的容量了。

复制管理和维护

监控复制：使用SHOW MASTER STATUS命令来查看当前主库的二进制日志位置和配置
测量备库延迟：忽略Seconds_behind_master的值，并使用一些可以直接观察和衡量的方式来监控备库延迟。最好的解决办法是使用heartbeat record，这是一个在主库上会每秒更新一次的时间戳。为了计算延时，可以直接用备库当前的时间戳减去心跳记录的值。这个方法能够解决刚刚我们提到的所有问题，另外一个额外的好处是我们还可以通过时间戳知道备库当前的复制状况。
确定主备是否一致
从主库重新同步备库：使用mysqldump转储受影响的数据并重新导入
改变主库：
- 计划内提升：
  - 停止向老的主库写入。
  - 让备库追赶上主库（可选的，会简化下面的步骤）。
  - 将一台备库配置为新的主库。
  - 将备库和写操作指向新的主库，然后开启主库的写入。
- 计划外的提升
  - 确定哪台备库的数据最新。
  - 让所有备库执行完所有其从崩溃前的旧主库那获得的中继日志。如果在未完成前修改备库的主库，它会抛弃剩下的日志事件，从而无法获知该备库在什么地方停止。
  - 停止向老的主库写入。
  - 比较每台备库和新主库上的Master_Log_File/Read_Master_Log_Pos的值。
  - 将备库和写操作指向新的主库，然后开启主库的写入。

复制的问题和解决方案

数据损坏或丢失的错误

主库意外关闭：指定备库从下一个二进制日志的开头读日志。
备库意外关闭：在重启后观察MySQL错误日志。InnoDB在恢复过程中会打印出它的恢复点的二进制日志坐标。可以使用这个值来决定备库指向主库的偏移量。
主库上的二进制日志损坏：除了忽略损坏的位置外你别无选择
备库上的中继日志损坏：以通过CHANGEMASTERTO命令丢弃并重新获取损坏的事件。只需要将备库指向它当前正在复制的位置
二进制日志与InnoDB事务日志不同步：当主库崩溃时，InnoDB可能将一个事务标记为已提交，此时该事务可能还没有记录到二进制日志中。除非是某个备库的中继日志已经保存，否则没有任何办法恢复丢失的事务。

对未复制数据的依赖性

如果在主库上有备库不存在的数据库或表，复制会很容易意外中断，反之亦然。假设主库上有一个备库不存在的数据库，命名为scratch。如果在主库上发生对该数据库中表的更新，备库会在尝试重放这些更新时中断。同样的，如果在主库上创建一个备库上已存在的表，复制也可能中断。没有什么好的解决办法，唯一的办法就是避免在主库上创建备库上没有的表。

丢失的临时表

临时表在某些时候比较有用，但不幸的是，它与基于语句的复制方式是不相容的。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。重启备库后，所有依赖于该临时表的语句都会失败。

如果备库重启后复制因找不到临时表而停止，可能需要做以下一些事情：可以直接跳过错误，或者手动地创建一个名字和结构相同的表来代替消失的临时表。不管用什么办法，如果写入查询依赖于临时表，都可能造成数据不一致。

避免使用临时表没有看起来那么难，临时表主要有两个比较有用的特性：

只对创建临时表的连接可见。所以不会和其他拥有相同名字临时表的连接起冲突。
随着连接关闭而消失，所以无须显式地移除它们。

可以保留一个专用的数据库，在其中创建持久表，把它们作为伪临时表，以模拟这些特性。

InnoDB加锁读引起的锁争用

正常情况下，InnoDB的读操作是非阻塞的，但在某些情况下需要加锁。特别是在使用基于语句的复制方式时，执行INSERT…SELECT操作会锁定源表上的所有行。MySQL需要加锁以确保该语句的执行结果在主库和备库上是一致的。实际上，加锁导致主库上的语句串行化，以确保和备库上执行的方式相符。这种设计可能导致锁竞争、阻塞，以及锁等待超时等情况。

一种缓解的办法就是避免让事务开启太久以减少阻塞。可以在主库上尽快地提交事务以释放锁。
把大命令拆分成小命令，使其尽可能简短。这也是一种减少锁竞争的有效方法。
另一种方法是替换掉INSERT…SELECT语句，在主库上先执行SELECTINTOOUTFILE，再执行LOADDATAINFILE。这种方法更快，并且不需要加锁。

过大的复制延迟

MySQL单线程复制的设计导致备库的效率相当低下。即使备库有很多磁盘、CPU或者内存，也会很容易落后于主库。因为备库的单线程通常只会有效地使用一个CPU和磁盘。而事实上，备库通常都会和主库使用相同配置的机器。

备库上的锁同样也是问题。其他在备库运行的查询可能会阻塞住复制线程。因为复制是单线程的，复制线程在等待时将无法做别的事情。

最好的分析办法是暂时在备库上打开慢查询日志记录，然后使用第3章讨论的ptquerydigest工具来分析。如果打开了log_slow_slave_statements选项，在标准的MySQL慢查询日志能够记录MySQL5.1及更新的版本中复制线程执行的语句，这样就可以找到在复制时哪些语句执行慢了。PerconaServer和MariaDB允许开启或禁止该选项而无须重启服务器。

一个简单的办法是配置InnoDB，使其不要那么频繁地刷新磁盘，这样事务会提交得更快些
还可以在备库上禁止二进制日志记录
不要重复写操作中代价较高的部分
在复制之外并行写入
为复制线程预取缓存

半同步复制

半同步复制在提交过程中增加了一个延迟：当提交事务时，在客户端接收到查询结束反馈前，必须保证二进制日志已经传输到至少一台备库上。主库将事务提交到磁盘上之后会增加一些延迟。同样的，这也增加了客户端的延迟，因此其执行大量事务的速度，不会比将这些事务传递给备库的速度更快。

关于半同步，有一些普遍的误解，下面是它不会去做的：

在备库提示其已经收到事件前，会阻塞主库上的事务提交。事实上在主库上已经完成事务提交，只有通知客户端被延迟了。
直到备库执行完事务后，才不会阻塞客户端。备库在接收到事务后发送反馈而非完成事务后发送。
半同步不总是能够工作。如果备库一直没有回应已收到事件，会超时并转化为正常的异步复制模式。

在性能方面，从客户端的角度来看，增加了事务提交的延时，延时的多少取决于网络传输，数据写入和刷新到备库磁盘的时间（如果开启了配置）以及备库反馈的网络时间。

事实上半同步复制在某些场景下确实能够提供足够的灵活性以改善性能，在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存（一台备库反馈）比写入本地的磁盘（写入并刷新）要更快。HenrikIngo运行了一些性能测试表明，使用半同步复制相比在主库上进行强持久化的性能有两倍的改善。在任何系统上都没有绝对的持久化——只有更加高的持久化层次——并且看起来半同步复制应该是一种比其他替代方案开销更小的系统数据持久化方法。除了半同步复制

mysql同步复制和异步复制的区别：

异步复制
MySQL复制默认是异步复制，Master将事件写入binlog，提交事务，自身并不知道slave是否接收是否处理；
缺点：不能保证所有事务都被所有slave接收。
同步复制
Master提交事务，直到事务在所有slave都已提交，才会返回客户端事务执行完毕信息；
缺点：完成一个事务可能造成延迟。
半同步复制
当Master上开启半同步复制功能时，至少有一个slave开启其功能。当Master向slave提交事务，且事务已写入relay-log中并刷新到磁盘上，slave才会告知Master已收到；若Master提交事务受到阻塞，出现等待超时，在一定时间内Master 没被告知已收到，此时Master自动转换为异步复制机制；

注：半同步复制功能要在Master和slave上开启才会起作用，只开启一边，依然是异步复制。