参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/replication-features.html

16.4.1 复制 特性 和问题

16.4.1.1 复制和 AUTO_INCREMENT

基于语句的AUTO_INCREMENT，LAST_INSERT_ID（）和TIMESTAMP值复制可以正确完成，但有以下例外情况：

• 在MySQL 5.7.1之前使用基于语句的复制时，从属表中的AUTO_INCREMENT列必须与主服务器上的相同列匹配; 也就是说，必须将AUTO_INCREMENT列复制到AUTO_INCREMENT列。

• 使用基于语句的复制不能正确复制调用导致更新AUTO_INCREMENT列的触发器或函数的语句。 这些语句被标记为不安全。 （BUG＃45677）
• INSERT进入具有复合主键的表中，该复合主键的包含非第一列的AUTO_INCREMENT列，对于基于语句的日志记录或复制而言是不安全的。 这些语句被标记为不安全。 （Bug＃11754117，Bug＃45670）

• 使用ALTER TABLE将AUTO_INCREMENT列添加的到表可能不会在从属和主服务器上生成相同的行顺序。 发生这种情况是因为行的编号顺序取决于用于表的特定存储引擎以及插入行的顺序。 如果在主站和从站上具有相同的顺序很重要，则必须在分配AUTO_INCREMENT编号之前对行进行排序。 假设您要将AUTO_INCREMENT列添加到具有列col1和col2的表t1，以下语句将生成一个与t1相同但具有AUTO_INCREMENT列的新表t2：

CREATE TABLE t2 LIKE t1;

ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;

INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

重要

为了保证主服务器和从服务器上的相同顺序，ORDER BY子句必须命名t1的所有列。

刚刚给出的指令受CREATE TABLE ... LIKE的限制：外键定义被忽略，DATA DIRECTORY和INDEX DIRECTORY表选项也被忽略。 如果表定义包含任何这些特性，则使用CREATE TABLE语句创建t2，该语句与用于创建t1的语句相同，但添加了AUTO_INCREMENT列。

无论用于创建和填充具有AUTO_INCREMENT列的副本的方法如何，最后一步是删除原始表，然后重命名副本：

DROP t1;

ALTER TABLE t2 RENAME t1;

16.4.1.3 复制和 字符集

以下内容适用于使用不同字符集的MySQL服务器之间的复制：

• 如果主服务器的数据库的字符集与全局character_set_server值不同，则应设计CREATE TABLE语句，以便它们不会隐式依赖数据库缺省字符集。 一个好的解决方法是在CREATE TABLE语句中显式声明字符集和排序规则。

16.4.1.4 复制和 chechsum 表

CHECKSUM TABLE返回一个逐行计算的校验和，使用的方法取决于表行存储格式。 MySQL版本之间的存储格式不保证保持不变，因此校验和值可能会在升级后发生变化。

16.4.1.5 复制中的 CREATE ..... IF NOT EXISTS 语句

当复制各种CREATE ... IF NOT EXISTS语句时，MySQL应用这些规则：

• 无论数据库是否已存在于主服务器上，都会复制每个CREATE DATABASE IF NOT EXISTS语句。
• 同样，无论表是否已存在于主服务器上，都会复制每个不带SELECT的CREATE TABLE IF NOT EXISTS语句。 这包括CREATE TABLE IF NOT EXISTS ... LIKE。 复制CREATE TABLEIF IF NOT EXIST ... SELECT遵循一些不同的规则。
• CREATE EVENT IF NOT EXISTS 总是被复制，不管语句中命名的事件是否已存在于主服务器上。

16.4.1.6 复制 CREATE TABLE SELECT 语句

MySQL 5.7不允许CREATE TABLE ... SELECT语句在除语句创建的表之外的表进行任何更改。一些旧版本的MySQL允许这些语句这样做;这意味着，当在MySQL 5.6或更高版本的从服务器与运行先前版本的MySQL的主服务器之间使用基于语句的复制时，导致主服务器上其他表发生更改的CREATE TABLE ... SELECT语句在从服务器上失败，从而导致复制停止。要防止这种情况发生，您应该使用基于行的复制，在主服务器上运行它之前重写有问题的语句，或者将主服务器升级到MySQL 5.7。 （如果选择升级主服务器，请记住，升级后这样的CREATE TABLE ... SELECT语句会失败，除非重写它以消除对其他表的任何副作用。）使用基于行的这不是问题复制，因为该语句记录为CREATE TABLE语句，对表数据的任何更改都记录为行插入事件，而不是整个CREATE TABLE ... SELECT。

这些行为不依赖于MySQL版本：

• CREATE TABLE ... SELECT始终执行隐式提交
• 如果目标表不存在，则记录如下。 IF NOT NOT EXISTS是否存在并不重要。
• STATEMENT or MIXED 格式：该语句按书面记录。
• ROW 格式：该语句记录为CREATE TABLE语句，后跟一系列插入行事件。

• 如果语句失败，则不记录任何内容。 这包括目标表存在且未给出IF NOT EXISTS的情况。

当目标表存在并且给出了IF NOT EXISTS时，MySQL 5.7完全忽略该语句; 不会插入或记录任何内容。 在这方面，这些语句的处理在以前的MySQL版本中发生了很大变化。

16.4.1.7 CREATE SERVER, ALTER SERVER, 和 DROP SERVER 的复制

