MySQL复制(一)异步复制之Position

1. MySQL异步复制介绍

1.1 概述

简单说，复制就是将来自一个 MySQL 数据库服务器 (主库) 的数据复制到一个或多个 MySQL 数据库服务器 (从库)。传统的 MySQL 复制提供了一种简单的 Primary-Secondary 复制方法，默认情况下，复制是单向异步的。MySQL 支持两种复制方式：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录二进制日志 (binlog)、在从库重放中继日志 (Rely log) 的方式来实现异步的数据复制。二进制日志或中继日志中的记录被称为事件。所谓异步asynchronous 包含两层含义，一是主库的二进制日志写入与将其发送到从库是异步进行的，二是从库获取与重放日志事件是异步进行的。这意味着，在同一时间点从库上的数据更新可能落后于主库，并且无法保证主从之间的延迟间隔。

复制给主库增加的开销主要体现在启用二进制日志带来的 I/O，但是开销并不大，MySQL 官方文档中称开启二进制日志会产生 1% 的性能损耗。出于对历史事务备份以及从介质失败中恢复的目的，这点开销是非常必要的。除此之外，每个从库也会对主库产生一些负载，例如网络和 I/O 开销。当从库读取主库的二进制日志时，可能会造成一定的 I/O 开销。如果从一个主库上复制到多个从库，唤醒多个复制线程发送二进制日志内容的开销将会累加。但所有这些复制带来的额外开销相对于应用对 MySQL 服务器造成的高负载来说是很小的。

1.2 复制的用途

横向扩展
- 通过复制可以将读操作指向从库来获得更好的读扩展。所有写入和更新都在主库上进行，但读取可能发生在一个或多个从库上。在这种读写分离模型中，主库专用于更新，显然比同时进行读写操作会有更好的写性能。需要注意的是，对于写操作并不适合通过复制来扩展。在一主多从架构中，写操作会被执行多次，这时整个系统的写性能取决于写入最慢的那部分。
负载均衡
- 通过 MySQL 复制可以将读操作分不到多个服务器上，实现对读密集型应用的优化。对于小规模的应用，可以简单地对机器名做硬编码或者使用DNS轮询(将一个机器名指向多个IP地址)。当然也可以使用复杂的方法，例如使用LVS网络负载均衡器等，能够很好地将负载分配到不同的 MySQL 服务器上。
提高数据安全性
- 提高数据安全性可以从两方面来理解。其一，因为数据被复制到从库，并且从库可以暂停复制过程，所以可以在从库上运行备份服务而不会影响相应的主库。其二，当主库出现问题，还有从库的数据可以被访问。但是，对备份来说，复制仅是一项有意义的技术补充，它既不是备份也不能够取代备份。例如，当用户误删除一个表，而且此操作已经在从库上被复制执行，这种情况下只能用备份来恢复。
提高高可用性
- 复制能够帮助应用程序避免 MySQL 单点失败，一个包含复制的设计良好的故障切换系统能够显著缩短宕机时间。
滚动升级
- 比较普遍的做法是，使用一个高版本 MySQL 作为从库，保证在升级全部实例前，查询能够在从库上按照预期执行。测试没有问题后，将高版本的 MySQL 切换为主库，并将应用连接至该主库，然后重新搭建高版本的从库。

1.3 二进制日志 binlog

https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html。如前所述，MySQL 复制依赖二进制日志，所以要理解复制如何工作，先要先了解 MySQL 的二进制日志。二进制日志包含描述数据库更改的事件，如建表操作或对表数据的更改等。开启二进制日志有两个重要目的:

用于复制。主库上的二进制日志提供要发送到从库的数据更改记录。主库将其二进制日志中包含的事件发送到从库，从库执行这些事件以对主服务器上的数据进行相同的更改。
用于恢复。当出现介质错误(如磁盘故障)时，数据恢复操作需要使用二进制日志。还原备份后，将重新执行备份后记录的二进制日志中的事件。

不难看出，MySQL 二进制日志所起的作用与 Oracle 的归档日志类似。二进制日志只记录更新数据的事件，不用于 SELECT 或 SHOW 等语句。通过设置 Log-bin 系统变量开启二进制日志，MySQL 8 中缺省是开启的。

二进制日志有 STATEMENT、ROW、MIXED 三种格式，通过 binlog-format 系统变量设置:

MySQL 8 缺省使用 ROW 格式。二进制日志的存放位置最好设置到与 MySQL 数据目录不同的磁盘分区，以降低磁盘 I/O 的竞争，提升性能，并且在数据磁盘故障的时候还可以利用备份和二进制日志恢复数据。

1.3.1 STATEMENT模式

(基于SQL语句的复制(statement-based replication, SBR))

每一条会修改数据的 SQL 语句会记录到 binlog 中。这种格式的优点是不需要记录每行的数据变化，这样二进制日志会比较少，减少磁盘 I/O，提高性能。缺点是在某些情况下会导致主库与从库中的数据不一致，例如 last_insert_id()、now() 等非确定性函数，以及用户自定义函数 (user-defined functions，udf) 等易出现问题。

binlog_format=STATEMENT

每一条会修改数据的sql语句会记录到binlog中。这是默认的binlog格式。

SBR 的优点:
- 历史悠久，技术成熟
- 不需要记录每一行的变化，减少了binlog日志量，文件较小
- binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
- binlog可以用于实时的还原，而不仅仅用于复制
- 主从版本可以不一样，从服务器版本可以比主服务器版本高
SBR 的缺点:
- 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候
  - 使用以下函数的语句也无法被复制:LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE()(除非启动时启用了 --sysdate-is-now 选项)
- INSERT ... SELECT会产生比 RBR 更多的行级锁
- 复制需要进行全表扫描 (WHERE 语句中没有使用到索引) 的 UPDATE 时，需要比 RBR 请求更多的行级锁
- 对于有AUTO_INCREMENT字段的 InnoDB 表而言，INSERT 语句会阻塞其他 INSERT 语句
- 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
- 执行复杂语句如果出错的话，会消耗更多资源
- 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错

1.3.2 ROW模式

(基于行的复制(row-based replication, RBR))

不记录每一条 SQL 语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子，能清楚记录每一行数据的修改细节。其优点是不会出现某些特定情况下的存储过程、函数或触发器的调用和触发无法被正确复制的问题。缺点是通常会产生大量的日志，尤其像大表上执行 alter table 操作时候会让日志暴涨。

binlog_format=ROW

5.1.5版本的 MySQL 才开始支持，不记录每条 sql 语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。

