18 复制

18 复制... 1

18.1 复制配置... 3

18.1.1 基于Binary Log的数据库复制配置... 3

18.1.2 配置基于Binary log的复制... 3

18.1.2.1 设置复制master的配置... 3

18.1.2.2 创建复制要用的用户... 4

18.1.2.3 获取复制Binary Log坐标... 4

18.1.2.4 选择同步数据快照的方法... 4

18.1.2.5配置Slave. 5

18.1.2.6 为复制环境增加一个slave. 6

18.1.3 基于全局事务标示符的复制... 7

18.1.3.1 GTID概述... 7

18.1.3.2 使用GTID配置复制... 10

18.1.3.3 使用GTID故障转移或者扩展... 11

18.1.3.4 使用GTID复制的限制... 13

18.1.4 多主复制... 14

18.1.4.1 MySQL多主复制概述... 14

18.1.4.2多主复制教程... 14

18.1.4.3 多主复制监控... 15

18.1.4.4 多主复制错误信息... 16

18.1.5 修改在线服务的复制模式... 17

18.1.5.1 复制模型概述... 17

18.1.5.2 在线启动GTID事务... 18

18.1.5.3 在线关闭GTID事务... 19

18.1.5.4 验证复制的匿名事务... 19

18.1.6 复制和Binary log选项和变量... 20

18.1.7 通常的管理任务... 20

18.1.7.1检查复制状态... 20

18.1.7.2 暂停Slave上的复制... 22

18.2 复制的实现... 22

18.2.1 复制格式... 22

18.2.1.1 基于语句和基于行复制的好处和坏处... 23

18.2.1.2 基于行复制的用处... 24

18.2.1.3 确定binary log中安全和非安全语句... 25

18.2.2 复制实现细节... 26

18.2.3 复制渠道... 27

18.2.3.1 单个渠道的命令和选项... 27

18.2.3.2 版本兼容... 28

18.2.3.3 复制渠道启动选项... 28

18.2.3.4 复制渠道命名协定... 28

18.2.4 复制 Relay日志和状态日志... 28

18.2.4.1 Slave Relay Log. 28

18.2.4.2 Slave状态日志... 29

18.2.5 服务如何评估复制过滤规则... 29

18.2.5.1 评估数据库级别复制和bianry log选项... 30

18.2.5.2 评估表复制选项... 31

18.2.5.3 复制规则应用... 33

18.3 复制解决方案... 33

18.3.1 使用复制备份... 33

18.3.1.1 使用mysqldump备份slave. 34

18.3.1.2 备份原生数据... 34

18.3.1.3 标记为只读备份master或者slave. 34

18.3.2 复制Master和Slave不同引擎... 35

18.3.3使用复制的横向扩展... 35

18.3.4 不同的slave复制不同的数据库... 36

18.3.5 提高复制性能... 36

18.3.6 在错误的时候切换Master37

18.3.7 使用安全连接配置复制... 39

18.3.8 半同步复制... 41

18.3.8.1 半同步复制管理接口... 42

18.3.8.2 半同步复制安装和配置... 42

18.3.8.3 半同步复制监控... 43

18.3.9 延迟复制... 44

18.4 复制注意和提示... 44

复制是一个MySQL服务master，复制到另外一个服务slave。复制默认是异步的。Slave不需要一直连接到slave获取master的更新。根据配置，你可以复制所有的数据库，或者指定数据库，或者数据库内的几个表。

MySQL复制的有点：

1.可以分散负荷到多个slave来提高性能。在这个环节，所有的写必须在master执行，读可以在slave上执行。这个模式可以提高些性能。也提高读性能。

2.数据安全，因为数据被复制到slave，slave可以暂停复制进场，可以执行备份，不会损坏master上的数据。

3.分析，live数据可以在master上创建，分析行为可以在slave上执行，不会影响master。

4.长距离数据分布，你可以使用在本地创建远程数据的副本，不需要访问master。

MySQL 5.7支持不同的复制方法。传荣的方法是基于master的binlog，根据log文件的positions来同步。新的方法是基于全局事务标示(GTIDs)是事务标示因此不需要log文件和位置，简化了很多复制任务。复制使用GTIDs保证了master和slave 的一致性，只要在master上提交的事务也会在slave 上提交。关于使用GTIDs复制具体看： Section 18.1.3, “Replication with Global Transaction Identifiers”

在MySQL中的复制支持不同类型的同步。通常是单路异步的同步，一个服务作为master，多个作为slave。在MySQL5.7，半同步复制也被支持，扩展了异步复制。使用半同步复制，在事务提交回复session之前，保证至少有一个slave接受到通知并且接受并且记录了事务的日志。MySQL也支持延迟复制比如slave至少延迟指定时间的日志。

有2个核心类型的复制，基于语句的复制，语句行的复制。也可以使用混合复制类型。

复制通过一些列的选项和变量控制。

当你使用复制来解决各种不同问题，包括性能，支持不同数据库的备份。和一些列解决方案来缓解系统错误。

18.1 复制配置

18.1.1 基于Binary Log的数据库复制配置

根据数据库的配置，Binary log的格式不同。Slave配置读取master的binary log并且执行。

没个slave复制整个binary log。Slave负责哪个语句要被执行。除非你执行了，否则搜有binary log 都会被执行到slave。你可以配置slave指定只执行那些数据库或者表的binary log。

每个slave都保留了一个binary log 的相关记录，记录已经从master传过来的文件名和文件中的位置。也就是说多个slave 可以连接到master并且执行不同的binary log部分。因为slave控制了这些进程，独立的slave可以连接或者不连接到服务，不会对master操作影响。也是因为每个slave记录了当前Binary log 的位置，可以让slave断开之后重新连接。

Master和每个slave必须配置一个唯一的id，server-id,另外每个slave必须配置关于master的一些信息，具体可以看change master to的参数。

18.1.2 配置基于Binary log的复制

大致步骤如下：

1.在master，启动binary log配置server id。可能需要重启服务。

2.每个slave想要连接的master，必须配置server id，可能需要重启服务。

3.可选，创建一个独立的用户来验证，读取binary log。

4.在创建数据快照或者复制进程之前，在master上需要记录当前binary log 的位置。你需要配置slave这样slave知道从哪里开始执行事件。

5.如果你已经有了数据在master上，想要使用它来同步到slave，你需要创建一个数据快照，并且复制到slave。存储引擎会影响创建快照的方式，当你使用MyISAM你必须停止语句获取READ-LOCK,获取当前的binary log并且导出数据。在允许master继续执行语句之前。如果你没有停止服务导出数据，master 的状态信息就不匹配，导致slave的数据库损坏或者不一致。如果你使用innodb，不需要使用read-lock。

6.配置slave连接到master 的信息比如host，login，binary log文件名和位置。

18.1.2.1 设置复制master的配置

为了配置master使用binarylog文件，你必须启动binary log并且设置server id。如果没有被设置，需要重启服务。

Binary Log必须被启动，因为binary log是传输修改的基础。如果binary log 没有启动，那么就不能配置复制。

在复制组内的每个服务必须配置唯一的server id。这个ID用来表示唯一的服务，必须是1到2^32-1中的一个值。

配置binary log和server id选项，关闭MySQL服务并且修改my.cnf在mysqld的配置下设置log-bin和server-id选项。如果已经存在那个根据需要来修改。例如：

[mysqld]

log-bin=mysql-bin

server-id=

修改之后需要重启。

18.1.2.2 创建复制要用的用户

每个slave连接到master使用MySQL用户名和密码，所以必须有个用户账号在master，slave可以用来连接。任何账号可以使用这个操作，必须要有REPLICATION SLAVE权限。你可以根据选择为不同的slave创建不同的账号，或者使用相同的账号连接到master。

尽管你没有创建指定的用户用于复制，要注意复制用户名密码是明文保存在码，master信息表或者文件中。

使用create user来创建用户。授予复制需要的权限。如果你创建账号是为了复制，只需要REPLICATION SLAVE权限就可以了。

18.1.2.3 获取复制Binary Log坐标

为了配置 slave启动复制进程你需要master 当前binary log的坐标。如果master已经运行但是没有启动binary log那么SHOW MASTER STATUS和mysqldump –master-data是空的。这个时候只要指定文件为空，位置为4.

如果master已经设置了binary log，使用以下过程来获取：

1.启动会话连接到master，以只读模式刷新所有的表，flush tables with read lock。

2.在其他会话中，使用SHOW MASTER STATUS语句回去binary log的位置和文件。

18.1.2.4 选择同步数据快照的方法

如果master数据库包含的数据需要复制到所有的slave。有2个复制数据的方法：

1.使用mysqldump导出数据

2.如果是binary portable文件你可以复制原生的数据文件。比mysqldump可能要有效的多。

18.1.2.4.1 使用mysqldump创建快照

