Definition of MariaDB Logs
MariaDB server uses a different log file such as error log file, binary log file, general query log, and slow query log files. As per our requirement, we can change the status of log files that enable and disable log files. The main purpose of a log file is that to store different data during the different operation, for example, if we consider error log file, the error log file is used to store the critical error occurred during the MariaDB server operation, table corruption and start and stop information. If we consider SQL errors it can also be used in different log files by using the SQL_ERROR plugin.
Syntax:
set global log type = value;
Explanation:
In the above syntax, we use the set global command, here log type means log file name and value means 1 or 0 that we need to set.
show global variables like ‘log file name’;
Explanation:
In the above example, we use show global variable command to see the log file, here we use log file name means you can use any log file name to see the details of a specified log file.
How Logs works in MariaDB?
Now let’s see how logs work in MariaDB as follows. Basically, MariaDB uses different log files let’s see one by one.
1. Error Log
In this log file, it contains all records of critical errors that occurred during the different operations of the server like a corrupted table, which service start and stop information. SQL error is also used in a separate log file using the SQL_ERROR_LOG plugin.
Now let’s see how we can write error logs to a file.
When we need to configure an error log to write a log file at that time we must need to set the log_error system variable. By using a system variable we can configure a specific file name, if we don’t use specified file name to the configuration at that time log will be written to hostname.err file in the directory and this is by default directory.
We can set the log_error system variable by using the server option group. For example to write error log in by default log file that is ${hostname}.err. We need to configure the following line as follows.
[mariadb]
……
log_error
If we need to configure a specific file name as a log_error system variable and this file doesn’t have an absolute path then it uses a relative path to the datadir directory. Now see the following example of a configured error log that would be written to the mariadb.err file from datadir directory.
[mariadb]
……
log_error = mariadb.err
See in above example path is a relative path but some it also uses absolute path. For example:
[mariadb]
……
log_error = \C:\Program Files (x86)\MariaDB 10.5\include\mysql\mariadb.err.
We can also use another path to configure the error log file that is to set log – basename option which is configured by MariaDB.
2. General Query Log
The general query log file is used to store all details about every SQL query received from the client-side as well as all connected and disconnected client information.
Now see how we can enable the general query log as follows.
The general query log is to be disabled by default. To enable the general log file then we need to set the general_log system variable to 1 and it also changes dynamically.
For Example:
set global general_log = 1;
We can also set general_log file by using the server option group. For example:
[mariadb]
………..
general_log
Now see how we can configure the general query log file name as follows.
For configuration purposes, we use a default data-dir directory that is ${hostname}.log and a general_log_file system variable can be changed dynamically. For example:
set global general_log_file = ‘mariadb.log’;
We can also set the general_log_file by using the server option group from the file option. for example:
[mariadb]
……………..
general_log
general_log_file = mariadb.log
In the above example, we use a relative path but it also uses an absolute path.
Let’s see how we select the destination file for the general query log as follows.
It can be written to a file on disk or it can be written on the general_log table in the MySQL database. For selecting the destination file for output we use log_output system variable.
set global log_output = ‘specified file name’;
3. Slow Query Log
In this type of log file, the slow query log file is used to store records of SQL queries that take a long time to execute. In this type of query, the password then slow query log also contains the password.
Now let’s see how we can enable the slow query log as follows.
To enable the slow query log file we use the slow_query_log system variable that means we set the global variable is 1 as follows.
set global slow_query_log = 1
We can also set it by using the server option group from the file option.
Configuration of the slow query log by using the file name as follows.
We can configure the slow query log by using a filename that means we need to set a slow_query_log_file system variable by using the relative path as well as an absolute path, same like the above-mentioned types.
4. Binary Log
In this type, we stored all records of the database and structure as well as how each query takes a long time to execute.
Examples
Let’s see the different examples of MariaDB log as follows.
Suppose we need to show log error at that time we use the following statement.
show global variables like 'log_error';
Explanation:
In the above example, we use the show variable command to show log error. The final output of the show databases queries we illustrate by using the following snapshot.
Now how we can set the global general log by using the following statement.
set global general_log = 1;
Explanation:
The final output of the show databases queries we illustrate by using the following snapshot.
In this way, we can set all log types as per requirement.
Conclusion
We hope from this article you have understood about the MariaDB Logs. From this article, we have learned the basic syntax of MariaDB Logs and we also see different examples of MariaDB Logs. From this article, we learned how and when we use MariaDB Logs.
Recommended Articles
This is a guide to MariaDB Logs. Here we discuss the definition, How Logs works in MariaDB? and examples with code implementation. You may also have a look at the following articles to learn more –