RBR 的优点:
- 任何情况都可以被复制，这对复制来说是最安全可靠的。(比如:不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题)
- 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
- 复制以下几种语句时的行锁更少:INSERT...SELECT、包含 AUTO_INCREMENT 字段的INSERT、没有附带条件或者并没有修改很多记录的UPDATE或DELETE语句
- 执行 INSERT，UPDATE，DELETE 语句时锁更少
- 从服务器上采用多线程来执行复制成为可能
RBR 的缺点:
- binlog 大了很多
- 复杂的回滚时 binlog 中会包含大量的数据
- 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而SBR只会写一次，这会导致频繁发生 binlog 的并发写问题
- 无法从 binlog 中看到都复制了些什么语句

1.3.3 MIXED模式

(混合模式复制(mixed-based replication, MBR))

是语句和行两种格式的混合使用，默认使用 STATEMENT 模式保存二进制日志，对于 STATEMENT 模式无法正确复制的操作，会自动切换到基于行的格式，MySQL 会根据执行的 SQL 语句选择日志保存方式。

binlog_format=MIXED

从 5.1.8 版本开始，MySQL 提供了Mixed格式，实际上就是Statement与Row的结合。在 Mixed 模式下，一般的语句修改使用statement格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。MySQL会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

1.4 复制原理

1.4.1 三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3个线程来操作，一个主库线程，两个从库线程。

一个主库线程
- 二进制日志转储线程 (Binlog dump thread) 是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件(Event)的时候，会在 Binlog上加锁，读取完成之后，再将锁释放掉。
两个从库线程
- 从库I/O线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 (Relay log)。
- 从库SQL线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

注意:
不是所有版本的 MySQL 都默认开启服务器的二进制日志。在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。除非特殊指定，默认情况下从服务器会执行所有主服务器中保存的事件。也可以通过配置，使从服务器执行特定的事件。

1.4.2 复制步骤

总的来说，MySQL 复制有五个步骤:

在主库上把数据更改事件记录到二进制日志binlog中。
从库上的 I/O 线程向主库询问二进制日志事件(binary log events)。
主库上的binlog dump线程向 I/O 线程发送二进制日志事件。
从库上的 I/O 线程将二进制日志事件复制到自己的中继日志中(relay log)。
从库上的 SQL 线程读取中继日志中的事件，并将其重放到从库上。

图中更详细地描述了复制的细节。

第一步是在主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，从库将主库的二进制日志复制到其本地的中继日志中。首先，从库会启动一个工作线程，称为 I/O 线程，I/O 线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制日志转储(binlog dump)线程，它会读取主库上二进制日志中的事件，但不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号通知其有新的事件时才会被唤醒，从库 I/O 线程会将接收到的事件记录到中继日志中。
从库的 SQL 线程执行最后一步，该线程从中继日志中读取事件并在从库上执行，从而实现从库数据的更新。当 SQL 线程追赶 I/O 线程时，中继日志通常已经在系统缓存中，所以重放中继日志的开销很低。SQL 线程执行的事件也可以通过log_slave_updates系统变量来决定是否写入其自己的二进制日志中，这可以用于级联复制的场景。

这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说 I/O 线程能够独立于 SQL 线程之外工作。但这种架构也限制了复制的过程，其中最重要的一点是在主库上并发更新的查询在从库上通常只能串行化执行，因为缺省只有一个 SQL 线程来重放中继日志中的事件。在 MySQL 5.6 以后已经可以通过配置slave_parallel_workers等系统变量进行并行复制，在后面讨论与复制相关的性能问题时会介绍并行复制的相关细节。

现在我们已经了解了 MySQL 复制是以二进制日志为基础的，但是像 Innodb 这样的事务引擎有自己的事务日志，如ib_logfile，这些事务日志通常被称为重做日志(redo log)。作为背景知识，简单介绍下 Innodb 重做日志的作用。
对 Innodb 的任何修改操作都会首先在称为缓冲池(innodb buffer pool)的内存页面上进行，然后这样的页面将被标记为脏页，并被放到专门的刷新列表上，后续将由 master thread 或专门的刷脏线程阶段性的将这些页面写入磁盘。这样的好处是避免每次写操作都操作磁盘导致大量的随机 I/O，阶段性的刷脏可以将多次对页面的修改合并成一次 I/O 操作，同时异步写入也降低了访问时延。然而，如果在脏页还未刷入磁盘时，服务器非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb 将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行实例恢复操作，这个文件就是重做日志文件。每当有更新操作时，在数据页变更之前将操作写入重做日志，这样当发生掉电之类的情况时系统可以在重启后继续操作。这就是所谓的预写日志(Write-ahead logging，WAL)。这样的技术推迟了缓冲区页面的刷新，从而提升了数据库的吞吐量。同时由于重做日志的写操作是顺序 I/O，相对于写数据文件的随机 I/O 要快得多。大多数数据库系统都采用类似的技术实现。

1.4.3 MySQL基本架构

你可能已经有了这样的疑问，在复制中二进制日志和重做日志如何协同工作？假设 Innodb 写完重做日志后，服务异常关闭。主库能够根据重做日志恢复数据，但由于二进制日志没写入，会导致从库同步时少了这个事务么？或者反之，二进制日志写成功，而重做日志没有写完，是否导致从库执行事务，而主库不执行？这些情况会不会产生主从数据不一致的问题呢？解决这些问题是 MySQL 的核心需求，让我们从 MySQL 基本架构说起。

最上层用于处理客户端连接、授权认证、安全等等。第二层架构是 MySQL 服务器层。大多数 MySQL 的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有内置函数，所有跨存储引擎的功能(存储过程、触发器、视图等)都在这一层实现。如你所料，二进制日志也在这一层实现。第三层包含了存储引擎，负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信，存储引擎只是简单地响应上层服务器的请求。显然 Innodb 的重做日志在这一层实现。

由于 MySQL 的事务日志包含二进制日志和重做日志，当发生崩溃恢复时，MySQL 主库通过重做日志进行恢复，而在主从复制的环境下，从库是依据于主节点的二进制日志进行同步数据的。这样的架构对两种日志有两个基本要求:

第一，保证二进制日志里面存在的事务一定在重做日志里面存在，也就是二进制日志里不会比重做日志多事务(可以少，因为重做日志里面记录的事务可能有部分没有提交，这些事务最终可能会被回滚)。
第二，两种日志中事务的顺序一致，这也是很重要的一点，假设两者记录的事务顺序不一致，那么会出现类似于主库事务执行的顺序是ta、tb、tc、td，但是二进制日志中记录的是 ta、tc、tb、td，被复制到从库后导致主从数据不一致。为了达到这两点要求，MySQL 使用内部XA来实现(XA是eXtended Architecture的缩写，是 X/Open 分布式事务定义的事务中间件与数据库之间的接口规范)，其核心是两阶段提交(two phase commit，2PC)。

