拷贝数据库表_mysql 详解-复制拓扑-详细

可以在任意数量的主数据库和备用数据库之间建立复制，只有一个限制：每个备用数据库只能有一个主数据库。有许多复杂的拓扑，但即使是最简单的拓扑也可以非常灵活。拓扑可用于多种目的。可以很容易地写一本书来介绍使用复制的不同方法。

现在我们已经讨论了如何为主数据库设置备用数据库，让我们讨论其他常见的拓扑及其优缺点。请记住以下基本原则：

1. 一个MySQL备库实例只能有一个主库。

2. 每个备用数据库必须有唯一的服务器ID。

3. 一个主数据库可以有多个备数据库（或者相应地，一个备数据库可以有多个兄弟备数据库）。

4. 如果打开该选项，备用数据库可以将其主数据库上的数据更改传播到其他备用数据库。

一个主数据库，多个备份数据库

除了我们已经提到的两台服务器主备结构外，这是最简单的拓扑结构。事实上，一主多备的结构几乎和基本配置一样简单，因为备份数据库之间根本没有交互，它们只是连接到同一个主数据库。该结构如图所示。

当写入较少而读取较多时，此配置非常有用。读可以分布到多个备库，直到备库对主库造成太大负担，或者主备库之间的带宽成为瓶颈。您可以按照前面介绍的方法一次性设置多个备库，也可以根据需要添加备库。

注 1：这在技术上是不正确的。但如果存在重复的服务器D，它们就会陷入竞争并反复将对方踢出主数据库。

虽然这是一个非常简单的拓扑，但是它非常灵活，可以满足多种需求。以下是它的一些用途：

不同的角色使用不同的备用数据库（例如添加不同的索引或使用不同的存储引擎）。将备用数据库视为备用主数据库，除了复制之外没有其他数据传输。将备用数据库放置在远程数据中心以进行灾难恢复。延迟一个或多个备用数据库以进行灾难恢复。

使用备用数据库之一作为备份、培训、开发或测试服务器。这种结构受欢迎的原因是它避免了其他拓扑的许多复杂性。例如，您可以轻松比较不同备库重放的事件在主库二进制日志中的位置。

换句话说，如果所有备用数据库的复制在同一逻辑点停止，它们将读取主数据库上同一日志文件的同一物理位置。这是一个非常好的功能，可以减轻管理员的很多工作，比如将备库提升为主库。此功能仅存在于同级备份数据库之间。在没有直接主备或同级关系的服务器上比较日志文件的位置要复杂得多。我们稍后将提到的许多拓扑，例如树复制或分布式主机，使得计算复制事件的逻辑顺序变得困难。

双活模式下的主到主复制

主-主复制（也称为双主复制或双向复制）由两台服务器组成，每台服务器都配置为对方的主数据库和备用数据库。换句话说，它们是一对主数据库。下图显示了结构。

双活模式下的主到主复制有一些应用场景，但通常用于特殊目的。一种可能的用例是位于不同地理位置的两个办公室，都需要数据的可写副本。

这种配置最大的问题是如何解决冲突。两个可写的相互主服务器会导致许多问题。这种情况通常发生在两个服务器同时修改一行记录时，或者两个服务器同时修改包含以下内容的行时：

将数据插入列表中。

注2：事实上，这些问题经常每周出现3次，并且我们发现需要几个月的时间才能解决这些问题。

注意：MySQL不支持多主复制。多主复制（Multi- ）特指一个备库有多个主库。尽管您之前知道，MySQL（与其他数据库产品不同）目前不支持如图所示的结构。本章稍后我们将向您展示如何模拟多主数据库复制。

.0 添加了一些功能，使此配置稍微安全一些，即设置 auto 和 auto.通过这两个选项，MySQL可以自动为语句选择不冲突的值。然而，允许对两个主机进行写入仍然是危险的。在两台机器上以不同的顺序更新可能会导致数据不同步。例如，一张只有一列的表，只有一条值为1的记录。假设同时执行以下两条语句：

在第一个主库上：

mysql> tbl SET col=col 1;

在第二个主数据库上：

mysql> tbl SET col=col 2;

