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 , a commandline tool for plain text processing of binary logs.

_{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.

Creating Relay Log Files

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.

Relay Log Names

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 causes problems if the replica's host name changes, returning this error:

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:

Viewing Relay Logs

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.

Removing Old Relay Logs

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.

See also

_{This page is licensed: CC BY-SA / Gnu FDL}

There are many variables in MariaDB that you can use to define what to log and when to log.

This article will give you an overview of the different logs and how to enable/disable logging to these.

Note that storage engines can have their logs too: for example, InnoDB keeps an and a Redo Log which are used for rollback and crash recovery. However, this page only lists MariaDB server logs.

The MyISAM log records all changes to tables. It is not enabled by default. To enable it, start the server with the option, for example:

The isam instead of myisam above is not a typo - it's a legacy from when the predecessor to the MyISAM format, called ISAM. The option can be used without specifying a filename, in which case the default, myisam.log is used.

To process the contents of the log file, use the utility.

_{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}

• Description: Queries slower than log_slow_always_query_time are not affected by or . Query will be logged to the if the execution time of the query is longer than and log_slow_always_query_time. The argument will be treated as a decimal value with microsecond precision.

• Command line: --log-slow-always-query-time=num

Switching it On

output can be switched on by specifying the explain keyword in the system variable. Alternatively, you can set with the log-slow-verbosity command line argument.

EXPLAIN output will only be recorded if the slow query log is written to a file (and not to a table - see ). This limitation also applies to other extended statistics that are written into the slow query log.

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 (INSERT, DELETE, UPDATE). An upcoming version of MariaDB will add support for flashback over DDL statements (DROP, TRUNCATE, ALTER, etc.) by copying or moving the current table to a reserved and hidden database, and then copying or moving back when using flashback. See MDEV-10571.

Flashback is achieved in MariaDB Server using existing support for full image format binary logs (binlog_row_image=FULL), so it supports all engines.

The real work of Flashback is done by mariadb-binlog with --flashback. This causes events to be translated: INSERT to DELETE, DELETE to INSERT, and for UPDATE statements, the before and after images are swapped.

When executing mariadb-binlog with --flashback, the Flashback events are stored in memory. You must make sure your server has enough memory for this feature.

Arguments

• has the option --flashback or -B that lets it work in flashback mode.

• has the option 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 and .

Example

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.

Common Use Case

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 , and ).

• 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.

