MySQL日志及分类
MySQL有不同类型的日志文件,各自存储了不同类型的日志。分析这些日志文件,除了可以了解MySQL数据库的运行情况,还可以为MySQL的管理和优化提供必要的信息。
日志管理是维护数据库的重要步骤,所以经常需要在MySQL中进行日志启动、查看、停止和删除等操作。这些操作是数据库管理中最基本、最重要的操作。
日志是数据库的重要组成部分,主要用来记录数据库的运行情况、日常操作和错误信息。
在MySQL中,日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。对于MySQL的管理工作而言,这些日志文件是不可缺少的。分析这些日志,可以帮助我们了解MySQL数据库的运行情况、日常操作、错误信息和哪些地方需要进行优化。
* MySQL中4种日志文件的作用:
二进制日志:该日志文件会以二进制的形式记录数据库的各种操作,但不记录查询语句。
错误日志:该日志文件会记录MySQL服务器的启动、关闭和运行错误等信息。
通用查询日志:该日志记录MySQL服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的SQL语句等。
慢查询日志:记录执行事件超过指定时间的操作,通过工具分析慢查询日志可以定位MySQL服务器性能瓶颈所在。
为了维护 MySQL 数据库,经常需要在 MySQL 中进行日志操作,包含日志文件的启动、查看、停止和删除等,这些操作都是数据库管理中最基本、最重要的操作。
例如,当用户root登录到MySQL服务器后,就会在日志文件里记录该用户的登录事件、执行操作等信息。当MySQL服务器运行时出错,出错信息就会被记录到日志文件里。
日志操作是数据库维护中最重要的手段之一。如果MySQL数据库系统意外停止服务,我们可以通过错误日志查看出现错误的原因。还可以通过二进制日志文件来查看用户分别执行了哪些操作、对数据库文件做了哪些修改。然后,还可以根据二进制日志中的记录来修复数据库。
在MySQL所支持的日志文件里,除了二进制日志文件外,其它日志文件都是文本文件。默认情况下,MySQL只会启动错误日志文件,而其它日志则需要手动启动。
使用日志有优点也有缺点。启动日志后,虽然可以对MySQL服务器性能进行维护,但是会降低MySQL的执行速度。例如,一个查询操作比较频繁的MySQL中,记录通用查询日志和慢查询日志要花费很多的时间。
日志文件还会占用大量的硬盘空间。对于用户量非常大、操作非常频繁的数据库,日志文件需要的存储空间甚至比数据库文件需要的存储空间还要大。因此,是否启动日志,启动什么类型的日志要根据具体的应用来决定。
MySQL错误日志(Error Log)
错误日志(Error Log)是MySQL中最常用的一种日志,主要记录MySQL服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。
启动和设置错误日志
在MySQL数据库中,默认开启错误日志功能。一般情况下,错误日志存储在MySQL数据库的数据文件夹下,通常名称为 hostname.err。其中,hostname表示MySQL服务器的主机名。
在MySQL配置文件中,错误日志所记录的信息可以通过log-error和log-warnings来定义,其中,log-err定义是否启用错误日志功能和错误日志的存储位置,log-warnings定义是否将警告信息也记录到错误日志中。
将log_error选项加入到MySQL配置文件的[mysqld]组中:
[mysqld]
log-error=dir/{filename}
其中,dir参数指定错误日志的存储路径;filename参数指定错误日志的文件名;省略参数时文件名默认为主机名,存放在Data目录中。
重启MySQL服务后,参数开始生效,可以在指定路径下看到filename.err的文件,如果没有指定filename,那么错误日志将直接默认为hostname.err。
* 错误日志中记录的并非全是错误信息,例如MySQL如何启动InnoDB的表空间文件、如何初始化自己的存储引擎等,这些也记录在错误日志文件中。
查看错误日志
错误日志中记录着开启和关闭MySQL服务的时间,以及服务运行过程中出现哪些异常等信息。如果MySQL服务出现异常,可以到错误日志中查找原因。
# 通过SHOW命令可以查看错误日志文件所在的目录及文件名信息
MySQL [mysql_chuid]> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------+
| Variable_name | Value |
+---------------+----------------------------+
| log_error | /usr/include/mysql/var.err |
+---------------+----------------------------+
1 row in set (0.11 sec)
错误日志以文本文件的形式存储,直接使用普通文本工具就可以查看。这里通过记事本打开,从上面可以知道错误日志的文件名。
[root@master ~]# cat /usr/include/mysql/var.err
200914 15:59:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
2020-09-14 15:59:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-14 15:59:04 68523 [Note] Plugin 'FEDERATED' is disabled.
2020-09-14 15:59:04 68523 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-09-14 15:59:04 68523 [Note] InnoDB: The InnoDB memory heap is disabled
2020-09-14 15:59:04 68523 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-09-14 15:59:04 68523 [Note] InnoDB: Compressed tables use zlib 1.2.3
2020-09-14 15:59:04 68523 [Note] InnoDB: Using Linux native AIO
2020-09-14 15:59:04 68523 [Note] InnoDB: Using CPU crc32 instructions
2020-09-14 15:59:04 68523 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2020-09-14 15:59:04 68523 [Note] InnoDB: Completed initialization of buffer pool
2020-09-14 15:59:04 68523 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2020-09-14 15:59:04 68523 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
删除错误日志
在MySQL中,可以使用mysqladmin命令来开启新的错误日志,以保证MySQL服务器上的硬盘空间。
mysqladmin命令的语法格式:mysqladmin -uroot -p flush-logs
执行该命令后,MySQL服务器首先会自动创建一个新的错误日志,然后将旧的错误日志更名为filename.err-old。
MySQL服务器发生异常时,管理员可以在错误日志中找到发生异常的时间、原因,然后根据这些信息来解决异常。对于很久之前的错误日志,查看的可能性不大,可以直接将这些错误日志删除。
MySQL二进制日志(Binary Log)
二进制日志(Binary Log)也可叫作变更日志(Update Log),是MySQL中非常重要的日志。主要用于记录数据库的变化情况,即SQL语句的DDL和DML语句,不包含数据记录查询操作。
如果MySQL数据库意外停止,可以通过二进制日志文件来查看用户执行了哪些操作,对数据库服务器文件做了哪些修改,然后根据二进制日志文件中的记录来恢复数据库服务器。
默认情况下,二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启
MySQL [mysql_chuid]> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
启动和设置二进制日志
在MySQL中,可以通过在配置文件中添加log-bin选项来开启二进制日志
[mysqld]
log-bin=dir/[filename]
其中,dir参数指定二进制文件的存储路径;filename参数指定二进制文件的文件名,其形式为filename.number,number的形式为000001、000002等。
* 每次重启MySQL服务后,都会生成一个新的二进制日志文件,这些日志文件的文件名中filename部分不会改变,number会不断递增。
如果没有dir和filename参数,二进制日志将默认存储在数据库的数据目录下,默认的文件名为hostname-bin.number,其中hostname表示主机名。
在my.ini文件的[mysqld]组中添加语句:log-bin
查看二进制日志
1)查看二进制日志文件列表
MySQL [mysql_chuid]> SHOW binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 408 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 38462 |
+------------------+-----------+
5 rows in set (0.05 sec)
2)查看当前正在写入的二进制日志文件
MySQL [mysql_chuid]> SHOW master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 38462 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3)查看二进制日志文件内容
二进制日志使用二进制格式存储,不能直接打开查看。如果需要查看二进制日志,必须使用mysqlbinlog命令。
mysqlbinlog命令的语法格式:mysqlbinlog filename.number
mysqlbinlog命令只在当前文件夹下查找指定的二进制日志,因此需要在二进制日志所在的目录下运行该命令,否则将会找不到指定的二进制日志文件。
# 使用mysqlbinlog命令查看mysql-bin.000001文件
[root@master ~]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mysql-bin.000001' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
除了filename.number文件,MySQL还会生成一个名为filename.index的文件,这个文件存储着所有二进制日志文件的列表,可以用记事本打开该文件。
* 实际工作中,二进制日志文件与数据库的数据文件不放在同一块硬盘上,这样即使数据文件所在的硬盘被破坏,也可以使用另一块硬盘上的二进制日志来恢复数据库文件。两块硬盘同时坏了的可能性要小得多,这样可以保证数据库中数据的安全。
删除二进制日志
二进制日志中记录着大量的信息,如果很长时间不清理二进制日志,将会浪费很多的磁盘空间。
1)删除所有二进制日志
使用RESET MASTER语句可以删除的所有二进制日志
语句格式:RESET MASTER;
登录MySQL数据库后,执行该语句来删除所有二进制日志。删除所有二进制日志后,MySQL将会重新创建新的二进制日志,新二进制日志的编号从000001开始。
2)根据编号删除二进制日志
每个二进制日志文件后面有一个6位数的编号,如000001。使用PURGE MASTER LOGS TO语句,可以删除指定二进制日志的编号之前的日志。
语句格式:PURGE MASTER LOGS TO 'filename.number';
该语句将删除编号小于 filename.number 的所有二进制日志
MySQL [mysql_chuid]> PURGE MASTER LOGS TO 'mysql-bin.000004'; # 删除mysql-bin.000004之前的二进制日志
Query OK, 0 rows affected (0.06 sec)
MySQL [mysql_chuid]> SHOW binary logs; # 二进制日志mysql-bin.000001、000002、000003都被删除了
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 38462 |
+------------------+-----------+
2 rows in set (0.00 sec)
3)根据创建时间删除二进制日志
使用PURGE MASTER LOGS TO语句,可以删除指定时间之前创建的二进制日志
语法格式:PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';
其中,“hh”为24制的小时。该语句将删除在指定时间之前创建的所有二进制日志
MySQL [mysql_chuid]> PURGE MASTER LOGS TO '2020-11-30 10:16:00'; # 删除2020年11月30日10:16:00之前创建的所有二进制日志
暂时停止二进制日志
在配置文件中设置了log_bin选项之后,MySQL服务器将会一直开启二进制日志功能。删除该选项后就可以停止二进制日志功能,如果需要再次启动这个功能,需要重新添加log_bin选项。由于这样比较麻烦,所以MySQL提供了暂时停止二进制日志功能的语句。
如果用户不希望自己执行的某些SQL语句记录在二进制日志中,可以在执行这些SQL语句之前暂停二进制日志功能。
使用 SET 语句来暂停/开启二进制日志功能:
SET SQL_LOG_BIN=0/1;
其中,0 表示暂停二进制日志功能,1 表示开启二进制功能。
* my.ini中的[mysqld]组下面有几个设置参数是关于二进制日志的:
* expire_logs_days = 10
* max_binlog_size = 100M
* expire_logs_day定义了MySQL清除过期日志的时间、二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。当MySQL启动或刷新二进制日志时可能删除。
* max_binlog_size定义了单个文件的大小限制,如果二进制日志写入的内容大小超出给定值,日志就会发生滚动(关闭当前文件,重新打开一个新的日志文件)。不能将该变量设置为大于1GB或小于4096B(字节),其默认值是1GB。
MySQL使用二进制日志还原数据库
二进制日志中记录了用户对数据库更改的所有操作,如 INSERT语句、UPDATE语句、CREATE语句等。如果数据库因为操作不当或其它原因丢失了数据,可以通过二进制日志来查看在一定时间段内用户的操作,结合数据库备份来还原数据库。
数据库遭到意外损坏时,应该先使用最近的备份文件来还原数据库。另外备份之后,数据库可能进行了一些更新,这时可以使用二进制日志来还原。因为二进制日志中存储了更新数据库的语句,如 UPDATE语句、INSERT语句等。
二进制日志还原数据库的语法格式:
mysqlbinlog filename.number | mysql -u root -p
* 可以理解成,先使用 mysqlbinlog命令来读取 filename.number中的内容,再使用mysql命令将这些内容还原到数据库中。
二进制日志虽然可以用来还原MySQL数据库,但是其占用的磁盘空间也是非常大的。因此,在备份MySQL数据库之后,应该删除备份之前的二进制日志。如果备份之后发生异常,造成数据库的数据损失,可以通过备份之后的二进制日志进行还原。
使用mysqlbinlog命令进行还原操作时,必须是编号(number)小的先还原。例如,mylog.000001必须在mylog.000002之前还原。
[root@master ~]# mysqlbinlog mysql-bin.000001 | mysql -uroot -p
[root@master ~]# mysqlbinlog mysql-bin.000002 | mysql -uroot -p
MySQL通用查询日志(General Query Log)
通用查询日志(General Query Log)用来记录用户的所有操作,包括启动和关闭MySQL服务、更新语句和查询语句等。
默认情况下,通用查询日志功能是关闭的。可以通过以下命令查看通用查询日志是否开启
# 可以看到通用查询日志是关闭的,general_log_file 变量指定了通用查询日志文件所在的位置
MySQL [mysql_chuid]> SHOW VARIABLES LIKE '%general%'; # 查看general数据的存放路径
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/mysql/var/master.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)
MySQL [mysql_chuid]> SET GLOBAL general_log=on; # 开启通用查询日志的功能
Query OK, 0 rows affected (0.11 sec)
MySQL [mysql_chuid]> SHOW VARIABLES LIKE '%general%'; # 查看通用查询日志的功能是开启状态
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | ON |
| general_log_file | /usr/local/mysql/var/master.log |
+------------------+---------------------------------+
2 rows in set (0.08 sec)
MySQL [mysql_chuid]> use mysql # 切换到mysql表
MySQL [mysql]> show tables; # 查看所有表
MySQL [mysql]> use mysql_chuid # 切换到mysql_chuid表
Database changed
MySQL [mysql_chuid]> select * from students; # 查看students表中的所有数据
# 执行完上面操作后,打开通用查询日志,可以看到该日志非常清晰的记录了客户端的所有行为
[root@master ~]# cat /usr/local/mysql/var/master.log
/usr/local/mysql/bin/mysqld, Version: 5.6.19-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
210317 12:27:34 1 Query SHOW VARIABLES LIKE '%general%'
210317 14:42:14 1 Query SELECT DATABASE()
210317 14:42:42 1 Query show tables
210317 14:43:04 1 Query SELECT DATABASE()
1 Init DB mysql_chuid
1 Query show databases
1 Query show tables
210317 14:43:19 1 Query select * from students
210317 14:43:45 1 Quit
停止通用查询日志
通用查询日志启动后,可以通过两种方法停止该日志。一种是将MySQL配置文件中的相关配置注释掉,然后重启服务器,来停止通用查询日志。
[mysqld]
#log=dir\filename
MySQL [mysql_chuid]> SET GLOBAL general_log=off;
Query OK, 0 rows affected (0.11 sec)
MySQL [mysql_chuid]> SHOW VARIABLES LIKE '%general_log%' \G
*************************** 1. row ***************************
Variable_name: general_log
Value: OFF
*************************** 2. row ***************************
Variable_name: general_log_file
Value: /usr/local/mysql/var/master.log
2 rows in set (0.00 sec)
删除通用查询日志
使用mysqladmin命令来开启新的通用查询日志。新的通用查询日志会直接覆盖旧的查询日志,不需要再手动删除了。
mysqladmin命令的语法格式:mysqladmin -uroot -p flush-logs
* 如果希望备份旧的通用查询日志,必须先将旧的日志文件拷贝出来或者改名。然后再执行mysqladmin命令。
还可以手工删除通用查询日志。删除之后需要重新启动MySQL服务。重启之后就会生成新的通用查询日志。如果希望备份旧的日志文件,可以将旧的日志文件改名,然后重启MySQL服务。
由于通用查询日志会记录用户的所有操作,如果数据库的使用非常频繁,通用查询日志将会占用非常大的磁盘空间,对系统性能影响较大。一般情况下,数据管理员可以删除很长时间之前的通用查询日志或关闭此日志,以保证MySQL服务器上的硬盘空间。
MySQL慢查询日志(Slow Query Log)
慢查询日志用来记录在MySQL中执行时间超过指定时间的查询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率低,以便进行优化。
MySQL慢查询日志是排查问题的SQL语句,以及检查当前MySQL性能的一个重要功能。如果不是调优需要,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。
默认情况下,慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志功能。
MySQL [mysql_chuid]> SHOW VARIABLES LIKE '%slow_query%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/var/master-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)
MySQL [mysql_chuid]> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
* 语法说明:
slow_query_log:慢查询开启状态
slow_query_log_file:慢查询日志存放的位置(一般设置为 MySQL 的数据存放目录)
long_query_time:查询超过多少秒才记录
启动和设置慢查询日志
可以通过log-slow-queries选项开启慢查询日志。通过long_query_time选项来设置时间值,时间以秒为单位。如果查询时间超过了这个时间值,这个查询语句将被记录到慢查询日志。
将log_slow_queries选项和long_query_time选项加入到配置文件的[mysqld]组中:
[mysqld]
log-slow-queries=dir\filename
long_query_time=n
* 语法说明:
1)dir参数指定慢查询日志的存储路径,如果不指定存储路径,慢查询日志将默认存储到MySQL数据库的数据文件夹下。
2)filename参数指定日志的文件名,生成日志文件的完整名称为filename-slow.log。 如果不指定文件名,默认文件名为hostname-slow.log,hostname是MySQL服务器的主机名。
3)“n”参数是设定的时间值,该值的单位是秒。如果不设置long_query_time选项,默认时间为10秒。
可以通过以下命令启动慢查询日志、设置指定时间:
SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;
查看慢查询日志
如果你想查看哪些查询语句的执行效率低,可以从慢查询日志中获得信息。和错误日志、查询日志一样,慢查询日志也是以文本文件的形式存储的,可以使用普通的文本文件查看工具来查看。
# 开启 MySQL 慢查询日志功能,并设置时间:
MySQL [mysql_chuid]> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.16 sec)
MySQL [mysql_chuid]> SET GLOBAL long_query_time=0.01;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql_chuid]> use mysql
MySQL [mysql]> select * from user;
# 慢查询日志的内容如下:
[root@master ~]# cat /usr/local/mysql/var/master-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.19-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
删除慢查询日志
慢查询日志的删除方法与通用日志的删除方法是一样的。可以使用mysqladmin命令来删除。也可以使用手工方式来删除。
mysqladmin命令的语法格式:mysqladmin -uroot -p flush-logs
执行该命令后,命令行会提示输入密码。输入正确密码后,将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志,不需要再手动删除。
[root@master ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@master ~]#
数据库管理员也可以手工删除慢查询日志,删除之后需要重新启动MySQL服务。
* 通用查询日志和慢查询日志都是使用这个命令,使用时一定要注意,一旦执行这个命令,通用查询日志和慢查询日志都只存在新的日志文件中。如果需要备份旧的慢查询日志文件,必须先将旧的日志改名,然后重启MySQL服务或执行mysqladmin命令。