Managing the error log
The error log contains a record of mysqld startup and shutdown times. It also contains diagnostic messages such as errors, warnings, and notes that occur during server startup and shutdown, and while the server is running.
Configuring the error log
The error logging is controlled by the log_error variable (--log-error for a startup script).
If --log-error is not given, the default destination is the console. If --log-error is given without naming a file, the default destination is a file named host_name.err in the data directory
The log_error_verbosity system variable controls server verbosity for writing error, warning, and note messages to the error log. Permitted log_error_verbosity values are 1 (errors only), 2 (errors and warnings), and 3 (errors, warnings, and notes), with a default of 3.
To change the error log location, edit the configuration file and restart MySQL:
sudo mkdir /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql
sudo vim /etc/my.cnf
[mysqld]
log-error=/var/log/mysql/mysqld.log
sudo systemctl restart mysql
Verify the error log:
SHOW VARIABLES LIKE 'log_error';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| log_error | /var/log/mysql/error.log |
+---------------+--------------------------+
1 row in set (0.03 sec)
To adjust the verbosity, you can change the log_error_verbosity variable dynamically. However, it is recommended to keep the default value of 3 so that error, warning, and note messages are logged:
SET @@GLOBAL.log_error_verbosity=2;
SELECT @@GLOBAL.log_error_verbosity;
+------------------------------+
| @@GLOBAL.log_error_verbosity |
+------------------------------+
| 2 |
+------------------------------+
1 row in set (0.00 sec)
Rotating the error log
Suppose the error log file has become bigger and you want to rotate it; you can simply move the file and execute the FLUSH LOGS command:
mysqladmin -u root -p flush-logs
Using the system log for logging
To use the system log for logging, you need to load the system log writer called log_sink_syseventlog. You can use the built-in filter, log_filter_internal, for filtering:
Load the system log writer:
INSTALL COMPONENT 'file://component_log_sink_syseventlog';
Make it persistent across restarts:
SHOW VARIABLES LIKE 'log_error_services';
+--------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------+-------------------------------------------+
| log_error_services | log_filter_internal; log_sink_syseventlog |
+--------------------+-------------------------------------------+
1 row in set (0.00 sec)
You can verify that the logs will be directed to the syslog. On Ubuntu, you can check in /var/log/syslog. For the sake of a demo, the server was restarted. You can see those logs in the syslog:
sudo systemctl restart mysql
sudo grep mysqld /var/log/syslog | tail
SELECT @@GLOBAL.log_syslog_tag;
+-------------------------+
| @@GLOBAL.log_syslog_tag |
+-------------------------+
| |
+-------------------------+
1 row in set (0.00 sec)
SET @@GLOBAL.log_syslog_tag='instance1';
SELECT @@GLOBAL.log_syslog_tag;
+-------------------------+
| @@GLOBAL.log_syslog_tag |
+-------------------------+
| instance1 |
+-------------------------+
1 row in set (0.00 sec)
sudo grep mysqld /var/log/syslog | tail
Sep 9 17:35:07 mysq-4gb-2vcpu-00 kernel: [ 889.007634] audit: type=1400 audit(1536514507.079:15): apparmor="STATUS" operation="profile_load" profile="unconfined" name="/usr/sbin/mysqld" pid=5404 comm="apparmor_parser"
Sep 9 17:35:07 mysq-4gb-2vcpu-00 kernel: [ 889.587388] audit: type=1400 audit(1536514507.655:16): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=5534 comm="apparmor_parser"
Sep 10 05:11:30 mysq-4gb-2vcpu-00 kernel: [42672.684283] audit: type=1400 audit(1536556290.734:17): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=6983 comm="apparmor_parser"
Sep 10 05:11:30 mysq-4gb-2vcpu-00 kernel: [42672.710815] audit: type=1400 audit(1536556290.758:18): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/etc/my.cnf" pid=6985 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Sep 10 05:24:50 mysq-4gb-2vcpu-00 mysqld-instance1[6985]: /usr/sbin/mysqld: Forcing close of thread 8 user: 'root'.
Sep 10 05:24:52 mysq-4gb-2vcpu-00 mysqld-instance1[6985]: /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.12) MySQL Community Server - GPL.
Sep 10 05:24:52 mysq-4gb-2vcpu-00 kernel: [43474.544469] audit: type=1400 audit(1536557092.597:19): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=7106 comm="apparmor_parser"
Sep 10 05:24:52 mysq-4gb-2vcpu-00 kernel: [43474.565785] audit: type=1400 audit(1536557092.621:20): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/etc/my.cnf" pid=7108 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Sep 10 05:24:53 mysq-4gb-2vcpu-00 mysqld[7108]: /usr/sbin/mysqld: ready for connections. Version: '8.0.12' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
You will notice that the instance1 tag is appended to the log so that you can easily identify between multiple instances.
If you wish to switch back to the original logging, you can set log_error_services to 'log_filter_internal; log_sink_internal':
Error logging in JSON format
Install the JSON log writer:
INSTALL COMPONENT 'file://component_log_sink_json';
Make it persistent across restarts:
SET PERSIST log_error_services = 'log_filter_internal; log_sink_json';
The JSON log writer determines its output destination based on the default error log destination, which is given by the log_error system variable:
SHOW VARIABLES LIKE 'log_error';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| log_error | /var/log/mysql/error.log |
+---------------+--------------------------+
1 row in set (0.00 sec)
After a restart, the JSON log file looks like this:
sudo systemctl restart mysql
sudo less /var/log/mysql/error.log.00.json
{ "prio" : 2, "err_code" : 10909, "subsystem" : "Server", "source_file" : "mysqld.cc", "function" : "operator()", "msg" : "/usr/sbin/mysqld: Forcing close of thread 8 user: 'root'.", "time" : "2018-09-10T05:28:26.820906Z", "err_symbol" : "ER_FORCE_CLOSE_THREAD", "SQL_state" : "HY000", "label" : "Warning" }
{ "prio" : 0, "err_code" : 10910, "subsystem" : "Server", "source_file" : "mysqld.cc", "function" : "clean_up", "msg" : "/usr/sbin/mysqld: Shutdown complete (mysqld 8.0.12) MySQL Community Server - GPL.", "time" : "2018-09-10T05:28:28.253589Z", "err_symbol" : "ER_SERVER_SHUTDOWN_COMPLETE", "SQL_state" : "HY000", "label" : "System" }
{ "log_type" : 1, "prio" : 0, "err_code" : 10931, "msg" : "/usr/sbin/mysqld: ready for connections. Version: '8.0.12' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.", "time" : "2018-09-10T05:28:29.179939Z", "err_symbol" : "ER_SERVER_STARTUP_MSG", "SQL_state" : "HY000", "subsystem" : "Server", "label" : "System" }