无论使用何种二进制日志记录格式，语句CREATE SERVER，ALTER SERVER和DROP SERVER都不会写入二进制日志。

16.4.18 CURRENT_USER()的复制

以下语句支持使用CURRENT_USER（）函数代替受影响的用户或定义者的名称，或可能的主机名：

• DROP USER
• RENAME USER
• GRANT
• REVOKE
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE TRIGGER
• CREATE EVENT
• CREATE VIEW
• ALTER EVENT
• ALTER VIEW
• SET PASSWORD

启用二进制日志记录并且CURRENT_USER（）或CURRENT_USER用作任何这些语句中的定义者时，MySQL Server确保在复制语句时将语句应用于主服务器和从服务器上的同一用户。 在某些情况下，例如更改密码的语句，函数引用在写入二进制日志之前会进行扩展，因此该语句包含用户名。 对于所有其他情况，主服务器上当前用户的名称将作为元数据复制到从服务器，并且服务器将语句应用于元数据中指定的当前用户，而不是从服务器上的当前用户。

16.4.1.9 DROP ... IF EXISTS 语句的复制

DROP DATABASE IF EXISTS，DROP TABLE IF EXISTS和DROP VIEW IF EXISTS语句总是被复制，即使主服务器上不存在要删除的数据库，表或视图。 这是为了确保一旦从属设备赶上主设备，主设备或从设备上不再需要这些对象。

存储程序（存储过程和函数，触发器和事件）的 DROP ... IF EXISTS 语句也被复制，即使主服务器上不存在要删除的存储程序。

16.4.1.10 在Master 和Slave 上不同定义表的复制

复制的源表和目标表不必相同。 主服务器上的表可以具有比服务器的从服务器副本更多或更少的列。 此外，主服务器和从服务器上的相应表列可以根据特定条件使用不同的数据类型。

注意

不支持不同分区表之间的复制。

在源表和目标表没有相同定义的所有情况下，主服务器和从服务器上的数据库和表名称必须相同。 在以下两节中，将通过示例讨论其他条件。

16.4.1.10.1在主服务器或从服务器上使用更多列进行复制

您可以将表从主服务器复制到从服务器，以使表的主副本副本具有不同数量的列，具体取决于以下条件：

• 两个版本的表共有的列必须在主服务器和从服务器上以相同的顺序定义。

（即使两个表的列数相同，也是如此。）

• 两个版本共有的列必须在任何其他列之前。

这意味着在从属服务器上执行ALTER TABLE语句，在两个表共有的列范围内将新列插入表中会导致复制失败，如以下示例所示：

假设主服务器和从服务器上存在的表t由以下CREATE TABLE语句定义：

CREATE TABLE t ( c1 INT, c2 INT, c3 INT );

假设此处显示的ALTER TABLE语句在slave上执行：

ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;

从属设备上允许使用先前的ALTER TABLE，因为表t的两个版本共有的列c1，c2和c3在任何不同的列之前保持在表的两个版本中组合在一起。

但是，在不导致复制中断的情况下，无法在从属服务器上执行以下ALTER TABLE语句：

ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;

在刚刚显示的ALTER TABLE语句的从属服务器上执行后，复制失败，因为新的列cnew2位于共有的列之间。

• 具有更多列的表版本中的每个“额外”的列必须具有默认值。

列的默认值由许多因素决定，包括它的类型，是否使用DEFAULT选项定义，是否声明为NULL，以及创建时生效的服务器SQL模式;

此外，当从表的副本具有比主副本更多的列时，表的每个列必须在两个表中使用相同的数据类型。

例子。 以下示例说明了一些有效和无效的表定义：

master 上有更多的列。 以下表定义有效且正确复制：

master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

slave> CREATE TABLE t1 (c1 INT, c2 INT);

下面的表定义会引发错误，因为表的两个版本共有的列的定义在从属服务器上的顺序与在主服务器上的顺序不同：

master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

slave> CREATE TABLE t1 (c2 INT, c1 INT);

下表定义也会引发错误，因为master上额外列的定义出现在表的两个版本共有的列的定义之前：

master> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);

slave> CREATE TABLE t1 (c1 INT, c2 INT);

在slave 上有更多的列。

以下表定义有效且正确复制：

master> CREATE TABLE t1 (c1 INT, c2 INT);

slave> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

以下定义会引发错误，因为表的两个版本共有的列在主服务器和从服务器上的定义顺序不同：

master> CREATE TABLE t1 (c1 INT, c2 INT);

slave> CREATE TABLE t1 (c2 INT, c1 INT, c3 INT);

下表定义也会引发错误，因为从表版本的表中额外列的定义出现在表的两个版本共有的列的定义之前：

master> CREATE TABLE t1 (c1 INT, c2 INT);

slave> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);

下表定义失败，因为表的slave版本与master版本相比具有其他列，并且表的两个版本对公共列c2使用不同的数据类型：

master> CREATE TABLE t1 (c1 INT, c2 BIGINT);

slave> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

16.4.1.10.2 字段有不同数据类型的复制

主机上的相应列和同一表的从机副本理想地应该具有相同的数据类型。 但是，只要满足某些条件，并不总是严格执行。

