MySQL 8 复制（三）——延迟复制与部分复制

用户1148526

发布于 2019-07-02 14:09:51

3.6K0

发布于 2019-07-02 14:09:51

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

一、延迟复制

1. 延迟复制简介

2. 延迟复制时间戳

3. 监控延迟复制

二、部分复制

1. 简介

2. 评估数据库级复制和二进制日志选项

3. 评估表级复制选项

4. 复制规则应用

5. 部分复制示例

三、主从切换

1. 计划内切换

2. 计划外切换

一、延迟复制

1. 延迟复制简介

    即使通常MySQL复制很快，但MySQL缺省的复制存在延迟，并且用户无法缩短延迟时间。另一方面，有时却需要特意增加复制的延迟。设想这样一种场景，用户在主库上误删除了一个表，并且该操作很快被复制到从库。当用户发现这个错误时，从库早就完成了该事件重放。此时主库、从库都没有那个被误删的表了，如何恢复？如果有备份，可以幸运地从备份恢复，丢失的数据量取决于备份的新旧和从备份时间点到表被删除时间点之间该表上数据的变化量。如果没有备份呢？这种情况下，延迟复制或许可以帮上忙，作为一种恢复数据的备选方案。如果在发现问题时，从库还没有来得及重放相应的中继日志，那么就有机会在从库获得该表，继而进行恢复。这里忽略一些其它数据恢复方案，例如已经存在类似Oracle闪回技术（Flashback）在MySQL上的实现，实现方式为解析相应的二进制日志事件，生成反向的SQL语句。这些程序多为个人作品，并没有被加入MySQL发行版本中，因此在易用性、适用性、可靠性等方面还不能与原生的功能相提并论。

    MySQL支持延迟复制，以便从库故意执行比主库晚至少在指定时间间隔的事务。在MySQL 8.0中，延迟复制的方法取决于两个时间戳：immediate\_commit\_timestamp和original\_commit\_timestamp。如果复制拓扑中的所有服务器都运行MySQL 8.0.1或更高版本，则使用这些时间戳测量延迟复制。如果从库未使用这些时间戳，则执行MySQL 5.7的延迟复制。

    复制延迟默认为0秒。使用CHANGE MASTER TO MASTER\_DELAY = N语句将延迟设置为N秒。从主库接收的事务比主库上的提交至少晚N秒才在从库上执行。每个事务发生延迟（不是以前MySQL版本中的事件），实际延迟仅强制在gtid\_log\_event或anonymous\_gtid\_log\_event事件上。二进制日志中的每个GTID事务始终都以Gtid\_log\_event开头，匿名事务没有分配GTID，MySQL确保日志中的每个匿名事务都以Anonymous\_gtid\_log\_event开头。对于事务中的其它事件，不会对它们施加任何等待时间，而是立即执行。注意，START SLAVE和STOP SLAVE立即生效并忽略任何延迟，RESET SLAVE将延迟重置为0。        例如，下面将实验环境中一主两从半同步复制中的一个从库设置为延迟60秒复制：

mysql> change master to master_delay = 60;
ERROR 3085 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD FOR CHANNEL '' first.
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay = 60;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql>

    联机设置延迟复制时，需要先停止sql\_thread线程。现在主库执行一个事务，观察从库的变化：

