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]
server_id=1 //服务id号，不可重复,为0则拒绝从服务器连接
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)