通常可以从给定数据类型的列复制到相同类型和相同大小或或更大宽度的另一列（如果适用）。 例如，您可以从CHAR（10）列复制到另一个CHAR（10），或从CHAR（10）列复制到CHAR（25）列，没有任何问题。 在某些情况下，也可以从具有一种数据类型（在主服务器上）的列复制到具有不同数据类型的列（在从服务器上）; 当主列版本的数据类型被提升为从属大小相同或更大的类型时，这称为属性提升。

属性提升可以与基于语句的复制和基于行的复制一起使用，并且不依赖于主服务器或从服务器使用的存储引擎。 但是，日志格式的选择会对允许的类型转换产生影响; 详情将在本节后面讨论。

重要

无论您使用基于语句还是基于行的复制，如果您希望使用属性提升，则表的从属副本不能包含比主副本更多的列。

基于语句的复制

当使用基于语句的复制时，一个简单的经验法则是，“如果在主服务器上运行的语句也将在从服务器上成功执行，它也应该成功复制”。 换句话说，如果语句使用的值与从属服务器上给定列的类型兼容，则可以复制该语句。 例如，您可以将适合TINYINT列的任何值插入到BIGINT列中; 因此，即使您将表的从属副本中的TINYINT列的类型更改为BIGINT，在成功的主服务器上对该列的任何插入也应该在从服务器上成功，因为不可能有合法的TINYINT 足以超过BIGINT列的值。

在MySQL 5.7.1之前，当使用基于语句的复制时，主服务器和从服务器上的AUTO_INCREMENT列必须相同; 否则，更新可能会应用于从站上的错误表。 （BUG 12669186）

基于行的复制：属性提升和降级。

基于行的复制支持较小数据类型和较大类型之间的属性提升和降级。 还可以指定是否允许降级列值的有损（截断）或无损转换，如本节后面所述。

有损或者无损转换

如果目标类型不能表示要插入的值，则必须决定如何处理转换。 如果我们允许转换但需要截断（或以其他方式修改）源值以在目标列中实现“合适”，则我们进行的是有损转换。 不需要截断或类似修改以适合目标列中的源列值的转换是无损转换。

类型转换模式（ slave_type_conversions 变量）

slave_type_conversions全局服务器变量的设置控制slave上使用的类型转换模式。 此变量从下表中获取一组值，该表显示每种模式对从属的类型转换行为的影响：

ALL_LOSSY：

在此模式下，允许丢失信息的类型转换。

这并不意味着允许进行无损转换，只是仅允许需要有损转换或根本不需要转换的情况; 例如，仅启用此模式允许INT列转换为TINYINT（有损转换），而不是TINYINT列转换为INT列（无损）。 在这种情况下尝试后一种转换会导致复制停止，从而出现错误。

ALL_NON_LOSSY

此模式允许不需要截断或对源值进行其他特殊处理的转换; 也就是说，它允许目标类型具有比源类型更宽范围的转换。

设置此模式与是否允许有损转换无关; 这是通过ALL_LOSSY模式控制的。 如果仅设置ALL_NON_LOSSY但不设置ALL_LOSSY，则尝试导致数据丢失的转换（例如INT到TINYINT或CHAR（25）到VARCHAR（20））会导致从站停止并出现错误。

ALL_LOSSY,ALL_NON_LOSSY：

设置此模式后，允许所有支持的类型转换，无论它们是否为有损转换。

ALL_SIGNED：

将被提升的整数类型视为有符号值（默认行为）。

ALL_UNSIGNED：

将被提升的整数类型视为无符号值。

ALL_SIGNED,ALL_UNSIGNED：

如果可能的话，将被提升的整数类型视为有符号的，否则是无符号的

[empty]：

未设置slave_type_conversions时，不允许属性提升或降级; 这意味着源表和目标表中的所有列必须是相同的类型。

此模式是默认模式。

提升整数类型时，其符号不会被保证。 默认情况下，从站将所有此类值视为有符号的。 从MySQL 5.7.2开始，您可以使用ALL_SIGNED，ALL_UNSIGNED或两者来控制此行为。 （bug 15831300）ALL_SIGNED告诉slave将所有提升的整数类型视为signed; ALL_UNSIGNED指示它将这些视为无符号。 指定两者会导致从属服务器将值视为有符号的，否则将其视为无符号; 它们的列出顺序并不重要。 如果还没有使用ALL_LOSSY或ALL_NONLOSSY中的至少一个，则ALL_SIGNED和ALL_UNSIGNED都不会产生任何影响。

变更类型模式需要设置新的 slave_type_conversions 变量并重启slave 服务。

被支持的 转换。以下列表中显示了不同但相似的数据类型之间支持的转换：

• 在任何整数类型之间TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。

这包括这些类型的符号和无符号版本之间的转换。

通过将源值截断为目标列允许的最大值（或最小值）来进行有损转换。 为了确保从无符号类型转换为有符号类型时的无损转换，目标列必须足够大，以适应源列中的值范围。 例如，您可以将TINYINT UNSIGNED非损耗降级为SMALLINT，但不降级为TINYINT。

• 在任何十进制类型DECIMAL，FLOAT，DOUBLE和NUMERIC之间。

FLOAT到DOUBLE是一种无损转换; DOUBLE 到FLOAT 只能被有损处理。 从DECIMAL（M，D）到DECIMAL（M'，D'）的转换，其中D'> = D且（M'-D'）> =（M-D）是无损的; 对于M'<M，D'<D或两者的任何情况，只能进行有损转换。

