MariaDB Logs – Helpful Guide

In this MariaDB tutorial, we will learn about “MariaDB logs” which means we will learn about many log files in MariaDB like error log, slow query log, and how to deal with these log files. Additionally, we will cover the following topics.

• MariaDB Logs
• MariaDB Logs Windows
• MariaDB Logs Ubuntu
• MariaDB Logs Location
• MariaDB Logs CentOS
• MariaDB Logs Linux
• MariaDB Logs docker
• MariaDB Logs To Syslog
• MariaDB Logs Config

MariaDB Logs

The log file contains the records of events of operating systems, software, and processes over a certain time. It keeps the record of every event that occurs within the system at a certain time. So here MariaDB has many log file that keeps records of the event like starting and stopping of server, error log related to the query, database crash log, and critical errors that occur with the database.

The MariaDB has variables that can be used to describe when to log and what to log. The MariaDB has many log files but here we will learn about the most common log files in MariaDB that this given below.

The error log: It keeps the record of the critical error that generates while performing the server operation, start and stop information, and table corruption. The destination path and file name of the error log file is configurable or we can specify the file name and destination where the error log should be written.

A variable log_error is used to set the path and file name of the error log in MariaDB. The code is given to specify the name and destination of the error log file.

SET GLOBAL log_error = path/file_name.err

Where,

• path: It is a destination where the file will be saved.
• file_name.err: It is the name of the file to which the error log is written.

General query log: It is a log that records every query sent to the server or query received by the server immediately and also which client is connecting to and disconnecting from a server. But general query log is not enabled by default, to enable this log set the system variable name general_log to 1. Use the below code to enable the general query log.

SET GLOBAL general_log =1

Where SET GLOBAL is the command to set the value of system variables.

To configure the path and general log filename, the general_log_file system variable is used. Use the below code to configure the path and file name.

SET GLOBAL general_log_file = 'file_name.log' -- To cofigure the file name only

SET GLOBAL general_log_file = path/file_name.log -- To configure the pathe with filename

Slow query log: The slow query log keeps the record of the queries that take a long time to execute. The slow query log is not enabled by default, to enable this log set the system variable slow_query_log to 1. Use the below code to enable a slow query log.

SET GLOBAL slow_query_log =1

Where SET GLOBAL is the command to set the value of system variables.

To configure the path and general log filename, the slow_query_log_file system variable is used. Use the below code to configure the path and file name.

SET GLOBAL slow_query_log_file = 'file_name.log' -- To cofigure the file name only

SET GLOBAL slow_query_log_file = path/file_name.log -- To configure the pathe with filename

The binary log: It is a log that keeps the records of all kinds of changes that occur in the database like time taken by query to execute, changes to data, or data structure. The query containing statements ALTER, DELETE, INSERT, UPDATE and CREATE is logged.

All these logs are saved in a binary format with binary log index files that is not readable with the regular editor. The logs are generated in three binary format row-based logging, statement-based logging and the combination of these two formats.

It is enabled by specifying the option --log-bin with command mysqld while starting the database server.

Read: MariaDB Temporary Table

MariaDB Logs Windows

The logs are saved in the data folder of MariaDB in Windows, Let’s check the file name and its location using the below code in MariaDB prompt.

SHOW VARIABLES LIKE 'log_error';

The log_error is system variables that can be viewed using the command SHOW VARIABLES LIKE.

From the output, we can see that the location of variable log_error is C:\Program Files\MariaDB 10.6\data\MSI.err by default and the name of the log file is MSI.err

Open the log file MSI.err with text editor notepad.

From the output, we can see that each event that occurs in the database has been recorded in the log file with date, time, database engine, and the description of events.

Let’s view the next log that is General query log and enable this log using the below code.

SHOW VARIABLES LIKE 'general_log';

As we can see that the general_log value is OFF which means the log is not enabled by default, so we need to enable this log using the below code.

SET GLOBAL general_log =1;
SHOW VARIABLES LIKE 'general_log';

After running the above code the general query log gets enabled. The output of the above code is given below.

Now the variable general_log value is ON which means it is enabled. By default, the general log file is saved in a folder data of MariaDB as {host_name}.log. This file name and path can be configured using the system variable general_log_file.

Let’s change the name of the general log file using the below code.

SET GLOBAL general_log_file = 'mariadbtips.log';

SHOW VARIABLES LIKE 'general_log_file';