1.4.4 两阶段提交

在两阶段提交(two phase commit)协议中一般分为事务管理器(协调者)和若干事务执行者(参与者)两种角色。在MySQL内部实现的两阶段提交中，二进制日志充当了协调者角色，由它来通知Innodb执行准备、提交或回滚步骤。从实现角度分析，提交流程和代码框架分别如图所示。

如下 commit 命令的 MySQL 代码框架

sql parse.cc	mysql_execute_command
sql parse.cc		SQLCOM_COMMIT
transaction.cc			trans_commit()
handler.cc					ha_commit_trans()
handler.cc						tc_log->prepare()
binlog.cc							MYSQL_BIN_LOG::prepare()		-- prepare入口
handler.cc						tc_log->commit()
binlog.cc							MYSQL_BIN LOG::commit()			-- commit入

(1). 先调用binglog_hton和innobase_hton的 prepare 方法完成第一阶段，binlog_hton的 papare 方法实际上什么也没做，innodb的prepare持有prepare_commit_mutex，将重做日志刷磁盘，并将事务状态设为TRX_PREPARED。
(2). 如果事务涉及的所有存储引擎的 prepare 都执行成功，则调用TC_LOG_BINLOG::log_xid将事务 (STATEMENT格式或ROW格式) 写到二进制日志，此时，事务已经铁定要提交了。否则，调用ha_rollback_trans回滚事务，而事务实际上也不会写到二进制日志。
(3). 最后，调用引擎的 commit 完成事务的提交。实际上binlog_hton->commit什么也不会做 (上一步已经将二进制日志写入磁盘)，innobase_hton->commit则清除回滚信息，向重做日志中写入 COMMIT 标记，释放prepare_commit_mutex，并将事务设为TRX_NOT_STARTED状态。

如果数据库系统发生崩溃，当重启数据库时会进行崩溃恢复操作。具体到代码层面，Innodb在恢复的时候，不同状态的事务，会进行不同的处理:

对于TRX_COMMITTED_IN_MEMORY的事务，清除回滚段，然后将事务设为TRX_NOT_STARTED;
对于TRX_NOT_STARTED的事务，表示事务已经提交，跳过;
对于TRX_PREPARED的事务，要根据二进制日志来决定事务是否提交，暂时跳过;
对于TRX_ACTIVE的事务，回滚。

简单来讲，当发生崩溃恢复时，数据库根据重做日志进行数据恢复，逐个查看每条重做条目的事务状态，根据图中的流程，如果已进行到TRX_NOT_STARTED阶段，也就是存储引擎 commit 阶段，那么说明重做日志和二进制日志是一致的，正常根据重做条目进行恢复即可;事务状态为TRX_ACTIVE，没写到二进制日志中，直接回滚；如果事务状态为TRX_PREPARED，要分两种情况，先检查二进制日志是否已写入成功，如果没写入成功，那么就算是TRX_PREPARED状态，也要回滚。如果写入成功了，那么就进行最后一步，调用存储引擎 commit，更改事务状态为TRX_NOT_STARTED，也就是真正提交状态，可以用作数据恢复。可见，MySQL 是以二进制日志的写入与否作为事务提交成功与否的标志，通过这种方式让 Innodb 重做日志和 MySQL 服务器的二进制日志中的事务状态保持一致。两阶段提交很好的保持了数据一致性和事务顺序性。

了解了所有这些技术细节后，当初的疑问自然也就有了答案。假设在阶段 (1) 结束之后程序异常，此时没有写入二进制日志，则从库不会同步这个事务。主库上，崩溃恢复时重做日志中这个事务没有trx_commit，因此会被回滚。逻辑上主从库都不会执行这个事务。假设在阶段 (2) 结束后程序异常，此时二进制日志已经写入，则从库会同步这个事务。主库上，根据重做日志能够正常恢复此事务。也就是说，若二进制日志写入完成，则主从库都会正常完成事务，反之则主从库都回滚事务，都不会出现主从不一致的问题。
MySQL 通过innodb_support_xa系统变量控制 Innodb 是否支持 XA 事务的 2PC，默认是 TRUE。如果关闭，则 Innodb 在 prepare 阶段就什么也不做，这可能会导致二进制日志的顺序与 Innodb 提交的顺序不一致，继而导致在恢复时或者从库上产生不同的数据。在 MySQL 8 中，innodb_support_xa系统变量已被移除，因为始终启用 Innodb 对 XA 事务中两阶段提交的支持，不再让用户来选择。

上述的 MySQL 两阶段提交流程并不是天衣无缝的，主从数据是否一致还与重做日志和二进制日志的写盘方式有关。innodb_flush_log_at_trx_commit和sync_binlog系统变量分别控制两者的落盘策略。

innodb_flush_log_at_trx_commit:有0、1、2三个可选值。
- 0 表示每秒进行一次刷新，但是每次事务提交不进行任何操作 (每秒调用fsync使数据落地到磁盘，不过这里需要注意如果底层存储有 cache，比如raid cache，那么这时也不会真正落盘，但是由于一般 raid 卡都带有备用电源，所以一般都认为此时数据是安全的)。
- 1 代表每次事务提交都会进行刷新，这是最安全的模式。
- 2 表示每秒刷新，每次事务提交时不刷新，而是调用write将重做日志缓冲区里面的内容刷到操作系统页面缓存。
- 从数据安全性和性能比较三种策略的优劣为:
  - 1 由于每次事务提交都会是重做日志落盘，所以是最安全的，但是由于fsync的次数增多导致性能下降比较严重。
  - 0 表示每秒刷新，每次事务提交不进行任何操作，所以 MySQL 或操作系统崩溃时最多丢失一秒的事务。
  - 2 相对于 0 来说了多了每次事务提交时的一个write操作，此时数据虽然没有落磁，但是只要没有操作系统崩溃，即使 MySQL崩溃，那么事务也是不会丢失的
sync_binlog: MySQL 在提交事务时调用MYSQL_LOG::write完成写二进制日志，并根据sync_binlog决定是否进行刷新。默认值是 0，即不刷新，从而把控制权交给操作系统。如果设为 1，则每次提交事务，就会进行一次磁盘刷新。