对于任何十进制类型，如果要存储的值不适合目标类型，则根据为文档中其他位置为服务器定义的舍入规则对该值进行舍入。

• 在任何字符串类型CHAR，VARCHAR和TEXT之间，包括不同宽度之间的转换。

将CHAR，VARCHAR或TEXT转换为相同大小或更大的CHAR，VARCHAR或TEXT列永远不会有损。 通过在从属设备上仅插入字符串的前N个字符来处理有损转换，其中N是目标列的宽度。

重要：

在字段中使用不同的字符集来复制是不被支持的

• 在任何二进制数据类型BINARY，VARBINARY和BLOB之间，包括不同宽度之间的转换。

将BINARY，VARBINARY或BLOB转换为相同大小或更大的BINARY，VARBINARY或BLOB列永远不会有损。 通过在从站上仅插入字符串的前N个字节来处理有损转换，其中N是目标列的宽度。

• 在任何2个大小的的任何2个BIT列之间。

当将来自BIT（M）列的值插入到BIT（M'）列中，其中M'> M时，BIT（M'）列的最高有效位被清除（设置为零）并且M位为 BIT（M）值被设置为BIT（M'）列的最低有效位。

当将来自源BIT（M）列的值插入目标BIT（M'）列时，其中M'<M，分配BIT（M'）列的最大可能值; 换句话说，将“全部设置”值分配给目标列。

在列表中没列出的类型转换都不支持

16.4.1.11 复制和DIRECTORY 表选项

如果在主服务器上的CREATE TABLE语句中使用DATA DIRECTORY或INDEX DIRECTORY表选项，则表选项也用于从服务器。 如果从属主机文件系统中不存在相应的目录，或者它存在但从属服务器无法访问，则会导致问题。 这可以通过在从属服务器上使用NO_DIR_IN_CREATE服务器SQL模式来覆盖，这会导致从属服务器在复制CREATE TABLE语句时忽略DATA DIRECTORY和INDEX DIRECTORY表选项。 结果是MyISAM数据和索引文件在表的数据库目录中创建。

16.4.1.12 复制和浮点值

使用基于语句的复制，值将从十进制转换为二进制。 因为它们的十进制和二进制表示之间的转换可能是近似的，所以涉及浮点值的比较是不精确的。 对于显式使用浮点值或使用隐式转换为浮点值的操作，情况也是如此。 由于计算机体系结构，用于构建MySQL的编译器等的差异，浮点值的比较可能在主服务器和从服务器上产生不同的结果。

16.4.1.14 复制和FLUSH

某些形式的FLUSH语句不会被记录，因为如果复制到从站可能会导致问题：FLUSH LOGS和FLUSH TABLES WITH READ LOCK。 有关语法示例，请参见第13.7.6.3节“FLUSH语法”。 FLUSH TABLES，ANALYZE TABLE，OPTIMIZE TABLE和REPAIR TABLE语句将写入二进制日志，从而复制到从属服务器。 这通常不是问题，因为这些语句不会修改表数据。

但是，这种行为在某些情况下会造成困难。 如果复制mysql数据库中的权限表并直接更新这些表而不使用GRANT，则必须在从属服务器上发出FLUSH PRIVILEGES以使新权限生效。 此外，如果在重命名属于MERGE表的MyISAM表时使用FLUSH TABLES，则必须在从属设备上手动发出FLUSH TABLES。 除非您指定NO_WRITE_TO_BINLOG或其别名LOCAL，否则这些语句将不会写入二进制日志。

16.4.1.15 复制和系统函数

某些函数在某些情况下不能很好地复制：

• USER（），CURRENT_USER（）（或CURRENT_USER），UUID（），VERSION（）和LOAD_FILE（）函数在被复制的时候不会变更，因此除非启用了基于行的复制，否则无法在从属设备上可靠地工作。

使用MIXED模式时，使用基于行的复制自动复制USER（）和CURRENT_USER（），并在STATEMENT模式下生成警告。 （另请参见第16.4.1.8节“CURRENT_USER（）的复制”。）对于VERSION（）和RAND（）也是如此。

• 对于NOW（），二进制日志包括时间戳。 这意味着在主服务器上对此函数的调用返回的值将复制到从服务器。 为避免在不同时区的MySQL服务器之间复制时出现意外结果，请在主服务器和从服务器上设置时区。

• 要解释在不同时区的服务器之间复制时可能出现的问题，假设master位于纽约，slave位于斯德哥尔摩，并且两个服务器都使用本地时间。 进一步假设，在master上，你创建一个表mytable，在这个表上执行一个INSERT语句，然后从表中执行select，如下所示：

mysql> CREATE TABLE mytable (mycol TEXT); Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO mytable VALUES ( NOW() ); Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;

+---------------------+

| mycol |

+---------------------+

| 2009-09-01 12:00:00 |

+---------------------+

1 row in set (0.00 sec)

斯德哥尔摩当地时间比纽约晚6个小时; 因此，如果您在同一时刻在从站上发出SELECT NOW（），则返回值2009-09-01 18:00:00。 因此，如果在复制了刚刚显示的CREATE TABLE和INSERT语句后从slave的副本中查询，则可能需要mycol包含值2009-09-01 18:00:00。 然而，这种情况并非如此; 当您从slave的mytable副本中查询时，您获得的结果与主完全相同：

mysql> SELECT * FROM mytable;

