MySQL日志管理

1、MySQL日志概述

日志是数据库的重要组成部分,主要用来记录数据库的运行情况、日常操作和错误信息。为了维护 MySQL 数据库,经常需要在 MySQL 中进行日志操作,包含日志文件的启动、查看、停止和删除等,这些操作都是数据库管理中最基本、最重要的操作。

1.1 日志分类

  • 二进制日志
  • 错误日志
  • 通用查询日志
  • 慢查询日志

1.2 日志作用

二进制日志:

  • 该日志文件会以二进制的形式记录数据库的各种操作,但不记录查询语句,二进制日志中的记录可以用来修复数据库。

错误日志:

  • 该日志文件会记录 MySQL 服务器的启动、关闭和运行错误等信息;默认只开启错误日志。

通用查询日志:

  • 该日志记录 MySQL 服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的 SQL 语句等。

慢查询日志:

  • 记录执行事件超过指定时间的操作,通过工具分析慢查询日志可以定位 MySQL 服务器性能瓶颈所在。

使用日志有优点也有缺点,启动日志后,虽然可以对 MySQL 服务器性能进行维护,但是会降低 MySQL 的执行速度;日志文件还会占用大量的硬盘空间。对于用户量非常大、操作非常频繁的数据库,日志文件需要的存储空间甚至比数据库文件需要的存储空间还要大。因此,是否启动日志,启动什么类型的日志要根据具体的应用来决定。

2、二进制日志(Binary Log)

二进制日志(Binary Log)也可叫作变更日志(Update Log),是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况。

//在数据库中查看二进制日志是否开启
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.001 sec)

//在配置文件中开启二进制日志
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin=mysql-bin	//二进制日志文件名称
expire_logs_days=10	//二进制日志过期时间,默认为0
max_binlog_size=100M	//单个文件二进制日志文件大小,默认1G
:wq!

//重启服务
[root@localhost ~]# systemctl restart mariadb

//二进制日志文件存放位置
[root@localhost ~]# ls /var/lib/mysql/
aria_log.00000001  ib_logfile0  multi-master.info  mysql.sock
aria_log_control   ib_logfile1  mysql              mysql_upgrade_info
hzz                ibdata1      mysql-bin.000001   performance_schema
ib_buffer_pool     ibtmp1       mysql-bin.index

//查看二进制日志文件内容
[root@localhost ~]# mysqlbinlog /var/lib/mysql/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 /*!*/;
........略.........

//登录数据库查看二进制日志已经开启
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.001 sec)

//在数据库中查看有哪些二进制日志文件
MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
| mysql-bin.000002 |       371 |
+------------------+-----------+

//在数据库中查看正在写入的是哪个二进制日志文件
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      371 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

//在数据库中删除'mysql-bin.000002'之前所有的二进制日志文件
MariaDB [(none)]> PURGE MASTER LOGS TO 'mysql-bin.000002';
Query OK, 0 rows affected (0.001 sec)

//在数据库中查看剩下的二进制日志发现'mysql-bin.000002'之前的日志都被删除
MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       371 |
+------------------+-----------+
1 row in set (0.000 sec)

//在数据库中根据时间删除二进制日志文件,删除该时间之前的所有二进制日志
MariaDB [(none)]> PURGE MASTER LOGS BEFORE '2021-03-05 19:16:01';
Query OK, 0 rows affected (0.000 sec)

//在数据库中删除所有二进制日志
MariaDB [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.004 sec)

//在数据库中暂停二进制日志进行记录,暂停并不是关闭
MariaDB [(none)]> SET sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

//在数据库中开启二进制日志继续记录
MariaDB [(none)]> SET sql_log_bin=1;
Query OK, 0 rows affected (0.000 sec)

3、错误日志(Error Log)

错误日志(Error Log)是 MySQL 中最常用的一种日志,主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等;作为初学者,要学会利用错误日志来定位问题。

//在数据库中查看错误日志存放的位置信息
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)

//如果错误日志未开启,在配置文件中开启错误日志
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-error=/var/log/mariadb/mariadb.log	//配置文件中默认存在
:wq!

//重启服务
[root@localhost ~]# systemctl restart mariadb