这两个参数不同的值会带来不同的效果。两者都设置为 1，数据最安全，能保证主从一致，这也是 MySQL 8 的默认设置。

innodb_flush_log_at_trx_commit非 1，假设在二进制日志写入完成后系统崩溃，则可能出现这样的情况:从库能够执行事务，但主库中 trx_prepare 的日志没有被写入到重做日志中，导致主库不执行事务，出现主从不一致的情况。
同理若sync_binlog非 1，可能导致二进制日志丢失 (操作系统异常宕机)，从而与 Innodb 层面的数据不一致，体现在复制上，从库可能丢失事务。在数据一致性要求很高的场景下，建议就使用缺省的全1配置。

2. 空库配置异步复制

2.1 服务器环境部署

参考【MySQL复制环境搭建】部署一主两从环境，本节实验环境部署的是 mysql 8.0.31 一主两从环境。

2.2 主库配置

2.2.1 参数配置

建议 mysql 版本一致且后台以服务运行，主从所有配置项都配置在[mysqld]节点下，且都是小写字母。具体参数配置如下:

必选

#修改配置文件
vim /etc/my.cnf
#[必须]主服务器唯一ID,比如可以取master的IP(192.168.2.80)最后80作为ID
server-id=80
#[必须]启用二进制日志,指名路径。比如:自己本地的路径/log/mysqlbin
log-bin=mysql-bin

可选

#[可选] 0(默认)表示读写(主机)， 1 表示只读(从机)
read-only= 0
#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds= 6000
#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选]设置不要复制的数据库
binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如:binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字
#[可选]设置binlog格式
binlog_format=STATEMENT

2.2.2 重启mysql服务

#CentOS 6
service mysqld restart 
#CentOS 7
systemctl restart mysqld

2.2.3 建立复制账户并授权

创建一个从机用户，并指定可以从本机外部进行访问。

# mysql v5
#在主机MySQL里执行授权主从复制的命令
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
# GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从机器数据库IP' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.58' IDENTIFIED BY 'repl';

# 如果使用的是MySQL v8，需要如下的方式建立账户，并授权slave:
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

#此语句必须执行。否则报错，见下面注意部分。
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
flush privileges;

注意:在从机执行show slave status\G时报错:

Last_IO_Error: error connecting to master 'slave1@192.168.1.150:3306' - retry-time: 60 retries: 1
message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires
secure connection.

2.2.4 查询File和Position

查询Master的状态，并记录下File和Position的值。

# 检查log_bin已经开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
# 状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下File和Position的值,从机同步会用到。

注意：执行完此步骤后，不要再操作主服务器 MySQL ，防止主服务器状态值变化。

2.3 从库配置

2.3.1 参数配置

要求主从所有配置项都配置在my.cnf的[mysqld]栏位下，且都是小写字母。

必选

#从1
vim /etc/my.cnf
#[必须]从服务器唯一ID,比如可以取Slave的IP(192.168.2.81)最后81作为ID
server-id=81

#从2
server-id=82

可选

#[可选]启用中继日志
relay-log=mysql-relay

2.3.2 重启mysql服务

#CentOS 6
service mysqld restart 
#CentOS 7
systemctl restart mysqld

2.4 主从关系配置

2.4.1 步骤1:从机上复制主机的命令

连接主库时，需要使用change master to提供连接到主库的连接选项，包括主机地址、端口、用户名、密码、二进制文件名、复制起始事件位置等。

https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-howto-slaveinit.html
Replication cannot use Unix socket files. You must be able to connect to the source MySQL server using TCP/IP.

CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;

change master to后，在mysql.slave_master_info表中就会生成一条记录，此行为是由master_info_repository系统变量控制的。MySQL 8 中，该变量的缺省值为TABLE，即将与复制相关的主库信息记录到mysql.slave_master_info表中。随着复制的进行，表中的数据会随之更新。change master to只是为 I/O 线程连接主库时提供连接参数，这条语句本身不会连接主库。以后启动 I/O 线程时，I/O 线程都会自动读取这条记录来连接主库，不需要再执行change master to语句。类似地，MySQL 8 缺省会将中继日志的重放信息存到mysql.slave_relay_log_info表中。该行为由系统变量relay_log_info_repository控制。中继日志信息在首次启动复制时生成，并随复制即时改变。SQL 线程再次启动时就能从中获取到从中继日志的的哪个地方继续读取、执行。

例如:

# 如果之前做过同步先stop slave
mysql> stop slave;
mysql> 
CHANGE MASTER TO
MASTER_HOST='192.168.2.80',
MASTER_USER='repl',MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=157;

Query OK, 0 rows affected, 8 warnings (0.02 sec)

2.4.2 步骤2:启动slave同步

# 启动slave同步
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

start slave语句会启动 I/O 线程和 SQL 线程，并创建一个到主库的客户端连接。该命令执行后，在主库的processlist中会看到类似如下的线程，这些就是从库上 I/O 线程所建立的，Binlog Dump表示由 I/O 线程在主库上启动了Binlog Dump线程，每个连接上来的从库对应一个线程，如 Id 32 和 56 是两个从库的连接线程:

#主库
mysql> show processlist;
+----+-----------------+------------+------+-------------+--------+---------------------------------------------------------------+------------------+
| Id | User            | Host       | db   | Command     | Time   | State                                                         | Info             |
+----+-----------------+------------+------+-------------+--------+---------------------------------------------------------------+------------------+
  ...
| 32 | repl            | hdp4:3723  | NULL | Binlog Dump | 328879 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 56 | repl            | hdp3:57308 | NULL | Binlog Dump | 319204 | Master has sent all binlog to slave; waiting for more updates | NULL             |
  ...

从库上的 processlist 中会看到类似如下的线程，Id 325 和 326 分别对应 I/O 线程与 SQL 线程:

#从库
mysql> show processlist;
+--------+-----------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+
| Id     | User            | Host      | db    | Command | Time   | State                                                  | Info             |
+--------+-----------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+
  ...
|    325 | system user     |           | NULL  | Connect | 320408 | Waiting for master to send event                       | NULL             |
|    326 | system user     |           | NULL  | Query   | 320408 | Slave has read all relay log; waiting for more updates | NULL             |
  ...

# 如果报错:
ERROR 1872 (HY000): slave failed to initialize relay log info structure from the repository
# 可以执行reset slave操作，删除之前的relay_log信息。
mysql> reset slave;
# 后重新执行 CHANGE MASTER TO ...语句即可

2.5 验证与测试

2.5.1 查看同步状态

