Logs

A MariaDB server maintains the following logs:

• Error log: This log contains the error occurred during the server execution. This includes both server problems (such as errors that stop a plugin from starting) and SQL errors.
• SQL_ERROR_LOG: This is a plugin introduced in MariaDB 5.5 that logs the errors generated by the SQL statements into a file. This is more specific than the error log, because it only logs SQL errors. Using this plugin is the easiest way to see the errors that occur in a stored routine or trigger.
• General query log: SQL statements are logged into this file.
• Slow query logs: This log can be configured to store the queries that take more than a given amount of time or do not use any index. It is useful for finding out why an application or database is slow.
• Binary log (binlog): Depending on the chosen format, this log contains data that is changed to a binary form, or the SQL statements that caused the change. It is necessary for implementing incremental backups, replication, or a database cluster.
• Relay log: This log only exists on replication slaves and it contains the data received by the master. Each entry in a slave's relay log matches an entry in a master's binary log.

InnoDB also has two logs named undo log and redo log. The undo log is used to keep track of the changes performed by the active transactions and roll them back if necessary. The redo log tracks data of the requested data changes and is used to recover tables after a crash.

Aria has a log (the Aria log) that contains the data not applied to the data files, and is used at startup to recover tables that were not closed properly. Changes to MyISAM tables are stored in the MyISAM log.

Each log consists of a set of files, stored in the installation directory and in the data directory, or in a different location determined by the user. However, some logs can be written in the system tables, which are located in the mysql database. The write process is slower in this case, but this allows querying such logs using SQL statements. Also, the CSV storage engine can be used, which allows you to import the logs into external programs using a well-known format.

Since logs are written very often, they have a buffer to improve performances (writing data in chunks has an overhead, which can be reduced by writing data together). Of course, logs are more reliable if they are written more often. Some variables control the use of the buffer, and the DBA can adjust them according to the need for reliability and speed.

Logs also need to be periodically rotated, which means that the new entries will be written in a new file, and the oldest file will probably be removed. The rotation can be automatic (for the binary log), or can be requested by the user via the FLUSH LOGS statement or the mysqladmin utility.

For each log that the user may need to read, there is a utility to show its contents. The log rotation can be done via the mysql-log-rotate script on Red Hat Linux.