• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 复制格式

    复制之所以有效,是因为从主数据库读取写入二进制日志的事件,然后在从数据库上对其进行处理。根据事件的类型,事件以不同的格式记录在二进制日志中。使用的不同复制格式对应于在主数据库的二进制日志中记录事件时使用的二进制日志记录格式。二进制日志记录格式与复制期间使用的术语之间的相关性是:

    • 使用基于语句的二进制日志记录时,主数据库将SQL语句写入二进制日志。通过在从服务器上执行SQL语句,可以将主服务器复制到从服务器。这称为基于语句的复制(可以缩写为 SBR),它对应于基于MySQL语句的二进制日志记录格式。
    • 使用基于行的日志记录时,主数据库将事件写入二进制日志,以指示如何更改各个表行。通过将表示对表行的更改的事件复制到从属服务器,可以将主控服务器复制到从属服务器。这称为基于行的复制(可以缩写为 RBR)。

      基于行的日志记录是默认方法。

    • 您还可以配置MySQL以同时使用基于语句的记录和基于行的记录,这取决于哪种记录最适合记录更改。这称为混合格式日志记录。使用混合格式日志记录时,默认情况下使用基于语句的日志。根据某些语句以及所使用的存储引擎,在特定情况下,日志会自动切换为基于行的日志。使用混合格式的复制称为基于混合的复制或混合格式的复制。有关更多信息,请参见“MySQL服务器二进制日志”。

    NDB群集。 MySQL NDB Cluster 8.0中的默认二进制日志记录格式为MIXED。您应注意,NDB群集复制始终使用基于行的复制,并且NDB存储引擎与基于语句的复制不兼容。有关更多信息,请参见“ NDB群集复制的一般要求”。

    使用MIXED格式时,二进制日志记录格式部分取决于所使用的存储引擎和所执行的语句。有关混合格式日志记录以及支持不同日志记录格式的规则的更多信息,请参见“MySQL服务器二进制日志”。

    正在运行的MySQL服务器中的日志记录格式是通过设置binlog_format服务器系统变量。可以使用会话或全局范围设置此变量。控制新设置的生效时间和方式的规则与其他MySQL服务器系统变量的规则相同。设置当前会话的变量仅持续到该会话结束,并且其他会话看不到该更改。全局设置变量对更改后连接的客户端生效,但不适用于任何当前客户端会话,包括更改了变量设置的会话。要使全局系统变量设置永久生效,以便将其应用于服务器重新启动,必须在选项文件中进行设置。有关更多信息,请参见“变量分配的SET语法”。

    在某些情况下,您无法在运行时更改二进制日志记录格式,否则会导致复制失败。请参见“MySQL服务器二进制日志”。

    更改全局binlog_format值需要足够的特权来设置全局系统变量。更改会话binlog_format值需要足够的特权来设置受限制的会话系统变量。请参见“系统变量特权”。

    基于语句的复制格式和基于行的复制格式具有不同的问题和限制。要比较它们的相对优缺点,

    使用基于语句的复制,复制存储的例程或触发器可能会遇到问题。您可以通过使用基于行的复制来避免这些问题。有关更多信息,请参见“存储程序二进制记录”。

    基于语句的复制和基于行的复制的优缺点

    每种二进制日志记录格式都有优点和缺点。对于大多数用户而言,混合复制格式应提供数据完整性和性能的最佳组合。但是,如果要在执行某些任务时利用特定于基于语句的复制格式或基于行的复制格式的功能,则可以使用本节中的信息,该信息概述了它们的相对优缺点。确定最适合您的需求。

    • 基于语句的复制的优点
    • 基于语句的复制的缺点
    • 基于行的复制的优点
    • 基于行的复制的缺点

    基于语句的复制的优点

    • 成熟的技术。
    • 写入日志文件的数据更少。当更新或删除影响许多行时,这将导致日志文件所需的存储空间大大减少。这也意味着从备份中获取和还原可以更快地完成。
    • 日志文件包含所有进行了任何更改的语句,因此它们可用于审核数据库。

    基于语句的复制的缺点

    • 对于SBR不安全的语句。不修改该数据的所有语句(如,和语句)可以使用基于语句的复制被复制。使用基于语句的复制时,很难复制任何不确定性行为。此类数据修改语言(DML)语句的示例包括以下内容:INSERTDELETEUPDATEREPLACE

      • 依赖于不确定性的UDF或存储程序的语句,因为这样的UDF或存储程序返回的值或取决于提供给它的参数以外的因素。(但是,基于行的复制只是复制UDF或存储的程序返回的值,因此它对表行和数据的影响在主服务器和从服务器上都是相同的。)请参见“复制功能””,以获取更多信息。
      • DELETEUPDATE使用不带LIMIT子句的语句ORDER BY是不确定的。请参见“复制功能”。
      • 锁定使用或选项的读取语句(SELECT ... FOR UPDATESELECT ... FOR SHARE)。请参阅使用NOWAIT和SKIP LOCKED锁定读取并发。NOWAITSKIP LOCKED
      • 确定性UDF必须应用于从站。
      • 使用以下基于语句的复制无法正确复制使用以下任何功能的语句:

        • LOAD_FILE()
        • UUID()UUID_SHORT()
        • USER()
        • FOUND_ROWS()
        • SYSDATE()(除非主机和从机都通过该--sysdate-is-now选项启动)
        • GET_LOCK()
        • IS_FREE_LOCK()
        • IS_USED_LOCK()
        • MASTER_POS_WAIT()
        • RAND()
        • RELEASE_LOCK()
        • SLEEP()
        • VERSION()

        但是,所有其他功能都可以使用基于语句的复制正确地复制,包括NOW()等等。

        有关更多信息,请参见“MySQL复制数据”。

      使用基于语句的复制无法正确复制的语句会记录一条警告,如下所示:

      [Warning] Statement is not safe to log in statement format.
      

      在这种情况下,也会向客户发出类似的警告。客户端可以使用显示它SHOW WARNINGS

    • INSERT ... SELECT与基于行的复制相比,需要更多的行级锁。
    • UPDATEWHERE与基于行的复制相比,要求进行表扫描的语句(因为在子句中未使用索引)必须锁定更多的行。
    • 对于InnoDB:使用的INSERT语句将AUTO_INCREMENT阻止其他不冲突的INSERT语句。
    • 对于复杂的语句,必须在更新或插入行之前在从属上评估并执行该语句。使用基于行的复制时,从属仅需修改受影响的行,而无需执行完整语句。
    • 如果在从属服务器上的评估出现错误,尤其是在执行复杂的语句时,基于语句的复制可能会随着时间的流逝缓慢地增加受影响行上的错误余量。请参见“复制期间的从属错误”。
    • 存储的函数以与NOW()调用语句相同的值执行。但是,对于存储过程,情况并非如此。
    • 确定性UDF必须应用于从站。
    • 主服务器和从服务器上的表定义必须(几乎)相同。有关更多信息,请参见“在主控和从属上使用不同的表定义进行复制”。

    基于行的复制的优点

    • 可以复制所有更改。这是最安全的复制形式。

      注意

      更新mysql系统架构中信息的语句(例如GRANTREVOKE触发器的操作,存储例程(包括存储过程)和视图)都使用基于语句的复制复制到从属服务器。

      对于诸如之类CREATE TABLE ... SELECTCREATE语句,将从表定义中生成一条语句,并使用基于语句的格式进行复制,而行插入则使用基于行的格式进行复制。

    • 对于以下类型的语句,在主服务器上需要较少的行锁,从而实现更高的并发性:

      • INSERT ... SELECT
      • INSERT陈述与AUTO_INCREMENT
      • UPDATEDELETE带有WHERE不使用键或不更改大多数已检查行的子句的语句。
    • 对于任何,或语句INSERT,从站上需要的行锁较少。UPDATEDELETE

    基于行的复制的缺点

    • RBR可以生成更多必须记录的数据。要复制DML语句(例如UPDATEor DELETE语句),基于语句的复制仅将语句写入二进制日志。相比之下,基于行的复制会将每个更改的行写入二进制日志。如果该语句更改了许多行,则基于行的复制可能会向二进制日志中写入更多的数据。即使对于回滚的语句也是如此。这也意味着制作和还原备份可能需要更多时间。此外,二进制日志被锁定更长的时间以写入数据,这可能会导致并发问题。采用binlog_row_image=minimal大大减少了缺点。
    • 生成大BLOB值的确定性UDF 与基于语句的复制相比,使用基于行的复制所花费的时间更长。这是因为BLOB记录了列值,而不是记录了生成数据的语句。
    • 您无法在从站上看到从主站接收并执行了哪些语句。但是,你可以看到使用被改变什么数据mysqlbinlog可以使用这些选项--base64-output=DECODE-ROWS--verbose

      或者,使用binlog_rows_query_log_events变量,如果启用该变量,则在使用该选项时会将Rows_query带有语句的事件添加到mysqlbinlog输出中-vv

    • 对于使用MyISAM存储引擎的表,将从属INSERT语句作为基于行的事件应用于二进制日志时,需要比从属语句对锁具有更强的锁定。这意味着MyISAM在使用基于行的复制时,不支持在表上进行并发插入。

    基于行的日志记录和复制的用法

    MySQL使用基于语句的日志(SBL),基于行的日志(RBL)或混合格式的日志。使用的二进制日志的类型会影响日志的大小和效率。因此,在基于行的复制(RBR)或基于语句的复制(SBR)之间进行选择取决于您的应用程序和环境。本节介绍使用基于行的格式日志时的已知问题,并介绍在复制中使用它的一些最佳实践。

    有关更多信息,请和“基于语句的复制和基于行的复制的优点和缺点”。

    有关特定于NDB群集复制的问题(取决于基于行的复制)的信息,请参见“ NDB群集复制中的已知问题”。

    • 临时表的基于行的日志记录。如“复制和临时表”所述,在使用基于行的格式或(从MySQL 8.0.4起)混合格式时,不会复制临时表。

      使用基于行的格式或混合格式时,不会复制临时表,因为没有必要。另外,由于只能从创建临时表的线程中读取临时表,因此即使使用基于语句的格式,复制临时表也几乎没有好处。

      即使已创建临时表,也可以在运行时从基于语句的二进制记录格式切换为基于行的二进制记录格式。但是,在MySQL 8.0中,您无法在运行时从二进制记录的行格式或混合格式切换为基于语句的格式,因为CREATE TEMPORARY TABLE在以前的模式下,二进制日志中将省略任何语句。

      MySQL服务器跟踪创建每个临时表时生效的日志记录模式。当给定的客户端会话结束时,服务器DROP TEMPORARY TABLE IF EXISTS将为每个临时表记录一条语句,该语句在使用基于语句的二进制日志记录时已创建。如果在创建表时使用了基于行或混合格式的二进制日志记录,DROP TEMPORARY TABLE IF EXISTS则不会记录该语句。在MySQL 8.0.4和5.7.25之前的版本中,DROP TEMPORARY TABLE IF EXISTS无论有效的记录方式如何,都会记录该语句。

      使用时binlog_format=ROW,允许使用涉及临时表的非事务DML语句,只要受该语句影响的任何非事务表都是临时表即可(错误#14272672)。

    • RBL和非事务表的同步。当许多行受到影响时,这组更改将分为几个事件。当语句提交时,所有这些事件都将写入二进制日志。在从属服务器上执行时,将对所有涉及的表进行表锁定,然后以批处理模式应用行。取决于用于从属表的副本的引擎,这可能有效或无效。
    • 延迟和二进制日志大小。 RBL将每一行的更改写入二进制日志,因此其大小可以快速增加。这会大大增加在从属服务器上进行与主服务器上的更改相匹配的更改所需的时间。您应该意识到应用程序中这种延迟的可能性。
    • 读取二进制日志。mysqlbinlog使用该BINLOG语句在二进制日志中显示基于行的事件(请参见“ BINLOG语句”)。该语句将事件显示为基本的64位编码字符串,其含义不明显。当使用--base64-output=DECODE-ROWS--verbose选项调用时,mysqlbinlog将二进制日志的内容格式化为易于阅读。如果二进制日志事件是以基于行的格式编写的,并且您想读取复制或数据库故障或从中恢复,则可以使用此命令读取二进制日志的内容。有关更多信息,请参见“ mysqlbinlog行事件显示”。
    • 二进制日志执行错误和slave_exec_mode。使用slave_exec_mode=IDEMPOTENT一般只与MySQL NDB集群复制有用的,这IDEMPOTENT是默认值。(请参见“ NDB群集复制:多主复制和循环复制”)。如果slave_exec_modeIDEMPOTENT,因为无法找到原始行而无法应用RBL的更改不会触发错误或导致复制失败。这意味着可能没有将更新应用于从属服务器,从而使主服务器和从属服务器不再同步。延迟问题,使用与RBR非事务性表的时候slave_exec_modeIDEMPOTENT可以进一步导致主从发散。有关的更多信息slave_exec_mode,请参见“服务器系统变量”。

      对于其他方案,设置slave_exec_modeSTRICT通常是足够的;这是除以外的存储引擎的默认值NDB

    • 不支持基于服务器ID的过滤。您可以使用语句IGNORE_SERVER_IDS选项基于服务器ID进行过滤CHANGE MASTER TO。该选项适用于基于语句和基于行的日志记录格式,但不建议在GTID_MODE=ON设置时使用。过滤掉某些从属服务器上的更改的另一种方法是使用一个WHERE子句,该子句包含带有and 语句的关系子句。例如,。但是,这不适用于基于行的日志记录。要使用@@server_id <>id_valueUPDATEDELETEWHERE @@server_id <> 1server_id系统变量用于语句过滤,请使用基于语句的日志记录。
    • 数据库级复制选项。的影响--replicate-do-db--replicate-ignore-db以及--replicate-rewrite-db选择差异很大依赖于基于行或基于语句的日志记录是否被使用。因此,建议避免使用数据库级选项,而应使用诸如--replicate-do-table和的表级选项--replicate-ignore-table。有关这些选项以及影响复制格式对其操作方式的更多信息,请参见“复制和二进制日志记录选项和变量”。
    • RBL,非事务表和已停止的从属。使用基于行的日志记录时,如果在从属线程更新非事务表时停止了从属服务器,则从属数据库可能会达到不一致的状态。因此,建议您InnoDB对所有使用基于行格式复制的表使用事务性存储引擎。在关闭从MySQL服务器之前STOP SLAVESTOP SLAVE SQL_THREAD在关闭MySQL服务器之前使用该功能有助于防止问题的发生,并且无论使用哪种日志记录格式或存储引擎,都始终建议这样做。

    二进制记录中安全和不安全语句的确定

    MySQL复制中语句的“安全性”是指是否可以使用基于语句的格式正确复制该语句及其影响。如果该陈述是正确的,则我们将该陈述称为安全;否则,我们将其称为不安全。

    通常,如果语句是确定性的,则它是安全的;否则,则是不安全的。但是,某些非确定性函数被认为是不安全的(请参阅本节后面的非确定性函数被视为不安全的)。此外,使用浮点数学函数的结果(取决于硬件)的语句始终被认为是不安全的(请参见“复制和浮点值”)。

    处理安全和不安全的声明。根据该语句是否被认为安全,以及相对于二进制日志记录格式(即的当前值binlog_format),对语句进行不同的处理。

    • 使用基于行的日志记录时,在安全和不安全语句的处理上没有区别。
    • 使用混合格式日志记录时,标记为不安全的语句将使用基于行的格式记录;使用基于语句的格式记录被视为安全的语句。
    • 使用基于语句的日志记录时,标记为不安全的语句会产生警告。安全语句正常记录。

    每个标记为不安全的语句都会生成警告。如果在主服务器上执行了大量此类语句,则可能导致错误日志文件过大。为了防止这种情况,MySQL具有警告抑制机制。ER_BINLOG_UNSAFE_STATEMENT在任何50秒的时间段内,只要最近的50条警告的生成次数超过50次,就会启用警告抑制功能。激活后,这将导致此类警告不会写入错误日志;相反,对于这种类型的每50条警告,都会有一个注释The last warning was repeated N times in last S seconds被写入错误日志。只要在50秒或更短的时间内发出了最近的50条警告,这种情况就会持续下去;一旦速率降低到此阈值以下,警告将再次正常记录。警告抑制不影响如何确定基于语句的日志记录的语句安全性,也不影响如何将警告发送到客户端。MySQL客户端仍然为每个这样的语句收到一个警告。

    声明不安全。具有以下特征的语句被认为是不安全的:

    • 包含系统函数的语句,这些函数可能在从属服务器上返回不同的值。这些功能包括FOUND_ROWS()GET_LOCK()IS_FREE_LOCK()IS_USED_LOCK()LOAD_FILE()MASTER_POS_WAIT()RAND()RELEASE_LOCK()ROW_COUNT()SESSION_USER()SLEEP()SYSDATE()SYSTEM_USER()USER()UUID(),和UUID_SHORT()

      非确定性功能不被认为是不安全的。虽然这些功能是不确定的,它们被视为安全的记录和复制的目的:CONNECTION_ID()CURDATE()CURRENT_DATE()CURRENT_TIME()CURRENT_TIMESTAMP()CURTIME(),,,LAST_INSERT_ID()LOCALTIME()LOCALTIMESTAMP()NOW()UNIX_TIMESTAMP()UTC_DATE()UTC_TIME()UTC_TIMESTAMP()

      有关更多信息,请参见“MySQL复制数据”。

    • 对系统变量的引用。使用基于语句的格式不能正确复制大多数系统变量。请参见“复制功能”。有关例外,请参见“MySQL服务器二进制日志”。
    • UDF。由于我们无法控制UDF的工作,因此我们必须假定它正在执行不安全的语句。
    • 全文插件。该插件在不同的MySQL服务器上的行为可能有所不同。因此,取决于它的语句可能会有不同的结果。因此,在MySQL中,所有依赖于全文插件的语句都被视为不安全。
    • 触发器或存储的程序将更新具有AUTO_INCREMENT列的表。这是不安全的,因为在主服务器和从服务器上,行的更新顺序可能不同。

      此外,INSERT到包含复合主键的表中的表AUTO_INCREMENT是不安全的,该主键包含的列不是该复合键的第一列。

      有关更多信息,请参见“InnoDB中的AUTO_INCREMENT处理”。

    • 对具有多个主键或唯一键的表执行INSERT ... ON DUPLICATE KEY UPDATE语句。当对包含多个主键或唯一键的表执行该语句时,该语句被认为是不安全的,它对存储引擎检查键的顺序(不确定的)以及由行更新的行的选择敏感。 MySQL Server取决于。

      INSERT ... ON DUPLICATE KEY UPDATE针对具有多个唯一键或主键的表的语句对于基于语句的复制被标记为不安全。(缺陷#11765650,错误#58637)

    • 使用LIMIT更新。未指定行的检索顺序,因此被认为是不安全的。请参见“复制功能”。
    • 访问或引用日志表。主服务器和从服务器之间的系统日志表的内容可能有所不同。
    • 交易操作之后的非交易操作。在事务内,允许任何非事务性读取或写入在任何事务性读取或写入之后执行是不安全的。

      有关更多信息,请参见“复制功能”。

    • 访问或引用自记录表。对自记录表的所有读取和写入均被视为不安全。在事务中,对自记录表进行读取或写入之后的任何语句也被认为是不安全的。
    • LOAD DATA语句。LOAD DATAbinlog_format=MIXED该语句以基于行的格式记录时,被视为不安全。与其他不安全的语句不同,何时不生成警告。binlog_format=STATEMENTLOAD DATA
    • XA交易。如果正在从服务器上以相反的顺序准备在主服务器上并行提交的两个XA事务,则无法安全解决的基于语句的复制会发生锁定依赖关系,并且复制可能会因从服务器上的死锁而失败。当binlog_format=STATEMENT设置,XA事务内部DML语句被标记为不安全的,并产生一个警告。当binlog_format=MIXEDbinlog_format=ROW设置,XA事务内的DML语句使用基于行的复制记录,以及潜在的问题是不存在的。
    • DEFAULT子句指的是不确定性函数。如果表达式默认值引用的是不确定函数,则任何导致对该表达式求值的语句对于基于语句的复制都是不安全的。这包括语句,如INSERTUPDATEALTER TABLE。与大多数其他不安全的语句不同,此类语句不能以基于行的格式安全地复制。当binlog_format设置为时STATEMENT,将记录并执行该语句,但是将警告消息写入错误日志。何时binlog_format设置为MIXEDROW,则不会执行该语句,并且会将错误消息写入错误日志。有关处理显式默认值的更多信息,请参见从MySQL 8.0.13开始的显式默认值的处理。

    有关更多信息,请参见“复制功能”。