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
sudoaccess. - 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):
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:
You will be redirected to MariaDB command-line.
Now, you can view system variables related to the error log by executing command
show variables:
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:
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:
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:
The option -u with the name of the service MariaDB displays only records
related to this service:
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):
The file contains the following lines that hold configuration variable
log_error (by default commented out):
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:
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):
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):
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):
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):
You'll see the program's output appear on the screen:
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:
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:
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:
The output shows two variables:
general_log: The variable holds valueON(general log enable), orOFF(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:
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):
The file contains the following lines that hold configuration variables (by default commented out):
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:
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):
You'll see the program's output appear on the screen:
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):
The file contains the following lines that hold configuration variable log_bin
(by default commented out):
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:
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:
You will be redirected to MariaDB prompt.
Now, you can check the binary logs status by executing show binary logs:
The command will list the binary log files on the server:
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:
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:
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:
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):
You'll see the program's output appear on the screen:
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):
The file contains following lines that holds configuration variables (by default commented out):
The slow query log is configured by following three variables:
slow_query_log: The slow query logging is disabled (value0) or enabled (value1).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 value1.0refers 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:
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):
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:
You will be redirected to MariaDB prompt.
Let's check the slow query log status by executing command show variables:
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:
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:
You'll see the program's output appear on the screen:
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:
The select will wait 6 seconds and then return 0:
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:
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):
You'll see the program's output appear on the screen:
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.
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.