# 从机上
# Slave_IO_Running: YES		-- 负责与主机的io通信
# Slave_SQL_Running: Yes	-- 负责自己的slave mysql进程
# 以上两个状态都是YES表表明主从搭建成功
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.2.80
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 157
               Relay_Log_File: Mysql80-Slave-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000006
             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: 157
              Relay_Log_Space: 544
              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: 80
                  Master_UUID: 9b647833-1bc0-11ed-a45d-000c295c445e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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, 1 warning (0.01 sec)

ERROR: 
No query specified

在从库上执行show slave status可以查看从库状态，输出信息非常多，其中除了那些描述 I/O 线程、SQL 线程状态的行，还有几个 log_file 和 pos 相关的行。理解这几行的意义至关重要，所以这里完整地描述它们:

Master_Log_File: I/O 线程正在读取的 master binlog;
Read_Master_Log_Pos: I/O 线程已经读取到 master binlog 的哪个位置;
Relay_Log_File: SQL 线程正在读取和执行的 relay log;
Relay_Log_Pos: SQL 线程已经读取和执行到 relay log 的哪个位置;
Relay_Master_Log_File: SQL 线程最近执行的操作对应的是哪个 master binlog;
Exec_Master_Log_Pos: SQL 线程最近执行的操作对应的是 master binlog 的哪个位置。

(Relay_Master_Log_File, Exec_Master_log_Pos)构成一个坐标，这个坐标表示从库上已经将主库上的哪些数据重放到自己的实例中，它可以用于下一次change master to时指定的二进制日志坐标。与这个坐标相对应的是从库上 SQL 线程的中继日志坐标(Relay_Log_File, Relay_Log_Pos)。这两个坐标位置不同，但它们对应的数据是一致的。还有一个延迟参数Seconds_Behind_Master需要说明一下，它的本质意义是 SQL 线程比 I/O 线程慢多少。如果主从之间的网络状况优良，那么从库的 I/O线程读速度和主库写二进制日志的速度基本一致，所以这个参数也用来描述 “SQL线程比主库慢多少”，也就是说从库比主库少多少数据，只不过衡量的单位是秒。需要注意的是，该参数的描述并不标准，只是在网速很好的时候做个大概估计，很多种情况下它的值都是 0，即使 SQL 线程比 I/O 线程慢了很多也是如此。

2.5.2 不通原因

1. 网络不通
1. 账户密码错误
1. 防火墙
1. mysql配置文件问题
1. 连接服务器时语法
1. 主服务器mysql权限
1. 主机show master status\G;从机show slave status\G;查看主机的File是否和从机的Relay_Master_Log_File相同

2.5.3 测试

主机新建库、新建表、insert记录，从机复制:

# 主机
CREATE DATABASE test_master_slave;
use test_master_slave
CREATE TABLE mytbl(id INT,NAME VARCHAR( 16 ));		
INSERT INTO mytbl VALUES( 1 , 'zhang3');
INSERT INTO mytbl VALUES( 2 ,@@hostname);
# 从机
mysql> use test_master_slave
mysql> SELECT * FROM mytbl;
+------+---------+
| id   | NAME    |
+------+---------+
|    1 | zhang3  |
|    2 | MySQL80 |
+------+---------+
2 rows in set (0.00 sec)

2.6 停止主从同步

停止主从同步命令:
```
stop slave;
```

重新配置主从，需要在从机上执行:

stop slave;
reset master; 
#删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)

3. 脱机配置异步复制

如果数据库已经存在应用数据，但允许一个可接受的脱机时间窗口做复制，这种场景下常用的做法是先直接将主库的数据目录整体拷贝到从库，再启动复制。具体步骤如下。

步骤和联机配置思路相同，区别是需要停库。

#1、在主库上建立复制专属用户
create user 'repl'@'%' identified with mysql_native_password by 'repl';
grant replication client,replication slave on *.* to 'repl'@'%';
#2、停掉复制涉及的实例
mysqladmin -uroot -proot shutdown
	# 涉及的主从实例都停止
#3、将复制主库的数据目录整体拷贝到从库
scp -r /var/lib/mysql/ 从库IP:/var/lib/mysql/
#4、确保所有参与复制实例的server-uuid和server_id都不同，查看主从的my.cnf配置
#5、重启实例
mysqld_safe --user=mysql &
#6、查看主库二进制日志信息
show master status;
#7、从库创建主库信息
CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;
#8、在从库启动复制并查看复制信息
start slave;
show slave status\G

4. 联机配置异步复制 mysqldump 方式

空库或脱机建立复制的需求太过理想化，部署简单，大多数情况下，复制是被要求在不影响线上业务的情况下，联机创建的，而且还要求对线上库的影响越小越好。例如，复制过程化中对主库加锁会影响对主库的访问，因此通常是不被允许的。这种场景下有两种备选的复制方案：使用 mysqldump 程序或使用如XtraBackup的第三方工具。这两种方案有各自的适用场合。使用 mysqldump 联机建立复制的过程如下。

4.1 服务器环境部署

参考【MySQL复制环境搭建】部署，本节实验环境部署的是mysql 8.0.31一主一从环境。

4.2 准备联机测试数据

# 主库
[root@mysql80-master ~]# mysql -uroot -proot
mysql>
create database test;
use test;
create table t1(id int);
insert into t1 values(1),(2);

4.3 主库配置

参考上一节: 空库配置主从异步复制的主库配置。

# 修改配置文件
[root@mysql80-master ~]# vim /etc/my.cnf
server-id=80
log-bin=mysql-bin
# 重启服务
systemctl restart mysqld
# 创建复制账号
[root@mysql80-master ~]# mysql -uroot -proot
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

4.4 从库配置

参考上一节：空库配置主从异步复制的从库配置。

# 参数配置
[root@mysql80-slave01 ~]# vim /etc/my.cnf
server-id=81
# 重启服务
systemctl restart mysqld

4.5 主从关系配置

# 从库上创建主库复制信息
[root@mysql80-slave01 ~]# mysql -uroot -proot
mysql>
change master to
master_host='192.168.2.80',
master_port=3306,
master_user='repl',
master_password='repl';
# 注意在上面这条命令中并没有指定主库二进制文件的文件名和位置。

4.6 在从库用 mysqldump 建立复制