+---------------------+

| mycol |

+---------------------+

| 2009-09-01 12:00:00 |

+---------------------+

1 row in set (0.00 sec)

与NOW（）不同，SYSDATE（）函数不是复制安全的，因为它不受二进制日志中SET TIMESTAMP语句的影响，并且如果使用基于语句的日志记录是不确定的。 如果使用基于行的日志记录，这不是问题。

另一种方法是使用--sysdate-is-now选项使SYSDATE（）成为NOW（）的别名。 这必须在主站和从站上完成才能正常工作。 在这种情况下，此函数仍会发出警告，但只要在主服务器和从服务器上都使用--sysdate-is-now，就可以安全地忽略该警告。

使用MIXED模式时，使用基于行的复制自动复制SYSDATE（），并在STATEMENT模式下生成警告。

• 以下限制仅适用于基于语句的复制，而不适用于基于行的复制。 处理用户级锁的GET_LOCK（），RELEASE_LOCK（），IS_FREE_LOCK（）和IS_USED_LOCK（）函数在没有主服务器上的并发上下文的情况下进行复制。 因此，不应使用这些函数插入主表，因为从站上的内容会有所不同。 例如，不要发出诸如INSERT INTO mytable VALUES（GET_LOCK（...））之类的语句。

使用MIXED模式时，使用基于行的复制自动复制这些函数，并在STATEMENT模式下生成警告。

作为基于语句的复制生效时上述限制的解决方法，您可以使用将有问题的函数结果保存在用户变量中并在稍后的语句中引用该变量的策略。 例如，由于引用了UUID（）函数，以下单行INSERT存在问题：

INSERT INTO t VALUES(UUID());

可以使用下面的替代方式 绕过这个问题：

SET @my_uuid = UUID();

INSERT INTO t VALUES(@my_uuid);

该语句顺序复制，因为@my_uuid的值作为INSERT语句之前的用户变量事件存储在二进制日志中，并且可用于INSERT。

同样的想法适用于多行插入，但使用起来更麻烦。 对于双行插入，您可以这样做：

SET @my_uuid1 = UUID();

@my_uuid2 = UUID();

INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);

但是，如果行数很大或未知，则解决方法很麻烦或不切实际。 例如，您不能将以下语句转换为给定的单个用户变量与每行相关联的语句：

INSERT INTO t2 SELECT UUID(), * FROM t1;

在存储函数中，只要在函数执行期间仅调用一次，RAND（）就会正确复制。 （您可以将函数执行时间戳和随机数种子视为主服务器和从服务器上一致的的隐式输入。）

使用基于语句的复制不能可靠地复制FOUND_ROWS（）和ROW_COUNT（）函数。 解决方法是将函数调用的结果存储在用户变量中，然后在INSERT语句中使用它。 例如，如果您希望将结果存储在名为mytable的表中，通常可以这样执行：

SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;

INSERT INTO mytable VALUES( FOUND_ROWS() );

但是，如果要复制mytable，则应使用SELECT ... INTO，然后将变量存储在表中，如下所示：

SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;

INSERT INTO mytable VALUES(@found_rows);

通过这种方式，用户变量被复制为上下文的一部分，并正确地应用于slave。

使用MIXED模式时，使用基于行的复制自动复制这些函数，并在STATEMENT模式下生成警告。 （Bug＃12092，Bug＃30244）

在MySQL 5.7.3之前，如果在从站上启用了任何过滤选项，例如--replicate-ignore-db和--replicate-do-table，则LAST_INSERT_ID（）的值未正确复制。 （Bug＃17234370，BUG＃69861）

16.4.1.17 复制和 LIMIT

由于受影响的行的顺序的不确定性，因此在DELETE，UPDATE和INSERT ... SELECT语句中基于语句的LIMIT子句复制是不安全的。 （只有当这些语句也包含ORDER BY子句时，才可以使用基于语句的复制正确复制这些语句。）遇到这样的语句时：

• 使用STATEMENT模式时，现在会发出语句对于基于语句的复制不安全的警告。

使用STATEMENT模式时，即使它们也有ORDER BY子句（因此也是确定性的），也会为包含LIMIT的DML语句发出警告。 这是一个已知的问题。 （BUG＃42851）

• 使用MIXED模式时，现在使用基于行的模式自动复制语句。

16.4.18 复制和LOAD DATA INFILE

对于基于语句的日志记录，LOAD DATA INFILE被认为是不安全的（参见第16.2.1.3节“二进制日志记录中安全和不安全语句的确定”）。 设置binlog_format = MIXED时，将以基于行的格式记录该语句。 设置binlog_format = STATEMENT时，请注意LOAD DATA INFILE不会生成警告，这与其他不安全的语句不同。

当mysqlbinlog读取以基于语句的格式记录的LOAD DATA INFILE语句的日志事件时，将在临时目录中创建生成的本地文件。 mysqlbinlog或任何其他MySQL程序不会自动删除这些临时文件。 如果确实将LOAD DATA INFILE语句与基于语句的二进制日志记录一起使用，则应在不再需要语句日志后自行删除临时文件。

16.4.1.19 复制和max_allowed_packet

max_allowed_packet设置MySQL服务器和客户端（包括复制从属服务器）之间任何单个消息大小的上限。 如果要复制大型列值（例如可能在TEXT或BLOB列中发现）并且master上的max_allowed_packet太小，则master会因错误而失败，并且slave会关闭I / O线程。 如果从机上的max_allowed_packet太小，这也会导致从机停止I / O线程。