使用--master-data会自动生成change master语句。

18.1.2.4.2 使用原生数据文件创建数据快照

可以使用企业版的备份工具mysqlbackup备份，也可以使用xtrabackup进行备份。

18.1.2.5配置Slave

配置slave前，确保以下步骤：

1.配置MySQL Master

2.获取master状态信息

3.在master上释放读锁

18.1.2.5.1 设置Slave配置

每个复制slave必须有一个唯一的server id。重启服务生效。

如果slave server id已经没有被设置，或当前值server id和master的冲入，需要重新设置一个server id。如果有多个slave每个slave 都要有一个唯一的server id。slave上没必要启动binary log。但是如果你在slave 上启动了binary log，你可以使用slave的binary log备份和恢复。也可以把slave作为复杂拓扑的一部分。比如这个slave对于其他slave是master。

18.1.2.5.2 在slave设置master

为了slave可以和master交互，需要配置master的连接信息。使用如下语句：

mysql> CHANGE MASTER TO

Change master to语句也有其他选项比如使用SSL。Change master to的所有选项可以查看：Section 13.4.2.1, “CHANGE MASTER TO Syntax”.

如果master有数据可以导入到slave中，导入方法如下：

1.如果有一个快照数据库导入，查看： Section 18.1.2.5.3, “Setting Up Replication between a New Master and Slaves”.

2.如果你没有快照数据库导入可以看：Section 18.1.2.5.3, “Setting Up Replication between a New Master and Slaves”.

18.1.2.5.3 在新的master和slave配置复制

当没有快照导入，为新的master配置slave。在所有slave都要执行

1.启动MySQL Slave并且连接

2.执行change master to配置master replication server。

这个方法也可以使用在设置一个新的master，已经有了数据dump要导入的master上。

如果配置复制环境使用了其他服务器的数据来配置新master，可以dump文件来生成型的服务。数据库更新会自动传播到slave。

18.1.2.5.4 使用已经存在数据来配置复制

当配置复制但是已经有数据了，在复制启动钱，把快照从master传输到slave。步骤如下：

1.启动slave，但是使用—skip-slave-start，不启动slave。

2.导入dump文件

如果使用raw data创建了快照：

1.把数据文件解压倒slave数据目录

2.启动slave，--skip-slave-start

3.使用master的相关信息来配置slave。获取change master to要的信息。

4.启动slave

你已经处理了过程，slave连接到master并且复制master上所有的更新。

1.如果master忘记了设置server-id，slave不能连接

2.如果slave忘记了server-id,slave的error日志就会有以下错误

slave使用的信息存放在master info中，跟踪了已经处理了多少master的binary log。这个数据可以是文件或者表的方式，有—master-info-repository来决定。当参数为file，你可以在数据文件中找到2个文件master.info和relay-log.info。如果为table那么信息保存在mysql下的master_slave_info表中。如果删除表或者文件master信息就会丢失。

18.1.2.6 为复制环境增加一个slave

可以在不停止master的情况下为复制环境增加一个slave。为新的slave配置一个server-id。

1.关闭存在的slave

2.复制数据目录到新的slave。你可以使用tar打包，也可以直接cp或者rsync。

当心的复制slave加入，往往会发生以下常见错误：

这个错误是因为—relay-log没有指定导致的，relay log文件以host名字开头，有一个index文件，--relay-log-index文件控制。

为了避免这个问题使用，--relay-log使用和老的slave一样的值。如果选项没有被显示设置使用hostname-relay-bin。设置--relay-log-index和老的slave一样。如果没有显示设置默认为hostname-relay-bin.index。如果你已经设置了relay log也是有以上的错误：

a.如果没有设置relay log设置，在新的slave上关闭slave，stop slave。如果来的slave也启动了，老的slave也需要关闭。

b.复制老的slave中的relay log index文件内容到新的slave relay log index文件中。

c.执行重命名

3.复制master信息和relay log信息内容到新的slave。

4.启动已经存在的slave

5.在新的slave 上给一个新的server-id

6.启动新的slave，使用master信息的内容来启动。

18.1.3 基于全局事务标示符的复制

18.1.3.1 GTID概述

GTID是事务的唯一标示。并不是对某一个服务来说是唯一标示，是所有服务内都是唯一的。

GTID有2部分组成，有冒号分隔如下：

Source_id标示原始服务，通常使用服务的server_uuid。Transaction_id标示事务的顺序好。比如第一个事务可能是1。比如有个GTID：

这种格式来表示GTID，在show slave status和binary log输出，也可以在show binlog events中看到。

在show master status输出的GTID可能会被收缩到一个语句，比如：

GTID set的语法格式如下：

GTID set在服务很多方面都被使用，比如，qtid_executed和qtid_purged系统变量使用GTID set表示。另外GTID_SUBSET和GTID_SUBTRACT需要GTID set作为输入。

GTID会在master和slave保留。也就是说你可以决定通过检查binary log可以知道slave应用了那些事务。另外一旦有GTID的事务被提交，任何语句使用这个GTID的都会被服务忽略。因此在master上的提交事务可以被多次应用到slave，也保证了一致性。

当GTID被使用，slave不需要本地数据，比如master上的文件名或者位置。所有必要的信息在master中被获取直接来至于复制数据流。GTIDs代替文件位置来决定开始，结束和恢复的点。也没有必要在change master上设置master_log_file和master_log_pos只需要启动master_auto_position选项。

GTID的生成和生命周期：

1.事务在master上被提交和执行。

GTID是由master的uuid和一个非0的顺序号，GTID会被写入到binary log。

2.binary log数据被传输到slave并且保存到slave的relay log。slave读取GTID并且设置gtid_next系统变量最为它的GTID。告诉slave下一个事务必须使用这个GTID来记录。

3.slave验证这个GTID是否已经被使用记录到binary log。如果GTID没有被使用，slave写GTID，应用事务，并且把事务写入到binary log。先读取和检查事务的GTID，然后执行事务本身，保证了之前没有事务使用过这个GTID，也没有其他事务读取了这个GTID，但是没有提交的情况。也就是说多个客户端不允许并行的应用同一个事务。

4.因为gtid_next是非空的，slave没有试图为事务生成GTID，而是写入被保存在这个变量的GTID。

Mysql.gtid_executed表

从MySQL 5.7.5开始，GTID被存储在gtid_executed表中。每个GTID，GTID set都有一样，uuid，事务的开始和结束id，如果只有一个那么开始和结束是一样的。在mysql安装或者更新的时候就已经被创建。

只有在gtid_mode=on或者on_premissive的时候GTID才会保存在mysql. gtid_executed下，存储方式的不同取决于log_bin是on或者off。

1.如果binary log没有启动，服务存储每个事务的GTID到这个表

2.如果binary log启动，除了把GTID保存在这个表上，不管binary log是不是被回绕或者服务关闭，服务把所有事务的GTID写入到之前binary log 的都写入到新的Binary log 上。

当服务异常关闭，GTID没有被保存到mysql.gtid_executed表。在恢复的时候GTID被加入到表并且gtid_executed系统变量。

Reset master重置mysql.gtid_executed。

Mysql.gtid_executed表压缩

一段时间之后，这个表就会变得很大，考虑到空间问题，可以把一些事务折叠比如：

压缩成

当GTID启动，服务定期对mysql.gtid_executed这类压缩。你可以设置executed_gtids_commpression_period修改压缩比率，默认是1000，表示每1000个事务一次压缩。设置为0表示不进行压缩。

有一个专门的后台线程来执行压缩，show processlist无法显示，可以用thread表查看：

当线程一次执行后，下次执行需要睡眠executed_gtids_commpression_period，再启动运行压缩。如果当binary log禁用设置为0，表示一直睡眠不执行压缩。

18.1.3.2 使用GTID配置复制

配置复制的关键步骤如下：

1.如果复制已经在运行，把同步服务都设置为只读。

2.关闭2个服务。

3.启动GTID和正确的配置启动2个服务。

4.指示使用master作为slave数据源，并且使用自动位置，然后启动slave。

5.启动read模式，让他们可以接受写请求。

一下例子是，2个服务已经是master和slave了，使用MySQL binary log位置为基础的复制协议。一下很多操作需要root账号或者SUPER权限。

步骤1：同步服务，保证服务是只读的，2个服务都启动只读模式

步骤2：关闭2个服务，

步骤3：重启2个服务并且启动GTID，为了使用基于GTID的服务，每个服务必须启动GTID模式，通过设置gtid_mode，设置--enforce-gtid-consistency选项保证只有对于GTID复制安全的语句才会被记录日志。另外使用—skip-slave-start启动slave。

Mysql 5.7.5没有强制要求使用使用GTID要启动binary log因为有额外的mysql.gtid_executed表。也就是说slave可以使用gtid不启动binary
log。

在Mysql 5.7.4或者更早的版本，使用GTID要启动binary log