# === 5.7 ===
mysqldump --single-transaction --all-databases --master-data=1 --host=192.168.2.80 --user=root --password=root --apply-slave-statements | mysql -uroot -proot -h127.0.0.1
# 如果是8.0运行以上语句
# WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
# WARNING: --apply-slave-statements is deprecated and will be removed in a future version. Use --apply-replica-statements instead.
# === 8.0 改成如下依旧有提示 ===
mysqldump --single-transaction --all-databases --source-data=1 --host=192.168.2.80 --user=root --password=root --apply-replica-statements | mysql -uroot -proot -h127.0.0.1
# 遇到新提示
# Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
# 提醒导入其他的库就有可能重复的GTID，一个提醒，根据建议，添加--set-gtid-purged=OFF参数继续
# === 最终 mysql8.0 mysqldump命令，如下 ===
# 其中两个warning只是提示作用，没有其他信息即表明数据复制成功
[root@mysql80-slave01 ~]# mysqldump --single-transaction --all-databases --set-gtid-purged=OFF --source-data=1 --host=192.168.2.80 --user=root --password=root --apply-replica-statements | mysql -uroot -proot -h127.0.0.1
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

说明：
- --single-transaction参数可以对 Innodb 表执行非锁定导出。此选项将事务隔离模式设置为REPEATABLE READ，并在转储数据之前向服务器发送START TRANSACTION SQL语句。它仅适用于 Innodb 等事务表，因为它会在发出START TRANSACTION时转储数据库的一致状态，而不会阻塞任何应用程序。因此这里假定：
  - 所有的应用数据表都使用 Innodb 引擎。
  - 所有系统表数据在备份过程中不会发生变化。
- --master-data参数会导致转储输出包含类似 CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1480; 的 SQL 语句，该语句指示主库的二进制日志坐标 (文件名和位置)。
  - 如果选项值为2，则CHANGE MASTER TO语句将写为 SQL 注释，因此仅提供信息，不会执行。
  - 如果参数值为1，则该语句不会写为注释，并在重新加载转储文件时执行。如果未指定选项值，则默认值为 1。
- --apply-slave-statements参数会在CHANGE MASTER TO语句之前添加 STOP SLAVE 语句，并在输出结尾处添加START SLAVE语句，用来自动开启复制。
- 通过管道操作符，导出导入一步进行，不需要中间落盘生成文件。

4.7 同步测试

-- 从库
[root@mysql80-slave01 ~]# mysql -uroot -proot
show slave status\G
-- 数据查看
mysql> select * from test.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

4.8 小结

mysqldump 方式的优点是可以进行部分复制，如在配置文件中定义replicate-do-table=db1.*，则用这种方法可以只复制 db1 库而忽略其它复制事件。缺点是由于 mysqldump 会生成主库转储数据的 SQL 语句，实际是一种逻辑备份方式所以速度较慢，不适用于大库。

5. 联机配置异步复制 XtraBackup 方式

5.1 服务器环境部署

参考【MySQL复制环境搭建】部署，本节实验环境部署的是mysql 5.7.40一主一从环境。

5.2 准备联机测试数据

# 主库
[root@MySQL57 ~]# mysql -uroot -proot
mysql>
create database test;
use test;
create table t1(id int);
insert into t1 values(1),(2);

5.3 主库配置

参考上一节: 空库配置主从异步复制的主库配置。

# 修改配置文件
[root@MySQL57 ~]# vim /etc/my.cnf
server-id=57
log-bin=mysql-bin
# 重启服务
systemctl restart mysqld
# 创建复制账号
[root@MySQL57 ~]# mysql -uroot -proot
#在主机MySQL里执行授权主从复制的命令
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
# GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从机器数据库IP' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

5.4 从库配置

参考上一节: 空库配置主从异步复制的从库配置。

#从库
[root@Mysql57-Slave ~]# vim /etc/my.cnf
server-id=58
# 重启服务
systemctl restart mysqld

5.5 使用XtraBackup备份传输数据

5.5.1 XtraBackup安装

联机建立复制的另一种可选方案是使用 XtraBackup。XtraBackup 是 Percona 公司的开源项目，用以实现类似 Innodb 官方的热备份工具 InnoDB Hot Backup 的功能，它支持在线热备份，备份时不影响数据读写。到目前为止，最新的版本为 Percona XtraBackup 8.0.6，可以从 https://www.percona.com/downloads 下载安装包。XtraBackup 有很多功能和优点，例如支持全备、增量备份、部分备份；支持压缩备份；备份不影响数据读写、事务等，但是也有缺陷不足：例如不支持脱机备份、不支持直接备份到磁带设备、不支持 Cloud Back，MyISAM 的备份也会阻塞。不过这些小瑕疵不影响XtraBackup成为一款流行的MySQL备份工具。另外，注意 XtraBackup 只支持Linux 平台，不支持Windows平台。下面演示用 XtraBackup 联机搭建主从复制的过程，主库已经建立了用于执行复制的用户 repl。

根据Mysql版本参考XtraBackup官网文档，本节使用yum在线安装。

# 这里使用yum在线安装
# 主从都要装
# 下载yum包
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools release
yum -y install qpress

# 适用MySQL 8.0的 XtraBackup
yum -y install percona-xtrabackup-80

# 适用MySQL 5.7的 XtraBackup
yum -y install percona-xtrabackup-24

# 检查安装结果
rpm -qa |grep xtraback

# 卸载xtrabackup
yum -y remove percona-xtrabackup-80
yum -y remove percona-xtrabackup-24

5.5.2 主从的SSH连接

# 主
[root@MySQL57 ~]#
ssh-keygen
# ... 一路回车连接从库IP ... 输入从库密码等
ssh-copy-id 192.168.2.58

5.5.3 清除从库数据

# 停库
[root@Mysql57-Slave ~]# mysqladmin -u root -proot shutdown
# 备份并清空数据目录
[root@Mysql57-Slave ~]# cd /var/lib/
[root@Mysql57-Slave lib]# mkdir -p mysql_backup
[root@Mysql57-Slave lib]# mv mysql/* mysql_backup/

5.5.4 数据传输

# 主库执行
# 这条命令连接主库，进行并行压缩流式备份，同时将备份通过管道操作符传输到从库。
xtrabackup -uroot -proot --socket=/var/lib/mysql/mysql.sock --no-lock --backup --compress --stream=xbstream --parallel=4 --target-dir=./ | ssh root@192.168.2.58 "xbstream -x -C /var/lib/mysql/"

# 如果是Mysql 8.0，遇到登录报错解决
use mysql;
ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'root';
#或者
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