//查看错误日志
[root@localhost ~]# cat /var/log/mariadb/mariadb.log
2021-03-05 19:22:18 11 [ERROR] MYSQL_BIN_LOG::purge_logs was called with file ./2021-3-5 19:22:00 not listed in the index.
2021-03-05 19:22:52 11 [ERROR] MYSQL_BIN_LOG::purge_logs was called with file ./2021-3-5 19:22:00 not listed in the index.
....略......

//清空错误日志
[root@localhost ~]# > /var/log/mariadb/mariadb.log

4、通用查询日志(General Query Log)

通用查询日志(General Query Log)用来记录用户的所有操作,包括启动和关闭 MySQL 服务、更新语句和查询语句等。默认情况下,通用查询日志功能是关闭的。

//在数据库中查看通用查询日志开启状态和存放的位置信息
MariaDB [(none)]> SHOW VARIABLES LIKE '%general%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | OFF           |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.001 sec)

//在配置文件中开启通用查询日志
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
general_log=on		//开启通用查询日志
general_log_file=/var/lib/mysql/mysql-log.log	//通用查询日志存放位置
:wq!

//重启服务
[root@localhost ~]# systemctl restart mariadb

//在数据库中查看通用查询日志已经开启,通用查询日志会记录用户的所有行为
MariaDB [(none)]> SHOW VARIABLES LIKE '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/lib/mysql/mysql-log.log |
+------------------+------------------------------+
2 rows in set (0.001 sec)

//在数据库中 开启/关闭 通用查询日志
MariaDB [(none)]> SET GLOBAL general_log=on/off;
Query OK, 0 rows affected (0.000 sec)

//清空通用查询日志
[root@localhost ~]# > /var/lib/mysql/mysql-log.log

5、慢查询日志(Slow Query Log)

慢查询日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率低,以便进行优化。通俗的说,MySQL 慢查询日志是排查问题的 SQL 语句,以及检查当前 MySQL 性能的一个重要功能。如果不是调优需要,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

//在数据库中查看慢查询日志开启状态和存放的位置信息
MariaDB [(none)]> SHOW VARIABLES LIKE '%slow_query%';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_query_log      | OFF                |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------+
2 rows in set (0.001 sec)

//在数据库中查看慢查询日志规定超时时间
MariaDB [(none)]> SHOW VARIABLES LIKE '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.000 sec)

//在配置文件中开启慢查询日志,并设置超时时间
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
slow_query_log=on	//开启慢查询日志
slow_query_log_file=/var/lib/mysql/mysql-slow.log	//慢查询日志存放位置
long_query_time=5	//慢查询日志查询超时时间

//重启服务
[root@localhost ~]# systemctl restart mariadb

//在数据库中查看慢查询日志已经开启,时间设置为5秒
MariaDB [(none)]> SHOW VARIABLES LIKE '%slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.000 sec)

//在数据库中 继续/暂停 慢查询日志,
MariaDB [(none)]> SET GLOBAL slow_query_log=on/off;
Query OK, 0 rows affected (0.000 sec)

//清空通用查询日志
[root@localhost ~]# > /var/lib/mysql/mysql-slow.log

6、日志输出方式(Log Output)

MySQL 的查询日志支持写入到文件或写入数据表两种输出形式。启用了通用查询日志或慢查询日志功能后,可以选择让服务器把日志写入到日志文件或mysql数据库中的日志表、或者同时写到这两个地方。

//在数据库中查看日志存放方式
MariaDB [(none)]> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.000 sec)

//在数据库中设置日志输出方式为TABLE
MariaDB [(none)]> SET GLOBAL log_output='TABLE';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.001 sec)

//在数据库中设置日志输出方式为FILE和TABLE
MariaDB [(none)]> SET GLOBAL log_output='FILE,TABLE';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.001 sec)

//日志表存放在mysql库中,可以直接用select查看表中内容
MariaDB [(none)]> SHOW TABLES FROM mysql LIKE '%log%';
+-------------------------+
| Tables_in_mysql (%log%) |
+-------------------------+
| general_log             |
| slow_log                |
+-------------------------+
2 rows in set (0.000 sec)