The above output shows that the name of general_log the file is mariadbtip.log that is configured using the system variable general_log_file.

Also, change the file path using the below command.

SET GLOBAL general_log_file = 'C:/Users/Mariadbtips/mariadbtips.log';
SHOW VARIABLES LIKE 'general_log_file';

From the output, we can see that the location of variable general_log is C:/Users/Mariadbtips/mariadbtips.log and the name of the log file is mariadbtips.log.

As we know that the general query log records each SQL query sent to the server, so run any query or the below query and open the log file mariadbtips.log by going to the location where it is saved.

SHOW VARIABLES LIKE 'general_log';

As we run the above query, this query get logged into the file mariadbtips.log. Let’s open this file and check the query above query.

The general log file mariadbtips.log contains the query that we issued to the server, the log file recorded the date, time, and the query that we run on the server as we can see in the log file.

Now we have another log that is Slow query log which keeps a record of the queries that take a long time to execute. Enable this query using the below steps because it was disabled by default.

Login into the MariaDB database using the below code.

mysql -u root -p

Enable the slow query log using the below code.

SET GLOBAL slow_query_log = 1;

Exit from the session or MariaDB database.

exit

Run the above code and log out from the MariaDB session or prompt and then log in again to see the changes.

mysql -u root -p

Check the variable whether it is enabled or not.

SHOW VARIABLES LIKE 'slow_query_log';

Now slow_query_log value is ON which means it is enabled now. we can configure the slow query log file name and location where it will be saved using the system variable name slow_query_log_file.

Let’s change the slow query filename and location using the below code.

SET GLOBAL slow_query_log_file = 'C:/Users/Mariadbtips/mariadbslowquery.log'

View the filename and path using the below code.

 SHOW VARIABLES LIKE 'slow_query_log_file';

The output of the code is given below.

From the output, we can see that the location of variable slow_query_log is C:/Users/Mariadbtips/mariadbslowquery.log and the name of the log file is mariadbslowquery.log.

This is the last most common log is Binary Log that keeps the record of all kinds of changes made to the database as well as data or data structure and time taken by each query. The system variable that can be used to enable this log is sql_log_bin for the current session but enabled by default. Use the below steps to enable this log file.

Login into the MariaDB database using the below code.

mysql -u root -p

Enable the slow query log using the below code.

SET sql_log_bin =1

Before running above, remember that the log is enabled for the current session by default.

Check the variable whether it is enabled or not.

SHOW VARIABLES LIKE 'log_bin';

Read: MariaDB Reset Root Password

MariaDB Logs Ubuntu

In MariaDB, we don’t need to enable the error log because it is enabled by default. So here in this section, we will change the error log file and path using the system variable log_error.

Log into MariaDB prompt and type the below code.

mysql -u root -p

Run the below query to check the status of system variable log_error.

SHOW VARIABLES LIKE 'log_error';

From the output, we can see that the location and name of the error log file using the system variable log_error is null by default.

Configure the error log file by making changes to the MariaDB configuration file. Go to folder /etc/mysql/mariadb.conf.d/50-server.cnf and open the file 50-server.cnf using any editor.

Find this line log_error = /var/log/mysql/error.log in a file 50-server.cnf and remove the symbol # from the front of the line or uncomment it.

Here /var/log/mysql/error.log is default directory to store the error log file, we can change this path according to our need.

To apply this new setting to the configuration file, restart the MariaDB server using the below code.

sudo systemctl restart mariadb

Let’s view this error.log file using any editor like gedit in Ubuntu that is stored at the location /var/log/mysql/error.log.

sudo gedit /var/log/mysql/error.log

Enable the general log query of MariaDB in Ubuntu by assigning value 1 to the system variable general_log. Use the below code to enable the log file.

SET GLOBAL gneral_log =1;

Check the status of the variable using the below code.

SHOW VARIABLES LIKE 'general_log';

The general query log is enabled now, let’s configure the general query log filename and path using the system variable general_log_file.

View the default location and filename of the general query log file using the system variable general_log_file using the below code.

SHOW VARIABLES LIKE 'general_log_file';

From the output, we can see that the location of variable general_log is /var/lib/mysql/ubuntu.log by default and the name of the log file is ubuntu.log.

Change the path and filename of the general log file using the below code.

SET GLOBAL general_log_file = '/var/lib/mysql/mariadb.log';

Check the changed path and filename using the below code.