# 从库查看传输过来的数据
[root@Mysql57-Slave ~]# cd  /var/lib/mysql
[root@Mysql57-Slave mysql]# ll
total 320
drwxr-x--- 2 root root   4096 Nov  4 20:38 atguigudb
-rw-r----- 1 root root    477 Nov  4 20:38 backup-my.cnf.qp
-rw-r----- 1 root root    362 Nov  4 20:38 ib_buffer_pool.qp
-rw-r----- 1 root root 269367 Nov  4 20:38 ibdata1.qp
drwxr-x--- 2 root root   4096 Nov  4 20:38 mysql
drwxr-x--- 2 root root   8192 Nov  4 20:38 performance_schema
drwxr-x--- 2 root root   8192 Nov  4 20:38 sys
drwxr-x--- 2 root root     57 Nov  4 20:38 test
-rw-r----- 1 root root    109 Nov  4 20:38 xtrabackup_binlog_info.qp
-rw-r----- 1 root root    135 Nov  4 20:38 xtrabackup_checkpoints
-rw-r----- 1 root root    565 Nov  4 20:38 xtrabackup_info.qp
-rw-r----- 1 root root    274 Nov  4 20:38 xtrabackup_logfile.qp

5.6 从库恢复并同步数据

5.6.1 从库解压数据恢复

# 解压数据
[root@Mysql57-Slave mysql]# 
xtrabackup --decompress --parallel=4 --target-dir=/var/lib/mysql/
# 应用日志
xtrabackup --prepare --target-dir=/var/lib/mysql/

5.6.2 查看二进制日志坐标

[root@Mysql57-Slave mysql]# cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001        737

5.6.3 启动从库服务

# 复制过来的文件授权
[root@Mysql57-Slave mysql]# chmod -R 777 /var/lib/mysql
# 开启mysql服务
[root@Mysql57-Slave mysql]# systemctl start mysqld

5.6.4 启动从库复制

-- 创建主库信息，其中的master_log_file和master_log_pos值来自第(6)步
[root@Mysql57-Slave mysql]# mysql -uroot -proot
mysql>
change master to
master_host='192.168.2.57',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos=737;
-- 启动复制
start slave;
-- 确认复制状态
show slave status\G
# 查看数据
mysql> select * from test.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

5.7 小结

XtraBackup 是物理复制，性能比 mysqldump 高的多，而且对主库的影响极小，非常适用于从头联机创建高负载、大数据量、全实例从库的场景。

6. 主从切换

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

6.1 计划内切换(SwitchOver)

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

(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). 将客户端连接到新主库。

6.2 计划外切换(Failover)

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

(1). 让所有从库执行完其从崩溃前的旧主库获得的中继日志。
(2). 选择并设置新主库。
(3). 查找其它所有从库最后执行的事件，在新主库上对应的二进制坐标。
(4). 其它所有从库重置复制，按上一步获得的二进制坐标连接到新主库，启动新复制。

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

log_bin=ON
binlog_format=ROW
log_slave_updates=ON
gtid_mode=OFF
# 两个从库的read_only=ON。

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

6.2.1 在M上做一些数据更新

drop database db1;
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;
-- 用于查看同样地SQL语句，event是否一样
insert into t1 select 1;
insert into t1 select 1;
insert into t1 select 1;
commit;

6.2.2 停止S2复制，模拟S2是落后的从库

#从库2
stop slave;

6.2.3 刷新S1的日志

flush logs;

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

6.2.4 在M再做一些更新

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

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

6.2.5 停止M，模拟主库损坏

[root@mysql80-master ~]# mysqladmin -uroot -proot shutdown

6.2.6 启动S2的复制

start slave;

6.2.7 查看两个从库

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

6.2.7.1 S1上的显示如下

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                                                    | Info             |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| 10 | event_scheduler | localhost       | NULL | Daemon  | 3949 | Waiting on empty queue                                   | NULL             |
| 15 | system user     | connecting host | NULL | Connect | 3932 | Reconnecting after a failed source event read            | NULL             |
| 21 | root            | localhost       | NULL | Query   |    0 | init                                                     | show processlist |
| 32 | system user     |                 | NULL | Query   |  133 | Replica has read all relay log; waiting for more updates | NULL             |
| 33 | system user     |                 | NULL | Query   |  133 | Waiting for an event from Coordinator                    | NULL             |
| 34 | system user     |                 | NULL | Connect | 2147 | Waiting for an event from Coordinator                    | NULL             |
| 35 | system user     |                 | NULL | Connect | 2147 | Waiting for an event from Coordinator                    | NULL             |
| 36 | system user     |                 | NULL | Connect | 2147 | Waiting for an event from Coordinator                    | NULL             |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
8 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed source event read
                  Master_Host: 192.168.2.80
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 9938
               Relay_Log_File: mysql80-slave01-relay-bin.000004
                Relay_Log_Pos: 9131
        Relay_Master_Log_File: mysql-bin.000001
             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: 9938
              Relay_Log_Space: 9520
              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 'slave@192.168.2.80:3306' - retry-time: 60 retries: 2 message: Can't connect to MySQL server on '192.168.2.80:3306' (111)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 80
                  Master_UUID: dd746660-528a-11ed-9c86-000c293b9f86
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 221103 10:01:47
     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, 1 warning (0.00 sec)
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    2 |
|   10 |
|   11 |
|   12 |
+------+
4 rows in set (0.00 sec)

6.2.7.2 S2上的显示如下

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                                                    | Info             |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 4474 | Waiting on empty queue                                   | NULL             |
| 15 | root            | localhost       | NULL | Query   |    0 | init                                                     | show processlist |
| 47 | system user     | connecting host | NULL | Connect |  184 | Connecting to source                                     | NULL             |
| 48 | system user     |                 | NULL | Query   |  184 | Replica has read all relay log; waiting for more updates | NULL             |
| 49 | system user     |                 | NULL | Connect |  184 | Waiting for an event from Coordinator                    | NULL             |
| 50 | system user     |                 | NULL | Connect |  184 | Waiting for an event from Coordinator                    | NULL             |
| 51 | system user     |                 | NULL | Connect |  184 | Waiting for an event from Coordinator                    | NULL             |
| 52 | system user     |                 | NULL | Connect |  184 | Waiting for an event from Coordinator                    | NULL             |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
8 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.2.80
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 9376
               Relay_Log_File: mysql-slave02-relay-bin.000003
                Relay_Log_Pos: 3977
        Relay_Master_Log_File: mysql-bin.000001
             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: 9376
              Relay_Log_Space: 9174
              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 'slave@192.168.2.80:3306' - retry-time: 60 retries: 4 message: Can't connect to MySQL server on '192.168.2.80:3306' (111)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 80
                  Master_UUID: dd746660-528a-11ed-9c86-000c293b9f86
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 221103 10:04:06
     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, 1 warning (0.00 sec)