步骤4：slave直接使用master，告诉slave使用使用master的作为复制源，并且使用基于GTID的自动位置，而不是基于文件的位置。执行change master to，使用master_auto_position选项告诉slave事务由GTID标示。

不需要再设置master_log_file选项和master_log_pos选项。使用master_auto_position设置为1，如果这么做可能导致change master to语句错误。

假设change master to语句执行正确，启动slave：

步骤5：取消只读模式

18.1.3.3 使用GTID故障转移或者扩展

MySQL复制基于GTID对加入的slave通过一些技术提供扩展和master的故障转移：

1.简单复制

2.复制数据和事务到slave

3.注入空的事务

4.使用gtid_purged清空事务

5.还原gtid模式的slave

GTID被加入的复制的目的是让复制的数据量和故障转移行为关闭变得更加简单。每个唯一标示是一组binary
log 的时间，表示一个事务。GTID在数据库修改起到关键的作用：服务自动跳过任何已经被服务识别为已经运行过的事务。这个行为对于复制重新定位和正确故障转移很重要。

标示符和一组事件组成的事务可以在binary log获得。这样可能一个新的服务从老的服务获取数据有一些问题。为了让新的服务重新生成标记，有必要把老服务的标记复制到新服务，并且保存标示符之间和实际事件之间的关系。这个对于一个新还原的slave马上能够成为故障转移或者切换的候选很重要。

简单复制

最简单的方式在新服务上生成所有的标示和事务是把新的服务弄成slave。

一旦服务启动，新的服务从master上复制整个binary
log，获取所有关于gtid的信息。

这个方法简单而且有效，但是需要slave从master上读取binary log。可能slave要赶上master要花相当长的一段时间。所以这个方法不适用于快速故障转移或者备份恢复。

复制数据和事务到slave

重播整个事务历史是很耗时的，会是启动新slave的瓶颈。为了消除这个请求，对数据集进行快照，binary log和全局信息导入到slave。Binary log重播完之后启动复制，使用了剩下的事务之后，就可以跟上。

有很多方式来完成这个方法，不同点是如何dump数据并且传输binary log到slave。

这个方法有个好处是新的服务可以很快的被使用，只有某些事物在快照或者dump文件产生的事务还是需要从master上获取。也就是说slave可用不是瞬间的，但是相对来说slave获取这些事务是很快能够完成的。

复制binary log到目标服务通常比读取整个事务执行历史要快。但是移动这些文件并不是一直可用的因为文件大小伙子其他原因。

注入空的事务

Master的全局gtid_executed变量包含了所有在master 上执行过的事务。为新服务提供快照之后，不通过复制binary
log，而是观察gtid_executed在快照执行完之后的变化。在新服务加入到复制链之前，简单的提交再gtid_executed上包含的内容比如：

一旦所有事务使用这种方式恢复。你要刷新日志并且清理之前的所有binary
log。

为了防止事件中的错误事务导致大量错误。（FLUSH LOGS语句强制创建一个新的binary log,PURGE BINARY LOGS清理空的事务，但是保留了标示符。）

这个创建的服务根据快照，但是可以马上变成master，因为已经赶上了master。

使用gtid_purged跳过事务

Master的全局变量gtid_purged包含所有被master binary log清理的事务。你可以记录gtid_executed在master快照的时候的值。不想之前的方法需要清空事务，或者purge binary
logs。而是直接在slave上设置gtid_purged，根据快照时候发生的gtid_executed。

和空事务方法一样，服务通过快照创建，只要binary log 赶上master就能够使用。

恢复GTID模式的Slave

当恢复在GTID复制中的slave，注入空事务不能解决问题因为这个事件没有GTID。

使用mysqlbinlog找出下一个事务，这个事件后哪个可能是下一个日志文件的第一个事务。复制所有东西直到commit语句，确保包含了set @@SESSION.GTID_NEXT。当你使用基于行的复制，你可以任然在客户端上运行binary
log的行事件。

停止slave，运行复制的事务。Mysqlbinlog会把定义符号设置为/*!*/，要设置回来。

重启复制使用自动定位：

18.1.3.4 使用GTID复制的限制

因为GTID复制依赖于事务，一些特性在mysql可用，但是不支持。

更新非事务存储引擎

当使用GTID，更新非事务表比如MyISAM的时候不会产生事务。

这个限制是因为这个表是非事务引擎上的表如果混合了更新了事务表，那么一个事务会产生多个GTID。这样的话一对一的事务和GTID的对应就破坏了。

CREATE TABLE … SELECT语句

Create table select语句对于给予语句的复制是不安全的，当使用基于行复制，实际上被记录为2个事件一个是创建表，另外一个是插入记录到新表。当语句在一个事务内执行，很有可能是2个事件有一个GTID。也就是说插入语句会被跳过，因此create table…select不会被GTID支持。

临时表

创建或者删除临时表语句使用GTID不被事务支持(当启动了—enforce-gtid-consistencyt的情况)。这个语句是可以被支持的，但是要在事务外面。或者自动提交的事务。

阻止不支持的语句

为了阻止会导致GTID复制失败的语句，所有服务必须以—enforce-gtid-consistency选项启动。这样前面说的任何语句都会报错。

Sql_slave_skip_counter不能被支持。如果你需要跳过事务，你使用master上的gtid_executed注入空事务。

GTID模式和mysqldump

导入一个从没有启动了GTID模式的MySQL服务的数据到一个GTID启动了mysql服务是很有可能。

GTID模式和mysql_upgrade

不推荐在使用了gtid-mode=on的服务使用mysql_upgrade，因为mysql_upgrade会修改系统表，系统表是使用myisam，非事务引擎。

18.1.4 多主复制

18.1.4.1 MySQL多主复制概述

MySQL多主复制可以让复制slave从多个源中获得事务。多主复制可以用来备份多个服务到单个服务上，共享表，并且把多个服务的数据联合到一个服务上。多主复制没有实现事务的冲突发现和解决，如果有需要这些工作就丢给了应用程序。在多主复制拓扑上slave为每个master创建一个replication channel用来获取事务。

18.1.4.2多主复制教程

18.1.4.2.1 配置多主复制教程

本节解释如何配置多主复制拓扑，提供详细的master和slave配置。比如拓扑至少要2个master和一个slave。

Master可以是基于GTID，也可以基于binary
log的。

多主复制不支持复制信息存放在文件中，要存放在表中，启动mysql要加以下配置：

为了修改现在的复制slave，把文件模式改为表模式。

18.1.4.2.2 增加一个基于GTID的Master到多主复制slave

假设你有一个启动了GTID的master，使用了gtid_mode=on，启动复制用户，并且保证slave是使用了table保存复制信息。使用change master to语句加上for channel子句，比如：

多主复制兼容自动定位，对每个额外的master使用这个步骤，加入到一个channel。

18.1.4.2.3 增加基于binary log的master到多主复制slave

假设你有一个基于binary log的master，注意当前的binary log位置保证slave的复制信息是保存在表中的。根据知道的master_log_file,master_log_position设置change master to，把master增加到新的channel中。

18.1.4.2.4 启动多主复制slave

一旦你都已经配置了所有master信息，使用start
slave thread_types语句来启动复制。当你在slave启动了多主复制，你可以选择启动所有的channel或者选择特定的channel。

1.启动所有channel

2.指定某个channel

18.1.4.2.5 停止多主复制slave

Stop slave语句可以用来停止多主复制的slave。默认使用stop slave语句停止所有的channel。也可以使用for channel指定一个channel。

1.停止所有的channel

2.停止一个命名的channel

使用thread_type选项选择想要停止的slave。

18.1.4.2.6 重置多主复制slave

Reset slave语句可以用来重置多主复制slave。默认reset slave重置所有channel。也可以指定channel。

1.重置所有channel

2.重置指定channel

18.1.4.3 多主复制监控

为了监控多主复制channel的状态：

1.使用复制性能框架表。这些表的第一列都是channel_name.

2.使用show slave status for channel。默认如果for channel子句没有使用，语句会显示所有channel的状态。Channel_name作为结果的一个列。如果提供了for
channel结果显示了改channel的状态。

18.1.4.3.1 使用性能框架表监控channel

一下显示所有channel的连接状态：

以上输出有2个channel，一个为master1，一个为master2.

通过表也可以查指定的channel_name。

18.1.4.4 多主复制错误信息

新的错误代码和信息已经被加入到了MySQL 5.7.6 来提供在多主复制的错误信息。这些错误代码和信息只有在多主复制启动的时候才会发生，并且提供那个channel错处，比如：

Slave is already running and Slave is already stopped have been replaced with Replication
thread(s) for channel channel_name are already running and Replication threads(s) for channel channel_name
are already stopped respectively.

这些服务日志信息会根据channel_name不同变化。

18.1.5 修改在线服务的复制模式