_{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 is 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 or specify a filename to ensure that doesn't stop if the hostname of the computer changes.

The directory storing the binary logs contains a binary log index, as well as the individual binary log files.

The binary log files have a series of numbers as filename extensions. Each additional binary log increments the extension number, so the oldest binary logs have lower numbers, and the most recent ones have 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 has, by default, 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.

Binary Log Format

There are three formats for the binary log. The default is , which is a mix of and . See for a full discussion.

See Also

• - Delete logs

• - Close and rotate logs

_{This page is licensed: CC BY-SA / Gnu FDL}

The transaction coordinator log (tc_log) is used to coordinate transactions that affect multiple . One of the main purposes of this log is in crash recovery.

Modes of Crash Recovery

There are two modes of crash recovery:

• Automatic crash recovery.

By default, all logs are disabled or written into files. The and the can also be written to special tables in the mysql database. During the startup, entries will always be written into files.

Note that will only be recorded if the slow query log is written to a file and not to a table.

To write logs into tables, the server system variable is used. Allowed values are FILE, TABLE and NONE. It is possible to specify multiple values, separated with commas, to write the logs into both tables and files. NONE disables logging and has precedence over the other values.

So, to write logs into tables, one of the following settings can be used:

Supported Binary Log Formats

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.

Statement-Based Logging

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.

Things to be aware of with statement-based logging

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

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.

Row-Based Logging

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.

Things to be aware of with row-based logging

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.

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 startup option.

Configuring the Binary Log Format

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 .

Effect of the Binary Log Format on Replicas

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.

The mysql Database

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.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

The general query log is a log of every SQL query received from a client, as well as each client connect and disconnect. Since it's a record of every query received by the server, it can grow large quite quickly.

However, if you only want a record of queries that change data, it might be better to use the instead. One important difference is that the only logs a query when the transaction is committed by the server, but the general query log logs a query immediately when it is received by the server.

Enabling the General Query Log

The general query log is disabled by default.

To enable the general query log, set the

See for a general overview of what the binary log is and for how to make sure it's running on your system.

For details on using the binary log for replication, see the section.

Purging Log Files

To delete all binary log files on the server, run the command. To delete all binary logs before a certain datetime, or up to a certain number, use .

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.

The SQL_ERROR_LOG plugin collects errors sent to clients in a log file defined by sql_error_log_filename, so that they can later be analyzed. The log file can be rotated if sql_error_log_rotate is set.

Errors are logged as they happen and an error will be logged even if it was handled by a condition handler and was never technically sent to the client.

From warnings can also be logged if sql_error_log_warnings is enabled.

Comments are also logged, which can make the log easier to search. But this is only possible if the client does not strip the comments away. For example, the mariadb command-line client only leaves comments when started with the --comments option.

Installing the Plugin

Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.

The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or . For example:

The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an . For example:

Uninstalling the Plugin

You can uninstall the plugin dynamically by executing or . For example:

If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.

Logging

The log format until is:

Starting from , the format is:

Starting from , , , , , , and , when the variable is enabled, the log also contains thread id and database name. If there is no database, NULL will be displayed.

Each separated by a space or : as above

Example of Logs

With enabled (database test and thread id 4):

Example Usage

Versions

System Variables and Options

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The server supports group commit. This is an important optimization that helps MariaDB reduce the number of expensive disk operations that are performed.

Durability

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.

Durable InnoDB Data and Binary Logs

Setting both and provides the most durability and the best guarantee of consistency after a crash.

Non-Durable InnoDB Data

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.

Non-Durable Binary Logs

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 .

Non-Durable InnoDB Data and Binary Logs

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.

Amortizing Disk Flush Costs

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.

Changing Group Commit Frequency

The frequency of group commits can be changed by configuring the and system variables.

Measuring Group Commit Ratio

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 .

Use of Group Commit with Parallel Replication

Group commit is also used to enable .

Effects of Group Commit on InnoDB Performance

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.

Status Variables

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:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

The transaction coordinator log (tc_log) is used to coordinate transactions that affect multiple XA-capable storage engines. If you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available.

Types of Transaction Coordinator Logs

There are currently two implementations of the transaction coordinator log:

• Binary log-based transaction coordinator log

• Memory-mapped file-based transaction coordinator log

If the is enabled on a server, then the server will use the binary log-based transaction coordinator log. Otherwise, it will use the memory-mapped file-based transaction coordinator log.

Binary Log-Based Transaction Coordinator Log

This transaction coordinator uses the , which is enabled by the server option.

Memory-Mapped File-Based Transaction Coordinator Log

This transaction coordinator uses the memory-mapped file defined by the server option. The size is defined by the system variable.

Some facts about this log:

• The log consists of a memory-mapped file that is divided into pages of 8KB size.

• The usable size of the first page is smaller because of the log header. There is a PAGE control structure for each page.

• Each page (or rather its PAGE control structure) can be in one of the three states - active, syncing, pool.

• There could be only one page in the active or syncing state, but many in the pool state - pool is a fifo queue.

The result of such an architecture is a natural "commit grouping" - If commits are coming faster than the system can sync, they do not stall. Instead, all commits that came since the last sync are logged to the same "active" page, and they all are synced with the next - one - sync. Thus, thought individual commits are delayed, throughput is not decreasing.

When an xid is added to an active page, the thread of this xid waits for a page's condition until the page is synced. When a syncing slot becomes vacant one of these waiters is awakened to take care of syncing. It syncs the page and signals all waiters that the page is synced. The waiters are counted, and a page may never become active again until waiters==0, which means that is all waiters from the previous sync have noticed that the sync was completed.

Note that a page becomes "dirty" and has to be synced only when a new xid is added into it. Removing a xid from a page does not make it dirty - we don't sync xid removals to disk.

Monitoring the Memory-Mapped File-Based Transaction Coordinator Log

The memory-mapped transaction coordinator log can be monitored with the following status variables:

Heuristic Recovery with the Transaction Coordinator Log

One of the main purposes of the transaction coordinator log is in crash recovery. See for more information about that.

Known Issues

You must enable exactly N storage engines

Prior to , if you were using the memory-mapped file-based transaction coordinator log, and then if the server crashed and you changed the number of XA-capable storage engines that it loaded, then you could see errors like the following:

To recover from this error, delete the file defined by the server option, and then restart the server with the option set.

See for more information.

Bad magic header in tc log

If you are using the memory-mapped file-based transaction coordinator log, then it is possible to see errors like the following:

This means that the header of the memory-mapped file-based transaction coordinator log is corrupt. To recover from this error, delete the file defined by the --log-tc server option, and then restart the server with the --tc-heuristic-recover option set.

This issue is known to occur when using docker. In that case, the problem may be caused by using a MariaDB container version with a data directory from a different MariaDB or MySQL version. Therefore, some potential fixes are:

• Pinning the docker instance to a specific MariaDB version in the docker compose file, so that it consistently uses the same version.

• Running to ensure that the data directory is upgraded to match the server version.

See for more information.

MariaDB Galera Cluster

builds include a built-in plugin called wsrep. Prior to , this plugin was internally considered an . Consequently, these MariaDB Galera Cluster builds have multiple XA-capable storage engines by default, even if the only "real" storage engine that supports external enabled on these builds by default is . Therefore, when using one these builds MariaDB would be forced to use a transaction coordinator log by default, which could have performance implications.

For example, describes performance problems where MariaDB Galera Cluster actually performs better when the is enabled. It is possible that this is caused by the fact that MariaDB is forced to use the memory-mapped file-based transaction coordinator log in this case, which may not perform as well.

This became a bigger issue in when the patch that powers MariaDB Galera Cluster was enabled on most MariaDB builds on Linux by default. Consequently, this built-in wsrep plugin would exist on those MariaDB builds on Linux by default. Therefore, MariaDB users might pay a performance penalty, even if they never actually intended to use the MariaDB Galera Cluster features included in .

In and later, the built-in wsrep plugin has been changed to a replication plugin. Therefore, it is no longer considered an storage engine, so it no longer forces MariaDB to use a transaction coordinator log by default.

See for more information.

_{This page is licensed: CC BY-SA / Gnu FDL}

Unix and Linux distributions offer the logrotate utility, which makes it very easy to rotate log files. This page will describe how to configure log rotation for the error log, general query log, and the slow query log.

Configuring Locations and File Names of Logs

The first step is to configure the locations and file names of logs. To make the log rotation configuration easier, it can be best to put these logs in a dedicated log directory.

We will need to configure the following:

• The location and file name is configured with the system variable.

• The location and file name is configured with the system variable.

• The location and file name is configured with the system variable.

If you want to enable the and immediately, then you will also have to configure the following:

• The is enabled with the system variable.

• The is enabled with the system variable.

These options can be set in a server in an prior to starting up the server. For example, if we wanted to put our log files in /var/log/mysql/, then we could configure the following:

We will also need to create the relevant directory:

If you are using , then you may also need to set the SELinux context for the directory. See for more information. For example:

After MariaDB is restarted, it will use the new log locations and file names.

Configuring Authentication for Logrotate

The utility needs to be able to authenticate with MariaDB in order to flush the log files.

The easiest way to allow the utility to authenticate with MariaDB is to configure the root@localhost user account.

Note

The root@localhost user account is configured to use unix_socket authentication by default

The root@localhost user account can be altered to use authentication with the statement. For example:

<>

Configuring Logrotate

At this point, we can configure the utility to rotate the log files.

On many systems, the primary configuration file is located at the following path:

• /etc/logrotate.conf

And the configuration files for individual services are located in the following directory:

• /etc/logrotate.d/

We can create a configuration file for MariaDB by executing the following command in a shell:

You may have to modify this configuration file to use it on your system, depending on the specific version of the utility that is installed. See the description of each configuration directive below to determine which versions support that configuration directive.

Each specific configuration directive does the following:

• missingok: This directive configures it to ignore missing files, rather than failing with an error.

• create 660 mysql mysql: This directive configures it to recreate the log files after log rotation with the specified permissions and owner.

• notifempty: This directive configures it to skip a log file during log rotation if it is empty.

If our system does not have 3.8.9 or later, which is needed to support the createolddir directive, then we will also need to create the relevant directory specified by the olddir directive:

Testing Log Rotation

We can test log rotation by executing the utility with the --force option. For example:

Keep in mind that under normal operation, the utility may skip a log file during log rotation if the utility does not believe that the log file needs to be rotated yet. For example:

• If you set the notifempty directive mentioned above, then it will be configured to skip a log file during log rotation if the log file is empty.

• If you set the daily directive mentioned above, then it will be configured to only rotate each log file once per day.

• If you set the minsize 1M directive mentioned above, then it will be configured to skip a log file during log rotation if the log file size is smaller than 1 MB.

However, when running tests with the --force option, the utility does not take these options into consideration.

After a few tests, we can see that the log rotation is indeed working:

Logrotate in Ansible

Let's see an example of how to configure logrotate in Ansible.

First, we'll create a couple of tasks in our playbook:

The first task creates a directory to store the old, compressed logs, and set proper permissions.

The second task uploads logrotate configuration file into the proper directory, and calls it mysql. As you can see the original name is different, and it ends with the .j2 extension, because it is a Jinja 2 template.

The file will look like the following:

The file is very similar to the file shown above, which is obvious because we're still uploading a logrotate configuration file. Ansible is just a tool we've chosen to do this.

However, both in the tasks and in the template, we used some variables. This allows to use different paths and rotation parameters for different hosts, or host groups.

If we have a group host called mariadb that contains the default configuration for all our MariaDB servers, we can define these variables in a file called group_vars/mariadb.yml:

After setting up logrotate in Ansible, you may want to deploy it to a non-production server and test it manually as explained above. Once you're sure that it works fine on one server, you can be confident in the new Ansible tasks and deploy them on all servers.

For more information on how to use Ansible to automate MariaDB configuration, see .

_{This page is licensed: CC BY-SA / Gnu FDL}

The slow query log is a record of SQL queries that took a long time to perform.

Note that, if your queries contain user's passwords, the slow query log may contain passwords too. Thus, it should be protected.

The number of rows affected by the slow query are also recorded in the slow query log.

Enabling the Slow Query Log

The slow query log is disabled by default.

To enable the slow query log, set the slow_query_log system variable (or, from MariaDB 10.11, log_slow_query) to 1. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Configuring the Slow Query Log Filename

By default, the slow query log is written to ${hostname}-slow.log in the directory. However, this can be changed.

One way to configure the slow query log filename is to set the system variable (or, from , ). It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

If it is a relative path, then the is relative to the directory.

However, the system variable can also be an absolute path. For example:

Another way to configure the slow query log filename is to set the option, which configures MariaDB to use a common prefix for all log files (e.g. slow query log, , , , etc.). The slow query log filename will be built by adding -slow.log to this prefix. This option cannot be set dynamically. It can be set in a server in an prior to starting up the server. For example:

The cannot be an absolute path. The log file name is relative to the directory.

Choosing the Slow Query Log Output Destination

The slow query log can either be written to a file on disk, or it can be written to the table in the database. To choose the slow query log output destination, set the system variable.

Writing the Slow Query Log to a File

The slow query log is output to a file by default. However, it can be explicitly chosen by setting the system variable to FILE. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Writing the Slow Query Log to a Table

The slow query log can either be written to the table in the database by setting the system variable to TABLE. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Some rows in this table might look like this:

See for more information.

Disabling the Slow Query Log for a Session

A user can disable logging to the slow query log for a connection by setting the system variable (or, from , ) to 0. For example:

Disabling the Slow Query Log for Specific Statements

It is possible to disable logging to the slow query log for specific types of statements by setting the system variable. This option cannot be set dynamically. It can be set in a server in an prior to starting up the server. For example:

Configuring the Slow Query Log Time

The time that defines a slow query can be configured by setting the system variable (or, from , ). It uses a units of seconds, with an optional milliseconds component. The default value is 10. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Logging Queries That Don't Use Indexes

It can be beneficial to log queries that don't use indexes to the slow query log, since queries that don't use indexes can usually be optimized either by adding an index or by doing a slight rewrite. The slow query log can be configured to log queries that don't use indexes regardless of their execution time by adding the option "not_using_index" to or setting the system variable to 1. It can be changed dynamically with . Some examples:

It can also be set in a server in an prior to starting up the server. For example:

As a significant number of queries can run quickly even without indexes, you can use the system variable (or, from , ) with to limit the logged queries to those having a material impact on the server.

Excluding Queries That Examine Fewer Than a Minimum Row Limit

It can be beneficial to exclude queries that examine fewer than a minimum number of rows from the log. This can be done by setting the system variable, or, from , . It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Logging Slow Administrative Statements

By default, the Slow Query Log logs administrative statements. To disable logging of administrative statements, remove "admin" from the system variable or alternatively set the system variable to OFF. The Slow Query Log considers the following statements administrative: , , , , , , and . In and later, this also includes statements.

You can dynamically enable this feature using a statement and setting it for just the current connection with LOCAL. Some examples:

It can also be set in a server in an prior to starting up the server. For example:

Enabling the Slow Query Log for Specific Criteria

It is possible to enable logging to the slow query log for queries that meet specific criteria by configuring the system variable. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

You can find all options for log_slow_filter at or at .

Throttling the Slow Query Log

The slow query log can create a lot of I/O, so it can be beneficial to throttle it in some cases. The slow query log can be throttled by configuring the system variable. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Configuring the Slow Query Log Verbosity

There are a few optional pieces of information that can be included in the slow query log for each query. This optional information can be included by configuring the system variable. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

It is possible to have .

Viewing the Slow Query Log

Slow query logs written to file can be viewed with any text editor, or you can use the tool to ease the process by summarizing the information.

Queries that you find in the log are key queries to try to optimize by constructing a or by making .

For queries that appear in the log that cannot be optimized in the above ways, perhaps because they are simply very large selects, due to slow hardware, or very high lock/cpu/io contention, using shard/clustering/load balancing solutions, better hardware, or stats tables may help to improve these queries.

Slow query logs written to table can be viewed by querying the table.

Variables Related to the Slow Query Log

• - enable/disable the slow query log. Renamed to from .

• - how the output will be written

• . Whether to log OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log. Deprecated from , use without admin.

• - name of the slow query log file. Renamed to

Rotating the Slow Query Log on Unix and Linux

Unix and Linux distributions offer the utility, which makes it very easy to rotate log files. See for more information on how to use this utility to rotate the slow query log.

_{This page is licensed: CC BY-SA / Gnu FDL}

The error log contains a record of critical errors that occurred during the server's operation, table corruption, start and stop information.

SQL errors can also be logged in a separate file using the .

Configuring the Error Log Output Destination

MariaDB always writes its error log, but the destination is configurable.