-- 主
mysql> create table test.t3(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> 

-- 从
mysql> desc test.t3;
ERROR 1146 (42S02): Table 'test.t3' doesn't exist
mysql> desc test.t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

    主库上建立了一个表test.t3，DDL语句自成一个事务。60秒后，从库上才出现该表。

    从库上performance\_schema模式下的replication\_applier\_configuration.desired\_delay表列显示使用master\_delay选项配置的延迟，replication\_applier\_status.remaining\_delay表列显示剩余的延迟秒数。

-- 从
mysql> select desired_delay from performance_schema.replication_applier_configuration;
+---------------+
| desired_delay |
+---------------+
|            60 |
+---------------+
1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

mysql>

-- 主
mysql> drop table test.t3;
Query OK, 0 rows affected (0.02 sec)

mysql> 

-- 从
mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              54 |
+-----------------+
1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              23 |
+-----------------+
1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              16 |
+-----------------+
1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

mysql>

    延迟复制可用于多种目的：

防止用户在主库上出错。延迟复制时，可以将延迟的从库回滚到错误之前的时间。
测试滞后时系统的行为方式。例如，在应用程序中，延迟可能是由从库设备上的重负载引起的。但是，生成此负载级别可能很困难。延迟复制可以模拟滞后而无需模拟负载。它还可用于调试与从库滞后相关的条件。
检查数据库过去的快照，而不必重新加载备份。例如，通过配置延迟为一周的从库，如果需要看一下最近几天开发前的数据库样子，可以检查延迟的从库。

2. 延迟复制时间戳

    MySQL 8.0提供了一种新方法，用于测量复制拓扑中的延迟，或称复制滞后。该方法取决于与写入二进制日志的每个事务（不是每个事件）的GTID相关联的以下时间戳：

original_commit_timestamp：将事务写入（提交）到主库二进制日志之后的自1970年1月1日00:00:00 UTC以来的微秒数。
immediate_commit_timestamp：将事务写入（提交）到从库的二进制日志之后的自1970年1月1日00:00:00 UTC以来的微秒数。

    mysqlbinlog的输出以两种格式显示这些时间戳，从epoch开始的微秒和TIMESTAMP格式，后者基于用户定义的时区以获得更好的可读性。例如：

#190516 15:12:18 server id 1125  end_log_pos 239 CRC32 0xc1ebcb7c       Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1557990738835397   immediate_commit_timestamp=1557990738838735     transaction_length=192
# original_commit_timestamp=1557990738835397 (2019-05-16 15:12:18.835397 CST)
# immediate_commit_timestamp=1557990738838735 (2019-05-16 15:12:18.838735 CST)
/*!80001 SET @@session.original_commit_timestamp=1557990738835397*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 239

    通常，original\_commit\_timestamp在应用事务的所有副本上始终相同。在主从复制中，主库二进制日志中事务的original\_commit\_timestamp始终与其immediate\_commit\_timestamp相同。在从库的中继日志中，事务的original\_commit\_timestamp和immediate\_commit\_timestamp与主库的二进制日志中的相同，而在其自己的二进制日志中，事务的immediate\_commit\_timestamp对应于从库提交事务的时间。

    在组复制设置中，当原始主服务器是组的成员时，将在事务准备好提交时生成original\_commit\_timestamp。再具体说，当事务在原始主服务器上完成执行并且其写入集准备好发送给该组的所有成员以进行认证时，生成original\_commit\_timestamp。因此，相同的original\_commit\_timestamp被复制到所有服务器应用事务，并且每个服务器使用immediate\_commit\_timestamp在其自己的二进制日志中存储本地提交时间。

    组复制中独有的视图更改事件是一种特殊情况。包含该事件的事务由每个服务器生成，但共享相同的GTID。因此，这种事务不是先在主服务器中执行，然后复制到该组其它成员，而是该组的所有成员都执行并应用相同的事务。由于没有原始主服务器，因此这些事务的original\_commit\_timestamp设置为零。

3. 监控延迟复制

    在MySQL 8之前的老版本中，监控复制的延迟（滞后）最常用的方法之一是依赖于show slave status输出中的seconds\_behind\_master字段。但是，当使用比传统主从复制更复杂的复制拓扑，例如组复制时，此度量标准不再适用。MySQL 8中添加的immediate\_commit\_timestamp和original\_commit\_timestamp可提供有关复制延迟的更精细的信息。监控支持这些时间戳的复制延迟的推荐方法是使用以下performance\_schema模式中的表。

replication_connection_status：与主服务器连接的当前状态，提供有关连接线程排队到中继日志中的最后和当前事务的信息。
replication_applier_status_by_coordinator：协调器线程的当前状态，仅在使用多线程复制时显示该信息，提供有关协调器线程缓冲到工作队列的最后一个事务的信息，以及当前正在缓冲的事务。
replication_applier_status_by_worker：应用从主服务器接收事务的线程的当前状态，提供有关应用程序线程或使用多线程复制时每个工作线程应用的事务信息。

    使用这些表，可以监控相应线程处理的最后一个事务以及该线程当前正在处理的事务的信息，包括：

事务的GTID。
从库中继日志中检索的事务的original_commit_timestamp和immediate_commit_timestamp。
线程开始处理事务的时间。
对于上次处理的事务，线程完成处理它的时间。

    除Performance Schema表之外，show slave status的输出还有三个字段与延迟复制有关：

SQL_Delay：非负整数，表示使用CHANGE MASTER TO MASTER_DELAY = N配置的复制延迟，以秒为单位。与performance_schema.replication_applier_configuration.desired_delay值相同。
SQL_Remaining_Delay：当Slave_SQL_Running_State等待主执行事件后的MASTER_DELAY秒时，该字段包含一个整数，表示延迟剩余的秒数。在它他时候，此字段为NULL。与performance_schema.replication_applier_status.remaining_delay值相同。
Slave_SQL_Running_State：一个字符串，指示SQL线程的状态（类似于Slave_IO_State）。该值与SHOW PROCESSLIST显示的SQL线程的State值相同。

    当从库的SQL线程在执行事件之前等待延迟时，SHOW PROCESSLIST将其状态值显示为：Waiting until MASTER\_DELAY seconds after master executed event。

二、部分复制

    到目前为止，我们讨论的都是MySQL实例级的复制，复制拓扑中的所有服务器都包含整个实例的全部数据集，主库的任何数据变化都会原封不动地再从库进行重放。本节说明另一种不同于此的复制——部分复制。

1. 简介

    如果主库未将修改数据的SQL语句或变化的数据行写入其二进制日志，则不会复制该事件。如果主库记录了二进制日志并将其中的事件发送到从库，从库也可以自己确定是执行它还是忽略它。这就是实现MySQL部分复制的两种方式。

    主库上，可以使用--binlog-do-db和--binlog-ignore-db选项来控制要记录更改的数据库，以控制二进制日志记录。但是不应该使用这些选项来控制复制哪些数据库和表，推荐的方法是在从库上使用过滤来控制从库上执行的事件。在从库端，是否执行接收事件的决定是根据从库上启动的--replicate-\* 选项做出的。在MySQL 5.7中，可以使用CHANGE REPLICATION FILTER语句动态设置由这些选项控制的过滤器，而不用重启MySQL实例。无论是使用--replicate-\* 选项在启动时创建还是通过CHANGE REPLICATION FILTER运行从库，管理此类过滤器的规则都是相同的。注意，复制过滤器不能用于为组复制，因为在某些服务器上过滤事务会使组无法就一致状态达成协议。

    缺省时没有--replicate-\* 选项，从库执行接收的所有事件，这是最简单的情况。否则，结果取决于给定的特定选项。首先检查数据库级选项（--replicate-do-db，--replicate-ignore-db），如果未使用任何数据库级选项，则继续检查可能正在使用的任何表级选项，未匹配的选项不会被执行。对于仅影响数据库的语句（即CREATE DATABASE，DROP DATABASE和ALTER DATABASE），数据库级选项始终优先于任何--replicate-wild-do-table选项。换句话说，对于此类语句，当且仅当没有适用的数据库级选项时，才会检查--replicate-wild-do-table选项。

    为了更容易确定选项集合会产生什么影响，建议避免混合使用“do”和“ignore”选项或通配符和非通配符选项。如果指定了任何--replicate-rewrite-db选项，则在测试--replicate- \*过滤规则之前应用它们。所有复制过滤选项都遵循相同的区分大小写规则，这些规则适用于MySQL服务器中其它位置的数据库和表的名称，包括lower\_case\_table\_names系统变量的效果。

2. 评估数据库级复制和二进制日志选项

    在评估复制选项时，从库首先检查是否存在适用的--replicate-do-db或--replicate-ignore-db选项。使用--binlog-do-db或--binlog-ignore-db时，过程类似，只是在主库上检查选项。检查匹配的数据库取决于正在处理的事件的二进制日志格式。如果使用ROW格式，则要更改数据的数据库是要检查的数据库。如果使用STATEMENT格式记录了语句，则默认数据库（使用USE语句指定）是要检查的数据库。看下面的实验。（1）在从库设置过滤器为replicate\_do\_db=(db2)。

-- 从
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_do_db=(db2);
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql>

（2）主库设置缺省数据库为db1，然后删除db2.t1。

-- 主
mysql> set binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

mysql> use db1;
Database changed
mysql> drop table db2.t1;
Query OK, 0 rows affected (0.01 sec)

mysql>

（3）检查从库的复制执行情况

-- 从
mysql> desc db2.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql>

    从库上并没有删除db2.t1。原因是在STATEMENT格式，过滤器没有匹配缺省数据库db1。（4）改变主的缺省数据库为db2，然后创建表db1.t1。

-- 主
mysql> use db2;
Database changed
mysql> create table db1.t1(a int);
Query OK, 0 rows affected (0.03 sec)

mysql>

（5）检查从库的复制执行情况

-- 从
mysql> desc db1.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

    因为过滤器匹配了缺省数据库，所以语句在从库上执行。（6）将主库的二进制日志格式改为ROW，再进行测试。

-- 主
mysql> use db1;
Database changed
mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> create table db2.t1(a int);
Query OK, 0 rows affected (0.02 sec)

mysql>

    此时从库已经存在db2.t1，并且replicate\_do\_db=(db2)，按照文档的说法，此时会执行复制，预想的结果是因为从库上表已经存在而报错，然而并没有。

-- 主
mysql> drop table db2.t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table db2.t1(a varchar(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into db2.t1 values('aaa');
Query OK, 1 row affected (0.01 sec)

mysql>

    当主库删除表db2.t1，而从库却没删除。再主库建立新表db2.t1，与从库已存在的db2.t1结构不兼容。向主库的db2.t1插入记录后，从库的复制却报错了：

Last_SQL_Error: Column 0 of table 'db2.t1' cannot be converted from type 'varchar(20(bytes))' to type 'int(11)'

    可以看到，当缺省数据库与replicate\_do\_db不同时，create table、drop table语句不会被复制到从库，但DML语句会正常复制。注意，行格式只记录DML语句，即使binlog\_format = ROW，DDL语句也始终记录为语句。因此，始终根据基于语句的复制规则筛选所有DDL语句。这意味着必须使用USE语句显式选择默认数据库，以便应用DDL语句。数据库级过滤选项的检查流程如图1所示。

图1 数据库级过滤选项的检查流程

    重要的是，此阶段通过的语句尚未实际执行，在检查了所有表级选项（如果有）之后，结果允许执行该语句，语句才会真正执行。二进制日志选项的检查步骤简单描述如下：

（1）是否有--binlog-do-db或--binlog-ignore-db选项？

是，继续第（2）步；

否，记录语句并退出。

（2）是否有默认数据库（USE选择了任何数据库）？

是，继续第（3）步；

否，忽略语句并退出。

（3）有一个默认数据库。是否有--binlog-do-db选项？

是，它们中的任何一个都匹配数据库吗？

 是，记录该语句并退出；

 否，忽略语句并退出。

否，继续执行第4步。

（4）是否有任何--binlog-ignore-db选项与数据库匹配？

是，忽略该语句并退出；

否，记录语句并退出。

    在确定CREATE DATABASE、ALTER DATABASE和DROP DATABASE语句是记录还是忽略时，正在创建、更改或删除的数据库将替换缺省数据库。--binlog-do-db有时可能意味着“忽略其他数据库”。例如，使用基于语句的日志记录时，仅使用--binlog-do-db = sales运行的服务器不会写入默认数据库与sales不同的二进制日志语句。使用具有相同选项的基于行的日志记录时，服务器仅记录那些更改sales库数据的更新。

3. 评估表级复制选项

    仅当满足以下两个条件之一时，从库才会检查并评估表选项：

没有数据库选项。
有数据库选项但与语句不匹配。

    作为初始规则，如果主库启用了基于语句的复制并且语句出现在存储函数内，则从库执行语句并退出。对于基于语句的复制，复制事件表示语句，构成给定事件的所有更改都与单个SQL语句相关联。对于基于行的复制，每个事件表示单个表行中的更改，因此单个语句（如UPDATE mytable SET mycol = 1）可能会产生许多基于行的事件。从事件角度来看，检查表选项的过程对于基于行和基于语句的复制都是相同的。

    到达表级选项检查时，如果没有表选项，从库简单地执行所有事件。如果有任何--replicate-do-table或--replicate-wild-do-table选项，则事件必须匹配其中一个才能执行，否则它会被忽略。如果存在任何--replicate-ignore-table或--replicate-wild-ignore-table选项，则执行所有事件，但匹配任何这些选项的事件除外。图2详细地描述了表级选项评估过程，起点是数据库级选项的评估结束，如上节图1所示。

图2 表级选项评估过程

    如果单个SQL语句中同时含有--replicate-do-table或--replicate-wild-do-table选项包含的表，以及--replicate-ignore-table或--replicate-wild-ignore-table选项包含的另一个表，如果语句是使用binlog\_format = ROW记录的DML语句，更新的表和忽略的表都可以按预期复制，该更新的行更新，该忽略的行忽略。如果是基于语句的复制，无论是DDL还是DML语句，如果匹配了--replicate-do-table中的表，语句将被复制，包括--replicate-ignore-table中的表也会更新。这点与MySQL 8官方文档中的描述不符：

Statement-based replication stops if a single SQL statement operates on both a table that is included by a --replicate-do-table or --replicate-wild-do-table option, and another table that is ignored by a --replicate-ignore-table or --replicate-wild-ignore-table option. The slave must either execute or ignore the complete statement (which forms a replication event), and it cannot logically do this. This also applies to row-based replication for DDL statements, because DDL statements are always logged as statements, without regard to the logging format in effect. The only type of statement that can update both an included and an ignored table and still be replicated successfully is a DML statement that has been logged with binlog_format=ROW.

    下面简单验证一下表级的过滤复制规则。（1）从库中设置表级复制过滤。

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> change replication filter replicate_do_table = (db1.t1), replicate_ignore_table = (db1.t2);
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql>

（2）在主库上执行更新，并在从库检查复制情况。

-- 主
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> create table db1.t1(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table db1.t2(a int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into db1.t1 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.t2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> 

-- 从
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

    可以看到，create database 以及和表匹配的DDL、DML语句正常复制。（3）在从库执行同样地语句使主从数据一致，以便继续实验。

-- 从
mysql> create table db1.t2(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into db1.t2 values (1);
Query OK, 1 row affected (0.01 sec)

mysql>

    在MySQL主从复制中，为主从保证数据一致性，通常将从库设置为只读（read\_only=on），这里只是为了方便后续实验才在从库执行写操作。（4）在主库上执行正常的单表更新，并在从库检查复制情况。

-- 主
mysql> update db1.t1 set a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update db1.t2 set a=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

-- 从
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select * from db1.t2;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

    符合预期，db1.t1正常复制，db1.t2被忽略。（5）在一句更新语句中同时包含replicate\_do\_table与replicate\_ignore\_table中的表

-- 主
mysql> update db1.t1 t1, db1.t2 t2 set t1.a=3, t2.a=3;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> 

-- 从
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> select * from db1.t2;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

    在binlog\_format=row时，两个表的复制符合预期，db1.t1正常复制，db1.t2被忽略。将二进制日志格式换成statement再试。

-- 主
mysql> set binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

mysql> update db1.t1 t1, db1.t2 t2 set t1.a=4, t2.a=4;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql>

-- 从
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql> select * from db1.t2;
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>

    这个语句还是复制成功了，包括replicate\_ignore\_table中的db1.t2，也正常更新。（6）在主库同时删除db1.t1和db1.t2表

-- 主
mysql> drop table db1.t1,db1.t2;
Query OK, 0 rows affected (0.04 sec)

mysql>

-- 从
mysql> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> select * from db1.t2;
ERROR 1146 (42S02): Table 'db1.t2' doesn't exist
mysql>

    从上复制成功，db1.t2也被删除了。

4. 复制规则应用

    本节提供一些有关复制过滤选项不同组合的说明和用法示例。下表给出了复制过滤规则类型的一些典型组合：

条件（选项类型）	结果
没有--relicate-*选项	从库执行从主库接收的所有事件。
有--replicate-*-db选项，但没有表选项	从服务器使用数据库选项接受或忽略事件。它执行这些选项允许的所有事件，因为没有表限制。
有--replicate-*-table选项，但没有数据库选项	由于没有数据库条件，因此在数据库检查阶段接受所有事件。从库仅根据表选项执行或忽略事件。
数据库和表选项的组合	从库使用数据库选项接受或忽略事件。然后，它根据表选项评估这些选项允许的所有事件。这有时会导致结果看似违反直觉，根据使用的是基于语句还是基于行的复制，结果可能会有所不同。

    下面是一个更复杂的示例，我们检查基于语句和基于行的设置的结果。假设主库上有两个表db1.t1和db2.t2，并且从库在运行时只有以下选项：

replicate-ignore-db = db1
replicate-do-table  = db2.t2

    执行下面的步骤初始化复制。

（1）从库去掉以前的过滤规则

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> change replication filter replicate_do_table = (), replicate_ignore_table = ();
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql>

（2）主库建立对象

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.01 sec)

mysql> create table db1.t1(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table db2.t2(a int);
Query OK, 0 rows affected (0.02 sec)

mysql>

（3）从库增加过新的滤规则

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_ignore_db = (db1), replicate_do_table = (db2.t2);
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql>

    现在我们在主库上执行以下语句：

mysql> set binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

mysql> use db1;
Database changed
mysql> insert into db2.t2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql>

    查看从库：

mysql> select * from db2.t2;
Empty set (0.00 sec)

mysql>

    从库的db2.t2表没有数据。USE语句使db1成为默认数据库，与--replicate-ignore-db选项匹配，因此忽略INSERT语句，不检查表选项。

    用row方式再执行一遍：

mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> use db1;
Database changed
mysql> insert into db2.t2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql>

    查看从库：

mysql> select * from db2.t2;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

    这回db2.t2复制了一条数据。使用基于行的复制时，缺省数据库对从库读取数据库选项的方式没有影响。因此，USE语句对如何处理--replicate-ignore-db选项没有影响。此选项指定的数据库与INSERT语句更改数据的数据库不匹配，因此从库继续检查表选项。--replicate-do-table指定的表与要更新的表匹配，并插入行。

5. 部分复制示例

    在某些情况下，可能只有一个主库（服务器），并且希望将不同的数据库复制到不同的从库（服务器）。例如，可能希望将不同的销售数据分发到不同的部门，以帮助在数据分析期间分散负载。如图3所示，将主库的db1复制到从库1，db2复制到从库2。

图3 将主库上不同的数据库复制到不同的从库

    实现时可以先配置正常的一主两从复制，然后通过在每个从库上使用--replicate-wild-do-table配置选项来限制每个从库执行的事件。注意，在使用基于语句的复制时，不应将--replicate-do-db用于此目的，因为基于语句的复制会导致此选项的影响因当前所选的数据库而异。这也适用于混合格式复制，因为这可能使用基于语句的格式复制某些更新。

    在从库1执行：

stop slave sql_thread;
change replication filter replicate_wild_do_table=('db1.%');
start slave sql_thread;

    在从库2执行：

stop slave sql_thread;
change replication filter replicate_wild_do_table=('db2.%');
start slave sql_thread;

    此配置中的每个从库从主库接收整个二进制日志，但仅执行二进制日志中--replicate-wild-do-table选项所包含的数据库和表的那些事件。        测试：

-- 主
create database db1;
create database db2;
create table db1.t1(a int);
create table db2.t2(a int);
insert into db1.t1 select 1;
insert into db2.t2 select 2;

-- 从1
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from db2.t2;
ERROR 1049 (42000): Unknown database 'db2'
mysql> 

-- 从2
mysql> select * from db1.t1;
ERROR 1049 (42000): Unknown database 'db1'
mysql> select * from db2.t2;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql>

    数据如预期复制，db1和db2的数据分别复制到从库1和从库2。下面看一下routine的复制情况。

-- 主
delimiter //
create procedure db1.p1 ()
begin
select 1;
end;
//
delimiter ;

-- 从1
mysql> call db1.p1();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

-- 从2
mysql> call db1.p1();
ERROR 1305 (42000): PROCEDURE db1.p1 does not exist
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.125
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000011
          Read_Master_Log_Pos: 73101
               Relay_Log_File: hdp4-relay-bin.000047
                Relay_Log_Pos: 1591
        Relay_Master_Log_File: binlog.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: db2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1049
                   Last_Error: Error 'Unknown database 'db1'' on query. Default database: ''. Query: 'CREATE DEFINER=`wxy`@`%` PROCEDURE `db1`.`p1`()
begin
select 1;
end'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 72869
              Relay_Log_Space: 2194
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1049
               Last_SQL_Error: Error 'Unknown database 'db1'' on query. Default database: ''. Query: 'CREATE DEFINER=`wxy`@`%` PROCEDURE `db1`.`p1`()
begin
select 1;
end'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1125
                  Master_UUID: 8eed0f5b-6f9b-11e9-94a9-005056a57a4e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 190524 15:20:03
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

mysql>

    在主库上的db1建立存储过程，从库1正常复制，但从库2却报错了，它还是执行了复制，只是因为缺少db1数据库而复制报错。可见，replicate\_wild\_do\_table只对表起作用，而对于routine无效，主库上所有库的routine都会在所有从库执行复制。

    如果在复制开始之前有必须同步到从库的数据，则可以将所有数据同步到每个从库，然后在从库删除不想保留的数据库或表。或者使用mysqldump为每个数据库创建单独的转储文件，并在每个从库上加载相应的转储文件，例如：

# 从库1
mysqldump --single-transaction --databases db1 --master-data=1 --host=172.16.1.125 --user=wxy --password=123456 --apply-slave-statements | mysql -uroot -p123456 
# 从库2
mysqldump --single-transaction --databases db2 --master-data=1 --host=172.16.1.125 --user=wxy --password=123456 --apply-slave-statements | mysql -uroot -p123456

    这两种方式都可与联机进行，并且对主库的影响不大。整实例复制与mysqldump的联机复制具体步骤参见“[MySQL 8 复制（一）——异步复制](https://wxy0327.blog.csdn.net/article/details/90081518)”。

三、主从切换

    有时需要把从库指向一个新的主库。例如滚动升级服务器，或者主库出现问题时需要把一台从库转换成主库。可以使用CHANGE MASTER TO语句告诉从库连接新的主库。从库不检查主库上的数据库是否与从库上现有的数据库兼容，它只是从新主库二进制日志中的指定坐标开始读取和执行事件。这种主从角色转换可以粗略地分为计划内和计划外两种。

1. 计划内切换

    计划内主从切换简单说就是事先有准备的维护性操作，通常需要执行以下步骤：

（1）停止当前主库的所有写操作。如果可以，最好能关闭所有的客户端连接。

（2）通过flush tables with read lock在主库上停止所有活跃的写入，这一步是可选的。也可以在主库上设置read_only选项。从这一刻开始，应该禁止向即将被替换的主库做任何写入。因为一旦它不是主库，写入就意味着数据丢失。注意，即使设置read_only也不会阻止当前已存在的事务继续提交。为了更好地保证这一点，可以“kill”所有打开的事务，这将会真正地结束所有写入。例如可以编写以下内容的kill_mysql_session.sh脚本：

#!/bin/bash

source ~/.bashrc

rm -rf /tmp/kill.sql
mysql -u root -p123456 -P3306 -h127.0.0.1 -e "select * into outfile '/tmp/kill.sql' from (select 'set global read_only=on;' union all select concat('kill ',id,';') from information_schema.processlist where command='sleep' ) t; "

mysql -u root -p123456 -P3306 -h127.0.0.1 < /tmp/kill.sql

    之后就可以执行kill\_mysql\_session.sh杀掉会话。较新版本的MySQL，需要在配置文件中设置secure\_file\_priv参数，并重启mysql后才能执行数据导出操作。

（3）选择一个备库作为新的主库，并确保它已经完全跟上主库（执行完所有中继日志）。

（4）确保新主库和旧主库数据一致。可选。例如在两个库上执行“mysqldump -uroot --skip-dump-date | md5sum”，检查校验和是否相同。

（5）在新主库上执行stop slave。

（6）在新主库上执行reset slave all，使其断开与老主库的连接。

（7）执行show master status记录新主库的二进制日志坐标。

（8）确保其它从库已经追上就主库。

（9）关闭旧主库。

（10）如果需要，在新主库上执行set global read_only=on使其可写。

（11）在每台从库上执行change master to语句，使用前面第（7）步获得的二进制坐标，来指向新主库。

（12）将客户端连接到新主库。

2. 计划外切换

    当主库崩溃时，需要提升一个从库来替代它。如果只有一个从库，那别无选择，只能使用这台备库。但如果有超过一个的从库，就需要做一些额外工作。对主从拓扑结构中的从库进行提升的过程可以简单描述如下：

（1）让所有从库执行完其从崩溃前的旧主库获得的中继日志。

（2）选择并设置新主库。

（3）查找其它所有从库最后执行的事件，在新主库上对应的二进制坐标。

（4）其它所有从库重置复制，按上一步获得的二进制坐标连接到新主库，启动新复制。

    这其中隐藏着很多细节，因此用一个具体的例子进行详细说明。假设一个标准的MySQL 8的一主两从复制结构，主库标记为M（172.16.1.125），两个从库分别标记为为S1（172.16.1.126）、S2（172.16.1.127）。全部使用MySQL 8缺省的复制相关配置：

log_bin=ON

binlog_format=ROW

log_slave_updates=ON

gtid_mode=OFF

两个从库的read_only=ON。

    我们在这个复制场景下，模拟主库服务器不可用时，如何提升一个从库称为新主库，并将其它从库指向新主库，目标是保证最少的事务丢失。以下是实验步骤。

（1）在M上做一些数据更新

create database db1;
use db1;
create table t1(a int);
insert into t1 values (1),(2),(3);
update t1 set a=3 where a=1;
delete from t1 where a=3;
insert into t1 select 1;  -- 用于查看同样地SQL语句，event是否一样
insert into t1 select 1;
insert into t1 select 1;
commit;

（2）停止S2复制，模拟S2是落后的从库

stop slave;

（3）刷新S1的日志

flush logs;

    由于服务器重启、不同的配置、日志轮转或者flush logs命令等原因，会造成同一事件在不同的服务器上有不同的偏移量，这步模拟此种情况。

（4）在M再做一些更新

insert into t1 values (10),(11),(12);
delete from t1 where a=1;
commit;

    此时S2的复制已经停止，上面的数据更新对它不可知。S1正常复制，以此人为模拟两个从库的快慢之分。

（5）停止M，模拟主库损坏

mysqladmin -uwxy -p shutdown

（6）启动S2的复制

start slave;

（7）查看当前两个从库的线程状态、复制状态和当前数据

show processlist;
show slave status\G
select * from db1.t1;

    S1上的显示如下：

mysql> show processlist;
+------+-----------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id   | User            | Host      | db   | Command | Time  | State                                                  | Info             |
+------+-----------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
|    4 | event_scheduler | localhost | NULL | Daemon  | 15228 | Waiting on empty queue                                 | NULL             |
| 7614 | wxy             | localhost | NULL | Query   |     0 | starting                                               | show processlist |
| 7619 | system user     |           | NULL | Connect |  9519 | Reconnecting after a failed master event read          | NULL             |
| 7620 | system user     |           | NULL | Query   |    59 | Slave has read all relay log; waiting for more updates | NULL             |
+------+-----------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 172.16.1.125
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000015
          Read_Master_Log_Pos: 2918
               Relay_Log_File: hdp3-relay-bin.000003
                Relay_Log_Pos: 837
        Relay_Master_Log_File: binlog.000015
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2918
              Relay_Log_Space: 3409
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error reconnecting to master 'repl@172.16.1.125:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1125
                  Master_UUID: 8eed0f5b-6f9b-11e9-94a9-005056a57a4e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 190528 14:15:22
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

mysql> select * from db1.t1;
+------+
| a    |
+------+
|    2 |
|   10 |
|   11 |
|   12 |
+------+
4 rows in set (0.00 sec)

mysql>

    S2上的显示如下：

mysql> show processlist;
+-------+-----------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
| Id    | User            | Host      | db    | Command | Time  | State                                                  | Info             |
+-------+-----------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
|     6 | event_scheduler | localhost | NULL  | Daemon  | 85864 | Waiting on empty queue                                 | NULL             |
| 48967 | wxy             | localhost | mysql | Query   |     0 | starting                                               | show processlist |
| 57199 | system user     |           | NULL  | Connect |    72 | Connecting to master                                   | NULL             |
| 57200 | system user     |           | NULL  | Query   |    72 | Slave has read all relay log; waiting for more updates | NULL             |
+-------+-----------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.16.1.125
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000015
          Read_Master_Log_Pos: 2356
               Relay_Log_File: hdp4-relay-bin.000002
                Relay_Log_Pos: 2520
        Relay_Master_Log_File: binlog.000015
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2356
              Relay_Log_Space: 2727
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl@172.16.1.125:3306' - retry-time: 60  retries: 2
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1125
                  Master_UUID: 8eed0f5b-6f9b-11e9-94a9-005056a57a4e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 190528 14:16:40
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

mysql> select * from db1.t1;
+------+
| a    |
+------+
|    2 |
|    1 |
|    1 |
|    1 |
+------+
4 rows in set (0.00 sec)

mysql>

    S1和S2的SQL线程状态均为“Slave has read all relay log; waiting for more updates”，说明两个从库都已经完成了所有中继日志的重放。S1的Relay\_Master\_Log\_File和Exec\_Master\_Log\_Pos分别是binlog.000015和2918，S2的Relay\_Master\_Log\_File和Exec\_Master\_Log\_Pos分别是binlog.000015和2356，说明S1更接近于原来的主库M，应当将S1提升为新主库。从表db1.t1的数据也可以明显看到S1的数据更新。

（8）提升S1为新主库

stop slave;
reset slave all;
set global read_only=off;

    同时去掉配置文件中的read\_only，以免重启库时忘记更改配置：

sed -i 's/^read_only/#&/' /etc/my.cnf

（9）找到S2上最后的重放事件，在S1上对应的二进制坐标

首先在S2上查看最后的二进制坐标：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 |     9620 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

然后在当前binlog文件binlog.000006里查找最后的重放事件，在S2上执行：

mysqlbinlog --base64-output=decode-rows --verbose /usr/local/mysql/data/binlog.000006

    结果显示如下：

...
# at 9347
#190528 14:14:24 server id 1125  end_log_pos 9433 CRC32 0xd6fcb00c     Anonymous_GTID    last_committed=36    sequence_number=37    rbr_only=yes    original_committed_timestamp=1559024064330837    immediate_commit_timestamp=1559024064349703    transaction_length=273
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1559024064330837 (2019-05-28 14:14:24.330837 CST)
# immediate_commit_timestamp=1559024064349703 (2019-05-28 14:14:24.349703 CST)
/*!80001 SET @@session.original_commit_timestamp=1559024064330837*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 9433
#190528 14:14:24 server id 1125  end_log_pos 9502 CRC32 0xf10333d5     Query    thread_id=8    exec_time=0    error_code=0
SET TIMESTAMP=1559024064/*!*/;
BEGIN
/*!*/;
# at 9502
#190528 14:14:24 server id 1125  end_log_pos 9549 CRC32 0x0fb24522     Table_map: `db1`.`t1` mapped to number 104
# at 9549
#190528 14:14:24 server id 1125  end_log_pos 9589 CRC32 0x6af67d3e     Write_rows: table id 104 flags: STMT_END_F
### INSERT INTO `db1`.`t1`
### SET
###   @1=1
# at 9589
#190528 14:14:24 server id 1125  end_log_pos 9620 CRC32 0x763d087a     Xid = 211
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[mysql@hdp4~]$

    最后一个事件的在原主库上的提交时间戳是original\_committed\_timestamp=1559024064330837，正如前面讨论延迟复制时所述，这个时间戳是主库的原始提交时间，单位精确到微妙，在所有从库上相同。因此可以通过它将不同从库上相同的事件联系起来。

最后查找S1上的binlog中“original_committed_timestamp=1559024064330837”对应的文件名和偏移量，在S1上执行：

while read LINE
do
    filename=/usr/local/mysql/data/${LINE:2}
    echo $filename
    mysqlbinlog --base64-output=decode-rows --verbose $filename | grep -A30 -n "original_committed_timestamp=1559024064330837"
done  < /usr/local/mysql/data/binlog.index

    结果显示如下：

[mysql@hdp3~]$while read LINE
> do
> filename=/usr/local/mysql/data/${LINE:2}
> echo $filename
> mysqlbinlog --base64-output=decode-rows --verbose $filename | grep -A30 -n "original_committed_timestamp=1559024064330837"
> done  < /usr/local/mysql/data/binlog.index
/usr/local/mysql/data/binlog.000011
/usr/local/mysql/data/binlog.000012
/usr/local/mysql/data/binlog.000013
/usr/local/mysql/data/binlog.000014
/usr/local/mysql/data/binlog.000015
/usr/local/mysql/data/binlog.000016
/usr/local/mysql/data/binlog.000017
/usr/local/mysql/data/binlog.000018
/usr/local/mysql/data/binlog.000019
/usr/local/mysql/data/binlog.000020
/usr/local/mysql/data/binlog.000021
/usr/local/mysql/data/binlog.000022
/usr/local/mysql/data/binlog.000023
194:#190528 14:14:24 server id 1125  end_log_pos 2193 CRC32 0x75e3db63     Anonymous_GTID    last_committed=8    sequence_number=9    rbr_only=yes    original_committed_timestamp=1559024064330837    immediate_commit_timestamp=1559024064361609    transaction_length=273
195-/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
196-# original_commit_timestamp=1559024064330837 (2019-05-28 14:14:24.330837 CST)
197-# immediate_commit_timestamp=1559024064361609 (2019-05-28 14:14:24.361609 CST)
198-/*!80001 SET @@session.original_commit_timestamp=1559024064330837*//*!*/;
199-/*!80014 SET @@session.original_server_version=80016*//*!*/;
200-/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
201-SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
202-# at 2193
203-#190528 14:14:24 server id 1125  end_log_pos 2262 CRC32 0xb047eaf4     Query    thread_id=8    exec_time=0    error_code=0
204-SET TIMESTAMP=1559024064/*!*/;
205-BEGIN
206-/*!*/;
207-# at 2262
208-#190528 14:14:24 server id 1125  end_log_pos 2309 CRC32 0xd9f43e3d     Table_map: `db1`.`t1` mapped to number 91
209-# at 2309
210-#190528 14:14:24 server id 1125  end_log_pos 2349 CRC32 0x69e276a4     Write_rows: table id 91 flags: STMT_END_F
211-### INSERT INTO `db1`.`t1`
212-### SET
213-###   @1=1
214-# at 2349
215-#190528 14:14:24 server id 1125  end_log_pos 2380 CRC32 0xee93c6db     Xid = 44
216-COMMIT/*!*/;
217-# at 2380
218-#190528 14:14:48 server id 1126  end_log_pos 2424 CRC32 0xe9f46f61     Rotate to binlog.000024  pos: 4
219-SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
220-DELIMITER ;
221-# End of log file
222-/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
223-/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
/usr/local/mysql/data/binlog.000024
[mysql@hdp3~]$

    可以看到，S2上最后一个事务在S1上对应的坐标为binlog.000023、2380，至此已经确定了S2连接新主库的change master to的坐标。

（10）将S2连接到新主库

stop slave;
reset slave all;
change master to
       master_host='172.16.1.126',
       master_port=3306,
       master_user='repl',
       master_password='123456',
       master_log_file='binlog.000023',
       master_log_pos=2380;

（11）在S2启动复制，查看复制状态，验证数据

start slave;
show slave status\G
select * from db1.t1;

    复制状态显示如下，可以看到S2从新主库的复制一切正常：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.126
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000024
          Read_Master_Log_Pos: 721
               Relay_Log_File: hdp4-relay-bin.000004
                Relay_Log_Pos: 929
        Relay_Master_Log_File: binlog.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 721
              Relay_Log_Space: 1180
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1126
                  Master_UUID: a70e460e-7309-11e9-93e8-005056a50f77
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

    查询db1.t1表的数据显示如下，事务已经执行到M崩溃的时间点：

mysql> select * from db1.t1;
+------+
| a    |
+------+
|    2 |
|   10 |
|   11 |
|   12 |
+------+
4 rows in set (0.00 sec)

    至此，新主库提升和其它从库的重新指向已经完成，复制拓扑中所有库的数据变化都已经追赶到最近，新的主从复制已经完成，客户端应用可以连接到新主库。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019年05月28日，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server