SHOW VARIABLES LIKE 'general_log_file';

Let’s run any query on MariaDB because the general query log file records every SQL query sent to the MariaDB server.

use mysql;
SELECT * FROM user;

Open the genera log file by going to a location /var/lib/mysql/mariadb.log where it is saved in Ubuntu, use the below code to open the mariadb.log file.

Let’s enable the next log which is the slow query log file using the below steps.

Enable the slow query log of MariaDB in Ubuntu by assigning value 1 to the system variable slow_query_log. Use the below code to enable the log file.

SET GLOBAL slow_query_log =1;
exit

After running the above code exit from the MariaDB session and log in again to check the status of the variable using the below code.

SHOW VARIABLES LIKE 'slow_query_log';

From the output, we have enabled the slow query log. We can change the filename and path of the slow query log file using the system variable slow_query_log_file. But first, check the default filename and path of the slow query log file using the below code.

SHOW VARIABLES LIKE 'slow_query_log_file`;

From the output, we can see that the location of the slow query log file using the system variable slow_query_log is /var/lib/mysql/ubuntu-slow.log by default and the name of the log file is ubuntu-slow.log.

Change the path and filename of the slow query log file using the below code.

SET GLOBAL slow_query_log_file = '/var/lib/mysql/mariadb-slow.log';

Check the changed path and filename using the below code.

SHOW VARIABLES LIKE 'slow_query_log_file';

We have successfully changed the filename and path of the slow query log file, now we can go to that location and open the file to check that which query has taken a long time to execute.

At last, we will learn about the log Binary Log which records all kinds of changes made to the database.

First, check whether it is ON or OFF using the below code into the MariaDB prompt.

SHOW VARIABLES LIKE 'log_bin';
exit

To enable this log file, configure the error log file by making changes to the MariaDB configuration file. Go to the folder /etc/mysql/mariadb.conf.d/50-server.cnf and open the file 50-server.cnf using any editor.

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

Find lines that start with (log_bin, expire_logs_days, max_binlog_size) in a file 50-server.cnf and remove the symbol # from the front of these lines.

Here in the below code /var/log/mysql/error.log is the default directory to store the binary log file, we can change this path according to our need.

log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100M

To apply this new setting to the configuration file, restart the MariaDB server using the below code.

sudo systemctl restart mariadb

After enabling the binary log, a new file is created with the name mysql-bin.000001 in the directory /var/log/mysql. So here we cannot view this file with a normal text editor because it is a binary format file.

Read: MariaDB Backup Database

MariaDB Logs Location

The log files reside in a different location based on a different platform or operating system, and we can configure the location of the logs file on each operating system according to our need.

So here we will cover the two operating systems Windows and Ubuntu(Linux).

On Windows by default, all log files are saved in the data folder of MariaDB which is C:\Program Files\MariaDB 10.6\data, this location is configurable using the system variable of that log files.

On Ubuntu (Linux) by default, all log files reside at the location /var/lib/mysql in MariaDB, In Linux MariaDB all files reside in folder name is mysql, because MariaDB is forked from MYSQL.

Read: MariaDB Enable Remote Access

MariaDB Logs CentOS

So here in this section, we will look at the logs file of MariaDB in CentOS, CentOS is also a Linux distribution-based operating system.

First, we will look the the Error log file in MariaDB which is enabled by default as we know, so login into MariaDB prompt on CentOS using the below code and check the filename and location of the log file.

mysql -u root -p

Check the filename and location of the log file using the system variable log_error. This system variable is also used to set the filename and path of the log file.

SHOW VARIABLES LIKE 'log_error';

From the output, we can see that the location of the error log file using the system variable log_error is /var/log/mariadb/mariadb.log by default and the name of the log file is mariadb.log.

Now, we can open the log file mariadb.log by going to that location using the below code.

sudo nano /var/log/mariadb/mariadb.log

The next log file that we will look at General Query Log , which records each query sent to the MariaDB server. Let’s enable the general query log in MariaDB using the system variable general_log by following the below steps.

Check the status of the general query log whether it ON or OFF but is disabled by default.

SHOW VARIABLES LIEK 'general_log';

Then enable the general query log using the below code, if it is not enabled.

SET GLOBAL general_log =1;

Again the status of the general query log file using the below code.

SHOW VARIABLES LIKE 'general_log';

Let’s view the filename and path of the general query log using the system variable general_log_file.

