How To Start Logging With MariaDB

This tutorial shows you how to configure and view different MariaDB logs. MariaDB is an open-source relational database based on SQL (Structured Query Language). MariaDB offers various built-in logs. In general, a database is the basis of almost every backend, and administrators want to log this service.

MariaDB has four main logs with different purpose:

• Error log: Problems encountered during starting, running, or stopping the server. The database always generates records for this log, but the destination is configurable. It is useful when you want to analyze the server itself.
• General query logs: Records every connection established with each client. This log records every query that the client sent to the server. This log is useful to determine client problems. However, the log can grow large quite quickly.
• Binary logs: Record each event that manipulates data in a database. It records operations such as table creating, modification of schema, inserting new values, or querying tables. These logs are used to backup and recover the database.
• Slow query log: Record of each query, which execution took too much time. This log could be useful for the optimisation of slow SQL queries.

In this tutorial, you will do following actions:

• You will install the MariaDB server, configure and view default error log.
• You will connect to the MariaDB server, view metadata about general query logs, enable it and view these logs.
• You will enable the MariaDB binary logs and list them.
• You will enable and configure a slow query log, simulate some slow query and check this incident in the new log.

Prerequisites

You will need:

• Ubuntu 20.04 distribution including the non-root user with sudo access.
• The server shouldn't include the installed MySQL server because there is a problem with coexistence with the MariaDB configuration. You can learn how to completely remove MySQL from the machine in the following article.
• Basic knowledge of SQL languages (understanding of simple select query statement).

Step 1 — Viewing and Configuring Error Log

The MariaDB server is maintained by the command-line program mariadb. This program manages access to the MariaDB data directory that contains databases and tables. The problems encountered during mariadb starting, running, or stopping are recorded as an error log. This log doesn't include any information about SQL queries. It is useful for the analysis of the MariaDB server.

Installing MariaDB server

First of all, let's install the MariaDB server. Ubuntu 20.04 allows to install the MariaDB from default packages with the apt install (installation requires sudo privilege):

$ sudo apt update
$ sudo apt install mariadb-server

The first command will update Ubuntu repositories, and the second will download and install required packages for the MariaDB server. Now, the server is installed.

Connecting to Server and Viewing the Default Configuration

The error log is always enabled, but the destination of this log is configurable. Let's view the default configuration.

You can connect to MariaDB server as a root client:

$ sudo mariadb

You will be redirected to MariaDB command-line.

Now, you can view system variables related to the error log by executing command show variables:

MariaDB> show variables like '%log_error%';

The clause specifies a pattern that should match the variable. In our case, the pattern '%log_error%' specifies to show variables that contain the string log_error. You'll see the program's output appear on the screen:

Output
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.002 sec)

The output shows the global variable log_error. This variable holds the absolute path to file where are stored error log records. As you can see, it is by default set to null. As a result, the error log output goes to syslog.

You can disconnect from the server by executing the exit command:

MariaDB> exit;

You will be redirected back to the terminal.

Viewing Error Log With Journal

Now, you can view the syslog records related to the MariaDB service with journalctl:

$ journalctl -u mariadb.service

The option -u with the name of the service MariaDB displays only records related to this service:

Output
May 19 08:54:02 alice systemd[1]: Started MariaDB 10.5.10 database server.
May 19 08:54:02 alice /etc/mysql/debian-start[17467]: Upgrading MySQL tables if necessary.
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Looking for 'mysql' as: /usr/bin/mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 1/7: Checking and upgrading mysql database
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.column_stats                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.columns_priv                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.db                                           OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.event                                        OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.func                                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.global_priv                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.gtid_slave_pos                               OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_category                                OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_keyword                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_relation                                OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_topic                                   OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.index_stats                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.innodb_index_stats                           OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.innodb_table_stats                           OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.plugin                                       OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.proc                                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.procs_priv                                   OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.proxies_priv                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.roles_mapping                                OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.servers                                      OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.table_stats                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.tables_priv                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone                                    OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_leap_second                        OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_name                               OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_transition                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_transition_type                    OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.transaction_registry                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 2/7: Installing used storage engines... Skipped
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 3/7: Fixing views
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: information_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.user                                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: performance_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 4/7: Running 'mysql_fix_privilege_tables'
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 5/7: Fixing table and database names
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: information_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: performance_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 6/7: Checking and upgrading tables
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: information_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: performance_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 7/7: Running 'FLUSH PRIVILEGES'
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: OK

The output shows that the syslog stores records about the mariadb server initialisation, and running.

Configuring Custom Error Log File

If you don't want to include MariaDB error log into syslog, you can set up a custom log file. You can configure custom error log file by editing MariaDB configuration file /etc/mysql/mariadb.conf.d/50-server.cnf (sudo required):

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

