mysql行级复制_mysql 行复制 官方文档翻译

16.1.2. Replication Formats

Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event.The differentreplication formats used correspond to the binary logging formatused when the events were recorded in the master's binary log. The correlation between binary logging formats and the terms used during replication are:

事件根据不同类型记录不同的格式到日志文件，master的日志根据不同的二进制日志格式采取不同的复制格式，日志格式和复制关系如下：

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is calledstatement-based replication(often abbreviated asSBR), which corresponds to the standard statement-based binary logging format. In MySQL 5.1.4 and earlier, binary logging and replication used this format exclusively.

sbr对应sbblf

Row-based binary logging logs changes in individual table rows. When used with MySQL replication, this is known asrow-based replication(often abbreviated asRBR). In row-based replication, the master writeseventsto the binary log that indicate how individual table rows are changed.

rbr 下 master写时间到日志反映单独表行如何改变的

As of MySQL 5.1.8, the server can change the binary logging format in real time according to the type of event usingmixed-format logging.

动态改变二进制日志类型根据事件类型  使用mix格式选项。

When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described later. Replication using the mixed format is often referred to asmixed-based replicationormixed-format replication. For more information, seeSection 5.2.4.3, “Mixed Binary Logging Format”.

mix格式时默认sbl 自动根据需要到rbl

From MySQL 5.1.12 to MySQL 5.1.28, mixed format is the default. Beginning with MySQL 5.1.29, statement-based format is the default.  默认sbl

Note 集群使用 行模式

MySQL Cluster.The default binary logging format in all MySQL Cluster NDB 6.1, 6.2, 6.3, and later 6.x releases isROW. MySQL Cluster Replication always uses row-based replication, and theNDBCLUSTERstorage engine is incompatible with statement-based replication. UsingNDBCLUSTERsets row-based logging format automatically.

Starting with MySQL 5.1.20, when usingMIXEDformat, the binary logging format is determined in part by the storage engine being used and the statement being executed. For more information on mixed-format logging and the rules governing the support of different logging formats, seeSection 5.2.4.3, “Mixed Binary Logging Format”.

5.1.20后 使用mixed format 日志格式根据存储引擎和执行的命令来确定，

The logging format in a running MySQL server is controlled by setting thebinlog_formatserver system variable. This variable can be set with session or global scope. The rules governing when and how the new setting takes effect are the same as for other MySQL server system variables — setting the variable for the current session lasts only until the end of that session, and the change is not visible to other sessions; setting the variable globally requires a restart of the server in order to take effect. For more information, seeSection 12.4.4, “SETSyntax”.

binlog_format变量 全局或session范围都可以

