By default, logs such as the MySQL or MariaDB general log are usually disabled, and using them is not recommended except for debugging. They can store a lot of information depending on database usage.
For example, we can use MySQLTuner to analyze automatically and optimize MySQL more easily.
The error log is enabled by default because it does not require as many resources, and it is important to have the log to check whether the service has reported any errors.
There are several types of logs we can enable:
| Type | Log information |
|---|---|
| Error log | Problems encountered when starting, running, or stopping mysqld |
| General query log | Client connection establishment and statements received from clients. |
| Binary log | Statements that change data (also used for replication) |
| Relay log | Data changes received from a replication source server |
| Slow query log | Queries that took more than 'long_query_time' seconds to execute |
| DDL log (metadata log) | Metadata operations performed by DDL statements |
Enabling logs until the service restarts
To temporarily enable the general log until the service restarts, follow these steps (additional options are available).
Log in to MySQL:
# sudo mysql -u root
Once logged in, we can check the log variables using (optional):
show variables like '%log%';
We can check the location of the Error log using (optional):
show variables like 'log_error';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| log_error | /var/log/mysql/error.log |
+---------------+--------------------------+
1 row in set (0.001 sec)Before enabling the general log or another type of log, we must make sure it is configured to use a file rather than a table:
show variables like 'log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.002 sec)To change this value to a file, run the following command:
SET GLOBAL log_output = 'FILE';
Specify the file location:
SET GLOBAL general_log_file='/var/log/mysql/general.log';
To check the log status:
show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.002 sec)Finally, to enable the log, we must set its status to "ON", in this case for the general log:
SET GLOBAL general_log = 'ON';
To disable the log, simply turn it off (optional):
SET GLOBAL general_log = 'OFF';
Enabling logs permanently
Restarting the service would disable the configuration applied within the database using the previous method.
To make it permanent, we must set it in the service configuration file.
We only need to add the following to the my.cnf file in /etc/mysql/:
general-log=1
general-log-file=/var/log/mysql/general.log
Perform a reload for the changes to take effect:
# systemctl reload mysql.service
Important note:
Remember that it is important to rotate logs to avoid filling up disk space. In the case of HestiaCP, among others, this action is already configured, including for this particular log. You can check it at /etc/logrotate.d/mysql-server.
If you use a table for logging, schedule a truncation of the table.