mysql> select * from db1.t1;
+------+
| a    |
+------+
|    2 |
|    1 |
|    1 |
|    1 |
+------+
4 rows in set (0.00 sec)

6.2.8 提升谁为新主

S1 和 S2 的 SQL 线程状态均为“Slave has read all relay log; waiting for more updates”，说明两个从库都已经完成了所有中继日志的重放。

S1 的
- Relay_Master_Log_File:mysql-bin.000001
- Exec_Master_Log_Pos:9938
S2 的
- Relay_Master_Log_File:mysql-bin.000001
- Exec_Master_Log_Pos:9376

说明 S1 更接近于原来的主库 M，应当将S1提升为新主库。从表 db1.t1 的数据也可以明显看到 S1 的数据更新。

# 提升S1为新主
stop slave;
reset slave all;
set global read_only=off;
-- RESET SLAVE ALL是清除从库的同步复制信息、包括连接信息和二进制文件名、位置
-- 从库上执行这个命令后，使用show slave status将不会有输出。

# 如果配置文件中有read_only配置，去掉，以免重启库时忘记更改配置
[root@mysql80-slave01 ~]# sed -i 's/^read_only/#&/' /etc/my.cnf

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

# 1、 首先在S2上查看最后的二进制坐标
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     7517 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 2、 然后在当前binlog文件binlog.000001里查找最后的重放事件，在S2上执行：
[root@mysql-slave02 ~]# mysqlbinlog --base64-output=decode-rows --verbose /var/lib/mysql/binlog.000001
# == 显示如下 ==
......
# at 1230
#221023 14:43:26 server id 1  end_log_pos 1261 CRC32 0xf8b996e4         Xid = 47
COMMIT/*!*/;
# at 1261
#221023 14:43:26 server id 1  end_log_pos 1338 CRC32 0x0121aa53         Anonymous_GTID  last_committed=2        sequence_number=3  rbr_only=no     original_committed_timestamp=1666507406355410   immediate_commit_timestamp=1666507406355410     transaction_length=172
# original_commit_timestamp=1666507406355410 (2022-10-23 14:43:26.355410 CST)
# immediate_commit_timestamp=1666507406355410 (2022-10-23 14:43:26.355410 CST)
/*!80001 SET @@session.original_commit_timestamp=1666507406355410*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1338
#221023 14:43:26 server id 1  end_log_pos 1433 CRC32 0x18f26e7e         Query   thread_id=8     exec_time=0     error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1666507406/*!*/;
flush privileges
/*!*/;
# at 1433
#221023 14:51:50 server id 1  end_log_pos 1456 CRC32 0x2d718c53         Stop
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*/;
[root@mysql-slave02 mysql]#

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

# 3、最后查找S1上的binlog中“original_committed_timestamp=1666507406355410”对应的文件名和偏移量，在S1上执行：
[root@mysql80-slave01 ~]#
while read LINE
do
    filename=/var/lib/mysql/${LINE:2}
    echo $filename
    mysqlbinlog --base64-output=decode-rows --verbose $filename | grep -A30 -n "original_committed_timestamp=1666507406355410"
done  < /var/lib/mysql/binlog.index
# 结果显示
    # 我这里没有显示想要的结果

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

这里采用【脱机配置从库】，重新配置从库。

6.2.10 将S2连接到新主库

stop slave;
reset slave all;
change master to
       master_host='192.168.2.81',
       master_port=3306,
       master_user='repl',
       master_password='repl',
       master_log_file='binlog.0000xx',
       master_log_pos=xxxx;
start slave;
show slave status\G
select * from db1.t1;

7. 参数解读

7.1 seconds_behind_master

主备延迟

在备库上执行 show slave status 命令，它的返回结果里面会包含 seconds_behind_master，用于表示当前备库延迟了多少秒。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.57
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 154
               Relay_Log_File: Mysql57-Slave-relay-bin.000012
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
        Seconds_Behind_Master: 0
....

与数据同步有关的时间点主要包括以下三个：

主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
备库 B 执行完成这个事务，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

seconds_behind_master 的计算：

每个事务的 binlog里面都有一个时间字段，用于记录主库上写入的时间；
备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。

可以看到，seconds_behind_master 这个参数计算的就是 T3-T1。所以，我们可以用 seconds_behind_master 来作为主备延迟的值，这个值的时间精度是秒。

如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？
其实不会的。因为，备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。

需要说明的是，在网络正常的时候，日志从主库传给备库所需的时间是很短的，即 T2-T1 的值是非常小的。网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。
所以说，主备延迟最直接的表现是，备库消费中转日志(relay log)的速度，比主库生产 binlog 的速度要慢。接下来，我就和你一起分析下，这可能是由哪些原因导致的。

总结

主备延迟就是同一个事务,在备库和主库执行完成的时间差值；

主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差；

seconds_behind_master 可以用于判断主备延迟。

8. 主从开关机顺序

==== 关机 ===================================
#1.查看当前的主从同步状态，看是否双yes
mysql> show slave status\G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
#2.关闭从库
	#2.1 停止从库同步
	mysql> stop slave;
	#2.2 停止从库服务
	[root@Mysql57-Slave ~]# 
	mysqladmin shutdown -uroot -proot
	#2.3 查看服务状态
	systemctl status mysqld
	#如果部署了多个实例，那每个实例都要按照以上步骤来操作
#3.关闭主库
	#3.1 停止主库服务
	[root@Mysql57-Master ~]# 
	mysqladmin shutdown -uroot -proot
	#3.2 查看服务状态
	systemctl status mysqld
==== 开机 ===================================
#1.启动主库
	#1.1 开启主库服务
	mysqladmin start -uroot -proot
	#1.2 查看服务状态
	[root@Mysql57-Master ~]# mysql -uroot -proot
	mysql> systemctl status mysqld
#2.启动从库
	#2.1 启动从库服务
	mysqladmin start -uroot -proot
	#2.2 启动从库同步
	[root@Mysql57-Slave ~]# mysql -uroot -proot
	mysql> start slave;
	#2.3 检查同步状态
	mysql> show slave status\G;
	#2.4 查看服务状态
	[root@Mysql57-Slave ~]# systemctl status mysqld

参考

https://cloud.tencent.com/developer/article/1433066

https://wxy0327.blog.csdn.net/article/details/90081518

扫码关注公众号，获取最新发布

posted on 2023-11-10 20:57 一介IT 阅读(239) 评论(0) 收藏举报

刷新页面返回顶部

kingdevops