18.1.5.1 复制模型概述

为了安全的配置在线服务的的复制模型，理解复制的关联原理十分重要。复制模型是否可用依赖于识别事务的技术。复制使用的事务类型：

1.GTID事务是通过GTID来识别。每个GTID事务都有gtid_log_event.GTID事务可以通过GTID或者文件名和位置识别。

2.匿名事务没有GTID分配，MySQL
5.7.6和之后的保本保证每个匿名事务在日志里面有一个anonymous_gtid_log_event。在之前的版本匿名事务，之前没有特殊事务。匿名事务只能通过文件和位置确定。

当使用GTID可以使用自动决定位置，自动故障转移，也可以使用WAIT_FOR_EXECUTED_GTID_SET()。Session_track_gtids和使用性能框架表，监控复制事务。使用GTID你不能使用sql_slave_skip_counter，可以使用空事务代替。

可以在线配置复制模式就表示可以童泰的修改gtid_mode，enforce_gtid_consistency变量。在之前的版本这2个参数不能动态被设置。Gtid_mode可以设置为on，off表示是否启动gtid标记事务。当gtid_mode=on不能复制匿名事务，当gtid_mode=off只能复制匿名事务。在MySQL
5.7.6 gtid_mode变量多了2个不同的状态，off_permissive, on_permissive。当为on_permissive表示当允许复制事务是GTID或者匿名，新的事务使用GTID。也就是说就可以复制拓扑的服务既可以用匿名或者GTID事务。比如gtid_mode=on，可以复制到gtid_mode=onpermissvie的slave。

Gtid_mode取值：off,off_permissive,on_permissive,on。Gtid_mode设置每次只能设置一次，并且严格这个顺序。比如如果是off,那么只能修改到off_permissive状态。因为要保证匿名事务到GTID事务已经被服务正确处理。当你在on和off之前切换的时候，GTID状态是不变的。不管个gtid_mode修改，保证GTID集合已经被服务应用。

master使用gtid_mode=on提供自动定位，配置change master to的时候使用master_auto_position=1，如果自动定位启动了，当匿名事务发生就会出错。强烈推荐在启动自动定位前，保证没有匿名事务。master和slave的可用GTID模式的组合如下：

Y:表示master，slave的gtid_mode模式兼容

N:表示master，slave的gtid_mode模式不兼容

*：表示支持自动定位。

当前选择的gtid_mode会影响gitd_next的变量，以下表显示服务gtid_mode,gtid_next的可用组合：

ANONYMOUS:生成匿名事务

Error:生成一个错误无法设置gtid_next

UUID:NUMBER：使用uuid：number生成GTID。

New GTID：使用自动生成的数值，生成GTID。

当binary log关闭，gtid_next设置为automatic，没有gtid会被生成。

18.1.5.2 在线启动GTID事务

本节介绍在服务online情况下启动GTID事务。这个过程不需要重启服务。当然先offline在设置会简单一点。

在启动前有一些前置条件：

1.所有服务必须是MySQL 5.7.6或者之后的版本。单个拓扑内的服务online不能启动GTID事务。

2.所有gtid_mode都设置为OFF。

一下过程可以被任何时间展厅并且之后继续：

1.在每个服务上执行：

服务在一般负荷情况下运行一会儿，然后监控日志。如果步骤导致了一些告警，调整应用程序这样只是用GTID的兼容特性并且不会生成任何警告。第一步很重要，要保证错误日志上没有告警，才能进入下一步。

2.在每个服务商执行：

3.在每个服务商执行：

不管在哪个服务商先运行这个语句，要现在所有服务上运行完，才能执行下一个步骤。

4.在所有服务上运行；

同上，在所有的服务上运行完，再执行下一步。

5.等待直到变量ONGOING_ANONYMOUS_TRANSACTION_COUNT为0.

6.等待直到步骤5的事务都已经复制到了所有服务。

7.如果binary
log有其他用途，比如备份，等待直到不需要老的binary log(没有GTID的)。

如果第6步完成你可以执行flush logs然后对binary log进行备份。

8.在每个服务上执行：

9.在每个服务的配置文件中加入，gtid-mode=on

现在你生成的所有事务都是带GTID的了。有了GTID之后就可以执行自动故障转移，执行以下语句到每个slave。如果你有多主复制，在每个slave上执行以下语句。

18.1.5.3 在线关闭GTID事务

在线关闭GTID事务。

在启动前有一些前提条件：

1.所有服务必须是MySQL 5.7.6或者之后的版本。单个拓扑内的服务online不能关闭GTID事务。

2.所有gtid_mode都设置为ON。

1.执行以下语句在每个slave上，如果是多主复制使用for
channel子句

2.在每个服务上执行

3.在服务商等待直到@@global.gtid_owned为空

4.等待所有当前存在的事务都复制到所有的slave

5.如果binary log有其他用处，等待直到不需要老的binary
log。

6.在每个服务商执行

7.在配置文件上设置gtid-mode=off

18.1.5.4 验证复制的匿名事务

本节解释如何监控复制拓扑验证所有匿名事务已经被复制。在修改复制模式的时候这个很有用。

有一些方法可以来等待事务到服务。

最简单的方法，如果不关心拓扑只和延迟有关，如果你确定最多N秒延迟，只要等待N秒就可以了。

如果和拓扑有关:如果有一个master和多个slave那么：

1.在master上执行，show
master status

2.在每个slave上使用master上的文件和位置：

如果你没有master和多个slave级别，或者slave的slave。在每个级别上运行上面的语句。

如果你有一个重要的复制拓扑有多个写入客户端，在每个master-slave上执行第二步语句。直到完成整个循环。

比如A->B->C->A：

在A上执行步骤1，在B上执行步骤2

在B上执行步骤1，在C上执行步骤2

在C上执行步骤1，在A上执行步骤2

18.1.6 复制和Binary log选项和变量

具体看：http://dev.mysql.com/doc/refman/5.7/en/replication-options.html

18.1.7 通常的管理任务

18.1.7.1检查复制状态

最常用的管理复制的方法是保证复制已经执行并且没有错误是使用SHOW
SLAVE STATUS。

·        
Slave_IO_State:当前 slave的状态

·        
Slave_IO_Running:I/O线程读取master binary log是否在执行。通常是yes的除非没有启动复制或者显示的使用stop
slave停止了。

·        
Slave_SQL_Running:时候在执行relay log上的信息。和slave_io_running一样一般是yes的。

·        
Last_IO_Error,Last_SQL_Error:在执行的时候，最后一次IO
slave线程或者sql slave线程报出的错误。

·        
Seconds_Behind_Master:已经被master binary
log落下的秒数，如果一直很大，说明slave无法及时的处理这些事件。这个状态值也有可能不能真实的反应情况，当Slave
SQL线程追上IO线程的时候为0，但是有一个队列的事件，也有可能值很大。

·        
Master_Log_file,Read_Master_Log_Pos:表示slave
IO线程已经读取了多少日志。

·        
Relay_Master_Log_File,Exec_Master_Log_Pos:Master Binary Log中已经被执行了多少日志。

·        
Relay_Log_File,Relay_Log_Pos:Relay log 已经有多少已经被执行过了，表示relay的文件和位置，和master binary log无关。

在master，你可以通过show
processlist查看在运行进程的状态。

因为是slave来执行复制进程的，因此master上只能看到很少的信息。

18.1.7.2 暂停Slave上的复制

你可以使用stop slave，start
slave来启动和停止复制。

当你要停止某个线程的时候，可以使用如下命令：

启动指定线程，可以使用如下命令：

slave的update只来之于对event的执行，如果你要备份，可以停止sql thread然后执行备份。IO线程会一直读取event，但是并不执行他们。

只停止I/O thread保证relay
log中的events都已经被执行。当你需要管理slave的时候保证slave已经运行到了指定的点。

18.2 复制的实现

复制是基于master server的binary
log，记录了所有写入性事件，比如数据库结构修改和数据内容修改。通常select不会被记录到binary log中。

每个slave连接到master获取binary log的副本。从master拉走数据，而不是master推送数据。slave执行获取的binary log的events，和在master上执行过的一样。

因为每个slave都是独立的，重播master上的修改也是独立的。另外每个slave获取master binary
log，但是slave可以更新读取自己的数据库。也可以停止启动复制进程，对master不影响。

18.2.1 复制格式

复制能工作是因为master的binary
log被获取并且在slave上执行。事件被记录到binary
log有很多种格式。不同的binary log格式，决定了复制的格式。

·        
当使用基于语句的binary log，master把sql语句写入到binary log。复制这些binary
log到slave，并执行这些语句。这个叫基于语句的复制，基于语句的是标准的binary
log格式。

·        
当使用基于行的格式，master表内行的修改写入到binary
log。复制使用这些binary log执行修改。这个称为基于行的复制。