基于行的复制当前发送从主服务器到从服务器的更新行的所有列和列值，包括更新未实际更改的列的值。 这意味着，当您使用基于行的复制复制大型列值时，必须注意将max_allowed_packet设置得足够大，以容纳要复制的任何表中的最大行，即使您只复制update，或者只插入相对较小的值。

16.4.1.21 复制和mysql 系统数据库

根据binlog_format的值复制对mysql数据库中的表进行的数据修改语句; 如果此值为MIXED，则使用基于行的格式复制这些语句。 但是，通常会间接更新此信息的语句（如GRANT，REVOKE和语句操作触发器，存储例程和视图）将使用基于语句的复制复制到从属服务器。

16.4.1.22 复制和查询优化器

如果以一种数据修改是不确定的方式编写语句，则主服务器和从服务器上的数据可能会不同; 也就是说，抛开了查询优化器。 （一般来说，即使在复制之外，这也不是一种好的做法。）非确定性语句的示例包括使用LIMIT且没有ORDER BY子句的DELETE或UPDATE语句;

16.4.1.23 复制和分区

分区表之间支持复制，需要它们使用相同的分区方案，具有相同的结构，除非特别允许例外

通常不支持具有不同分区的表之间的复制。 这是因为在这种情况下直接作用于分区的语句（例如ALTER TABLE ... DROP PARTITION）可能会在主服务器和从服务器上产生不同的结果。 如果表在主服务器上而不是在从服务器上进行分区，则在从服务器副本上的分区上运行的任何语句都会失败。 当从表的副本被分区但主副本没有被分区时，作用于分区的语句不能在主服务器上运行而不会在引起错误。

由于这些危险导致复制完全失败（由于语句失败）和不一致（当分区级SQL语句的结果在主服务器和从服务器上产生不同的结果时），我们建议确保任何要被复制表的分区与从库上的版本要匹配 。

16.4.1.24 复制 和REPAIR TABLE

在损坏的表或其他损坏的表上使用时，REPAIR TABLE语句可以删除无法恢复的行。 但是，不会复制此语句执行的表数据的任何此类修改，这可能导致主服务器和从服务器失去同步。 因此，如果主服务器上的表损坏并且您使用REPAIR TABLE进行修复，则应在使用REPAIR TABLE之前先停止复制（如果它仍在运行），然后再比较主服务器和从服务器的副本 ，并在在重新启动复制之前手动更正该表的任何差异。

16.4.1.25 复制和保留字

当您尝试从较旧的主服务器复制到较新的主服务器并且您使用主服务器上的标识符时，您可能会遇到问题，这些标识符是从服务器上运行的较新MySQL版本中的保留字。

所以最好的不要使用 跨数据库版本的主从环境，可以避免很多问题。

16.4.1.27 复制和master 或 slave 关闭

关闭主服务器并在以后重新启动它是安全的。 当从属设备失去与主设备的连接时，从设备会尝试立即重新连接，如果失败则会定期重试。 默认设置是每60秒重试一次。 可以使用CHANGE MASTER TO语句更改此设置。 从站也能够处理网络连接中断。 但是，只有在slave_net_timeout秒内没有从主站接收数据后，从站才会注意到网络中断。 如果您的中断很短，您可能希望减少slave_net_timeout。

由于主二进制日志文件未被刷新，因此主站侧的不正常关闭（例如，崩溃）可导致主二进制日志的最终位置小于从站读取的最近位置。 这可能导致主服务器重新启动时无法复制从服务器。 在主my.cnf文件中设置sync_binlog = 1有助于最小化此问题，因为它会导致主服务器更频繁地刷新其二进制日志。 为了在使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性，还应设置innodb_flush_log_at_trx_commit = 1。 使用此设置，InnoDB重做日志缓冲区的内容将在每次事务提交时写入日志文件，并将日志文件刷新到磁盘。 请注意，此设置仍无法保证事务的持久性，因为操作系统或磁盘硬件可能会告诉mysqld已发生刷新到磁盘操作，即使它没有

干净地关闭一个slave 是安全的，因为其跟踪它停止的地方。 但是，要小心slave没有打开临时表; 请参见第16.4.1.30节“复制和临时表”。 不干净的关闭可能会产生问题，尤其是在问题发生之前磁盘缓存未刷新到磁盘时：

• 对于事务，slave提交然后更新http://relay-log.info。 如果在这两个操作之间发生崩溃，则中继日志被处理的将比信息文件指示的的迟，并且从服务器将在重新启动后重新执行中继日志中最后一个事务的事件。
• 如果从站更新http://relay-log.info 但服务器主机在写入刷新到磁盘之前崩溃，则会发生类似的问题。 要最大限度地减少发生这种情况的可能性，请在slave my.cnf文件中设置sync_relay_log_info = 1。 将sync_relay_log_info设置为0会导致不强制写入磁盘，并且服务器依赖操作系统来不时的刷新文件。

如果您拥有良好的不间断电源，系统对这些类型问题的容错能力将大大提高。

16.4.1.28 在复制中的 slave 错误

如果语句在主服务器和从服务器上产生相同的错误（相同的错误代码），则会记录错误，但会继续复制。

