預設情況下查詢日誌是關閉的。由於查詢日誌會記錄使用者的所有操作，其中還包含增刪查改等資訊，在併發操作大的環境下會產生大量的資訊從而導致不必要的磁碟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

啟動資料庫就可以了

mysql 日誌之普通查詢日誌

相關文章