·        
还有一种是混合了基于语句的和基于行的日志格式，根据哪种是最适合记录的来决定。称为混合日志格式。相应的复制称为混合复制。

18.2.1.1 基于语句和基于行复制的好处和坏处

每个binary log格式有自己的好处和坏处。对于很多用户来说混合复制格式可能是性能和数据一致性上兼顾的最好的。

基于语句复制的好处

·        
很早就已经提供这个功能

·        
写入的数据少到binary log，当更新和删除影响很多行，log文件的空间要求也不会很高。

·        
所有的语句都在binary log上，可以用来审计。

基于语句复制的坏处

·        
SBR的语句不安全，并不是所有的语句都可以使用基于语句的复制。当使用基于语句复制的时候，任何不确定的行为很难复制：

o   
语句依赖于UDF或者存储过程是不确定的，因为UDF或者存储过程的返回和输入的参数有关。

o   
delete和update语句使用了limit子句，但是没有order by。

o   
确定性的UDF必须应用在slave

o   
使用了一下函数的语句：

• LOAD_FILE()
• UUID(), UUID_SHORT()
• USER()
• FOUND_ROWS()
• SYSDATE() (unless
  both the master and the slave are started with the --sysdate-is-now option)
• GET_LOCK()
• IS_FREE_LOCK()
• IS_USED_LOCK()
• MASTER_POS_WAIT()
• RAND()
• RELEASE_LOCK()
• SLEEP()
• VERSION()

·        
Insert…select，会生产比基于行复制多的行级锁。

·        
update语句要全表扫描的，会生产大量的行级锁。

·        
对于InnoDB，insert语句使用了自增和其他非冲突insert语句。

·        
对于复杂的语句，语句在被执行前必须先评估，对于基于行的复制就不需要可以直接执行语句。

·        
如果复杂的语句运行超过时间阀值，那么执行就会报错。

·        
存储过程里面的now()，但是在master和slave上执行结果是不一样的。

·        
表的定义在master和slave上必须唯一。

基于行复制的好处

·        
所有的修改都是安全的。

·        
以下语句行锁会很少：

o   
Insert…select

o   
insert有自增字段的

o   
update或者delete语句有where但是没有走索引的。

基于行复制的坏处

·        
RBR会生成比SBR更多的数据。

·        
确定性的UDF如果生成blob会很慢

·        
无法查看从master获取的语句

·        
对于使用MyISAM存储引擎，insert的锁会很强力。

18.2.1.2 基于行复制的用处

MySQL使用SBL，RBL或者混合日志模式。binary log的模式影响日志的大小和对日志的效率。因此选择RBR或者SBR要更具你的应用和环境决定。

ž  
基于行的临时表日志。临时表在使用基于行的模式下是不能复制。当使用混合模式的安全的语句设计到临时表的会被以基于语句格式记录。临时表是不会被复制的，因为没有必要。另外因为临时表只能由创建他们的线程读取，复制他们很少能够获得好处，不管是不是在使用基于语句的日志格式下。
在MySQL 5.7，你可以把基于语句的方式切换到基于日志的方式。

ž  
RBL和非实物表同步。当很多行被响应，那么修改会被分为多个事件，当语句提交，这些事件被写入到binary
log上。当在slave执行时，所有涉及到的表都会被锁表，然后行会以batch方式写入。

ž  
延迟和binary log文件大小。RBL把每行的修改都写入到binary log，因此大小会迅速上升。这样会上升应用到slave的时间。要注意程序时候能够忍受这些延迟。

ž  
读binary log。mysqlbinlog使用binlog语句来显示基于行事件的binary log。语句显示了以64编码的字符串，当使用了--base64-output=DECODE-ROWS 和--verbose选项，mysqlbinlog会把内容格式化为已读的。当binary log事件被以基于行格式写入，如果出现错误就可以使用这个命令读取binary
log的内容。

ž  
Binary log执行错误和slave_exec_mode。如果slave_exec_mode不是严格的，错误不会因为原来的行找不到而触发错误，导致复制失败。也就意味着可能没有被应用到slave，所以master和slave就不是同步状态。如果是非事务表，slave_exec_mode=IDEMPOTENT会导致master和slave未来分叉。
对于其他场景，设置slave_exec_mode=STRICT一般都可以满足，也是除了NDB储存引起之外的其他引擎的默认值。

ž  
基于server id过滤不支持。在mysql 5.7，你可以CHANGE MASTER TO的选项设置IGNOGE_SERVER_IDs对server id进行过滤。这个选项适用于基于语句和基于行的日志格式。另外一个方法是使用where子句包含@@server_id
<>id_value。不过在基于行的日志下并不能正确的工作。如果使用server_id系统变量来过滤语句，那么要使用基于语句的日志。

ž  
数据库级别的复制选项。--replicate-do-db,--replicate-ignore-db,--replicate-rewrite-db选项根据不同的场景，是否基于行或者基于语句决定。因此，推荐避免使用数据库级别的选项，而是用表级别的，--replicate-do-table,--replicate-ignore-table。

ž  
RBL，非事务表和slave停止。当使用基于行的日志，如果当slave线程在更新非事务表的时候，slave服务停止，那么就会进入非一致性状态，因为这个原因推荐使用事务存储引擎，比如使用INNODB表语句行格式。

18.2.1.3 确定binary log中安全和非安全语句

语句的安全性在mysql复制中，会影响语句应用到slave的正确性。

通常语句是安全的，因为是确定的，一些使用浮点数学函数的基本都是不安全的。

控制语句是否安全。语句对待不同是因为考虑语句是否安全，并且和binary
log格式有关。

ž  
当使用行日志模式，安全和非安全没有什么区别。

ž  
当使用混合模式，如果语句被标记为不安全就会以行格式被记录，语句如果是安全的就以基于语句格式被记录

ž  
当使用基于语句的日志，语句如果被标记为不安全的就会生成一个警告。如果是安全的就会正常记录日志。

每个语句被标记为不安全都会生成一个日志。如果大量语句在master被生成，会导致大量的error生成在错误日志上。为了防止这个，MySQL
5.7提供了一个机制，如果最近的50个ER_BINLOG_UNSAFE_STATEMENT被生成在任意50秒内，告警抑制就会被启动。当达到就不会被写入到错误日志，而是每50个生成一个提醒，写入到错误日志，直到50秒内生成的错误少于50个，之后还是一个错误一条记录正常记录。告警抑制不会影响安全语句的应用，告警如何发到客户端。MySQL客户端还是回收到告警。

语句被认为是不安全。语句如果有下列特性就是不安全的:

ž  
语句包含以下系统函数，因为在slave会返回不同的值。FOUND_ROWS(), GET_LOCK(), IS_FREE_LOCK(),IS_USED_LOCK(), LOAD_FILE(), MASTER_POS_WAIT(),PASSWORD(), RAND(), RELEASE_LOCK(), ROW_COUNT(),SESSION_USER(), SLEEP(), SYSDATE(), SYSTEM_USER(),USER(), UUID(), and UUID_SHORT().
非确定性函数但是不认为是不安全的。尽管这些函数是不确定的，但是会被认为是安全的：CONNECTION_ID(),CURDATE(), CURRENT_DATE(), CURRENT_TIME(),CURRENT_TIMESTAMP(), CURTIME(),, LAST_INSERT_ID(),LOCALTIME(), LOCALTIMESTAMP(), NOW(), UNIX_TIMESTAMP(),UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP().

ž  
引用了系统变量。很多系统变量不能被正确的复制，在基于语句格式下。

ž  
UDFs,因为我们无法控制UDF是做什么的，我们会假设是不安全的。

ž  
全文插件。这个插件可能在不同的MySQL服务是不一样的，因此依赖于语句的会导致不同的结果。所有的依赖于全文插件的都会被认为是不安全的。

ž  
触发器，存储过程更新带AUTO_INCREMENT的列。这个是不安全的因为master上的相应行和slave上的不一样。
另外，INSERT into表是组合主键，如果主键包含AUTO_INCREMENT并且不是第一个主键的列，那么也是不安全的。

ž  
INSERT…ON DUPLICATE KEY UPDATE语句在表中有多个primary或者唯一键。当执行的表包含多余一个primary key或者唯一键。这个语句被认为是不安全的，因为存储引擎检查key，是不确定的。所以是不安全的。

ž  
使用limit更新。因为获取的行是无法确定的，所以是不安全的。

ž  
访问和应用日志表。系统日志表在slave和master是不同的所以不安全。

ž  
在事务之后执行非事务操作。在事务内，任何事务的读写之后运行，非事务的读写都认为是不安全的。

ž  
LOAD DATE INFILE语句，LOAD DATE INFILE被认为不安全。

18.2.2 复制实现细节

MySQL复制能力的实现有3个线程，一个在master上，2个在slave上：

