数据库拷贝命令_深入了解MySQL主从复制的原理

0.主从复制

一、什么是主从复制？简单来说，就是让一台MySQL服务器复制另一台MySQL的数据，以保持两台服务器的数据一致。

这种方式与Redis的主从复制思想没有太大区别。如果你对Redis的主从复制感兴趣，可以阅读《Redis的主从复制》。那么既然Redis和MySQL都采用了复制，那么主从复制的意义何在呢？

通过复制功能，构建一个或多个从数据库可以提高数据库的高可用性和可扩展性，同时实现负载均衡。当主库出现故障时，可以快速切换到其从库之一，并将从库提升为主库。由于数据相同，不会影响系统的运行；当MySQL服务器需要处理较多的读请求时，可以将读请求流量分流到各个从库，而写请求可以转发到主库，形成读写分离架构，提供更好的读扩展和请求负载平衡。

读写分离架构其实应用非常广泛，比如MySQL、Redis，还有我们熟悉的那些。当收到读请求时，它不会自行处理，而是将读请求转发给。如果大家有兴趣的话，可以自己下来了解一下。我暂时不会偏离主题。

1、复制原理

MySQL的主从复制支持两种方式：

基于语句的复制从 0.23 开始就可用，而基于行的复制仅在 5.1 中实现。它的本质是基于主库的。主库记录，然后从库在自己的服务器上回放，从而保证了主从之间的数据一致性。

1.1

MySQL中的日志分为两个维度，一是针对MySQL服务器的，二是针对底层存储引擎的。上面提到的是属于MySQL服务器的日志，也称为二进制日志，它记录了对MySQL所做的所有更改。

基于行和语句的复制方法与存储方法有关。存储格式有行式和混合式三种。

为什么有这么多方法？因为只会记录 SQL 语句，所以不能保证这些语句在所有情况下都能在从库上正确重放。因为可能顺序错了。

MySQL什么时候会记录它？事务提交时，并不是按照语句的执行顺序来记录的。记录完成后，会通知底层存储引擎提交事务，因此可能会因为语句顺序不正确而出现语句错误。

1.2 视图

我们以 MySQL 5.6 为例。默认情况下它是关闭的。我们可以通过命令show like '%%'来查看配置。

默认配置

表示是否开启，默认值为OFF。

通过命令show log可以查看MySQL中的所有文件

查看

知道了有哪些文件之后，我们就可以看一下文件的内容了。我们可以通过MySQL中的show命令来查看它们。

show 查看第一个文件，我们也可以通过 in 参数指定。假设我们要查看的文件名是mysql-bin.，那么我们可以使用命令show in 'mysql-bin.'。查看指定文件

查看

接下来我们看看我们在MySQL中的操作对应的是什么。

初始化

正如我们上面提到的，它是由一个又一个事件组成的。从MySQL 5.0开始，第一个事件位于图中的列中。可以看到内容为ver;5.6.50-log，ver:4，说明当前使用的MySQL版本为5.6.50，版本为V4。

创建数据库

然后我创建了一个名为 Query 的数据库。该事件的内容是SET=，一条数据库创建语句。

创建新表

然后我创建了一个表，名为，也是Query，内容是use； TABLE (id INT(11) NOT NULL KEY)，建表语句。

插入数据

然后我们执行语句向表中插入两行数据并再次查看。

INSERT INTO `student` (`id`, `name`) VALUES (NULL, '张三');
INSERT INTO `student` (`id`, `name`) VALUES (NULL, '李四');

可以看到每次都有一笔交易被打开。你可能会感到困惑。我们只是简单的执行了语句，并没有显式的开启事务。那么事情为什么会发生呢？

这是因为 MySQL 使用自动 () 机制。我使用的存储引擎支持事务，所有用户活动都发生在事务中。我们可以通过'%%'这样的显示来查看；命令。如果结果为ON，则表示已开启。

1.3 复制的核心步骤

我们假设主库已经打开并且正在正常录制。

首先，启动从库的I/O线程，并与主库建立客户端连接。

主库启动dump线程，读取主库上的事件并发送给从库的I/O线程。 I/O线程获取事件后，将其写入到自己的Relay Log中。

然后从数据库启动SQL线程，重放Relay中的数据，完成从数据库的数据更新。

综上所述，主库上只会有一个线程，从库上会有两个线程。