那么结果是什么呢？一台服务器上的值为 4，另一台服务器上的值为 3，并且不会报告复制错误。数据不同步只是一个开始。当正常复制因错误而停止，但应用程序仍在同时向两台服务器写入数据时，会发生什么情况？您不能简单地将数据从一台服务器复制到另一台服务器，因为需要复制的数据可能在两台计算机上都已更改。解决这个问题将会非常困难。

如果你足够仔细地配置这个架构，比如很好地划分数据和权限，并且你确切地知道你在做什么，一些问题是可以避免的（注3）。然而，这通常很难做好，并且有更好的方法来实现您的需求。一般来说，允许人们向两台服务器写入的麻烦远远超过了好处，但是下一节中描述的主动-被动模式可能非常有用。

注3：我们中的一些人（但不是全部）会挑剔并指出你能想象到的任何漏洞。

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

这是前面描述的主-主结构的一种变体，它避免了我们前面讨论的问题。这也是构建容错和高可用性系统的一种非常强大的方法，主要区别在于其中一台服务器是只读被动服务器，如图所示。

由于服务器配置是对称的，这种方法使得重复切换主动和被动服务器变得非常方便。这使得故障转移和故障恢复变得容易。它还允许您在不关闭服务器的情况下执行维护、优化表、升级操作系统（或应用程序、硬件等）或其他任务。

例如，执行ALTER TABLE操作可能会锁定整个表，从而阻塞对该表的读写，这可能会花费很长时间并导致服务中断。然而，在主动-主动配置中，您可以先停止主动服务器上的备用复制线程（这样就不会在被动服务器上执行任何更新），然后在被动服务器上执行 ALTER 操作，交换角色，最后执行对前一个活动服务器执行 ALTER 操作。在服务器上启动复制线程（注4）。该服务器将读取中继日志并执行相同的 ALTER 语句。这可能需要很长时间，但这并不重要，因为服务器不提供任何活动查询。

注4：您可以通过设置=0暂时禁用二进制日志记录而不停止复制。某些语句（例如 TABLE）还支持 LOCAL 或 NO WRITE TO 选项来停止日志记录。

主动-被动主-主结构可以帮助规避MySQL的许多问题和限制，并且有一些工具可以完成此类操作。

让我们看看如何配置主-次-主服务器对。在两台服务器上执行以下设置后，它们将具有对称设置：

1. 确保两台服务器上的数据相同。

2、启用二进制日志，选择唯一的服务器D，并创建复制帐户。

3. 启用备用数据库更新的日志记录。正如您稍后将看到的，这是故障转移和故障恢复的关键。

4. 将被动服务器配置为只读，以防止与主动服务器上的更新可能发生冲突。这是可选的。

5. 启动每个服务器的MySQL 实例。

6. 将每个主数据库设置为另一个数据库的备用数据库，并开始使用新创建的二进制日志工作。

让我们看看在活动服务器上更新时会发生什么。更新被记录到二进制日志中，并通过复制中继到被动服务器的中继日志。被动服务器执行查询并将其记录到自己的二进制日志中（因为 : 选项已打开）。由于事件的服务器 ID 与活动服务器的服务器 ID 相同，因此活动服务器会忽略这些事件。您可以在下面的“修改主库”部分了解有关角色切换的更多信息。设置主动-被动主-主拓扑在某种意义上类似于创建热备份，但是您可以使用此“备份”来提高性能，例如，用它来执行读取操作、备份、“离线”维护、和升级等待。真正的热备份无法做到这些事情。但是，您不会获得比单个服务器更好的写入性能（稍后会详细介绍）。当我们讨论使用复制的场景和用途时，也会提到这种类型的复制。这是一种非常常见且重要的拓扑结构。

带备库的主主结构：这个相关配置是为每个主库添加一个备库如下图

这种配置的优点是增加了冗余并消除了不同地理位置的复制拓扑的单点站点故障问题。您还可以照常将读取查询分配给备用数据库。如果您在本地使用主-主架构进行故障转移，则此配置也很有用。当主数据库发生故障时，用备用数据库替换主数据库仍然是可行的，尽管这有点复杂。也可以将备用数据库指向不同的主数据库，但需要考虑增加的复杂性。

循环副本