ž  
Binlog dump线程。当slave连接，master会创建一个线程用来发送binary log的内容。这个可以使用show
processlist发现。
binary log dump线程获取master binary log的读锁，当事件读完，锁就释放，甚至在发送到slave之前。

ž  
Slave I/O 线程。当执行start slave语句，slave创建I/O先，用来连接到master，并且要求发送binary log中的记录。
slave I/O先读取binary log dump的记录复制到本地relay
log中
可以通过show slave status 查看slave_io_running的状态。

ž  
Slave SQL thread，slave黄建一个线程来读取relay
log的内容并且执行它们。

按上面，master和slave中有3个线程。如果有多个slave，那么master会为每个slave连接创建一个binary log dump线程，每个slave都有自己的I/O，SQL线程。

slave使用2个线程来分离读取master上的更新和执行这些更新，因此，读取语句不会因为语句执行的慢，而被拉后。如果slave在SQL
thread执行完所有的语句前停止了。因为有一份安全的副本在relay log，因此下次slave启动的时候会正常执行。

show processlist语句提供了一些关于复制的信息：

ž  
在master上show
processlist输出

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

     Id: 2

   User: root

   Host: localhost:32931

     db: NULL

Command: Binlog Dump

   Time: 94

  State: Has sent all binlog to slave; waiting for binlog to

         be updated

   Info: NULL

thread 2就是binlog dump，state信息表示等待更多的更新发生。如果没有发现这个线程，表示服务没有启动，没有slave连接到master上。

ž   在slave上，show processlist输出：

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

     Id: 10

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 11

  State: Waiting for master to send event

   Info: NULL

*************************** 2. row ***************************

     Id: 11

   User: system user

   Host:

 db: NULL

Command: Connect

   Time: 11

  State: Has read all relay log; waiting for the slave I/O

         thread to update it

   Info: NULL

线程10 的状态信息表示I/O线程已经连接到了master，线程11表是已经执行了relay log中的日志。这个时候所有的线程都是理想状态等待后续的更新。

18.2.3 复制渠道

MySQL 5.7.6出了一个复制渠道的概念，表示从master 到slave的路径。为了兼容以前的版本在MySQL服务启动后自动创建默认的渠道，渠道名为“”。

复制渠道表示事务从master到slave的提交。多主复制的slave会打开多个渠道，每个master一个，每个渠道都有自己的relay log和SQL 线程。一旦事务在渠道接收器 IO thread接收，就被增加到这个渠道的relay log文件并且传递到应用线程。

复制渠道也有自己的host和端口， 你可以配置多个渠道使用相同的host和端口，在MySQL 5.7，一个slave最多可以有256个多主复制拓扑。没有复制渠道要有唯一的名称。

18.2.3.1 单个渠道的命令和选项

以下命令有 FOR CHANNEL 选项：

·         CHANGE MASTER TO

·         START SLAVE

·         STOP SLAVE

·         SHOW RELAYLOG EVENTS

·         FLUSH RELAY LOGS

·         SHOW SLAVE STATUS

·         RESET SLAVE

以下函数可以参入channel_name参数：

·         MASTER_POS_WAIT()

·         WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()

18.2.3.2 版本兼容

如果没有指定FOR CHANNEL那么会应用到所有的channel：start slave,stop slave,show slave status,flush relay logs,reset slave。

有些语句不能在所有的channel上执行，需要指定 FOR CHANNEL：

·         SHOW RELAYLOG EVENTS

·         CHANGE MASTER TO

·         MASTER_POS_WAIT()

·         WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()

·         WAIT_FOR_EXECUTED_GTID_SET()

18.2.3.3 复制渠道启动选项

具体看：http://dev.mysql.com/doc/refman/5.7/en/channels-startup-options.html

18.2.3.4 复制渠道命名协定

具体看：

http://dev.mysql.com/doc/refman/5.7/en/channels-naming-conventions.html

18.2.4 复制 Relay日志和状态日志

在复制的时候，slave服务创建一些日志用来保存从master传过来的binary log。记录信息和当前状态记录在relay log。这里有3种类型的日志处理：

1.Relay log由从master读过来的binary log组成。

2.master info log包含当前用来连接到master的配置信息。这个日志包含master host，登录凭证和相关的读取了多少master binary log。一般写在mysql.slave_master_info表，可以通过参数—master-info-repository=table来设置。

3.relay log信息保存了执行点的状态信息。通过—replay-log-info-repository=table来设置保存到mysql.slave_relay_log_info表里面。

Crash-safe replication。为了让复制能够crash safe使用表来记录状态和日志信息，这些表必须是innodb表。因此为了保证crash safe 要把--relay-log-recovery和参数--relay-log-info-repository=table。

在MySQL 5.7，mysqld不能够启用初始化日志日志表 ，但是slave依然可以启动。

在MySQL 5.7，在复制执行中，slave_master_info或者slave_relay_log_info，执行任何语句获取写锁，都是不被运行的。只有读可以允许被执行。

18.2.4.1 Slave Relay Log

Relay log和binary log相似，有一系列编号的文件包含了事件，这些事件用来描述数据库的修改。

Relay log和binary log格式一样，可以使用mysqlbinlog读取。默认relay log的文件名为 host_name-relay-bin.nnnnnn  host_name是slave的hostname。默认relay log文件和relay log所有文件可以自己定义。使用参数—relay-log,--relay-log-index

如果slave使用默认hostname的命名方式，修改slave host name会导致复制报错，Failed to open the relay log and Could not find target log during relay log initialization. 可以通过指定—relay-log,--relay-log-index来避免这个问题。

如果已经发生了这个问题，有个方法可以修复，仙停止slave服务，把老的relay log index文件写入到新的，然后重启服务。

在以下情况下，slave服务会创建一个新的relay log文件：

1.每次IO线程启动

2.当log被flush

3.当前relay log变的太大：

如果max_relay_log_size大于0，那么就是超过这个值。

如果max_relay_log_size = 0,max_binlog_size决定了relay log的最大大小。

SQL Thread不在需要的relay文件会被自动删除。

18.2.4.2 Slave状态日志

复制slave服务创建2个日志，默认日志文件命名为master.info和relay-log.info，可以通过—master-info-file,--relay-info-file修改文件名。而且这个2个文件也可以写在表里面通过设置—master-info-repository,写入到mysql.slave_master_info表中。设置—relay-log-info-repository把relay info log 写入到mysql.slave_relay_log_info。

Status log包含的信息也显示在show slave status上。因为状态文件是以文件保存的，他们会在slave服务关闭之后保存下来。等到下次启动的时候，读取这2个文件决定已经从master读取了多少binary log，并且已经执行了多少relay logs。

Master info log或者表要被保护起来，因为里面包含了连接到master的账号密码。

当备份slave数据的时候也要备份2个状态日志和relay日志文件。当恢复slave数据的时候这些数据也要被恢复。如果都是了relay log但是任然有relay log info日志，你可以检查来决定sql thread已经执行了多少master binary log。然后可以使用change master to来决定冲那里读取master的文件。当然这个master binary log文件要依然在master中。

18.2.5 服务如何评估复制过滤规则

如果master服务没有写入入局到binary log，语句不会被复制。如果服务记录了语句，语句会被发送的slave，slave决定是否运行语句。

在master上，你可以控制那些数据库记录binary，--binlog-do-db,--binlog-ignore-db选项用来控制写入binary log的数据库。

在slave侧，通过控制--replication-*选项控制那些语句在slave上执行。在MySQL 5.7.3之后这些过来可以动态的设置，使用语句CHANGE REPLICATION FILTER语句。

数据库级别的选项--replication-do-db，--replication-ignore-db会被先检查。语句对于数据库的影响要在--replication-wild-do-table前面。也就是说--replication-wild-do-table选项，只有在没有数据库级别的选项应用之后才会被检查。

避免do，-ignore选项混用，避免wildcard，nowildcard混用。

18.2.5.1 评估数据库级别复制和bianry log选项

当评估复制选项，slave开始检查查看是否有任何--replication-do-db，--replication-ignore-db选项，当使用--binlog-do-db,--binlog-ignore-db和上面相似，但是处理确实在master上。

使用基于语句的复制，默认数据库检查是否匹配，使用基于行的复制，数据修改数据库会被检查，不管binary log格式，以下图检查图片：

对于binary log步骤设计列表如下：

1.是否设置了--binlog-do-db会在—binlog-ignore-db选项

是，第二步

不是，记录语句退出

2.是否有默认数据库

是，第三步

否，ignore语句退出

3.是否在—binlog-do-db中

是，是否都匹配数据库

是，记录日志，并且推出

否，忽略语句并推出

否，继续第4步

4.是否有数据库匹配--binlog-ignore-db

是，忽略并且推出

否，记录语句推出。