主从复制流程

1.4 中继日志

中继日志实际上没有太大区别。 MySQL 4.0之前，没有Relay Log部分。整个过程只有两个线程。但这也带来了一个问题，就是复制过程需要同步进行，容易受到影响，效率不高。例如，主库必须等待从库读取完成才能发送下一个事件。这有点类似于阻塞通道和非阻塞通道。

阻塞通道

封锁频道就像你在柜台一样。你必须递归地将一些东西传递给出纳员，但是你和出纳员之间没有地方可以放置该对象。您只能继续保留文件直到柜员接管；非阻塞通道就像你中间有一个放文件的地方，你就可以直接放在那里，不用等柜员接手。

引入Relay Log后，原本同步的获取事件和重放事件解耦了。这两个步骤可以异步执行，并且中继日志充当缓冲区。 Relay Log 有一个文件，用于记录当前复制的进度以及将写入下一个事件的 Pos。该文件由 SQL 线程更新。

1.5 中继日志核心参数

接下来我们来了解一下Relay Log的核心参数。

2.复制模型

在平时的开发中，很少会从主从架构入手。这既耗时又昂贵，并且增加了额外的复杂性。最后，我发现在一台 MySQL 服务器上投入这么多就足够了。

这和产品的架构迭代是一样的。当它刚刚开始时，一个应用程序就足够了。当你的业务扩展、请求扩大，单个实体无法承受压力时，你会考虑部署多个实例，开始使用微服务架构进行水平扩展和负载均衡。

2.1 一主多从

当然你也可以把它当作主人和奴隶。

这是最简单的模型，特别适合小写入和大读取。将读请求分发到各个从库，有效分散主库压力，提高读并发度。当然，你也可以只使用从库作为容灾数据库，除了主从复制之外，不进行任何其他请求或数据传输。

您甚至可以使用备用数据库之一作为预发布环境的数据库。当然，归根结底，这还是直接触及生产环境的数据库，这是一个过于理想的使用方式，因为这还涉及到生产环境数据库的数据敏感性。性别。并不是所有人都可以访问，需要完整的权限机制。

MySQL一主多从

值得注意的是，如果有n个从库，那么主库上就会有n个dump线程。如果n比较大，可能会导致复制时主库的性能抖动。因此，当从库较多时，可以采用级联复制。

2.2 级联复制

用白话来说，级联复制就像俄罗斯套娃。

本来从库B、C、D、E、F、G都是从主库A复制过来的，但是由于A压力比较大，所以我们不再这样做，调整为如下模式。

MySQL级联复制

这就是所谓的级联复制，开启疯狂的套娃模式。你甚至可能会觉得这种嵌套娃娃看起来很眼熟。 Redis主从复制中，还可以使用级联模式，一个slave复制另一个slave。

级联复制的好处是大大减轻了主库的压力。主库只需要关心与其有直接复制关系的从库，剩下的复制就交给从库吧。相反，由于这种层层嵌套的关系，如果上层出现错误，就会影响服务器下挂的所有子库，并且这些错误的影响被放大。

2.3 主主复制

顾名思义，两个主库互相复制，客户端可以写入任意一个主库。一台主数据库服务器上的任何数据更改都将同步到另一台服务器。有点类似于双节点模式，两个注册中心互相注册。这样一来，如果其中任何一个挂掉了，都不会对系统产生任何影响。

而且主主复制可以打破数据库性能瓶颈，一个很酷的功能——水平扩展。为什么很酷？如果DB能够水平扩展，那么很多数据库并发限制的瓶颈就可以被突破。然而...

但主主复制其实并不可靠，双方数据冲突的可能性很大。例如，如果复制停止，系统仍在将数据写入两个主数据库。也就是说，一部分数据在A，另一部分数据在B，但是没有相互复制，数据不同步。修复这部分数据将是相当困难的。

所以我认为双主的意义在于HA而不是负载均衡。

2.4 主动和被动主主复制

仍然是双主结构，但不同的是其中一个是只读的被动服务器，客户端不会对库进行写操作。

它有什么用？例如，我们需要在不中断服务的情况下维护和优化MySQL。比如我们需要修改表结构。假设我们有两个数据库，主数据库A和被动主数据库B。注意，这里的被动主数据库是只读的。我们首先停止A到B的复制，即停止A上的​​SQL线程。

