Logs

A MariaDB server maintains the following logs:

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.