--binlog-do-db有时候也可以认为忽略其他数据库。比如，使用了基于语句的日志，服务只有--binlog-do-db=sales就不会写入默认数据库不是sales的日志。当使用基于行的日志，使用了向东选项，只会记录只在sales发生修改的日志。

18.2.5.2 评估表复制选项

只有在以下一个条件为真的情况下才会评估表选项：

1.没有匹配数据库被找到

2.匹配数据库级别的过滤

首先，最为初步条件，slave检查是否启动了基于语句的复制。如果启动了，并且语句发生在存储函数中，则执行语句退出。如果是基于行的复制，slave不知道语句是否发生在存储函数中，所以不应用。

到了这里，如果没有表的复制选项，那么slave会直接执行，如果有—replicate-do-table或者—replicate-wild-do-table选项，那么时间必须匹配这些选项的其中一个。否则会被忽略。如果没有—replicate-ignore-table或者—replicate -wild-ignore-table选项，所有的事件不匹配的都会被执行。执行流程：

18.2.5.3 复制规则应用

假设我们有2个表db1. mytb1，db2.mytb2。选项设置如下：

然后执行以下语句：

这种情况下就和binary log有关了：

基于语句复制：使用use导致db1变成默认数据库，这样—replicate-ignore-db选项匹配就会被忽略。

基于行复制：默认数据库不会影响slave读取数据库选项。因此use语句的数据库和—replicate-ignore-db一样，但是insert的数据库和选项不批匹配，然后检查表选项，和—replicate-do-table匹配，行被插入。

18.3 复制解决方案

18.3.1 使用复制备份

如果复制是作为备份的一个解决方案，复制master的数据到slave，备份slave数据。Slave可以暂停，关闭不会影响master的运行。

如何备份数据库和用途有关，比如只是备份数据，或者备份数据用来创建新的slave：

1.如果为了备份数据，并且数据库大小不是很大，那么可以使用mysqldump工具进行备份。

2.对于大数据库，mysqldump效率很低，可以直接复制原生的数据文件。

18.3.1.1 使用mysqldump备份slave

使用mysqldunmp备份数据库，因为备份结果是sql语句，可以很简单的发布或者应用到其他服务，紧急使用。

1.停止slave

2.使用mysqldump导出所有数据库

3.启动slave

18.3.1.2 备份原生数据

为了保证文件的一致性，备份文件只能在服务关闭的情况下进行。如果mysql服务还是运行的，后台任务依然会去修改数据库文件，如果是innodb表那么这些问题在crash恢复的时候会被修正。

1.关闭mysql服务

2.使用cp，tar等命令复制数据文件。

3.启动服务

如果你想要恢复一个slave，通常要备份整个数据目录。Slave数据，slave状态文件，master info文件relay log info，relay log文件。这些文件在恢复复制的时候会使用到。

如果你丢失了relay日志但是依然有relay-log.info文件，你可以检查slave已经运行到了什么地方然后使用change
master to来告诉slave需要重新读取的binary
log。

如果slave。

18.3.1.3 标记为只读备份master或者slave

通过在master或者slave上，设置全局读锁修改到只读模式：

1.服务设置为只读，堵塞写入

2.执行备份

3.修改服务为读写模式

以下是具体如何操作：

ž  
Master服务，M1

ž  
Slave服务，S1，M1作为master

ž  
C1连接到M1

ž  
C2连接到S1

场景1：在master上使用只读备份

把master设置到只读状态：

当M1只读状态，一下属性为真：

1.因为是只读模式，所有更新都会堵塞。

2.但是可以查询数据

3.备份M1是安全的

4.在s1上备份不安全，因为服务依然是运行的，可能在执行binary
log或者执行来着C2的更新。

当备份完成有，执行以下语句：

场景2：备份只读Slave

执行以下脚本：

这个是比较流行的备份方式：有一个slave执行备份不会有什么问题，因为不会影响网络，并且系统任然能够正常运行。

备份完成后执行：

18.3.2 复制Master和Slave不同引擎

Master和slave存储引擎不一样是没有问题的。实际上，default_storage_engine和storage_engine是不会被复制的。

使用不同的存储引擎在不同场景有不同的好处。

使用不同的存储引擎还决定于，复制初始化的过程：

1.如果你使用吗，mysqldump创建数据库快照，那么可以使用文本编辑替换存储引擎

2.如果使用原文件备份，就不能在初始化阶段修改，要在slave配置好有alter table修改。

3.如果复制已经创建，并且master没有表，那么在创建表的时候不要指定存储引擎。

如果已经有复制了，但是想要修改表的存储引擎：

1.关闭slave

2.使用alter table修改存储引擎

3.启动slave。

虽然参数default_storage_engine不会被复制，但是create table和alter table的语句包含了存储引擎还是会被复制的。

18.3.3使用复制的横向扩展

你可以把复制作为扩展解决方案，比如想要分离负荷到其他服务器。因为复制可以分布一个或者多个slave。这个最好使用在读多写少的情况下。

这样的情况下读取负荷可以分散到复制slave。当有写入服务的时候还是写入到master。如图：

18.3.4 不同的slave复制不同的数据库

有一个master但是想把不同的数据库复制到不同的slave，如图：

那么可以在每个slave上使用—replication-wild-do-table来处理。比如：

1. slave1，--replicate-wild-do-table=databaseA.%

2. slave2，--replicate-wild-do-table=databaseB.%

3. slave3，--replicate-wild-do-table=databaseC.%

每个slave都会收到整个master的binary log，但是只执行在--replicate-wild-do-table内的。

如果在复制开始前有数据要挺不到slave，那么有几个选择：

1.同步所有的数据到每个slave，然后删除不要的表或者数据库。

2.使用mysqldump为每个数据库创建slave。

3.使用原生文件和特定的文件和数据库。

18.3.5 提高复制性能

Slave连接到master的线程尽管开销很小，但是也会有影响。每个slave必须接受所有的binary log，网络带宽也会影响。

如果有大量的slave，master还需要处理用户请求，这个时候你就想要提升复制的性能。

有个方法提高性能是创建一个配对复制结构让master只复制到一个slave。然后其他的slave都连接到这个slave上。如图：

为了让上图工作，必须如下配置：

1.master1,是主master所有的修改都会写入到这里。这台要启动binary
log

2.master2，是master1的slave，master2也要启动binary log并且—log-slave-update，这样master1的写入才会被记录到master2的binary log。

3.slave1,slave2,slave3连接到master2,复制master2上的信息。

以上解决方案，减少了用户负荷和网络接口的负荷，可以主master的总体性能。

如果slave更上master有困难，那么有以下几个措施：

1.如果可以，把relay log和数据文件分开到不同的磁盘，通过配置—relay-log选项。

2.如果slave比master慢很多，你可能想要把不同的数据库分到不同的slave。

3.如果你master使用了事务，如果你不在乎事务，那么可以在slave上使用myisam表。

4.如果slave不作为master，并且保证在事件错误的时候可以跟上master，然后关掉log-slave-updates.防止了slave爆炸，也可以把他们执行的记录到binary log。

18.3.6 在错误的时候切换Master

当使用GTID的复制，你可以在错误的时候使用mysqlfailover在master和slave之间进行切换。如果没有使用GTID那么就无法使用mysqlfailover，你不许配置一个master和多个slave。然后你需要些一个脚本或者程序来监控master检查是否正常，并且指示slave和应用程序切换到另外一个master。

你可以通过change master通知一个slave修改master。slave不会检查master的数据库是否和slave兼容。指示简单的开始读取并且执行指定master的binary log。在故障转移时，所有的组内的服务执行相同的事件来自相同的binary
log文件，所以修改事件的来源不会影响数据库结构和数据的一致性。保证修改的安全性。

slave也要开始--log-bin选项，如果没有使用GTID那么要开启--log-slave-update这样，slave变成master不需要重启slave的mysqld服务。假设目前你的数据库结构如下：

MySQL Master保存了master数据库，MySQL Slave是复制的slave。并且web client发生数据库读写。Web Client的读取会直接走slave不再这里显示，不需要再错误发生后切换。

每个slave都启动了--log-bin,--log-slave-updates.因为从master收到的修改不会被写入到binary log除非开了--log-slave-updates。这样的话当MySQL
Master不可用，叫你可以选一个slave变成master。比如slave 1，那么所有的Web Client会重新定向到Slave 1，写入都会被写入binary log。Slave 2，Slave3从Slave
1上复制。

不开--log-slave-updates的理由是防止slave变成master之后防止slave会受到2次更新。如果Slave 1有--log-slave-updates开启，那么会把master上的修改也写入到binary log，那么slave 1变成master之后，slave
2会接收到master之前的修改。

保证所有的slave已经读取了所有的relay
log，执行stop slave io_thread，然后show
preocesslist，然后会看到has read all realy log。当所有的slave都是这样，就可以重新配置到新的。在slave 1上执行stop slave并且reset master。

