默认情况下查询日志是关闭的。由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。

控制的参数

| general_log | OFF |
| general_log_file | /mysql/data/localhost.log |

开启这2个参数刻意通过修改全量立即生效还可以通过修改my.cnf来进行控制

1. 修改全局变量
mysql> show variables like "%general_log_file%";
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log_file | /mysql/log/general.log |
+------------------+------------------------+
1 row in set (0.00 sec)

mysql> set global general_log=1;
Query OK, 0 rows affected (0.33 sec)

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

这个时候做操作
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| tts |
+--------------------+
5 rows in set (0.00 sec)

mysql> use tts
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> show tables
-> ;
+---------------+
| Tables_in_tts |
+---------------+
| person |
| pet |
| shirt |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 1 |
| 5 | polo | red | 1 |
| 6 | dress | blue | 1 |
| 7 | t-shirt | white | 1 |
+----+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> create table a1 (a int);
Query OK, 0 rows affected (0.56 sec)

mysql> insert into a1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

查看查询日志
[root@localhost log]# tail -f general.log
/mysql/bin/mysqld, Version: 5.6.25-enterprise-commercial-advanced (MySQL Enterprise Server - Advanced Edition (Commercial)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
150615 10:20:20 1 Query show variables like "%general_log%"
150615 10:20:55 1 Query SELECT DATABASE()
150615 10:21:04 1 Query show databases
150615 10:21:08 1 Query SELECT DATABASE()
1 Init DB tts
1 Query show databases
1 Query show tables
1 Field List person
150615 10:21:09 1 Field List pet
1 Field List shirt
150615 10:21:13 1 Query show tables
150615 10:21:17 1 Query select * from shirt
150615 10:21:52 1 Query create table a1 (a int)
150615 10:21:58 1 Query insert into a1 values (1)
150615 10:22:15 1 Query select * from a1

2.修改my.cnf文件
[root@localhost mysql]# cat my.cnf
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /mysql/data
server-id = 1
#error log
log-error = /mysql/log/mysql.log
log-warnings = 1
#querry log
general_log=1
general_log_file=/tmp/mysql/query.log

启动数据库就可以了

ITPub博客

mysql 日志之普通查询日志

最新文章