SHOW VARIABLES LIEK 'general_log_file';

From the output, we can see that the location of the general query log file using the system variable general_log_file is /var/lib/mysql/localhost.log by default and the name of the log file is localhost.log.

We can open the file localhost.log by going to that location and seeing logs related to the general query log using the below code.

sudo nano /var/lib/mysql/localhost.log

Next log is Slow Query Log the file that keeps a record of SQL queries that take a long time to execute.

Enable the slow query log of MariaDB in CentOS by assigning value 1 to the system variable slow_query_log. Use the below code to enable the log file.

SET GLOBAL slow_query_log =1;
exit

After running the above code exit from the MariaDB session and log in again to check the status of the variable using the below code.

SHOW VARIABLES LIKE 'slow_query_log`;

From the output, we have enabled the slow query log. We can change the filename and path of the slow query log file using the system variable slow_query_log_file. But first, check the default filename and path of the slow query log file using the below code.

SHOW VARIABLES LIKE 'slow_query_log_file`;

From the output, we can see that the location of the slow query log file using the system variable slow_query_log is /var/lib/mysql/localhost-slow.log by default and the name of the log file is localhost-slow.log.

Change the path and filename of the slow query log file using the below code.

SET GLOBAL slow_query_log_file = '/var/lib/mysql/mariadbtips-slow.log';

Check the changed path and filename using the below code.

SHOW VARIABLES LIKE 'slow_query_log_file';

We have successfully changed the filename and path of the slow query log file, now we can go to that location /var/lib/mysql/mariadbtips-slow.log and open the file to check that which query has taken a long time to execute.

At last, we will learn about the log Binary Log which records all kinds of changes made to the database.

First, check whether it is ON or OFF using the below code into the MariaDB prompt.

SHOW VARIABLES LIKE 'log_bin';
exit

From the output, we can see that the binary log is OFF.

To enable this log file, configure the error log file by making changes to the MariaDB configuration file. Go to the folder /etc/my.cnf.d/mariadb-server.cnf and open the file mariadb-server.cnf using any editor.

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

Add the following lines as shown in the below output.

log-bin=/var/log/mariadb/lob-bin.01

Here in the above code /var/log/mariadb/lob-bin.01 is the default directory to store the binary log lob-bin.01 file, we can change this path according to our need.

To apply this new setting to the configuration file, restart the MariaDB server using the below code.

sudo systemctl restart mariadb

After enabling the binary log, a new file is created with the name lob-bin.01 in the directory /var/log/mariadb. So here we cannot view this file with a normal text editor because it is a binary format file.

Read: MariaDB Truncate Table

MariaDB Logs Config

In MariaDB, we can configure the logs filename and the using the different system variables relate to that log file.

So here in this section, we will cover the steps to configure the most common log files in MariaDB that we have learned in the above sub-section of this tutorial. Whatever the operating systems you are using, the step is the same but the location of the log file can be different.

Before configuring the logs in MariaDB, make sure that the log file is enabled that we are going to use. If you don’t know how to enable then follow the above sub-topics according to your operating system.

To configure Error log, the system variable log_error is used to configure this kind of log file. If we need to change the filename then run the command given below.

SET GLOBAL log_error = 'mariadbtips.err';

Or if we want to change the path of the log file used the below code.

SET GLOBAL log_error = 'path/filename.err';

Where,

• path: It is a destination where the file will be saved.
• file_name.err: It is the name of the file to which the error log is written.

To configure General query log the system variable general_log_file is used to configure this kind of log file. If we need to change the filename then run the command given below.

SET GLOBAL general_log_file = 'mariadbtips.log';

Or if we want to change the path of the log file used the below code.

SET GLOBAL general_log_file = 'path/filename.log';

Where,

• path: It is a destination where the file will be saved.
• file_name.log: It is the name of the file to which the error log is written.

To configure Slow Query Log the system variable slow_query_log_file is used to configure this kind of log file. If we need to change the filename then run the command given below.

SET GLOBAL slow_query_log_file = 'mariadbtips-slow.log';

Or if we want to change the path of the log file used the below code.

SET GLOBAL slow_query_log_file = 'path/filename-slow.log';

Where,

• path: It is a destination where the file will be saved.
• file_name-slow.log: It is the name of the file to which the error log is written.

Read: MariaDB Rename Column

MariaDB Logs docker

So, here in this section, we will look at the logs file of MariaDB running in Docker.

