How to configure and enable MariaDB logs

MariaDB logs are powerful tools for monitoring and optimizing your database instances. This guide explains the main types of logs, how they work and how to configure and enable them.

What logs are available in MariaDB?

MariaDB provides four main log types. Each records a different aspect of database activity:

Error log

The error log records critical events such as server start and stop, crashes and other serious errors. It is enabled by default and is essential for diagnosing server problems.

General query log

The general query log records every connection to the server and logs each SQL command that is run. It is useful for error analysis and monitoring user activity.

Binary log

A binary log records all changes to the database, including both data changes and structural modifications. It is essential for replication and point-in-time recovery.

Slow query log

The slow query log records SQL queries that take longer than a defined execution time. It is an important tool for identifying performance bottlenecks and optimizing queries.

How to configure and use MariaDB logs

The steps below show you how to activate, configure and review the four main log files in MariaDB. These include the error log, general query log, binary log and slow query log, along with the MariaDB log file location.

Prerequisites

• A server with a current version of Ubuntu or another Linux distribution
• A user account with sudo privileges
• No MySQL installation on the same server, as it can conflict with MariaDB configuration files and ports
• Basic SQL knowledge

Step 1: Display and configure the error log

Make sure that MariaDB is installed. To do so, run the following command:

sudo apt update
sudo apt install mariadb-server

The first command updates your system’s package list to ensure you install the latest available version. The second command installs the MariaDB server package on your system.

To connect to MariaDB, run the following command:

sudo mariadb

This opens the interactive SQL console with administrative rights so you can run SQL commands directly.

In the next step, check where MariaDB writes error messages. Do this with this SQL statement:

SHOW VARIABLES LIKE '%log_error%';

If log_error has no value, error messages are written to the central system log (syslog) by default.

To view the system log, run the following command:

journalctl -u mariadb.service

This displays a chronological list of system messages generated by the MariaDB service. This is useful for investigating startup problems or other critical events.

To use a custom error log file instead, you need to edit the MariaDB configuration file. Open it with a text editor like nano:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

In the [mysqld] section, add or uncomment:

log_error = /var/log/mariadb/error.log

You now need to ensure that the directory you specified in the configuration exists and has the correct permissions. Create it and assign the appropriate ownership so that MariaDB can write to it:

sudo mkdir /var/log/mariadb
sudo chown mysql:mysql /var/log/mariadb

The first command creates the /var/log/mariadband directory if it does not already exist. The second command changes its ownership to the mysql user and group, so that the MariaDB server process has the necessary write permissions for logging.

Restart MariaDB to apply the changes:

sudo systemctl restart mariadb

You can now view the error log:

sudo cat /var/log/mariadb/error.log

This file contains information such as startup and shutdown times, warnings and plugin errors.

Step 2: Enable general query log

Check whether the general query log is currently active and where it is stored:

SHOW VARIABLES LIKE '%general%';

Look for the general_log variable to see if it is enabled (general_log = ON). The general_log_file variable shows the file path where entries are saved.

To permanently enable these MariaDB logs, edit the configuration file again:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

In the [mysqld] section, add the following lines:

general_log = 1
general_log_file = /var/log/mariadb/general-query.log

This turns on general query logging and defines the file location for storing the log entries. Restart the MariaDB service so the new settings take effect:

sudo systemctl restart mariadb

You can then view the log with:

sudo cat /var/log/mariadb/general-query.log

The file contains all SQL statements and connection attempts, including timestamps. This makes it easy to track exactly when and what queries were executed.

Step 3: Enable the binary log

To configure the Binary Log, open the same configuration file as before:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following parameters in the [mysqld] section:

log_bin = /var/log/mariadb/binary.log
server_id = 1
binlog_format = ROW

The server_id is required for replication, and every server must have a unique ID. The binlog_format = ROW setting records every change at the row level, which ensures accurate replication of data changes.

Restart MariaDB to apply the configuration:

sudo systemctl restart mariadb

Then check the binary log is active:

SHOW BINARY LOGS;

The active binary log files will then appear there with filenames and size information.

To view the contents of a binary log file, use:

sudo mysqlbinlog /var/log/mariadb/binary.000001

This shows a chronological list of all recorded changes in the log.

Step 4: Enable the slow query log

Edit the configuration file again

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following:

slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow-query.log
long_query_time = 10

long_query_time = 10 means that any query taking longer than 10 seconds will be recorded.

Then, restart MariaDB:

sudo systemctl restart mariadb

Check the status of the slow query log:

SHOW VARIABLES LIKE '%slow_query_log%';

Run a deliberately slow query to test the log:

SELECT SLEEP(12);

This forces the server to wait for 12 seconds, so it should appear in the slow query log.

Finally, view the log:

sudo cat /var/log/mariadb/slow-query.log

You will see details such as timestamps, query duration and the SQL statement that was executed.