--主从上使用不同的存储引擎可能会导致这种情况

如果语句在主服务器和从服务器上产生不同的错误，则从属SQL线程终止，并且从服务器将消息写入其错误日志，并等待数据库管理员决定如何处理该错误。 要解决此问题，请手动连接到从站并确定问题的原因。 SHOW SLAVE STATUS对此非常有用。 然后解决问题并运行START SLAVE。 例如，您可能需要先创建一个不存在的表，然后才能再次启动从属服务器。

注意：

如果从站的错误日志中记录了临时错误，则您不必采取引用的错误消息中建议的任何操作。 重试事务的客户端应该处理临时错误。 例如，如果从属SQL线程记录与死锁相关的临时错误，则不需要在从属服务器上手动重新启动事务，除非从属SQL线程随后以非临时错误消息终止。

如果不希望出现此错误代码验证行为，则可以使用--slave-skip-errors选项屏蔽（忽略）部分或全部错误。

16.4.1.30 复制和临时表

当binlog_format = ROW时，以下段落中的讨论不适用，因为在这种情况下，不会复制临时表; 这意味着在从站意外关闭的情况下，从站上永远不会有任何临时表丢失。 本节的其余部分仅在使用基于语句或混合格式的复制时适用。 对于涉及可以使用基于语句的格式安全记录的临时表的语句，每当binlog_format为STATEMENT或MIXED时，从站上复制的临时表的丢失可能是一个问题。

在使用临时表的时候安全关闭slave。

除非您停止从属服务器（不仅仅是从属线程）并且您已复制临时表，这些临时表在尚未在从属服务器上执行的更新中使用，临时表被复制。 如果停止从属服务器，则重新启动从属服务器时，这些更新所需的临时表将不再可用。 要避免此问题，请勿在临时表打开时关闭从站。 而是，使用以下过程：

1. 发出STOP SLAVE SQL_THREAD语句。
2. 使用SHOW STATUS检查Slave_open_temp_tables变量的值。
3. 如果该值不为0，请使用START SLAVE SQL_THREAD重新启动从属SQL线程，稍后重复此过程。
4. 当值为0时，发出mysqladmin shutdown命令以停止从站。

临时表和复制选项

默认情况下，所有的临时表被复制; 无论是否存在任何匹配的--replicate-do-db， - replicate-do-table或--replicate-wild-do-table选项，都会发生这种情况。 但是，--replicate-ignore-table和--replicate-wild-ignore-table选项适用于临时表。 例外情况是，为了在会话结束时正确删除临时表，从站始终复制DROP TEMPORARY TABLE IF EXISTS语句，而不管通常适用于指定表的任何排除规则。

使用基于语句或混合格式的复制时，建议的做法是指定一个前缀，以便在不需要复制的临时表命名时使用，然后使用--replicate-wild-ignore-table选项匹配该前缀。 例如，您可以给出以norep开头的所有这些表名（例如norepmytable，norepyourtable等），然后使用--replicate-wild-ignore-table = norep％来防止它们被复制。

16.4.1.32 复制和时区

默认情况下，主服务器和从属服务器假定它们位于同一时区。 如果要在不同时区的服务器之间进行复制，则必须在主服务器和从服务器上设置时区。 否则，将无法正确复制取决于主服务器上本地时间的语句，例如使用NOW（）或FROM_UNIXTIME（）函数的语句。 使用mysqld_safe脚本的--timezone = timezone_name选项或设置TZ环境变量，设置运行MySQL服务器的时区。

16.4.1.33 复制和事务不一致

根据您的复制配置，可能会发生从中继日志执行的事务序列中的不一致。 本节介绍如何避免不一致并解决它们导致的任何问题。

可能存在以下类型的不一致：

• Half-applied transactions。 更新非事务性表的事务已应用了一些但不是全部更改。
• Gaps。 间隙是尚未（完全）应用的事务，即使已应用某些后续事务。 只有在使用多线程从站时才会出现间隙。 为避免出现Gap，请设置slave_preserve_commit_order = 1，这需要slave_parallel_type = LOGICAL_CLOCK，并且还启用了log-bin和log-slave-updates。
• Gap-free low-watermark position。即使没有GAp，也可能已应用Exec_master_log_pos之后的事务。 也就是说，已经应用了到N点的所有事务，并且在N之后没有任何事务被应用，但Exec_master_log_pos的值小于N.这只能在多线程从设备上发生。 启用slave_preserve_commit_order不能保证 gap-free low-watermark位置。

以下情景与存在被半应用事务，gap和gap-free low-watermark位置有关：

1.当从属线程正在运行时，可能存在gap和被半应用事务。

2.mysqld 关闭。干净和不干净的关闭都会中断正在进行的事务，并可能留下空白和被半应用的事务。

3.KILL复制线程（使用单线程从属时的SQL线程，使用多线程从属时的协调器线程）。 这会中断正在进行的事务，并可能留下差距和被半应用的事务。

4.应用程序线程出错。 这可能会留下gap。 如果错误发生在混合事务中，则该事务处于被半应用状态。 使用多线程slave时，未收到错误的works会完成队列，因此停止所有线程可能需要一些时间。

5.使用多线程从站时 stop SLAVE。 发出STOP SLAVE后，从站等待填充任何Gap，然后更新Exec_master_log_pos。 这确保它永远不会留下间隙或无间隙的低水位位置，除非上述任何情况适用（换句话说，在STOP SLAVE完成之前，发生错误，或者另一个线程发出KILL，或者服务器重新启动。） 在这种情况下，STOP SLAVE返回成功。