如下图所示，双主结构实际上是环结构的一种特例。环形结构可以具有三个或更多个主数据库。每台服务器都是其之前服务器的备用数据库以及其之后服务器的备份数据库。服务器的主数据库。这种结构也称为环复制（）。环形结构没有双主结构的一些优点，例如对称配置和简单的故障转移，并且完全依赖于环上的每个可用节点，这大大增加了整个系统发生故障的机会。如果某个节点从环中移除，则该节点发起的事件将陷入无限循环：它们将永远围绕服务器链循环，因为唯一可以根据服务器 ID 过滤的服务器是创建的服务器。本次活动的服务器。总的来说，环形结构非常脆弱，应该避免。

通过在每个节点增加备库可以降低环复制的风险，如下图。但这只能防范服务器故障的风险；断电或其他影响网络连接的问题可能会破坏整个环路。

主库、分发主库、备库

之前我们提到，当备库足够多时，会导致主库负载过重。每个备库都会在主库上创建一个线程并执行命令。该命令读取二进制日志文件中的数据并将其发送到备用数据库。每个备用数据库都会重复此工作，并且它们不会共享转储资源。如果备库较多，并且有较大的事件，比如大型的LOAD DATA操作，主库的负载会大幅增加，甚至可能会因为备库在同一时刻请求相同的事件而导致内存不足而崩溃。同时。另一方面，如果备库请求的数据不在文件系统缓存中，则可能会导致大量的磁盘检索，这也会影响主库的性能并增加锁争用。

因此，如果需要多个备用数据库，一个好的方法是移除主数据库的负载并使用分布式主数据库。分发主库实际上是一个备用库，它的唯一目的是提取并提供主库的二进制日志。多个备用数据库连接到分发主数据库，从而减轻了原始主数据库的负担。为了避免对分发主数据库进行实际查询，可以将其表修改为存储引擎，如下图所示。

很难说什么时候备库数据达到多少就需要分布主库。根据一般准则，如果主数据库接近满载，则不应为其创建超过 10 个备用数据库。如果写操作较少，或者只复制部分表，主库可以提供更多的复制。另外，没有必要只使用一个分发主库。如果需要，可以使用多个分发主库复制到大量备用数据库，或者使用金字塔形的分发主库。在某些情况下，您可以通过设置 Tocol 来节省一些主库带宽。这有利于跨数据中心复制。

您还可以将主库分发用于其他目的，例如对二进制日志事件执行过滤和重写规则。这比在每个备用数据库上重复记录、重写和过滤要高效得多。

如果使用分发主数据库上的表，则可以支持更多备用数据库。虽然查询会在分发主数据库上执行，但由于表中没有数据，所以成本很小。该表的缺点是存在bug，比如某些情况下忘记将自增ID写入二进制日志。因此使用表格时要小心（注5）。一个常见的问题是如何保证分发服务器上的每个表都是一个存储引擎。如果有人在主数据库中创建一个表并指定不同的存储引擎怎么办？事实上，在备用数据库上使用不同的存储引擎总是会导致同样的问题。一个常见的解决方案是设置服务器的存储选项：这只会影响那些未指定存储引擎的TABLE语句。如果应用程序无法控制该拓扑，则该拓扑可能非常脆弱。

它可以通过选项禁用，并且表格将降级为...但您不能禁止或引擎。使用分布式主库的另一个主要缺点是不能使用备用库来替代主库。因为由于分发主库的存在，每个备库和原主库的二进制日志坐标不再相同（注6）。

注5：开始看MySQL和62829。一般来说，志果使用的是非标准存储引擎特性。最好查看打开或关闭的受影响的错误。

注6：您可以使用工具集中的pt-来创建一个粗略的全局事务D。这样可以很容易地找到多个服务器上的二进制日志的位置。因为“心跳表”本身记录了二进制日志的大致位置。

树形或金字塔形

如果要将主数据库复制到大量备用数据库。无论是将数据分布到不同的地方，还是提供更高的读取性能，使用金字塔结构都可以更好地进行管理，如图所示。这样设计的好处是减轻了主库的负担，就像上一节提到的主库的分布一样。缺点是中间层的任何错误都会影响多个服务器。如果每个备库都直连主库，就不会出现这样的问题。同样，中间层越多，处理故障就越困难和复杂。