First, we will look at the Error log file in MariaDB which is enabled by default as we know, so login into the MariaDB prompt by clicking on the button CLI of container MariaDB in Docker and use the below code and check the filename and location of the log file.

mysql -u root -p

Check the filename and location of the log file using the system variable log_error. This system variable is also used to set the filename and path of the log file.

SHOW VARIABLES LIKE 'log_error';

From the output, we can see that the location and name of the error log file using the system variable log_error is null by default.

Configure the error log file by making changes to the MariaDB configuration file.

Now, we can open the log file hostname.log by going to that location using the below code.

sudo nano /var/log/mariadb/mariadb.log

The next log file that we will look at General Query Log , which records each query sent to the MariaDB server. Let’s enable the general query log in MariaDB using the system variable general_log by following the below steps.

Check the status of the general query log whether it ON or OFF but is disabled by default.

SHOW VARIABLES LIEK 'general_log';

Then enable the general query log using the below code, if it is not enabled.

SET GLOBAL general_log =1;

Again the status of the general query log file using the below code.

SHOW VARIABLES LIKE 'general_log';

Let’s view the filename and path of the general query log using the system variable general_log_file.

SHOW VARIABLES LIEK 'general_log_file';

From the output, we can see that the location of the general query log file using the system variable general_log_file is /var/lib/mysql/41d2cef2a4c0.log by default and the name of the log file is 41d2cef2a4c0.log.

We can open the file 41d2cef2a4c0.log by going to that location and seeing logs related to the general query log using the below code.

sudo nano /var/lib/mysql/41d2cef2a4c0.log

Next log is Slow Query Log the file that keeps a record of SQL queries that take a long time to execute.

Enable the slow query log of MariaDB in Docker by assigning value 1 to the system variable slow_query_log. Use the below code to enable the log file.

SET GLOBAL slow_query_log =1;

exit

After running the above code exit from the MariaDB session and log in again to check the status of the variable using the below code.

mysql -u root -p

SHOW VARIABLES LIKE 'slow_query_log`;

From the output, we have enabled the slow query log. We can change the filename and path of the slow query log file using the system variable slow_query_log_file. But first, check the default filename and path of the slow query log file using the below code.

SHOW VARIABLES LIKE 'slow_query_log_file`;

From the output, we can see that the location of the slow query log file using the system variable slow_query_log is /var/lib/mysql/41d2cef2a4c0-slow.log by default and the name of the log file is 41d2cef2a4c0-slow.log

Change the path and filename of the slow query log file using the below code.

SET GLOBAL slow_query_log_file = '/var/lib/mysql/41d2cef2a4c0-slow.log';

Check the changed path and filename using the below code.

SHOW VARIABLES LIKE 'slow_query_log_file';

We have successfully changed the filename and path of the slow query log file, now we can go to that location /var/lib/mysql/41d2cef2a4c0-slow.log and open the file to check that which query has taken a long time to execute.

Read: MariaDB Transaction

MariaDB Logs Linux

The steps to enable, configure and view the log files of MariaDB is the same for all Linux based systems, refer to above sub-section MariaDB Logs Ubuntu and MariaDB Logs CentOS depending on what Linux distribution you are using.

Read: MariaDB Rename Index

MariaDB Logs To Syslog

The Syslog is called System Logging Protocol that is used on different devices like Windows Servers, Linux Servers, Routers, etc. It keeps the log or event information on these devices and sends it to the Syslog Server which is also called a collector.

In MariaDB, by default the Error Log is redirected to Syslog, the redirection of the error log to Syslog depends upon the way the MariaDB server starts.

We can start the MariaDB server with two ways to redirect the error log to Syslog.

• Start with mysqld_safe.
• Start with systemd.

You may also like to read the following MariaDB tutorials.

• MariaDB Order By Clause
• MariaDB Drop Table
• MariaDB Union Operator
• MariaDB Select Statement
• MariaDB Set Variable
• MariaDB If Null + Examples
• MariaDB Variables Tutorial

So, in this tutorial, we have learned about the “MariaDB Logs” and covered the following topics.

• MariaDB Logs
• MariaDB Logs Windows
• MariaDB Logs Ubuntu
• MariaDB Logs Location
• MariaDB Logs CentOS
• MariaDB Logs Linux
• MariaDB Logs docker
• MariaDB Logs To Syslog
• MariaDB Logs Config