在slave2，slave3，运行stop slave然后change master to=“salve 1”修改master。执行change
master修改到slave1。change master
to语句不需要指定slave 1的binary log文件名字或者log位置，因为第一个binary log位置是4.然后，在slave2，slave3启动slave。

一旦新的复制被创建，你就需要通知每个web client连接到slave 1。所有的更新语句都发送到slave1。

当master 变得可用，就在master上执行change master to语句。master 变成slave1的slave。

如果slave1不可用，为了master变成master，使用前面的过程让master变成新的master。在设置master变成master之前，不要忘记执行reset master。

要注意的是slave并不是同步的，也就是说有些slave比较靠前，有些延迟比较多。可能没有办法像之前的预想一样。在实际操作中，尽量让slaves的relay
log比较接近。有个方法可以让应用程序定位到master，就是master有个动态DNS。使用bind可以使用nsupdate来动态更新dns。

18.3.7 使用安全连接配置复制

使用安全连接加密binary log的传输，master和slave必须都支持加密连接，如果任一一个不支持就不能使用加密连接。

配置安全连接和客户端/服务端连接差不多。你必须使用合适的安全证书来创建连接。

为了在master启用安全连接，必须创建或者获取证书和key文件，并且增加以下配置选项：

文件的路径可以是相对的也可以是绝对的。推荐使用绝对路径。

1.ssl-ca 表明CA证书

2.ssl-cert 表示公钥证书

3.ssl-key 表示私钥

在slave，有2个方法设置要的信息。你可以在配置文件的[client]中配置，或者在change
master中配置：

1.在配置文件[client]中配置：

重启slave，使用--skip-slave-start防止连接到master，使用change master to修改设置使用ssl：

2.直接在change master to上设置：

设置好后start slave启动。

你可以使用show slave status查看安全连接是否成功。

如果你想要强制使用安全连接，创建一个用户设置REQUIRE SSL选项，并设置复制权限。

如果账号已经存在，你可以直接加REQUIRE SSL

18.3.8 半同步复制

处理异步复制之外，MySQL 5.7支持半同步复制。复制默认是异步的。master写事件到binary log不管slave是否获取处理他们。使用异步复制，如果master崩溃，已经提交的事务可能无法传输到其他slave。

半同步复制是复制的一个替代：

1.slave表示是否可以半同步连接到master

2.如果半同步复制在master上启动，至少有一个半同步slave，一个在master执行提交的事务堵塞并且等待直到一个半同步slave通知已经收到了事务的所有事件，或者发生超时。

3.Slave在relay log被写入到磁盘后通知

4.如果超时发生，master被还原为异步复制。当至少一个slave跟上事务后，master恢复半同步复制。

5.半同步复制必须在master和slave都启动。如果半同步复制在任意一端被禁用master使用异步复制。

当master被堵塞，执行的事务没有返回。当堵塞结束，master返回到会话，就可以执行其他语句。这个时候会话提交的事务至少已经传到了一个slave。

MySQL 5.7.3之后，通知的事务个数可以通过参数repl_semi_sync_master_wait_for_
slave_count设置默认为1.

当事务已经写入binary log，但是回滚了，也会堵塞。当事务修改了非事务表被回滚的情况。回滚的事务也会被记录，因为非事务表不能回滚，只能发送到slave。

对于语句不是在事务内的，如果启动的自动提交，那么每个语句会隐式提交。当半同步复制会堵塞所有的语句，就好像了调用了显示事务一样。

半同步复制被称为半同步的原因：

1.对于异步复制无法保证每个事务发送到了任意一个slave。

2.对于同步复制，当master提交事务所有的slave必须受到之后master才能返回。

3.半同步复制在异步复制和同步复制之前，只要有一个slave收到了事务就可以返回。

比较异步复制，半同步复制提供更好的数据一致性。对于同步复制一旦有个slave太慢那么就会拖累整个复制。

半同步复制有一些性能印象，因为提交比较慢因为要等slave返回。

Repl_semi_sync_master_wait_point系统变量控制master在什么时候等待 slave通知才能返回会话：

AFTER_SYNC:默认，master把每个事务写入到binary log和slave，并且同步binary log 到磁盘。Master等待slave通知。接受到通知后，master提交事务到存储引擎并且返回到客户端。

AFTER_COMMIT:master把事务写入到binary log 和slave，同步binary log，提交事务到存储引擎，master等待slave通知事务已经被slave接受。接收到通知，master返回到client然后继续处理。

不同设置，不同特性：

1.使用AFTER_SYNC，所有客户端可以同时看到提交的事务，接收到通知后提交到存储引擎。所以所有的client在master都看到相同的数据。

2.使用AFTER_COMMIT，客户端获取事务提交到存储引擎，并且获取slave通知后才返回。在提交后，但是通知前，其他客户端可以比提交事务先看到数据。

如果因为一些错误slave没有处理事务，master
crash，切到slave，有可能会出现一部分数据丢失。

18.3.8.1 半同步复制管理接口

半同步复制的管理接口有一些组件：

ž  
2个插件实现半同步复制的能力。一个是master侧一个是slave侧、

ž  
有一些系统变量可以控制插件的行为:

o   
Rpl_semi_sync_master_enabled
控制半同步复制是否在master上启动。为了启动或者禁用插件，默认为0.

o   
Rpl_semi_sync_master_timeout
毫秒控制master等待slave通知的超时时间。

o   
Rpl_semi_sync_slave_enabled
控制slave半同步复制是否启动。

ž  
用来监控复制的状态变量：

o   
Rpl_semi_sync_master_clients
半同步的slave个数

o   
Rpl_semi_sync_master_status
如果是1表示插件已经启动并且提交通知还没有发生，如果是0表示插件没有启动，或者已经退回为异步复制。

o   
Rpl_semi_sync_master_no_tx
没有被成功通知的事务

o   
Rpl_semi_sync_slave_status
如果为1表示slave IO 线程已经运行，0表示没有运行。

变量和状态只有在安装了插件之后才可用。

18.3.8.2 半同步复制安装和配置

半同步复制使用插件实现，所以插件必须被撞到服务上并且启用。启动后可以通过系统变量来控制。系统变量在插件安装好后启用。

要使用半同步复制，要满足以下几点：

1.必须MySQL 5.5以上的版本。

2.MySQL服务要支持动态加载插件。

3.复制已经开始工作。

4.不能是多源复制。

使用下列命令来安装半同步复制。INSTALL PLUGIN,SET
GLOBAL,STOP SLAVE,START SLAVE.

MySQL发布的时候已经包含半同步复制插件。

安装组件目录里面的插件：

不同的系统要选择不同的缀名。如果不确定插件名字，可以到插件目录上查看。

如果在类linux系统上报错如下，那么就要安装libimf

可以通过show plugin查看已经安装的插件。

半同步复制安装好后，默认是不启用的。插件必须在master和slave同时启动。如果只有一边启动那么是异步复制。

通过设置变量来控制插件启动：

在master上：

在slave上：

如果slave在运行时，启动半同步复制，然后重启slave
I/O线程。

服务启动，要把半同步复制的参数写入到命令行或者配置文件中。

Master：

Slave：

18.3.8.3 半同步复制监控

半同步复制会释放出一些系统变量和状态变量可以通过来决定配置和操作状态。

比如使用show variables查看变量：

使用show status查看状态：

当master在异步和同步之间切换回根据提交堵塞超时时间或者slave
catching up来决定，通过rpl_semi_sync_master_status查看当前同步方式是同步还是异步。

通过rpl_semi_sync_master_clients查看连接的半同步复制slave个数。

提交的事务的成功通知未成功通知可以查看rpl_semi_sync_master_ves_tx和rpl_semi_sync_master_ves_no_tx。

在slave端使用rpl_semi_sync_slave_status。

18.3.9 延迟复制

MySQL 5.7支持延迟复制，slave比master延迟指定的时间。默认延迟为0可以通过change master设置延迟N秒。

一个事务收到后不会被运行，直到比master延迟N秒之后。不会影响事件或者日志文件的回绕，值会影响SQL thread线程。

延迟事务有几个用处：

1.保护用户误操作。

2.测试当有测试的时候系统如何处理。

3.用来检查以前数据库。

Start slave，stop slave会马上执行忽略任何延迟。Reset slave 重置延迟。

Show slave status有3个信息和延迟相关：

1.SQL_Delay：非负整数，表示要延迟的N秒

2.SQL_Remaining_Delay:当SQL_Running_state等待直到master执行完N秒之后。表示还剩下多少秒的延迟。

3.Slave_SQL_Running_State:字符串表示SQL
thread的状态。

如果SQL thread等待延迟，show
processlist会显示 Waiting until MASTER_DELAY seconds after master
executed event.

18.4 复制注意和提示

具体看：http://dev.mysql.com/doc/refman/5.7/en/replication-notes.html