Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Understand the binary log in MariaDB Server. This section explains its role in replication and point-in-time recovery, detailing its format, management, and use for data integrity.
The binary log contains a record of all changes to the databases, both data and structure, as well as how long each statement took to execute. It consists of a set of binary log files and an index.
This means that statements such as CREATE, ALTER, INSERT, UPDATE and DELETE will be logged, but statements that have no effect on the data, such as SELECT and SHOW, will not be logged. If you want to log these (at a cost in performance), use the general query log.
If a statement may potentially have an effect, but doesn't, such as an UPDATE or DELETE that returns no rows, it will still be logged (this applies to the default statement-based logging, not to row-based logging - see Binary Log Formats).
The purpose of the binary log is to allow replication, where data is sent from one or more masters to one or more slave servers based on the contents of the binary log, as well as assisting in backup operations.
A MariaDB server with the binary log enabled will run slightly more slowly.
It is important to protect the binary log, as it may contain sensitive information, including passwords.
Binary logs are stored in a binary, not plain text, format, and so are not viewable with a regular editor. However, MariaDB includes mariadb-binlog, a commandline tool for plain text processing of binary logs.
This page is licensed: CC BY-SA / Gnu FDL
To enable binary logging, start the server with the [--log-bin [=name](../../../server-usage/replication-cluster-multi-master/standard-replication/replication-and-binary-log-system-variables.md)] option.
If you specify a filename with an extension (for example .log), the extension will be silently ignored.
If you don't provide a name (which can, optionally, include an absolute path), the default will be datadir/log-basename-bin, datadir/mysql-bin or datadir/mariadb-bin (the latter two if --log-basename is not specified, and dependent on server version). Datadir is determined by the value of the datadir system variable.
We strongly recommend you use either --log-basename or specify a filename to ensure that replication doesn't stop if the hostname of the computer changes.
The directory storing the binary logs will contain a binary log index, as well as the individual binary log files.
The binary log files will have a series of numbers as filename extensions. Each additional binary log will increment the extension number, so the oldest binary logs will have lower numbers, the most recent, higher numbers.
A new binary log, with a new extension, is created every time the server starts, the logs are flushed, or the maximum size is reached (determined by ).
The binary log index file contains a master list of all the binary logs, in order. From , if is enabled (the default), an additional index file (.idx) is present.
A sample listing from a directory containing the binary logs:
The binary log index file will by default have the same name as the individual binary logs, with the extension .index. You can specify an alternative name with the --log-bin-index [=filename] .
Clients with the privilege (or, from , the privilege, can disable and re-enable the binary log for the current session by setting the variable.
There are three formats for the binary log. The default is , which is a mix of and . See for a full discussion.
- Delete logs
- Close and rotate logs
This page is licensed: CC BY-SA / Gnu FDL
Selected events in the binary log can be optionally compressed, to save space in the binary log on disk and in network transfers.
The events that can be compressed are the events that normally can be of a significant size: Query events (for DDL and DML in statement-based replication), and row events (for DML in row-based replication).
Compression is fully transparent. Events are compressed on the primary before being written into the binary log, and are uncompressed by the I/O thread on the replica before being written into the relay log. The mariadb-binlog command will likewise uncompress events for its output.
Currently, the zlib compression algorithm is used to compress events.
Compression will have the most impact when events are of a non-negligible size, as each event is compressed individually. For example, batch INSERT statements that insert many rows or large values, or row-based events that touch a number of rows in one query.
The log_bin_compress option is used to enable compression of events. Only events with data (query text or row data) above a certain size are compressed; the limit is set with the log_bin_compress_min_len option.
This page is licensed: CC BY-SA / Gnu FDL
Flashback is a feature that allows instances, databases or tables to be rolled back to an old snapshot.
Flashback is currently supported only over DML statements (, , ). An upcoming version of MariaDB will add support for flashback over DDL statements (, , , etc.) by copying or moving the current table to a reserved and hidden database, and then copying or moving back when using flashback. See .
Flashback is achieved in MariaDB Server using existing support for full image format binary logs (), so it supports all engines.
The real work of Flashback is done by with --flashback. This causes events to be translated: INSERT to DELETE, DELETE to INSERT, and for UPDATEs, the before and after images are swapped.
When executing mariadb-binlog with --flashback, the Flashback events will be stored in memory. You should make sure your server has enough memory for this feature.
mariadb-binlog has the option --flashback or -B that will let it work in flashback mode.
mariadbd has the option --flashback that enables the binary log and sets binlog_format=ROW. It is not mandatory to use this option if you have already enabled those options directly.
Do not use -v -vv options, as this adds verbose information to the binary log which can cause problems when importing. See MDEV-12066 and MDEV-12067.
With a table "mytable" in database "test", you can compare the output with --flashback and without.
If you know the exact position, --start-position can be used instead of --start-datetime.
Then, by importing the output file (mariadb < flashback.sql), you can flash your database/table back to the specified time or position.
A common use case for Flashback is the following scenario:
You have one primary and two replicas, one started with --flashback (i.e. with binary logging enabled, using binlog_format=ROW, and binlog_row_image=FULL).
Something goes wrong on the primary (like a wrong update or delete) and you would like to revert to a state of the database (or just a table) at a certain point in time.
Remove the flashback-enabled replica from replication.
Invoke to find the exact log position of the first offending operation after the state you want to revert to.
Run mariadb-binlog --flashback --start-position=xyz | mariadb to pipe the output of mariadb-binlog directly to the mariadb client, or save the output to a file and then direct the file to the command-line client.
This page is licensed: CC BY-SA / Gnu FDL
Mixed Logging
Regardless of the format, binary log events are always stored in a binary format, rather than in plain text. MariaDB includes the mariadb-binlog utility that can be used to output binary log 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 mixed logging which is replication-safe and requires less storage space than row logging.
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 binary log 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 binlog_format 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.
ALTER TABLE of a table using a storage engine that stores data remotely, such as the S3 storage engine, to another storage engine.
One is using SEQUENCEs in the statement or the CREATE TABLE definition.
In certain cases, a statement may not be deterministic, and therefore not safe for replication. If MariaDB determines that an unsafe statement has been executed, then it will issue a warning. For example:
See Unsafe Statements for Statement-based Replication 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. Mixed logging 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 Unsafe Statements for Statement-based Replication: Unsafe Statements 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 binlog_format system variable to MIXED.
When row-based logging is enabled, DML statements are not logged to the binary log. Instead, each insert, update, or delete performed by the statement for each row is logged to the binary log separately. DDL statements are still logged to the binary log.
Row-based logging uses more storage than the other log formats but is the safest to use. In practice mixed logging should be as safe.
If one wants to be able to see the original query that was logged, one can enable annotated rows events, that is shown with mariadb-binlog, with --binlog-annotate-row-events. This option is on by default.
This mode can be enabled by setting the binlog_format 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, truncate will be used, if possible (no triggers, no foreign keys etc). This is much faster and uses less space in the binary log.
Compression of 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 --log_bin_compress startup option.
The format for binary log events can be configured by setting the binlog_format system variable. If you have the SUPER privilege, then you can change it dynamically with SET GLOBAL. For example:
You can also change it dynamically for just a specific session with SET SESSION. For example:
It can also be set in a server option group in an option file prior to starting up the server. For example:
Be careful when changing the binary log format when using replication. 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 parallel replication 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 parallel replication, because the worker threads will remain running even after STOP SLAVE is executed. This can be worked around by resetting the slave_parallel_threads system variable. For example:
For considerations when replicating temporary tables, see Replicating temporary tables.
A replica will apply any events it gets from the primary, regardless of the binary log format. The binlog_format 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 binlog_format. Statements that directly edit the mysql database include INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, and TRUNCATE TABLE.
If the mysql database is edited indirectly, statement logging is used regardless of binlog_format setting. Statements editing the mysql database indirectly include GRANT, REVOKE, SET PASSWORD, RENAME USER, ALTER, DROP and CREATE (except for the situation described below).
CREATE TABLE ... SELECT can use a combination of logging formats. The CREATE TABLE portion of the statement is logged using statement-based logging, while the SELECT portion is logged according to the value of binlog_format.
This page is licensed: CC BY-SA / Gnu FDL
mariadb-binlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T mytable \
--start-datetime="2013-03-27 14:54:00" > review.sqlmariadb-binlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T mytable \
--start-datetime="2013-03-27 14:54:00" --flashback > flashback.sql[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 SLAVEshell> ls -l
total 100
...
-rw-rw---- 1 mysql adm 2098 Apr 19 00:46 mariadb-bin.000079
-rw-rw---- 1 mysql adm 332 Apr 19 00:56 mariadb-bin.000080
-rw-rw---- 1 mysql adm 347 Apr 19 07:36 mariadb-bin.000081
-rw-rw---- 1 mysql adm 306 Apr 20 07:15 mariadb-bin.000082
-rw-rw---- 1 mysql adm 332 Apr 20 07:41 mariadb-bin.000083
-rw-rw---- 1 mysql adm 373 Apr 21 07:56 mariadb-bin.000084
-rw-rw---- 1 mysql adm 347 Apr 21 09:09 mariadb-bin.000085
-rw-rw---- 1 mysql adm 398 Apr 21 21:24 mariadb-bin.000086
-rw-rw---- 1 mysql adm 816 Apr 21 17:05 mariadb-bin.indexSET sql_log_bin = 0;
SET sql_log_bin = 1;The server supports group commit. This is an important optimization that helps MariaDB reduce the number of expensive disk operations that are performed.
In ACID terminology, the "D" stands for durability. In order to ensure durability with group commit, and/or should be set. These settings are needed to ensure that if the server crashes, then any transaction that was committed prior to the time of crash will still be present in the database after crash recovery.
Setting both and provides the most durability and the best guarantee of consistency after a crash.
If is set, but is not set to 1 or 3, then it is possible after a crash to end up in a state where a transaction present in a server's is missing from the server's . If the server is a , then that means that the server can become inconsistent with its replicas, since the replicas may have replicated transactions from the primary's that are no longer present in the primary's local data.
If is set to 1 or 3, but is not set, then it is possible after a crash to end up in a state where a transaction present in a server's is missing from the server's . If the server is a , then that also means that the server can become inconsistent with its replicas, since the server's replicas would not be able to replicate the missing transactions from the server's .
Setting when is not set can also cause the transaction to be missing from the server's due to some optimizations added in those versions. In that case, it is recommended to always set . If you can't do that, then it is recommended to set to 3, rather than 1. See for more information.
After every transaction , the server normally has to flush any changes the transaction made to the and the to disk (i.e. by calling system calls such as fsync() or fdatasync() or similar). This helps ensure that the data changes made by the transaction are stored durably on the disk. Disk flushing is a time-consuming operation, and can easily impose limits on throughput in terms of the number of transactions-per-second (TPS) which can be committed.
The idea with group commit is to amortize the costs of each flush to disk over multiple commits from multiple parallel transactions. For example, if there are 10 transactions trying to commit in parallel, then we can force all of them to be flushed disk at once with a single system call, rather than do one system call for each commit. This can greatly reduce the need for flush operations, and can consequently greatly improve the throughput of transactions-per-second (TPS).
However, to see the positive effects of group commit, the workload must have sufficient parallelism. A good rule of thumb is that at least three parallel transactions are needed for group commit to be effective. For example, while the first transaction is waiting for its flush operation to complete, the other two transactions will queue up waiting for their turn to flush their changes to disk. When the first transaction is done, a single system call can be used to flush the two queued-up transactions, saving in this case one of the three system calls.
In addition to sufficient parallelism, it is also necessary to have enough transactions per second wanting to commit that the flush operations are a bottleneck. If no such bottleneck exists (i.e. transactions never or rarely need to wait for the flush of another to complete), then group commit will provide little to no improvement.
The frequency of group commits can be changed by configuring the and system variables.
Two status variables are available for checking how effective group commit is at reducing flush overhead. These are the and status variables. We can obtain those values with the following query:
is the total number of transactions committed to the .
is the total number of groups committed to the . As explained in the previous sections of this page, a group commit is when a group of transactions is flushed to the together by sharing a single flush system call. When is set, then this is also the total number of flush system calls executed in the process of flushing commits to the .
Thus the extent to which group commit is effective at reducing the number of flush system calls on the binary log can be determined by the ratio between these two status variables. will always be as equal to or greater than . The greater the difference is between these status variables, the more effective group commit was at reducing flush overhead.
To calculate the group commit ratio, we actually need the values of these status variables from two snapshots. Then we can calculate the ratio with the following formula:
transactions/group commit = (Binlog_commits (snapshot2) - Binlog_commits (snapshot1))/(Binlog_group_commits (snapshot2) - Binlog_group_commits (snapshot1))
For example, if we had the following first snapshot:
And the following second snapshot:
Then we would have:
transactions/group commit = (220 - 120) / (145 - 120) = 100 / 25 = 4 transactions/group commit
If your group commit ratio is too close to 1, then it may help to .
Group commit is also used to enable .
When both (the default) is set and the is enabled, there is now one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3). See for more information.
is the total number of transactions committed to the .
is the total number of groups committed to the .
is the total number of group commits triggered because of the number of commits in the group reached the limit set by the system variable .
is the total number of group commits triggered because a commit was being delayed because of a lock wait where the lock was held by a prior binary log commit. When this happens the later binary log commit is placed in the next group commit.
is the total number of group commits triggered because of the time since the first commit reached the limit set by the system variable .
To query these variables, use a statement such as:
This page is licensed: CC BY-SA / Gnu FDL
The relay log is a set of log files created by a replica during replication.
It's the same format as the binary log, containing a record of events that affect the data or structure; thus, mariadb-binlog can be used to display its contents. It consists of a set of relay log files and an index file containing a list of all relay log files.
Events are read from the primary's binary log and written to the replica's relay log. They are then performed on the replica. Old relay log files are automatically removed once they are no longer needed.
New relay log files are created by the replica at the following times:
when the IO thread starts
when the logs are flushed, with or .
when the maximum size, determined by the system variable, has been reached
By default, the relay log will be given a name host_name-relay-bin.nnnnnn, with host_name referring to the server's host name, and #nnnnnnthe sequence number.
This will cause problems if the replica's host name changes, returning the error Failed to open the relay log and Could not find target log during relay log initialization. To prevent this, you can specify the relay log file name by setting the and system variables.
If you need to overcome this issue while replication is already underway,you can stop the replica, prepend the old relay log index file to the new relay log index file, and restart the replica.
For example:
The shows events in the relay log, and, since relay log files are the same format as binary log files, they can be read with the utility.
Old relay logs are automatically removed once all events have been implemented on the replica, and the relay log file is no longer needed. This behavior can be changed by adjusting the system variable from its default of 1 to 0, in which case the relay logs will be left on the server.
Relay logs are also removed by the statement unless a is used.
One can also flush the logs with the commands.
If the relay logs are taking up too much space on the replica, the system variable can be set to limit the size. The IO thread will stop until the SQL thread has cleared the backlog. By default there is no limit.
This page is licensed: CC BY-SA / Gnu FDL
SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Binlog_commits | 120 |
| Binlog_group_commits | 120 |
+----------------------+-------+
2 rows in set (0.00 sec)SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Binlog_commits | 220 |
| Binlog_group_commits | 145 |
+----------------------+-------+
2 rows in set (0.00 sec)SHOW GLOBAL STATUS LIKE 'Binlog_%commit%';shell> cat NEW_relay_log_name.index >> OLD_relay_log_name.index
shell> mv OLD_relay_log_name.index NEW_relay_log_name.indexSee Overview of the Binary Log for a general overview of what the binary log is and Activating the Binary Log for how to make sure it's running on your system.
For details on using the binary log for replication, see the Replication section.
To delete all binary log files on the server, run the RESET MASTER command. To delete all binary logs before a certain datetime, or up to a certain number, use PURGE BINARY LOGS.
If a replica is active but has yet to read from a binary log file you attempt to delete, the statement will fail with an error. However, if the replica is not connected and has yet to read from a log file you delete, the file will be deleted, but the replica will be unable to continue replicating once it connects again.
Log files can also be removed automatically with the system variable. This is set to 0 by default (no removal), but can be set to a time, in days, after which a binary log file will be automatically removed. Log files will only be checked for being older than upon log rotation, so if your binary log only fills up slowly and does not reach on a daily basis, you may see older log files still being kept. You can also force log rotation, and so expiry deletes, by running on a regular basis. Always set higher than any possible replica lag.
From , the variable allows more precise control over binlog deletion and takes precedence if both are non-zero.
If the binary log index file has been removed, or incorrectly manually edited, all of the above forms of purging log files will fail. The .index file is a plain text file and can be manually recreated or edited so that it lists only the binary log files that are present, in numeric/age order.
To be sure replication is not broken while deleting log files, perform the following steps:
Get a listing of binary log files on the primary by running .
Go to each replica server and run to check which binary log file each replica is currently reading.
Find the earliest log file still being read by a replica. No log files before this one will be needed.
If you wish, make a backup of the log files to be deleted
MariaDB starting with
From , it's possible to limit the size of the binlog by setting the system variable. If not set to zero, the total size of the binlog will be stored in the status variable. It's also possible to limit the size of a single binlog file by setting .
There are three formats for the binary log. The default is statement-based logging, while row-based logging and a mix of the two formats are also possible. See for a full discussion.
By default, all changes to data or data structure are logged. This behavior can be changed by starting the server with the --binlog-ignore-db=database_name or --binlog-do-db=database_name .
--binlog-ignore-db=database_name specified a database to ignore for logging purposes, while --binlog-do-db=database_name will not log any statements unless they apply to the specified database.
Neither option accepts comma-delimited lists of multiple databases as an option, since a database name can contain a comma. To apply to multiple databases, use the option multiple times.
--binlog-ignore-db=database_name behaves differently depending on whether statement-based or row-based logging is used. For statement-based logging, the server will not log any statement where the default database is database_name. The default database is set with the statement.
Similarly, --binlog-do-db=database_name also behaves differently depending on whether statement-based or row-based logging is used.
For statement-based logging, the server will only log statement where the default database is database_name. The default database is set with the statement.
For row-based logging, the server will log any updates to any tables in the named database/s, irrespective of the current database.
Assume the server has started with the option --binlog-ignore-db=employees. The following example is logged if statement-based logging is used, and is not logged with row-based logging.
This is because statement-based logging examines the default database, in this case, customers. Since customers is not specified in the ignore list, the statement will be logged. If row-based logging is used, the example will not be logged as updates are written to the tables in the employees database.
Assume instead the server started with the option --binlog-do-db=employees. The following example is not logged if statement-based logging is used, and is logged with row-based logging.
This is again because statement-based logging examines the default database, in this case, customers. Since customers is not specified in the do list, the statement will not be logged. If row-based logging is used, the example will be logged as updates are written to the tables in the employees database.
If MariaDB encounters a full disk error while trying to write to a binary log file, then it will keep retrying the write every 60 seconds. Log messages will get written to the error log every 600 seconds. For example:
However, if MariaDB encounters a full disk error while trying to open a new binary log file, then it will disable binary logging entirely. A log message like the following will be written to the error log:
This page is licensed: CC BY-SA / Gnu FDL
Purge all log files before (not including) the file identified above.
PURGE BINARY LOGS TO 'mariadb-bin.000063';PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';USE customers;
UPDATE employees.details SET bonus=bonus*1.2;USE customers;
UPDATE employees.details SET bonus=bonus*1.2;2018-11-27 2:46:46 140278181563136 [Warning] mysqld: Disk is full writing '/var/lib/mariadb-bin.00001' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
2018-11-27 2:46:46 140278181563136 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs2018-11-27 3:30:49 140278181563136 [ERROR] Could not open '/var/lib/mariadb-bin.00002 for logging (error 28). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
2018-11-27 3:30:49 140278181563136 [ERROR] mysqld: Error writing file '(null)' (errno: 9 "Bad file descriptor")
2018-11-27 3:30:49 140278181563136 [ERROR] mysqld: Error writing file '(null)' (errno: 28 "No space left on device")