Rechercher dans le manuel MySQL
17.2.1.2 Usage of Row-Based Logging and Replication
MySQL uses statement-based logging (SBL), row-based logging (RBL) or mixed-format logging. The type of binary log used impacts the size and efficiency of logging. Therefore the choice between row-based replication (RBR) or statement-based replication (SBR) depends on your application and environment. This section describes known issues when using a row-based format log, and describes some best practices using it in replication.
For additional information, see Section 17.2.1, “Replication Formats”, and Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
For information about issues specific to NDB Cluster Replication (which depends on row-based replication), see Section 22.6.3, “Known Issues in NDB Cluster Replication”.
Row-based logging of temporary tables. As noted in Section 17.4.1.30, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format or (from MySQL 8.0.4) mixed format. For more information, see Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
Temporary tables are not replicated when using row-based or mixed 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.
You can switch from statement-based to row-based binary logging format at runtime even when temporary tables have been created. However, in MySQL 8.0, you cannot switch from row-based or mixed format for binary logging to statement-based format at runtime, because any
CREATE TEMPORARY TABLE
statements will have been omitted from the binary log in the previous mode.The MySQL server tracks the logging mode that was in effect when each temporary table was created. When a given client session ends, the server logs a
DROP TEMPORARY TABLE IF EXISTS
statement for each temporary table that still exists and was created when statement-based binary logging was in use. If row-based or mixed format binary logging was in use when the table was created, theDROP TEMPORARY TABLE IF EXISTS
statement is not logged. In releases before MySQL 8.0.4 and 5.7.25, theDROP TEMPORARY TABLE IF EXISTS
statement was logged regardless of the logging mode that was in effect.Nontransactional DML statements involving temporary tables are allowed when using
binlog_format=ROW
, as long as any nontransactional tables affected by the statements are temporary tables (Bug #14272672).RBL and synchronization of nontransactional tables. When many rows are affected, the set of changes is 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. Depending on the engine used for the slave's copy of the table, this may or may not be effective.
Latency and binary log size. RBL writes changes for each row to the binary log and so its size can increase quite rapidly. 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. mysqlbinlog displays row-based events in the binary log using the
BINLOG
statement (see Section 13.7.7.1, “BINLOG Syntax”). This statement displays an event as a base 64-encoded string, the meaning of which is not evident. When invoked with the--base64-output=DECODE-ROWS
and--verbose
options, mysqlbinlog formats the contents of the binary log to be human readable. When binary log events were written in row-based format and you want to read or recover from a replication or database failure you can use this command to read contents of the binary log. For more information, see Section 4.6.8.2, “mysqlbinlog Row Event Display”.Binary log execution errors and slave_exec_mode. Using
slave_exec_mode=IDEMPOTENT
is generally only useful with MySQL NDB Cluster replication, for whichIDEMPOTENT
is the default value. (See Section 22.6.10, “NDB Cluster Replication: Multi-Master and Circular Replication”). Whenslave_exec_mode
isIDEMPOTENT
, 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_mode
isIDEMPOTENT
can cause the master and slave to diverge even further. For more information aboutslave_exec_mode
, see Section 5.1.8, “Server System Variables”.For other scenarios, setting
slave_exec_mode
toSTRICT
is normally sufficient; this is the default value for storage engines other thanNDB
.Filtering based on server ID not supported. You can filter based on server ID by using the
IGNORE_SERVER_IDS
option for theCHANGE MASTER TO
statement. This option works with statement-based and row-based logging formats, but is deprecated for use whenGTID_MODE=ON
is set. Another method to filter out changes on some slaves is to use aWHERE
clause that includes the relation@@server_id <>
clause withid_value
UPDATE
andDELETE
statements. For example,WHERE @@server_id <> 1
. However, this does not work correctly with row-based logging. To use theserver_id
system variable for statement filtering, use statement-based logging.Database-level replication options. The effects of the
--replicate-do-db
,--replicate-ignore-db
, and--replicate-rewrite-db
options differ considerably depending on whether row-based or statement-based logging is used. Therefore, it is recommended to avoid database-level options and instead use table-level options such as--replicate-do-table
and--replicate-ignore-table
. For more information about these options and the impact replication format has on how they operate, see Section 17.1.6, “Replication and Binary Logging Options and Variables”.RBL, nontransactional tables, and stopped slaves. When using row-based logging, if the slave server is stopped while a slave thread is updating a nontransactional table, the slave database can reach an inconsistent state. For this reason, it is recommended that you use a transactional storage engine such as
InnoDB
for all tables replicated using the row-based format. Use ofSTOP SLAVE
orSTOP SLAVE SQL_THREAD
prior to shutting down the slave MySQL server helps prevent issues from occurring, and is always recommended regardless of the logging format or storage engine you use.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-replication-rbr-usage.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.