停止复制

这样我们后面对B进行的非常耗时的、可能需要锁表的操作就不会立即同步到A上。因为此时A正在对外提供服务，所以无法受到影响。但由于采用异步复制模式，Relay Log会继续由I/O线程写入，但不会重放。

然后我们在B上执行这个维护操作。注意，A上发生的更新仍然会正常同步到B上。执行后，读和写的角色互换。也就是说A成为只读的被动主库，B成为主动主库对外提供服务。

重新启动SQL线程

然后重新启动SQL线程，A开始重放之前Relay Log中积累的事件。虽然此时A可能被屏蔽了，但是A已经不再对外提供服务了，所以没有问题。

大家都知道主动模式和被动模式下双主动模式的好处。它可以在不停止服务的情况下更新数据库的结构。其次，当主数据库出现故障时，可以快速切换，保证数据库的HA。

3、复制方法

我们上面提到复制是异步的不止一次。接下来我们看一下MySQL的主从复制方式。

3.1 异步复制

第一种是异步的，这也是MySQL默认的方式。异步复制下，主库不会主动向从库发送消息，而是等待从库的I/O线程建立连接。然后主库创建一个转储线程并将事件发送到I/O线程。流程如下。

MySQL复制模式

主库执行完自己的交易并记录后会直接返回，不与客户端确认任何结果。然后由dump线程异步读取，然后发送到从库。处理请求和主从复制是两个完全异步的过程。

3.2 同步复制

同步模式下，当主库执行事务时，主库必须等待所有从库都完成事务并返回后，才能向客户端返回成功。

同步复制

值得注意的是，主库会直接提交事务，而不是等待所有从库返回后再提交。 MySQL只延迟返回给客户端，并不延迟事务的提交。

同步模式的性能会大大降低。它将客户端的请求与主从复制耦合起来。如果从复制线程执行缓慢，对客户端的响应也会慢很多。

3.3 半同步复制

半同步和同步的区别在于，同步需要等待所有从库返回，而半同步只需要一个从库返回。如果默认时间过后仍然没有从库，则会切换到异步模式，然后提交。客户不会一直等待。

MySQL复制模式

因为即使后面主库宕机了，也能保证至少有一个从库节点可用。此外，还减少了同步期间的等待时间。

4. 复制中的数据一致性

我们在1.3中讨论了复制的核心步骤。这似乎是一个非常简单的过程。读取主库的dump，然后从库的I/O线程读取并写入Relay Log，然后读取从库的SQL线程。然后读取中继日志进行重播。

如果 I/O 线程在复制过程中突然挂起怎么办？或者当复制完成一半时主数据库可能已关闭？如果保证数据一致性呢？

上面我们提到，有一个文件用来记录当前从库复制写入的Relay Log的Pos。只要这个文件还存在，当从库意外重启时，就会重新读取该文件。从上次复制的位置继续复制。这类似于Redis中的主从复制。双方需要维护一个，通过比较进行psync增量数据同步。

然而，在MySQL 5.5及之前版本中，复制的进度只能记录在文件中。换句话说，参数列表只支持FILE。大家可以回到上面的1.5 Relay Log核心参数来看一下。因此，只有在该事务之后，文件才会刷新到磁盘。

如果从库在刷新到磁盘之前就挂掉了，那么重启后就会发现SQL线程的实际执行位置与数据库记录不一致，就出现了数据一致性的问题。

因此，在MySQL 5.6中，参数itory支持了TABLE，这样我们就可以将复制进度放在系统的mysql中。表，并将更新进度和SQL线程执行用户事务绑定到一个事务执行中。即使slave宕机了，我们也可以利用MySQL内置的崩溃恢复机制来恢复实际执行位置和数据库中保存的进度一致。

其次，还有上面提到的半同步复制。主库会先提交事务，然后等待从库返回，然后将结果返回给客户端。但是如果主库等待时从库挂掉了怎么办？

此时主库上已经提交了事务，但从库上还没有该数据。因此MySQL 5.7中引入了无损半同步复制，并且增加了参数值。在 MySQL 5.7 中，默认值为 ，在 MySQL 5.6 中，默认值为 。

好了，这就是本博客的全部内容。如果您觉得这篇文章对您有帮助，请点赞、关注、分享、留言。