Mysql通用查询日志(General Query Log)解析

文档课题：Mysql通用查询日志(General Query Log)解析.
数据库：mysql 8.0.11
1、概念知识
说明：通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句、查询语句等.默认情况下通用查询日志功能是关闭的.
通过以下命令查看通用查询日志是否开启.
2、开启通用查询日志
mysql> show variables like '%general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | OFF                                 |
| general_log_file | /usr/local/mysql/data/leo-mysql.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

说明：可以看出通用查询日志是关闭的，general_log_file变量指定通用查询日志文件所在位置.通用查询日志以文本文件的形式存储，可以使用普通文本文件查看该类型日志内容.
--打开通用查询日志功能
mysql> set global general_log=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | /usr/local/mysql/data/leo-mysql.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| leo                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> use leo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table test (id int(2) not null auto_increment,name varchar(10) not null,sex char(5) not null,primary key(id));
Query OK, 0 rows affected (0.12 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(2)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| sex   | char(5)     | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into test values (1,'Jack','man');
Query OK, 1 row affected (0.07 sec)

mysql> insert into test values(2,'Cherry','woman');
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+----+--------+-------+
| id | name   | sex   |
+----+--------+-------+
|  1 | Jack   | man   |
|  2 | Cherry | woman |
+----+--------+-------+
2 rows in set (0.00 sec)
3、查看查询日志
执行成功后，打开通用查询日志leo-mysql.log，如下为通用查询日志中部分内容.
[root@sztech ~]# cd /usr/local/mysql/data
[root@leo-mysql data]# more leo-mysql.log
/usr/local/mysql/bin/mysqld, Version: 8.0.11 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/mysql.sock
Time                 Id Command    Argument
2023-03-27T00:28:53.811857Z         8 Query     show variables like '%general%'
2023-03-27T00:33:08.064632Z         8 Query     show databases
2023-03-27T00:33:23.090309Z         8 Query     SELECT DATABASE()
2023-03-27T00:33:23.091216Z         8 Init DB    leo
2023-03-27T00:33:23.094592Z         8 Query     show databases
2023-03-27T00:33:23.096214Z         8 Query     show tables
2023-03-27T00:33:23.108782Z         8 Field List        test 
2023-03-27T00:37:14.570996Z         8 Query     select * from leo.tables
2023-03-27T00:37:57.272751Z         8 Query     show tables
2023-03-27T00:39:53.115583Z         8 Query     select table_name from infomation_schema.tables where table_schema='LEO'
2023-03-27T00:40:27.592514Z         8 Query     select table_name from information_schema.tables where table_schema='LEO'
2023-03-27T00:40:38.494288Z        8 Query     select table_name from information_schema.tables where table_schema='leo'
2023-03-27T00:42:30.057322Z         8 Query     drop table if exists test
2023-03-27T00:44:45.519995Z         8 Query     create table test (id int(2) not null auto_increment,name varchar(10) not null,sex char(5) not null,primary key(id))
2023-03-27T00:44:51.317058Z         8 Query     desc test
2023-03-27T00:45:13.112450Z         8 Query     insert into test values (1,'Jack','man')
2023-03-27T00:45:32.976748Z         8 Query     insert into test values(2,'Cherry','woman')
2023-03-27T00:46:08.971591Z         8 Query     select * from test
说明：可以看出通用查询日志非常清晰地记录了客户端的所有行为.
4、关闭通用日志
通用查询日志启动后，可通过如下两种方法停止.
A、将MySQL配置文件中相关配置注释掉，然后重启服务器.具体如下：
[mysqld]
#log=dir\filename
说明：上述方法需重启MySQL服务器，若有业务访问时是不允许的，此时可通过另一种方法动态控制通用查询日志的开启和关闭.
B、设置MySQL的环境变量general_log为关闭状态可以停止该日志.
mysql> SET GLOBAL general_log=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general_log%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | OFF                                 |
| general_log_file | /usr/local/mysql/data/leo-mysql.log |
+------------------+-------------------------------------+
2 rows in set (0.01 sec)