文档课题：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)
