在本章中,我们将介绍以下配方:
现在,您将了解如何管理不同类型的日志:错误日志、常规查询日志、慢速查询日志、二进制日志、中继日志和 DDL 日志。
根据 MySQL 文档:
错误日志包含mysqld
启动和关闭时间的记录。它还包含诊断消息,如服务器启动和关闭期间以及服务器运行时出现的错误、警告和注释。
错误日志子系统包括执行日志事件过滤和写入的组件,以及一个名为log_error_services
的系统变量,该变量配置哪些组件以实现所需的日志记录结果。global.log_error_services
的默认值为log_filter_internal; log_sink_internal
:
mysql> SELECT @@global.log_error_services;
+----------------------------------------+
| @@global.log_error_services |
+----------------------------------------+
| log_filter_internal; log_sink_internal |
+----------------------------------------+
该值表示日志事件首先通过内置过滤器组件log_filter_internal
,然后通过内置日志编写器组件log_sink_internal
。组件顺序很重要,因为服务器按列出的顺序执行组件。在log_error_services
值中命名的任何可加载(非内置)组件必须首先安装本节将描述的INSTALL COMPONENT
。
要了解所有类型的错误记录,请参阅https://dev.mysql.com/doc/refman/8.0/en/error-log.html
在某种程度上,错误日志很容易。让我们先看看如何配置错误日志。
错误记录由log_error
变量控制(--log-error
用于启动脚本)。
如果未给出--log-error
,则默认目的地为控制台。
如果给出的--log-error
没有命名文件,则默认目的地是data directory
中名为host_name.err
的文件。
如果--log-error
用于命名文件,则默认目标是位于data directory
下的文件(如果名称没有后缀,则添加.err
后缀),除非给出了绝对路径名以指定不同的位置。
log_error_verbosity
系统变量控制服务器将错误、警告和注释消息写入错误日志的详细程度。允许的log_error_verbosity
值为1
(仅限错误)、2
(错误和警告)和3
(错误、警告和注释),默认值为3
。
要更改错误日志位置,请编辑配置文件并重新启动 MySQL:
shell> sudo mkdir /var/log/mysql
shell> sudo chown -R mysql:mysql /var/log/mysql
shell> sudo vi /etc/my.cnf
[mysqld]
log-error=/var/log/mysql/mysqld.log
shell> sudo systemctl restart mysql
验证错误日志:
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| log_error | /var/log/mysql/mysqld.log |
+---------------+---------------------------+
1 row in set (0.00 sec)
要调整详细程度,可以动态更改log_error_verbosity
变量。但是,建议保留默认值3
,以便记录错误、警告和注释消息:
mysql> SET @@GLOBAL.log_error_verbosity=2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.log_error_verbosity;
+------------------------------+
| @@GLOBAL.log_error_verbosity |
+------------------------------+
| 2 |
+------------------------------+
1 row in set (0.00 sec)
假设错误日志文件变得更大,您希望旋转它;您只需移动文件并执行FLUSH LOGS
命令即可:
shell> sudo mv /var/log/mysql/mysqld.log /var/log/mysql/mysqld.log.0;
shell> mysqladmin -u root -p<password> flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
shell> ls -lhtr /var/log/mysql/mysqld.log
-rw-r-----. 1 mysql mysql 0 Oct 10 14:03 /var/log/mysql/mysqld.log
shell> ls -lhtr /var/log/mysql/mysqld.log.0
-rw-r-----. 1 mysql mysql 3.4K Oct 10 14:03 /var/log/mysql/mysqld.log.0
您可以使用一些脚本自动化前面的步骤,并将它们放入cron
。
如果服务器无法写入错误日志文件的位置,则日志刷新操作无法创建新的日志文件:
shell> sudo mv /var/log/mysqld.log /var/log/mysqld.log.0 && mysqladmin flush-logs -u root -p<password>
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: refresh failed; error: 'Unknown error'
要使用系统日志进行日志记录,需要加载名为log_sink_syseventlog
的系统日志编写器。您可以使用内置过滤器log_filter_internal
进行过滤:
mysql> INSTALL COMPONENT 'file://component_log_sink_syseventlog';
Query OK, 0 rows affected (0.43 sec)
mysql> SET PERSIST log_error_services = 'log_filter_internal; log_sink_syseventlog';
Query OK, 0 rows affected (0.00 sec)
mysql> 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)
/var/log/messages
登记手续;在 Ubuntu 上,您可以签入/var/log/syslog
。
为了演示,服务器重新启动。您可以在系统日志中看到这些日志:shell> sudo grep mysqld /var/log/messages | tail
Oct 10 14:50:31 centos7 mysqld[20953]: InnoDB: Buffer pool(s) dump completed at 171010 14:50:31
Oct 10 14:50:32 centos7 mysqld[20953]: InnoDB: Shutdown completed; log sequence number 350327631
Oct 10 14:50:32 centos7 mysqld[20953]: InnoDB: Removed temporary tablespace data file: "ibtmp1"
Oct 10 14:50:32 centos7 mysqld[20953]: Shutting down plugin 'MEMORY'
Oct 10 14:50:32 centos7 mysqld[20953]: Shutting down plugin 'CSV'
Oct 10 14:50:32 centos7 mysqld[20953]: Shutting down plugin 'sha256_password'
Oct 10 14:50:32 centos7 mysqld[20953]: Shutting down plugin 'mysql_native_password'
Oct 10 14:50:32 centos7 mysqld[20953]: Shutting down plugin 'binlog'
Oct 10 14:50:32 centos7 mysqld[20953]: /usr/sbin/mysqld: Shutdown complete
Oct 10 14:50:33 centos7 mysqld[21220]: /usr/sbin/mysqld: ready for connections. Version: '8.0.3-rc-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
如果您有多个mysqld
进程在运行,您可以使用[]
中指定的 PID 进行区分。否则,您可以设置log_syslog_tag
变量,该变量将服务器标识符附加一个前导连字符,从而生成一个标识符mysqld-tag_val
。
例如,您可以使用类似instance1
的内容标记实例:
mysql> SELECT @@GLOBAL.log_syslog_tag;
+-------------------------+
| @@GLOBAL.log_syslog_tag |
+-------------------------+
| |
+-------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.log_syslog_tag='instance1';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.log_syslog_tag;
+-------------------------+
| @@GLOBAL.log_syslog_tag |
+-------------------------+
| instance1 |
+-------------------------+
1 row in set (0.01 sec)
shell> sudo systemctl restart mysqld
shell> sudo grep mysqld /var/log/messages | tail
Oct 10 14:59:20 centos7 mysqld-instance1[21220]: InnoDB: Buffer pool(s) dump completed at 171010 14:59:20
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: InnoDB: Shutdown completed; log sequence number 350355306
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: InnoDB: Removed temporary tablespace data file: "ibtmp1"
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: Shutting down plugin 'MEMORY'
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: Shutting down plugin 'CSV'
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: Shutting down plugin 'sha256_password'
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: Shutting down plugin 'mysql_native_password'
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: Shutting down plugin 'binlog'
Oct 10 14:59:21 centos7 mysqld-instance1[21220]: /usr/sbin/mysqld: Shutdown complete
Oct 10 14:59:22 centos7 mysqld[21309]: /usr/sbin/mysqld: ready for connections. Version: '8.0.3-rc-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
您会注意到,instance1
标记被附加到日志中,以便您可以轻松地在多个实例之间进行识别。
如果要切换回原来的日志记录,可以将log_error_services
设置为'log_filter_internal; log_sink_internal'
:
mysql> SET @@global.log_error_services='log_filter_internal; log_sink_internal';
Query OK, 0 rows affected (0.00 sec)
要使用 JSON 格式进行日志记录,需要加载名为log_sink_json
的 JSON 日志编写器。您可以使用内置过滤器log_filter_internal
进行过滤:
mysql> INSTALL COMPONENT 'file://component_log_sink_json';
Query OK, 0 rows affected (0.05 sec)
mysql> SET PERSIST log_error_services = 'log_filter_internal; log_sink_json';
Query OK, 0 rows affected (0.00 sec)
log_error
系统变量给出的默认错误日志目的地确定其输出目的地:mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| log_error | /var/log/mysql/mysqld.log |
+---------------+---------------------------+
1 row in set (0.00 sec)
mysqld.log.00.json
。
重启后,JSON 日志文件如下:shell> sudo less /var/log/mysql/mysqld.log.00.json
{ "prio" : 2, "err_code" : 4356, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "sql_plugin.cc", "function" : "reap_plugins", "msg" : "Shutting down plugin 'sha256_password'", "time" : "2017-10-15T12:29:08.862969Z", "err_symbol" : "ER_PLUGIN_SHUTTING_DOWN_PLUGIN", "label" : "Note" }
{ "prio" : 2, "err_code" : 4356, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "sql_plugin.cc", "function" : "reap_plugins", "msg" : "Shutting down plugin 'mysql_native_password'", "time" : "2017-10-15T12:29:08.862975Z", "err_symbol" : "ER_PLUGIN_SHUTTING_DOWN_PLUGIN", "label" : "Note" }
{ "prio" : 2, "err_code" : 4356, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "sql_plugin.cc", "function" : "reap_plugins", "msg" : "Shutting down plugin 'binlog'", "time" : "2017-10-15T12:29:08.863758Z", "err_symbol" : "ER_PLUGIN_SHUTTING_DOWN_PLUGIN", "label" : "Note" }
{ "prio" : 2, "err_code" : 1079, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "mysqld.cc", "function" : "clean_up", "msg" : "/usr/sbin/mysqld: Shutdown complete\u000a", "time" : "2017-10-15T12:29:08.867077Z", "err_symbol" : "ER_SHUTDOWN_COMPLETE", "label" : "Note" }
{ "log_type" : 1, "prio" : 0, "err_code" : 1408, "msg" : "/usr/sbin/mysqld: ready for connections. Version: '8.0.3-rc-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)", "time" : "2017-10-15T12:29:10.952502Z", "err_symbol" : "ER_STARTUP", "SQL_state" : "HY000", "label" : "Note" }
如果您希望切换回原始日志记录,可以将log_error_services
设置为'log_filter_internal; log_sink_internal'
:
mysql> SET @@global.log_error_services='log_filter_internal; log_sink_internal';
Query OK, 0 rows affected (0.00 sec)
有关错误记录配置的更多信息,请参阅https://dev.mysql.com/doc/refman/8.0/en/error-log-component-configuration.html 。
有两种方法可以记录查询。一种是通过常规查询日志,另一种是通过慢速查询日志。在本节中,您将了解如何配置它们。
我们将在以下小节中详细介绍。
根据 MySQL 文档:
一般查询日志是mysqld
正在做什么的一般记录。当客户端连接或断开连接时,服务器将信息写入此日志,并记录从客户端接收的每个 SQL 语句。当您怀疑客户机中存在错误并希望确切了解客户机发送给mysqld
的内容时,常规查询日志非常有用:
hostname.log
的data directory
中创建。
服务器在data directory
中创建文件,除非给出绝对路径名以指定不同的目录:mysql> SET @@GLOBAL.general_log_file='/var/log/mysql/general_query_log';
Query OK, 0 rows affected (0.04 sec)
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
shell> sudo cat /var/log/mysql/general_query_log
/usr/sbin/mysqld, Version: 8.0.3-rc-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2017-10-11T04:21:00.118944Z 220 Connect root@localhost on using Socket
2017-10-11T04:21:00.119212Z 220 Query select @@version_comment limit 1
2017-10-11T04:21:03.217603Z 220 Query SELECT DATABASE()
2017-10-11T04:21:03.218275Z 220 Init DB employees
2017-10-11T04:21:03.219339Z 220 Query show databases
2017-10-11T04:21:03.220189Z 220 Query show tables
2017-10-11T04:21:03.227635Z 220 Field List current_dept_emp
2017-10-11T04:21:03.233820Z 220 Field List departments
2017-10-11T04:21:03.235937Z 220 Field List dept_emp
2017-10-11T04:21:03.236089Z 220 Field List dept_emp_latest_date
2017-10-11T04:21:03.236337Z 220 Field List dept_manager
2017-10-11T04:21:03.237291Z 220 Field List employee_names
2017-10-11T04:21:03.237999Z 220 Field List employees
2017-10-11T04:21:03.247921Z 220 Field List titles
2017-10-11T04:21:03.248217Z 220 Field List titles_only
~
~
2017-10-11T04:21:09.483117Z 220 Query select count(*) from employees
2017-10-11T04:21:10.523421Z 220 Quit
常规查询日志生成一个非常大的日志文件。在生产服务器上启用时要非常小心。它会极大地影响服务器的性能。
根据 MySQL 文档:
"The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined."
要记录所有查询,您可以将long_query_time
的值设置为0
。long_query_time
的默认值为10
秒,min_examined_row_limit
为0
。
默认情况下,不会记录不使用索引进行查找和管理语句的查询(例如ALTER TABLE
、ANALYZE TABLE
、CHECK TABLE
、CREATE INDEX
、DROP INDEX
、OPTIMIZE TABLE
和REPAIR TABLE
。可以使用log_slow_admin_statements
和log_queries_not_using_indexes
更改此行为。
要启用慢速查询日志,可以动态设置slow_query_log=1
并使用slow_query_log_file
设置文件名。要指定日志目的地,请使用--log-output
:
long_query_time
并根据您的要求进行调整:mysql> SELECT @@GLOBAL.LONG_QUERY_TIME;
+--------------------------+
| @@GLOBAL.LONG_QUERY_TIME |
+--------------------------+
| 10.000000 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.LONG_QUERY_TIME=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.LONG_QUERY_TIME;
+--------------------------+
| @@GLOBAL.LONG_QUERY_TIME |
+--------------------------+
| 1.000000 |
+--------------------------+
1 row in set (0.00 sec)
hostname-slow
日志一起出现在data directory
中:mysql> SELECT @@GLOBAL.slow_query_log_file;
+---------------------------------+
| @@GLOBAL.slow_query_log_file |
+---------------------------------+
| /var/lib/mysql/server1-slow.log |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.slow_query_log_file='/var/log/mysql/mysql_slow.log'; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.slow_query_log_file;
+-------------------------------+
| @@GLOBAL.slow_query_log_file |
+-------------------------------+
| /var/log/mysql/mysql_slow.log |
+-------------------------------+
1 row in set (0.00 sec)
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT @@GLOBAL.slow_query_log;
+-------------------------+
| @@GLOBAL.slow_query_log |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@GLOBAL.slow_query_log;
+-------------------------+
| @@GLOBAL.slow_query_log |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
shell> sudo less /var/log/mysql/mysql_slow.log
/usr/sbin/mysqld, Version: 8.0.3-rc-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2017-10-15T12:43:55.038601Z
# User@Host: root[root] @ localhost [] Id: 7
# Query_time: 2.000845 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1508071435;
SELECT SLEEP(2);
您可以通过指定log_output
变量,将查询记录到 MySQL 本身的FILE
或TABLE
,该变量可以是FILE
或TABLE
,也可以同时是FILE
和TABLE
。
如果将log_output
指定为FILE
,则一般查询日志和慢速查询日志将分别写入general_log_file
和slow_query_log_file
指定的文件中。
如果将log_output
指定为TABLE
,则一般查询日志和慢速查询日志将分别写入mysql.general_log
和mysql.slow_log
表中。可以通过 SQL 语句访问日志内容。
例如:
mysql> SET @@GLOBAL.log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@GLOBAL.general_log='ON';
Query OK, 0 rows affected (0.02 sec)
执行几个查询,然后查询mysql.general_log
表:
mysql> SELECT * FROM mysql.general_log WHERE command_type='Query' \G
~
~
*************************** 3\. row ***************************
event_time: 2017-10-25 10:56:56.416746
user_host: root[root] @ localhost []
thread_id: 2421
server_id: 32
command_type: Query
argument: show databases
*************************** 4\. row ***************************
event_time: 2017-10-25 10:56:56.418896
user_host: root[root] @ localhost []
thread_id: 2421
server_id: 32
command_type: Query
argument: show tables
*************************** 5\. row ***************************
event_time: 2017-10-25 10:57:08.207964
user_host: root[root] @ localhost []
thread_id: 2421
server_id: 32
command_type: Query
argument: select * from salaries limit 1
*************************** 6\. row ***************************
event_time: 2017-10-25 10:57:47.041475
user_host: root[root] @ localhost []
thread_id: 2421
server_id: 32
command_type: Query
argument: SELECT * FROM mysql.general_log WHERE command_type='Query'
您可以用类似的方式使用slow_log
表:
mysql> SET @@GLOBAL.slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@GLOBAL.long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
mysql> SELECT * FROM mysql.slow_log \G
*************************** 1\. row ***************************
start_time: 2017-10-25 11:01:44.817421
user_host: root[root] @ localhost []
query_time: 00:00:02.000530
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db: employees
last_insert_id: 0
insert_id: 0
server_id: 32
sql_text: SELECT SLEEP(2)
thread_id: 2421
1 row in set (0.00 sec)
如果慢速查询日志表变得巨大,您可以通过创建一个新表并交换它来旋转它:
mysql.general_log_new
:mysql> DROP TABLE IF EXISTS mysql.general_log_new;
Query OK, 0 rows affected, 1 warning (0.19 sec)
mysql> CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
Query OK, 0 rows affected (0.10 sec)
RENAME TABLE
命令交换表格:mysql> RENAME TABLE mysql.general_log TO mysql.general_log_1, mysql.general_log_new TO mysql.general_log;
Query OK, 0 rows affected (0.00 sec)
本节将介绍如何在复制环境中管理二进制日志。基本二进制日志处理已在第 6 章、二进制日志中介绍,使用PURGE BINARY LOGS
命令和expire_logs_days
变量。
在复制环境中使用这些方法是不安全的,因为如果任何一个从属服务器未使用二进制日志,而您已将其删除,则从属服务器将失去同步,您将需要重建它。
删除二进制日志的安全方法是检查在每个从机上读取了哪些二进制日志并将其删除。您可以使用mysqlbinlogpurge
实用程序来实现这一点。
在任何服务器上执行mysqlbinlogpurge
脚本,并指定主主机和从主机。该脚本连接到所有从属服务器,并查找应用的最新二进制日志。然后它清除主二进制日志,直到该点。您需要超级用户才能连接到所有从属设备:
mysqlbinlogpurge
脚本:shell> mysqlbinlogpurge --master=dbadmin:<pass>@master:3306 --slaves=dbadmin:<pass>@slave1:3306,dbadmin:<pass>@slave2:3306
mysql> SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master-bin.000001 | 177 |
~
| master-bin.000018 | 47785 |
| master-bin.000019 | 203 |
| master-bin.000020 | 203 |
| master-bin.000021 | 177 |
| master-bin.000022 | 203 |
| master-bin.000023 | 57739432 |
+--------------------+-----------+
23 rows in set (0.00 sec)
shell> mysqlbinlogpurge --master=dbadmin:<pass>@master:3306 --slaves=dbadmin:<pass>@slave1:3306,dbadmin:<pass>@slave2:3306
# Latest binlog file replicated by all slaves: master-bin.000022
# Purging binary logs prior to 'master-bin.000023'
report_host
和report_port
并重新启动 MySQL 服务器。在每个从机上:shell> sudo vi /etc/my.cnf
[mysqld]
report-host = slave1
report-port = 3306
shell> sudo systemctl restart mysql
mysql> SHOW VARIABLES LIKE 'report%';
+-----------------+---------------+
| Variable_name | Value |
+-----------------+---------------+
| report_host | slave1 |
| report_password | |
| report_port | 3306 |
| report_user | |
+-----------------+---------------+
4 rows in set (0.00 sec)
discover-slaves-login
选项执行mysqlbinlogpurge
:mysql> SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| centos7-bin.000025 | 203 |
| centos7-bin.000026 | 203 |
| centos7-bin.000027 | 203 |
| centos7-bin.000028 | 154 |
+--------------------+-----------+
4 rows in set (0.00 sec)
shell> mysqlbinlogpurge --master=dbadmin:<pass>@master --discover-slaves-login=dbadmin:<pass>
# Discovering slaves for master at master:3306
# Discovering slave at slave1:3306
# Found slave: slave1:3306
# Discovering slave at slave2:3306
# Found slave: slave2:3306
# Latest binlog file replicated by all slaves: master-bin.000027
# Purging binary logs prior to 'master-bin.000028'