You must have theSUPERprivilege to set the globalbinlog_formatvalue. Starting with MySQL 5.1.29, you must have theSUPERprivilege to set either the global or sessionbinlog_formatvalue. (Bug#39106) 权限

The statement-based and row-based replication formats have different issues and limitations. For a comparison of their relative advantages and disadvantages, 优劣比较 seeSection 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”.

With statement-based replication, you may encounter issues with replicating stored routines or triggers. You can avoid these issues by using row-based replication instead. For more information, seeSection 19.6, “Binary Logging of Stored Programs”. sbr遇到存储过程和触发器？？

16.1.2.1. Comparison of Statement-Based and Row-Based Replication

Each binary logging format has advantages and disadvantages. For most users, the mixed replication format should provide the best combination of data integrity and performance. If, however, you want to take advantage of the features specific to the statement-based or row-based replication format when performing certain tasks, you can use the information in this section, which provides a summary of their relative advantages and disadvantages, to determine which is best for your needs.

Advantages of statement-based replication:

Proven technology that has existed in MySQL since 3.23.

Less data written to log files. When updates or deletes affect many rows, this results inmuchless storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.

Log files contain all statements that made any changes, so they can be used to audit the database.

Disadvantages of statement-based replication:

Statements that are unsafe for SBR.Not all statements which modify data (such asINSERTDELETE,UPDATE, andREPLACEstatements) can be replicated using statement-based replication. Any nondeterministicbehavior is difficult to replicate when using statement-based replication. Examples of such DML (Data Modification Language) statements include the following:

A statement that depends on a UDF or stored program that is nondeterministic, since the value returned by such a UDF or stored program or depends on factors other than the parameters supplied to it. (Row-based replication, however, simply replicates the value returned by the UDF or stored program, so its effect on table rows and data is the same on both the master and slave.) SeeSection 16.4.1.8, “Replication of Invoked Features”, for more information.

DELETEandUPDATEstatements that use aLIMITclause without anORDER BYare nondeterministic. SeeSection 16.4.1.12, “Replication andLIMIT”.

Statements using any of the following functions cannot be replicated properly using statement-based replication:

However, all other functions are replicated correctly using statement-based replication, includingRAND(),NOW(), and so forth.

Statements that cannot be replicated correctly using statement-based replication are logged with a warning like the one shown here:090213 16:58:54 [Warning] Statement is not safe to log in statement format.

A similar warning is also issued to the client in such cases. The client can display it usingSHOW WARNINGS.

INSERT ... SELECTrequires a greater number of row-level locks than with row-based replication.

UPDATEstatements that require a table scan (because no index is used in theWHEREclause) mustlock a greater number of rows than with row-based replication.

ForInnoDB: AnINSERTstatement that usesAUTO_INCREMENTblocks other nonconflictingINSERTstatements.

For complex statements, the statement must be evaluated and executed on the slave before the rows are updated or inserted. With row-based replication, the slave only has to modify the affected rows, not execute the full statement.

If there is an error in evaluation on the slave, particularly when executing complex statements, statement-based replication may slowly increase the margin of error across the affected rows over time. SeeSection 16.4.1.23, “Slave Errors During Replication”.

Stored functions execute with the sameNOW()value as the calling statement. However, this is not true of stored procedures.

Deterministic UDFs must be applied on the slaves.

Table definitions must be (nearly) identical on master and slave. SeeSection 16.4.1.6, “Replication with Differing Table Definitions on Master and Slave”, for more information.

Advantages of row-based replication:

All changes can be replicated. This is the safest form of replication.

For MySQL versions earlier than 5.1.14, DDL (Data Definition Language) statements such asCREATE TABLEare replicated using statement-based replication, while DML statements, as well asGRANTandREVOKEstatements, are replicated using row-based replication.

In MySQL 5.1.14 and later, themysqldatabase is not replicated. Themysqldatabase is instead seen as a node-specific database. Row-based replication is not supported on tables in this database. Instead, statements that would normally update this information — such asGRANT,REVOKEand the manipulation of triggers, stored routines (including stored procedures), and views — are all replicated to slaves using statement-based replication.

For statements such asCREATE TABLE ... SELECT, aCREATEstatement is generated from the table definition and replicated using statement-based format, while the row insertions are replicated using row-based format.

The technology is the same as in most other database management systems; knowledge about other systems transfers to MySQL.

Fewer locks are required on the master, which thus achieves higher concurrency, for the following types of statements:

INSERTstatements withAUTO_INCREMENT

UPDATEorDELETEstatements withWHEREclauses that do not use keys or do not change most of the examined rows.

Fewer locks are required on the slave for anyINSERT,UPDATE, orDELETEstatement.

Disadvantages of row-based replication:

RBR tends to generate more data that must be logged. To replicate a DML statement (such as anUPDATEorDELETEstatement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that taking and restoring from backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems.

Deterministic UDFs that generate largeBLOBvalues take longer to replicate with row-based replication than with statement-based replication. This is because theBLOBcolumn value is logged, rather than the statement generating the data.

You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.

However, beginning with MySQL 5.1.29, you can see what data was changed usingmysqlbinlogwith the options--base64-output=DECODE-ROWSand--verbose.

Formerly, when performing a bulk operation that includes nontransactional storage engines, changes were applied as the statement executed. With row-based logging, this meant that the binary log was written while the statement was running. On the master, this does not cause problems with concurrency, because tables are locked until the bulk operation terminates. On the slave server, tables were not locked while the slave applied changes, because the slave did not know that those changes were part of a bulk operation.

In such cases, if you retrieved data from a table on the master (for example, usingSELECT * FROM table_name), the server waited for the bulk operation to complete before executing theSELECTstatement, because the table was read-locked. On the slave, the server did not wait (because there was no lock). This meant that, until the bulk operation on the slave completed, different results were obtained for the sameSELECTquery on the master and on the slave.

This issue was resolved in MySQL 5.1.24. (Bug#29020)

16.1.2.2. Usage of Row-Based Logging and Replication

Major changes in the replication environment and in the behavior of applications can result from using row-based logging (RBL) or row-based replication (RBR) rather than statement-based logging or replication. This section describes a number of issues known to exist when using row-based logging or replication, and discusses some best practices for taking advantageof row-based logging and replication.

For information about issues specific to MySQL Cluster Replication (which depends on row-based replication), seeSection 17.6.3, “Known Issues in MySQL Cluster Replication”.

RBL, RBR, and temporary tables.As noted inSection 16.4.1.19, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format. When mixed format is in effect, “safe” statements involving temporary tables are logged using statement-based format. For more information, seeSection 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”.

Note

Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format.

RBL and theBLACKHOLEstorage engine.Prior to MySQL 5.1.29,DELETEandUPDATEstatements forBLACKHOLEtables did not work with RBL. (Bug#38360)

RBL and synchronization of nontransactional tables.When using row-based replication of aMyISAMor other nontransactional table, changed rows are written to the transaction cache. Often, when many rows are affected, the set of changes are split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, and then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave's copy of the table.)

Latency and binary log size.Because RBL writes changes for each row to the binary log, its size can increase quite rapidly. In a replication environment, this can significantly increase the time required to make changes on the slave that match those on the master. You should be aware of the potential for this delay in your applications.

Reading the binary log.As of MySQL 5.1.28, when invoked with the--base64-output=DECODE-ROWSand--verboseoptions,mysqlbinlogformats the contents of the binary log in a manner that is easily human readable. This is helpful when binary log events were written in row-based format if you want to read or recover from a replication or database failure using the contents of the binary log. For more information, seeSection 4.6.7.2, “mysqlbinlogRow Event Display”.

Binary log execution errors andslave_exec_mode.Ifslave_exec_modeisIDEMPOTENT, a failure to apply changes from RBL because the original row cannot be found does not trigger an error or cause replication to fail. This means that it is possible that updates are not applied on the slave, so that the master and slave are no longer synchronized. Latency issues and use of nontransactional tables with RBR whenslave_exec_modeisIDEMPOTENTcan cause the master and slave to diverge even further. For more information aboutslave_exec_mode, seeSection 5.1.4, “Server System Variables”.

Note

slave_exec_mode=IDEMPOTENTis generally useful only for circular replication or multi-master replication with MySQL Cluster, for whichIDEMPOTENTis the default value (seeSection 17.6, “MySQL Cluster Replication”).

For other scenarios, settingslave_exec_modetoSTRICTis normally sufficient; this is the default value for storage engines other thanNDB.

Lack of binary log checksums.RBL uses no checksums. This means that network, disk, and other errors may not be identified when processing the binary log. To ensure that data is transmitted without network corruption, you may want to consider using SSL, which adds another layer of checksumming, for replication connections. TheCHANGE MASTER TOstatement has options to enable replication over SSL. See alsoSection 12.5.2.1, “CHANGE MASTER TOSyntax”, for general information about setting up MySQL with SSL.

Filtering based on server ID not supported.A common practice is to filter out changes on some slaves by using aWHEREclause that includes the relation@@server_id <>id_valueclause withUPDATEandDELETEstatements, a simple example of such a clause beingWHERE @@server_id <> 1. However, this does not work correctly with row-based logging. If you must use theserver_idsystem variable for statement filtering, you must also use--binlog_format=STATEMENT.

Database-level replication options.The effects of the--replicate-do-db,--replicate-ignore-db, and--replicate-rewrite-dboptions differ considerably depending on whether row-based or statement-based logging is used. Because of this, it is recommended to avoid database-level options and instead use table-level options such as--replicate-do-tableand--replicate-ignore-table. For more information about these options and the impact that your choice of replication format has on how they operate, seeSection 16.1.3, “Replication and Binary Logging Options and Variables”.