定制复制解决方案

MySQL复制非常灵活，可以根据您的需求定制解决方案。典型的定制解决方案包括将过滤、分发和复制结合到不同的存储引擎。还可以使用“黑客”，例如，使用存储引擎从服务器复制或复制到服务器。可以根据需要设计。最大的限制是合理的监控和管理，以及你拥有的资源（网络带宽、CPU能力等）的限制。

选择性复制

为了利用访问局部性原则（1）并将需要读取的工作集保留在内存中，可以将少量数据复制到备用数据库。如果每个备库只拥有主库的部分数据，并将读分配给备库，则可以更好地利用备库的内存。并且每个备库只承担主库的一部分写负载，这样主库的能力更强，并且可以保证备库的延迟。

这种解决方案有点类似于我们下一章将讨论的水平数据分区，但它的优点是主数据库包含所有数据集，这意味着写查询不需要访问多个服务器。

如果读操作在备库上找不到数据，也可以通过主库查询。即使无法从备库读取所有数据，也可以减轻主库的大量读取负担。最简单的方法是在主数据库上将数据划分到不同的数据库中。然后将每个数据库复制到不同的备用数据库。

例如，如果需要将公司各个部门的数据复制到不同的备用数据库中，则可以创建名为 sales 等的数据库，每个备用数据库使用 wild 选项来限制给定数据库的数据。以下是销售数据库的配置： ble sales.% 也可以通过分发主数据库进行分发。例如，如果要通过缓慢或昂贵的网络从负载较重的数据库复制部分数据，则可以使用包含表和过滤规则的本地分发主数据库。分发主数据库可以通过进行复制，去除不必要的日志。这可以避免在主数据库上设置不安全的日志记录选项，并且无需将所有数据传输到远程备用数据库。

分割功能

许多应用程序混合了在线事务处理 (OLTP) 和在线数据分析 (OLAP) 查询。 OLTP 查询较短且具有事务性，而 OLAP 查询通常较大、缓慢且不需要绝对最新的数据。这两个查询给服务器带来了完全不同的负担，因此它们需要不同的配置，甚至可能使用不同的存储引擎或硬件。

一种常见的方法是将数据从 OLTP 服务器复制到专门为 OLAP 工作负载准备的备用数据库。这些备用数据库可以具有不同的硬件、配置、索引或不同的存储引擎。如果您决定在备用数据库上执行 OLAP 查询，则可能需要忍受更大的复制延迟或降低备用数据库的服务质量。这意味着在非专用备用数据库上执行某些任务可能会导致性能不可接受，例如执行长时间运行的查询。不需要做任何特殊的配置，只是需要选择忽略主数据库上的一些数据，前提是可以实现显着的改进。即使通过复制过滤器过滤掉一小部分数据也会减少 I/O 和缓存活动。

数据归档

备库上可以实现数据归档，即在主库上删除的数据可以在备库上保留。通过主库上的语句删除数据，可以通过保证语句不传到备库来实现。常见的做法有两种：一种是在主数据库上选择性地禁用二进制日志记录，另一种是在备用数据库上使用 db 规则（是的，这两种方法都很危险）。第一种方法需要先将其设置为0，然后清理数据。这种方法的优点是不需要在备库中进行任何配置。由于$QL语句根本不记录在二进制日志中，因此效率会略有提高。最大的缺点是主数据库中的修改不会被记录，因此二进制日志不能用于审计或时间点数据恢复。

此外，还需要超级许可。第二种方法是在清理数据之前对主数据库上的特定数据库使用 USE 语句。例如，您可以创建一个名为purge的数据库，然后在备库的f文件中设置=purge并重新启动服务器。备用数据库将忽略使用 USE 语句指定的数据库。这种方法没有第一种方法的缺点，但是还有一个小缺点：备用数据库需要读取它不需要的事件。另外，有人可能对puge数据库执行非清理查询，导致备用数据库无法重放事件。 pt-in 支持以上两种方式。第三种方法是使用过滤复制事件。但正如之前提到的，这是一个危险的操作。

使用备用数据库作为全文检索

