MariaDB logs: errors, queries & configuration at a glance

MariaDB logs have comprehensive features that help you monitor and optimize your database instances. We explain the different types of logs and how to configure and activate them.

What logs are available in MariaDB?

MariaDB offers four main types of logs which record different aspects of the database operation:

Error log

The error log documents critical events such as server startup and shutdown operations, crashes, or other serious errors. It is enabled by default and helps diagnose server issues.

General queries log

This log logs every connection to the server as well as all SQL commands executed. It is therefore suitable for error analysis and monitoring user activities.

Binary log

A binary log saves all database changesincluding data manipulations and structure changes. It is essential for relications and restorations to a specific point in time.

Slow query log

This log lists SQL queries whose execution time exceeds a certain duration. It is a valuable aid in identifying and optimizing performance bottlenecks.

Configure and use MariaDB logs: step-by-step guide

In the following, we show you how to activate, configure and analyze the four main log files (also called log files) from MariaDB. We will cover the error log, the general query log, the binary log, the slow query log and the location (rental) from the MariaDB log.

Prerequisites

• A server with a recent version ofUbuntu or another Linux distribution
• A user account with sudo rights
• Not having MySQL installed in parallelbecause MySQL and MariaDB can overlap in configuration files and ports
• Basic knowledge of SQL

Step 1: View and configure the error log

First, you need to make sure you have MariaDB installed. To do this, run the following command:

sudo apt update
sudo apt install mariadb-server

This command first updates your system's package list and then installs the MariaDB server.

To connect to the MariaDB database, run the following command:

This gives you access to the interactive SQL console as an administrator.

In the next step, check where MariaDB writes logs such as error messages. You can do this with this SQL statement:

SHOW VARIABLES LIKE '%log_error%';

If the value of log_error is empty, error messages are by default written to the central system log (syslog).

To view this log, use the following command:

journalctl -u mariadb.service

This gives you a chronological list of system messages generated by the MariaDB service. This is useful when investigating startup errors or other critical events.

If you want to use a clean error log fileyou need to modify the MariaDB configuration file. Open it with a text editor like nano:

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

Search the section [mysqld] and add the following line to it or uncomment it:

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

Verify that the directory exists and has the correct access rights. To do this, run these commands:

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

Commands create the directory and set it for the user and group mysqlso that MariaDB's logging function can write to it.

Then restart the MariaDB service for the configuration changes to take effect:

sudo systemctl restart mariadb

Finally, you can view the error log file:

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

There you will find information about startup times, warnings or problems with plugins.

Step 2: Enable the General Query Log

To check the current status, open the SQL console and enter the following command:

SHOW VARIABLES LIKE '%general%';

There you will see if the general query log is active (general_log = ON) and where it is stored (general_log_file).

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

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

Add the following lines in the section [mysqld] :

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

Then restart the service:

sudo systemctl restart mariadb

To view saved entries, use:

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

There you will find all requests and connections, including timestamps. This helps determine when each request was issued.

Step 3: Enable 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 section [mysqld] :

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

THE server_id is important for replication (each server must have a unique ID). With binlog_format = ROWeach row-level data change is recorded in the MariaDB logs.

Restart the server:

sudo systemctl restart mariadb

To check if the binary log is active, enter in the SQL console:

The active binary logs then appear with their name and size.

To read the contents of a file, use the following command:

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

There you will see all the modification commands listed chronologically.

Step 4: Log slow queries (Slow Query Log)

Edit the configuration file:

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

Insert the following:

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

This means that long_query_time = 10 logs all queries whose execution time exceeds ten seconds.

Then restart the service:

sudo systemctl restart mariadb

Check if the log is active:

SHOW VARIABLES LIKE '%slow_query_log%';

Run a deliberately slow test query:

This instruction forces the server to wait twelve seconds. It should therefore appear in the Slow Query Log.

Finally, you can display the log:

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

There you will find timestamps, query time and the affected SQL statement.

For more information on analyzing log files, please see our guide.