6.如果中继日志中的最后一个事务只有一半被接收，并且多线程从协调器已开始将事务安排给works，则STOP SLAVE最多等待60秒以便接收事务。 超时后，协调员放弃并中止事务。 如果事务混合，可能会完成一半。

7.stop slave 使用单线程从站时。 如果正在进行的事务仅更新事务表，则它将回滚并且STOP SLAVE立即停止。 如果正在进行的事务是混合的，则STOP SLAVE最多等待60秒以完成事务。 超时后，它会中止事务，因此可能会完成一半。

全局变量rpl_stop_slave_timeout与停止复制线程的过程无关。 它只会使发出STOP SLAVE的客户端返回到客户端，但复制线程继续尝试停止。

如果复制通道存在间隙，则会产生以下后果：

1.从属数据库处于主机上可能永远不存在的状态。

2.SHOW SLAVE STATUS中的字段Exec_master_log_pos只是一个“低水印”。 换句话说，在该位置之前出现的事务保证已经提交，但该位置之后的事务可能已经提交或未提交。

3.除非应用程序线程正在运行且CHANGE MASTER TO语句仅设置接收器选项，否则该通道的CHANGE MASTER TO语句将失败并显示错误。

4.如果mysqld 使用 --relay-log-recovery 启动。该通道不会进行进行恢复，并打印出警告。

5.如果 mysqldump 使用 --dump-slave，它没有记录存在的gap; 因此其打印 RELAY_LOG_POS置为 Exec_master_log_pos. 中低水位位置的 CHANGE MASTER TO

在另一台服务器上应用转储并启动复制线程后，将再次复制位置之后出现的事务。 请注意，如果启用了GTID，则这是无害的（但是，在这种情况下，建议不要使用--dump-slave）。

如果复制通道具有无间隙的低水印位置，则上述情况2至5适用，但情况1不适用。

无间隙的低水印位置信息以二进制格式保存在内部表mysql.slave_worker_info中。 START SLAVE [SQL_THREAD]总是查询此信息，以便它仅应用正确的事务。 即使在START SLAVE之前slave_parallel_workers已经更改为0，并且即使START SLAVE与UNTIL子句一起使用，这仍然是正确的。 START SLAVE UNTIL SQL_AFTER_MTS_GAPS仅根据需要应用尽可能多的事务以填补gap。 如果START SLAVE与UNTIL子句一起使用，该子句告诉它在消耗所有间隙之前停止，那么它会留下剩余的间隙。

警告

RESET SLAVE删除中继日志并重置复制位置。 因此，在具有间隙的从设备上发出RESET SLAVE意味着从设备丢失有关间隙的任何信息，而不纠正间隙。

slave-preserve-commit-order确保没有间隙。 但是，在上面的场景1到4中，Exec_master_log_pos仍然只是 gap-free low-watermark 位置。 也就是说，在Exec_master_log_pos之后可能存在已应用的事务。 因此，即使启用了slave-preserve-commit-order，上面编号为2到5的情况（没有1）也适用。

16.4.1.35 复制和触发器

使用基于语句的复制，在主服务器上执行的触发器也在从服务器上执行。 使用基于行的复制时，在主服务器上执行的触发器不会在从服务器上执行。 相反，由触发器执行产生的主服务器上的行更改将被复制并应用于从服务器。

此行为是设计使然。 如果在基于行的复制下，slave应用了触发器以及由它们引起的行更改，则更改实际上将在从上应用两次，从而导致主和从上出现不同数据。

如果要在主服务器和从服务器上执行触发器 - 可能是因为主服务器和从服务器上有不同的触发器 - 您必须使用基于语句的复制。 但是，要启用从属端触发器，不必专门使用基于语句的复制。 仅对那些需要此效果的语句切换到基于语句的复制，并在其余情况使用基于行的复制就足够了

使用基于语句的复制不会正确复制调用导致更新AUTO_INCREMENT列的触发器（或函数）的语句。 MySQL 5.7将此类语句标记为不安全。 （BUG＃45677）

触发器可以具有针对触发事件（INSERT，UPDATE，DELETE）和动作时间（BEFORE，AFTER）的不同组合的触发器，但是在MySQL 5.7.2之前不能具有具有相同触发事件和动作时间的多个触发器。 MySQL 5.7.2解除了这个限制，允许多个触发器。 此更改对升级和降级的复制有意义。

16.4.1.36 复制和 TRUNCATE 表

TRUNCATE TABLE通常被视为DML语句，因此当二进制日志记录模式为ROW或MIXED时，可以使用基于行的格式记录和复制TRUNCATE TABLE。 但是，当在事务隔离级别为READ COMMITTED或READ UNCOMMITTED时，在STATEMENT或MIXED模式下记录或复制使用事务存储引擎（如InnoDB）的表时，这会导致问题，从而排除基于语句的日志记录。

TRUNCATE TABLE被视为以DDL而不是DML，所以可以被作为语句进行日志记录和复制。 但是，适用于InnoDB和复制从属上的其他事务表的语句的效果仍然遵循第13.1.34节“管理此类表的TRUNCATE TABLE语法”中所述的规则。 （BUG 36763）