The file contains the following lines that hold configuration variable log_error (by default commented out):

# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log

You can configure a custom error log file by uncommenting the line with the variable log_error, and set up the absolute path to the log file:

# Enable this if you want to have error logging into a separate file
log_error = /var/log/mariadb/error.log

In our example, we change the default log_error value to the directory /var/log/mariadb (the Linux default directory for logs). Now, you can save the file.

Now, let's create a subdirectory mariadb in the /var/log. You can create the subdirectory with the command mkdir (sudo required):

$ sudo mkdir /var/log/mariadb

Next, you must set proper access right to this directory for the MariaDB daemon. You can set up user and group owner of this subdirectory by command chown (sudo required):

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

The command chown set directory user to mysql and group to mysql, which is the proper access rights for the MariaDB daemon.

At last, if you want immediately apply the new configuration rules then you must restart the MariaDB server with systemctl (sudo required):

$ sudo systemctl restart mariadb.service

Now, the MariaDB server record error log to custom file.

Viewing Custom Error Log

You can view the new file with cat (sudo required because file is maintained by the system):

$ cat /var/log/mariadb/error.log

You'll see the program's output appear on the screen:

Output
2021-05-19 11:17:25 0 [Note] InnoDB: Uses event mutexes
2021-05-19 11:17:25 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-05-19 11:17:25 0 [Note] InnoDB: Number of pools: 1
2021-05-19 11:17:25 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-05-19 11:17:25 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2021-05-19 11:17:25 0 [Note] InnoDB: Using Linux native AIO
2021-05-19 11:17:25 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2021-05-19 11:17:25 0 [Note] InnoDB: Completed initialization of buffer pool
2021-05-19 11:17:25 0 [Note] InnoDB: 128 rollback segments are active.
2021-05-19 11:17:25 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-05-19 11:17:25 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-05-19 11:17:25 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-05-19 11:17:25 0 [Note] InnoDB: 10.5.10 started; log sequence number 45214; transaction id 20
2021-05-19 11:17:25 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-05-19 11:17:25 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-05-19 11:17:25 0 [Note] InnoDB: Buffer pool(s) load completed at 210519 11:17:25
2021-05-19 11:17:25 0 [Note] Server socket created on IP: '127.0.0.1'.
2021-05-19 11:17:25 0 [Note] Reading of all Master_info entries succeeded
2021-05-19 11:17:25 0 [Note] Added new Master_info '' to hash table
2021-05-19 11:17:25 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.5.10-MariaDB-1:10.5.10+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

The output shows records similar to syslog records.

Step 2 — Viewing General Query Logs

The server writes records about each client event during connection to the general query log. Basically, it keeps the information about all SQL statements that happened. This log is useful when the administrators want to know what clients exactly execute. However, keep in mind that the general query log slows down performance, and administrators usually turn it on only for a short time for debugging.

Connecting to Server and Checking General Query Log Status

First of all, let's check the status of the general query log because this logging feature is usually turned off.

You can connect to MariaDB server as a root client:

$ sudo mariadb

You will be redirected to MariaDB command-line.

Now, you can view system variables related to the general query log by executing command show variables:

MariaDB> show variables like '%general%';

The clause specifies a pattern that should match the variable. In our case, the pattern '%general%' specifies to show variables that contain the string general. You'll see the program's output appear on the screen:

Output
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| general_log      | OFF          |
| general_log_file | alice.log    |
+------------------+--------------+
2 rows in set (0.002 sec)

The output shows two variables:

• general_log: The variable holds value ON (general log enable), or OFF (general log disabled).
• general_log_file: The variable defines where is the log stored in the file system.

As you can see, the general query log is by default disabled. We can disconnect from the server by executing the exit command:

MariaDB> exit;

You will be redirected back to the terminal.

Enabling the General Query Log

You can enable general query log by editing MariaDB configuration file /etc/mysql/mariadb.conf.d/50-server.cnf (sudo required):

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

The file contains the following lines that hold configuration variables (by default commented out):

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

You can configure a general query log file by uncommenting the lines with these variables and set up the general_log to 1 (enabled) and general_log_file to the absolute path to the log file:

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
general_log_file       = /var/log/mariadb/general-query.log
general_log            = 1

In our example, we change the directory to the /var/log/mariadb, same as for the error log. This directory has already proper access right, as we set in the previous step.  Now, you can save the file.

Viewing General Query Log

Next, let's view the content of the new log with a cat (the sudo is required because this file is maintained by the system):

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

You'll see the program's output appear on the screen:

Output
/usr/sbin/mariadbd, Version: 10.5.10-MariaDB-1:10.5.10+maria~focal-log (mariadb.org binary distribution). started with:
Tcp port: 0  Unix socket: /run/mysqld/mysqld.sock
Time                Id Command  Argument
210519 11:58:23      3 Connect  root@localhost on mysql using Socket
                     3 Query    SET SQL_LOG_BIN=0, WSREP_ON=OFF
                     3 Query    show variables like 'datadir'
                     3 Quit
                     4 Connect  root@localhost on  using Socket
                     4 Query    SELECT count(*) FROM mysql.user WHERE user='root' and password='' and plugin in ('', 'mysql_native_password', 'mysql_old_password')
                     4 Quit
                     5 Connect  root@localhost on  using Socket
                     5 Query    select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`')
      from information_schema.TABLES where TABLE_SCHEMA<>'INFORMATION_SCHEMA' and TABLE_SCHEMA<>'PERFORMANCE_SCHEMA' and ( ENGINE='MyISAM' or ENGINE='Aria' )
                     5 Quit
                     6 Connect  root@localhost on  using Socket
                     6 Query    select count(*) into @discard from `mysql`.`procs_priv`
                     6 Quit
...

The output shows all statement executed at each session. You can see the timestamp of the session beginning and the list of SQL queries. There are already a lot of queries because the system executes them at the server starting (it retrieves the metadata about the environment).

Step 3 — Enabling and Listing Binary Logs

The binary log contains events that manipulate the database. If you want to recover the database, you need a backup and a binary log relevant to this backup. There are multiple binary logs because they are versioned.

Enabling Binary Logs

By default, the binary logs are disabled. You can enable binary logs by editing MariaDB configuration file /etc/mysql/mariadb.conf.d/50-server.cnf (sudo required):

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

The file contains the following lines that hold configuration variable log_bin (by default commented out):

#log_bin                = /var/log/mysql/mysql-bin.log

You can enable binary logging by uncommenting the line with this variable and set up the log_bin to the absolute path to the directory where you want to store them:

log_bin                = /var/log/mariadb/binary.log

In our example, we change the directory to the /var/log/mariadb, same as for the error and general query logs. Now, you can save the file.

Listing Binary Logs

At last, let's check the binary log configuration in the MariaDB server. Let's connect to the MariaDB server as a root client:

$ sudo mysql

You will be redirected to MariaDB prompt.

Now, you can check the binary logs status by executing show binary logs:

MariaDB> show binary logs;

The command will list the binary log files on the server:

Output
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binary.000001 |       325 |
+---------------+-----------+
1 row in set (0.000 sec)

The output shows all binary logs. We enable this service just a minute ago, soo there is just a single file. However, there will be more binary logs indexed with increasing suffix (for example, binary.000002, binary.000003) Now, we can find out where are these logs stored.

We can show logs location by executing command show variables:

MariaDB> show variables like '%log_bin%';

We already use this show clause in the previous step. This time, the clause shows variables that contain the string log_bin. You'll see the program's output appear on the screen:

Output
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /var/log/mariadb/binary       |
| log_bin_compress                | OFF                           |
| log_bin_compress_min_len        | 256                           |
| log_bin_index                   | /var/log/mariadb/binary.index |
| log_bin_trust_function_creators | OFF                           |
| sql_log_bin                     | ON                            |
+---------------------------------+-------------------------------+
7 rows in set (0.002 sec)

The output shows that the binary logs are stored in directory /var/log/mariadb, as we set up in the configuration file.

We can disconnect from the server by executing the exit command:

MariaDB> exit;

You will be redirected back to the terminal.

Viewing the Binary Log

The binary logs are not plain text files and you can not read them with the text editor. However, the MariaDB includes command-line utility mysqlbinlog.

You can view the binary log binary.000001 with this utility by executing the following command (sudo required because the file binary.000001 is maintained by the system):

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

You'll see the program's output appear on the screen:

Output
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210519 12:10:58 server id 1  end_log_pos 256 CRC32 0xb7f7dbc5  Start: binlog v 4, server v 10.5.10-MariaDB-1:10.5.10+maria~focal-log created 210519 12:10:58 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
MuSkYA8BAAAA/AAAAAABAAABAAQAMTAuNS4xMC1NYXJpYURCLTE6MTAuNS4xMCttYXJpYX5mb2Nh
bC1sb2cAAAAAAAAAAAAy5KRgEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHF2/e3
'/*!*/;
# at 256
#210519 12:10:58 server id 1  end_log_pos 285 CRC32 0x218f4e3e  Gtid list []
# at 285
#210519 12:10:58 server id 1  end_log_pos 325 CRC32 0x346ae813  Binlog checkpoint binary.000001
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

The output shows an encoded binary log. You can see the warning, which told us that the log is still in use. You can read about the format and meaning of each record in this log in the manual pages (man mysqlbinlog). The purpose of the binary log is to allow replication, as well as assisting in backup operations.

Step 4 — Configuring Slow Query Log

MariaDB allows to log queries, which took too much time. This mechanism is called a slow query log. Once again, it is a heavy offload to database performance, and you should use it just for a short time for database performance optimisation.

Enabling Slow Query Logging

By default, the slow query log is disabled. You can enable it by editing MariaDB configuration file /etc/mysql/mariadb.conf.d/50-server.cnf (sudo required):

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

The file contains following lines that holds configuration variables (by default commented out):

# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time        = 10

The slow query log is configured by following three variables:

• slow_query_log: The slow query logging is disabled (value 0) or enabled (value 1).
• slow_query_log_file: The absolute path to the slow query log. You can specify your own file.
• long_query_time: By default, the slow query logs record each SQL query that takes more than 10 seconds. You can change this minimal time interval to another value. The value can be specified as a floating-point number where the value 1.0 refers to 1 second.

You can enable a slow query log by inserting the new line into the configuration file with the variable slow_query_log with value 1 (the variable is not included by default). Also, you can set up your own values to the variables slow_query_log_file and long_query_time when you uncomment the lines with these variables:

# Enable the slow query log to see queries with especially long duration
slow_query_log         = 1
slow_query_log_file    = /var/log/mariadb/slow-query.log
long_query_time        = 5

In our example, we change the default long_query_time value to 5 seconds and set the log file to /var/log/mariadb/slow-query (same directory as for error, general and binary logs). Now, you can save the file.

Once again, if you want immediately apply the new configuration rules then you must restart the MariaDB server with systemctl (sudo required):

$ sudo systemctl restart mariadb.service

Now, the MariaDB server enables slow query log.

Checking Slow Query Log Status

You can check that the log is enabled if you login into the MariaDB server as a root client:

$ sudo mariadb

You will be redirected to MariaDB prompt.

Let's check the slow query log status by executing command show variables:

MariaDB> show variables like '%slow_query_log%';

Once again, we use the show clause. This time, the clause shows variables that contain the string slow_query_log. You'll see the program's output appear on the screen:

Output
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/log/mariadb/slow-query.log |
+---------------------+---------------------------------+
2 rows in set (0.002 sec)

The output shows that the slow query log is enabled (the variable slow_query_log holds the value ON).

Let's view actual slow query time interval by executing the command show variables:

MariaDB> show variables like '%long_query_time%';

You'll see the program's output appear on the screen:

Output
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time |  5.000000 |
+-----------------+-----------+
1 row in set (0.002 sec)

The output shows that the variable long_query_time holds the value 5 seconds (as we define in the configuration script).

Viewing Slow Query Log

At last, we can check that the MariaDB records slow queries to the new log. You can execute the following select query that takes 6 seconds:

MariaDB> select sleep(6);

The select will wait 6 seconds and then return 0:

Output
+----------+
| sleep(6) |
+----------+
|        0 |
+----------+
1 row in set (6.001 sec)

The output shows that this query takes 6 seconds. As a result, it should be recorded in a slow query log.

We can disconnect from the server by executing the exit command:

MariaDB> exit;

You will be redirected back to the terminal.

At last, we can print the content of the slow query log /var/log/mariadb/slow-query.log (the sudo is required because the file is maintained by the system):

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

You'll see the program's output appear on the screen:

/usr/sbin/mariadbd, Version: 10.5.10-MariaDB-1:10.5.10+maria~focal-log (mariadb.org binary distribution). started with:
Tcp port: 0  Unix socket: /run/mysqld/mysqld.sock
Time            Id Command  Argument
# Time: 210519 14:09:32
# User@Host: root[root] @ localhost []
# Thread_id: 31  Schema:   QC_hit: No
# Query_time: 6.000286  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 64
SET timestamp=1621426172;
select sleep(6);

You can see that the output shows record about execution query select sleep(6).

Conclusion

In this tutorial, you configured and viewed different MariaDB logs. You installed the MariaDB server, configured and viewed the error log. You enabled and viewed the general query logs and their configuration. You enabled and listed binary logs. At last, you enabled, configured and viewed a slow query log.

Article by

Jenda Tovarys

Jenda leads Growth at Better Stack. For the past 5 years, Jenda has been writing about exciting learnings from working with hundreds of developers across the world. When he's not spreading the word about the amazing software built at Better Stack, he enjoys traveling, hiking, reading, and playing tennis.

Got an article suggestion? Let us know

Next article

How To Start Logging With Redis

Learn how to start logging with Redis and go from basics to best practices in no time.

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.