PlaceOweb Dotclear

Quel serait la meilleure méthode de réplication ?

• SBR : Statement Based Replication
• RBR : Row Based Replication
• MBR ? Mixed Based Replication

MariaDB

• Binary Log Formats
• Replication and Binary Log Server System Variables replicate_ignore_db
• mysqld Options --binlog-ignore-db
• Dynamic Replication Variables SET GLOBAL replicate_ignore_db = "mydbtoignore";

Row-based instead of statement-based logging will be used in the following situations:

• ...
• When a table with an AUTO_INCREMENT column is updated and a trigger or stored function is used.
• ...

MySQL 5.1 et replication

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.

Incompatible change: As of MySQL 5.1.29, the default binary logging mode has been changed from MIXED to STATEMENT for compatibility with MySQL 5.0.

--binlog-format={ROW|STATEMENT|MIXED}

• Default STATEMENT (>= 5.1.5, <= 5.1.7) ROW|STATEMENT
• Default STATEMENT (>= 5.1.8, <= 5.1.11) ROW|STATEMENT|MIXED
• Default MIXED (>= 5.1.12, <= 5.1.28) ROW|STATEMENT|MIXED
• Default STATEMENT (>= 5.1.29) ROW|STATEMENT|MIXED

When using statement-based binary logging on a replication master server, statements received by its slaves are written to the query log of each slave. Statements are written to the query log of the master server if a client reads events with the mysqlbinlog utility and passes them to the server.

However, when using row-based binary logging, updates are sent as row changes rather than SQL statements, and thus these statements are never written to the query log when binlog_format is ROW. A given update also might not be written to the query log when this variable is set to MIXED, depending on the statement used. See Section 16.1.2.1, “Advantages and Disadvantages of Statement-Based and Row- Based Replication”, for more information.

Binary Logging Formats

The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:

• Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.

• In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlogformat= ROW.

• A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlogformat= MIXED.

Support for row-based logging was added in MySQL 5.1.5. Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12, MIXED become the default logging mode; in 5.1.29, the default was changed back to STATEMENT for compatibility with MySQL 5.0.

Starting with MySQL 5.1.20, the logging format can also be set or limited by the storage engine being used. This helps to eliminate issues when replicating certain statements between a master and slave that are using different storage engines.

With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.

Prior to MySQL 5.1.36, this warning read, Statement is not safe to log in statement format. (Bug #42415)

You can avoid these issues by using MySQL's row-based replication instead.

Setting The Binary Log Format

You can select the binary logging format explicitly by starting the MySQL server with --binlogformat= type. The supported values for type are:

• STATEMENT causes logging to be statement based.
• ROW causes logging to be row based.
• MIXED causes logging to use mixed format.

The default binary logging format depends on the version of MySQL you are using:

• For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by

default.

• For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

-- The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable:
SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_format = 'MIXED';

-- An individual client can control the logging format for its own statements by setting the session value of binlog_format:
SET SESSION binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';

Mixed Binary Logging Format

When running in MIXED logging format, the server automatically switches from statement-based to rowbased logging under the following conditions: Mixed Binary Logging Format

• ...
• When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked.
• ...
• When a statement refers to one or more system variables. Exception : ..., auto_increment_increment, last_insert_id, timestamp, ...
• ...

storage engine InnoDB :

• Row Logging Supported ? Yes
• Statement Logging Supported ? Yes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.

This is the MySQL™ Reference Manual. It documents MySQL 5.1 through 5.1.73, as well as MySQL Cluster releases based on versions 6.1, 6.2, 6.3, 7.0, and 7.1 of the NDB storage engine through 5.1.15-ndb-6.1.23, 5.1.51-ndb-6.2.19, 5.1.73-ndb-6.3.55, 5.1.73-ndb-7.0.42, and 5.1.77-ndb-7.1.37.

MySQL

• Replication Formats Prior to MySQL 5.7.7, statement-based format was the default. In MySQL 5.7.7 and later, row-based format is the default. When using MIXED format, 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, see “Mixed Binary Logging Format”.
  • Advantages and Disadvantages of Statement-Based and Row-Based Replication