There are three supported formats for binary log events:
Statement-Based Logging
Row-Based Logging
Mixed Logging
Regardless of the format, events are always stored in a binary format, rather than in plain text. MariaDB includes the utility that can be used to output events in a human-readable format.
You may want to set the binary log format in the following cases:
If you execute single statements that update many rows, then statement-based logging will be more efficient than row-based logging for the replica to download.
If you execute many statements that don't affect any rows, then row-based logging will be more efficient than statement-based logging for the replica to download.
If you execute statements that take a long time to complete, but they ultimately only insert, update, or delete a few rows in the table, then row-based logging will be more efficient than statement-based logging for the replica to apply.
The default is which is replication-safe and requires less storage space than .
The storage engine API also allows storage engines to set or limit the logging format, which helps reduce errors with replicating between primaries and replicas with different storage engines.
When statement-based logging is enabled, statements are logged to the exactly as they were executed. Temporary tables created on the primary will also be created on the replica. This mode is only recommended where one needs to keep the binary log as small as possible, the primary and replica have identical data (including using the same storage engines for all tables), and all functions being used are deterministic (repeatable with the same arguments). Statements and tables using timestamps or auto_increment are safe to use with statement-based logging.
This mode can be enabled by setting the system variable to STATEMENT.
In certain cases when it would be impossible to execute the statement on the replica, the server will switch to row-based logging for the statement. Some cases of this are:
When replication has been changed from row-based to statement-based and a statement uses data from a temporary table created during row-based mode. In this case, the temporary tables are not stored on the replica, so row logging is the only alternative.
of a table using a storage engine that stores data remotely, such as the , to another storage engine.
One is using in the statement or the definition.
In certain cases, a statement may not be deterministic, and therefore not safe for . If MariaDB determines that an unsafe statement has been executed, then it will issue a warning. For example:
See for more information.
If you need to execute non-deterministic statements, then it is safer to use mixed logging or row-based.
Note that some tables, like temporary tables created in row mode, does not support statement based logging (as the data is not in the binary log). Any statement that uses a table that does not support statement logging will use row based logging. This is to ensure that the data on master and the slave are consistent.
Statement-based logging was the default prior to and before. is now the default.
Mixed logging is the default binary log format.
When mixed logging is enabled, the server uses a combination of statement-based logging and row-based logging. Statement-based logging is used where possible, but when the server determines a statement may not be safe for statement-based logging, it will use row-based logging instead. See for a list of unsafe statements.
During one transaction, some statements may be logged with row logging while others are logged with statement-based logging.
This mode can be enabled by setting the system variable to MIXED.
When row-based logging is enabled, DML statements are not logged to the . Instead, each insert, update, or delete performed by the statement for each row is logged to the separately. DDL statements are still logged to the .
Row-based logging uses more storage than the other log formats but is the safest to use. In practice should be as safe.
If one wants to be able to see the original query that was logged, one can enable , that is shown with , with . This option is on by default.
This mode can be enabled by setting the system variable to ROW.
When using row base logging, some statement works different on the master.
DELETE FROM table_name
In row base mode the table will always use deletion row-by-row which can take a long time if the table is big. It can also use a lot of space in the binary log.
In STATEMENT or MIXED mode, will be used, if possible (no triggers, no foreign keys etc). This is much faster and uses less space in the binary log.
can be used with any of the binary log formats, but the best results come from using mixed or row-based logging. You can enable compression by using the startup option.
The format for events can be configured by setting the system variable. If you have the privilege, then you can change it dynamically with . For example:
You can also change it dynamically for just a specific session with . For example:
It can also be set in a server in an prior to starting up the server. For example:
Be careful when changing the binary log format when using . When you change the binary log format on a server, it only changes the format for that server. Changing the binary log format on a primary has no effect on the replica's binary log format. This can cause replication to give inconsistent results or to fail.
Be careful changing the binary log format dynamically when the server is a replica and is enabled. If you change the global value dynamically, then that does not also affect the session values of any currently running threads. This can cause problems with , because the will remain running even after is executed. This can be worked around by resetting the system variable. For example:
For considerations when replicating temporary tables, see .
A replica will apply any events it gets from the primary, regardless of the binary log format. The system variable only applies to normal (not replicated) updates.
If you are running MySQL or an older MariaDB than 10.0.22, you should be aware of that if you are running the replica in binlog_format=STATEMENT mode, the replica will stop if the primary is used with binlog_format set to anything else than STATEMENT.
The binary log format is upwards-compatible. This means replication should always work if the replica is the same or a newer version of MariaDB than the primary.
Statements that affect the mysql database can be logged in a different way to that expected.
If the mysql database is edited directly, logging is performed as expected according to the . Statements that directly edit the mysql database include , , , , , , , and .
If the mysql database is edited indirectly, statement logging is used regardless of setting. Statements editing the mysql database indirectly include , , , , , and (except for the situation described below).
CREATE TABLE ... SELECT can use a combination of logging formats. The portion of the statement is logged using statement-based logging, while the portion is logged according to the value of binlog_format.
This page is licensed: CC BY-SA / Gnu FDL
[Warning] Unsafe statement written to the binary log using statement format since
BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause.
This is unsafe because the set of rows included cannot be predicted.SET GLOBAL binlog_format='ROW';SET SESSION binlog_format='ROW';[mariadb]
...
binlog_format=ROWSTOP SLAVE;
SET GLOBAL slave_parallel_threads=0;
SET GLOBAL binlog_format='ROW';
SET GLOBAL slave_parallel_threads=4;
START SLAVE