许多应用程序需要组合事务和全文检索。然而，在撰写本书时，仅支持全文检索，但不支持事务（0.6 中有一个实现全文检索的实验室预览版本，但尚未 GA）。常见的做法是配置一个备用数据库，设置一些表作为存储引擎，然后创建全文索引并进行全文搜索查询。这样就避免了主库同时使用事务型和非事务型存储引擎带来的复制问题，减轻了主库维护全文索引的负担。

只读备用数据库

许多机构选择将备用数据库设置为只读，以防止意外修改备用数据库而中断复制。这可以通过设置选项来实现。它将禁止大多数写入操作，但具有超级权限的用户的复制线程和小吃操作除外。只要超级权限不是也不应该给予普通用户，这应该是一个很好的方法。

模拟多主复制

目前MySQL不支持多主库复制（一个备库有多个主库）。不过，可以通过将一个备库依次指向多个主库来模拟这种结构。例如，可以先将备库指向主库A并运行一段时间，然后指向主库B并运行一段时间，然后再次切换回主库A。这种方法的有效性取决于数据以及两个主数据库导致备用数据库完成所需的工作量。如果主服务器上的负载较低并且主服务器之间不存在更新冲突，则此方法效果很好。需要一些额外的工作来跟踪每个主节点的二进制日志坐标。您可能还需要确保备用数据库的 I/O 线程在每个周期提取的数据多于所需的数据。否则，由于每个周期重复提取和丢弃大量数据，网络流量和主数据库的开销可能会显着增加。您还可以使用存储引擎表来模拟使用主-主（或环）复制结构和备用数据库，如图所示。

在这种配置中，两个主服务器都有自己的数据，但也包含对方的表，但对方的表使用存储引擎来避免在其中存储实际数据。备用数据库可以连接到任何主数据库。备库不使用存储引擎，因此对两个主库都有效。事实上，它并不一定需要主主拓扑来实现。您可以简单地复制到，然后复制 from:2 到备用数据库。如果对从中复制的数据使用存储引擎，则不会包含任何数据，如图所示。

这些配置方法经常会遇到一些常见的问题，比如更新冲突或者创建表时显式指定存储引擎等。另一种选择是使用 ，我们将在本章后面讨论。创建日志服务器 MySQL 复制的另一个用途是创建没有数据的日志服务器。它的唯一目的是使重播和/或过滤二进制日志事件变得更容易。正如本章后面所讨论的，它对于在崩溃后重新启动复制很有帮助。它对于时间点恢复也很有帮助，我们将在第 15 章中讨论这一点。

假设您有一组二进制日志或中继日志（可能从备份或崩溃的服务器获取），并且您希望能够重播这些日志中的事件。您可以使用工具从中提取事件，但更方便有效的方法是配置一个不带任何数据的MySQL实例，并让它认为这些二进制日志是它自己的。如果只是暂时需要，可以从http://获取MySQL沙箱脚本来创建日志服务器。由于不需要执行二进制日志记录，因此日志服务器不需要任何数据。其目的只是向其他服务器提供数据（但仍然需要复制帐户）。让我们看看这个策略是如何工作的（稍后将展示一些相关的应用程序）。假设日志的名称为-bin.、-bin.等，将这些日志放到日志服务器的日志文件中，假设是war/log/mysql。然后在启动服务器之前编辑 f 文件，如下所示：

=/var/log/mysql/-bin

=/var/log/mysql/-bin.index

服务器不会自动发现日志文件，因此日志的索引文件也需要更新。

#/bin/1s -1 /var/log/mysql/-bin.[o-9]*/var/log/mysql/-bin.index

确保运行 MySQL 的帐户可以读写日志索引文件。您现在可以启动日志服务器并确保它通过 LOGS 命令找到日志文件。为什么使用日志服务器比实施恢复更好？有以下几个原因：

1、复制作为二进制日志的一种应用方法，已经经过大量用户的测试，可以证明是可行的。它不能保证像复制一样工作，并且二进制日志中的数据更新可能无法正确生成。

2.复制速度更快，因为不需要从日志中导出语句并将其发送到MySQL。

3、复制过程易于观察。使处理错误变得更加容易。例如，您可以跳过无法执行的语句。

4、复制事件过滤更方便。

5. 有时由于日志格式的改变，导致二进制日志无法读取。