MySQL Universal Query log (General query logs)

Like most relational databases, log files are an important part of the MySQL database. MySQL has several different log files, usually including error log files, binary logs, generic logs, slow query logs, and so on. These logs can help us define what is happening inside MYSQLD, database performance failures, record data changes history, user recovery database, and so on. This article mainly describes the general query log.

1, the MySQL log file system composition
A, error log: Records the issue that occurs when you start, run, or stop mysqld.
B. General log: Records the established client connections and executed statements.
C, update log: The statement that records the change data. The log is no longer used in MySQL 5.1.
D, binary log: A statement that records all changes to the data. Also used for replication.
E, slow query log: Records all queries that have been executed for more than long_query_time seconds, or queries that do not use indexes.
F, InnoDB logs: InnoDB redo Log

By default, all logs are created in the MYSQLD data directory.
You can force mysqld to close and reopen the log file (or, in some cases, switch to a new log) by refreshing the log.
When you execute a FLUSH logs statement or perform mysqladmin flush-logs or mysqladmin refresh, the log is aged.
In the case of MySQL replication, more log files will be maintained from the replication server, known as the replacement log.

2. General Query Log
The common query log can be stored in a text file or table, and all connections and statements are logged to the log file or table, and the log is not turned on by default.
Start it with the--log[=file_name] or the-l [file_name] option. If there is no value for the given file_name, the default name is Host_name.log.
MYSQLD records the statement to the query log in the order it was received. This may be different from the order of execution.
Unlike update logs and binary logs, they log after the query executes, but before any of the locks are released.
The query log contains all statements, and the binary log does not contain statements that only query the data.
Server restarts and log refreshes do not produce a new generic query log file.

3. System variables for general query log
Log_output=[none|file|table|file,table] #通用查询日志输出格式
General_log=[on|off] #是否启用通用查询日志
General_log_file[=filename] #通用查询日志位置及名字

4, the General query log backup
In Linux or UNIX, you can rename a file by using the following command
and create a new file:
shell> MV Hostname.log Hostname-old.log
Shell> mysqladmin Flush-logs
shell> CP Hostname-old.log To-backup-directory
shell> RM Hostname-old.log
In Windows, the log file cannot be renamed while the server is opening the log file. You must first stop the server and then rename the log file. Then restart the server to create a new log file.

5, demonstrate the use of general query log

a. Enable general query log
-Demo environment
[email protected] [(none)]> show variables like ‘% version%’;
+ ------------------------- + ----------------------- ------- +
| Variable_name | Value |
+ ------------------------- + ----------------------- ------- +
| innodb_version | 5.5.39 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.39-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+ ------------------------- + ----------------------- ------- +

--View system variables
[email protected] [(none)]> show variables like ‘% general%’;
+ ------------------ + ---------------------------- +
| Variable_name | Value |
+ ------------------ + ---------------------------- +
| general_log | OFF |
| general_log_file | /var/lib/mysql/suse11b.log |
+ ------------------ + ---------------------------- +

--View the current general log, showing no log file
[email protected] [(none)]> system ls /var/lib/mysql/suse11b.log
ls: cannot access /var/lib/mysql/suse11b.log: No such file or directory
--Set the variable general_log to open the general query log
[email protected] [(none)]> set @@ global.general_log = 1;
Query OK, 0 rows affected (0.00 sec)

--Check again that the common log file already exists
[email protected] [(none)]> system ls /var/lib/mysql/suse11b.log
/var/lib/mysql/suse11b.log
[email protected] [(none)]> select * from tempdb.tb1;-execute query
+ ------ + ------ +
| id | val |
+ ------ + ------ +
| 1 | jack |
+ ------ + ------ +

--View the contents of the general log file
[email protected] [(none)]> system more /var/lib/mysql/suse11b.log
/ usr / sbin / mysqld, Version: 5.5.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
141003 16:18:12 4 Query show variables like ‘% general%’
141003 16:18:55 4 Query select * from tempdb.tb1


b. Change the general query log location
[email protected] [(none)]> exit
Bye
suse11b: ~ # service mysql stop
Shutting down MySQL ... done
suse11b: ~ # mysqld --general_log_file = / tmp / suse11b.log --user = mysql &
[1] 47009
suse11b: ~ # ps -ef | grep mysql | grep -v grep
mysql 47009 44514 1 16:22 pts / 0 00:00:00 mysqld --general_log_file = / tmp / suse11b.log --user = mysql
root 47053 44514 0 16:22 pts / 0 00:00:00 grep mysql
suse11b: ~ # mysql
[email protected] [(none)]> system ls /tmp/suse11b.log
ls: cannot access /tmp/suse11b.log: No such file or directory
[email protected] [(none)]> show variables like ‘% gener%’;
+ ------------------ + ------------------ +
| Variable_name | Value |
+ ------------------ + ------------------ +
| general_log | OFF |
| general_log_file | /tmp/suse11b.log |
+ ------------------ + ------------------ +

[email protected] [(none)]> set global general_log = on;
Query OK, 0 rows affected (0.01 sec)

--At this time, it can be seen from the system variables that the general log has reached the / tmp directory
[email protected] [(none)]> show variables like ‘% gener%’;
+ ------------------ + ------------------ +
| Variable_name | Value |
+ ------------------ + ------------------ +
| general_log | ON |
| general_log_file | /tmp/suse11b.log |
+ ------------------ + ------------------ +

--Post query
[email protected] [(none)]> select count (*) from tempdb.tb1;
+ ---------- +
| count (*) |
+ ---------- +
| 1 |
+ ---------- +

--View the contents of the general log file
[email protected] [(none)]> system more /tmp/suse11b.log
mysqld, Version: 5.5.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
141003 16:30:03 1 Query show variables like ‘% gener%’
141003 16:30:09 1 Query select count (*) from tempdb.tb1


c. General query log output method
-Can be output as files, tables and not output, namely TABLE, FILE, NONE
--System variable log_output
[email protected] [(none)]> show variables like ‘log_output‘;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| log_output | FILE |
+ --------------- + ------- +

-The following is modified to output as a table
[email protected] [(none)]> set global log_output = ‘TABLE’;
Query OK, 0 rows affected (0.00 sec)

[email protected] [(none)]> show variables like ‘log_output‘;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| log_output | TABLE |
+ --------------- + ------- +

--Post query
[email protected] [(none)]> select * from tempdb.tb1;
+ ------ + ------ +
| id | val |
+ ------ + ------ +
| 1 | jack |
+ ------ + ------ +

--Author: Leshami
--Blog: http://blog.csdn.net/leshami

[email protected] [(none)]> system more /tmp/suse11b.log
mysqld, Version: 5.5.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
141003 16:30:03 1 Query show variables like ‘% gener%’
141003 16:30:09 1 Query select count (*) from tempdb.tb1
141003 16:31:00 1 Query show variables like ‘log_output’
141003 17:00:48 1 Qu
ery set global log_output = ‘TABLE’ #General query log output to file only records changes to global variables

--mysql.general_log records the general query log information
[email protected] [(none)]> desc mysql.general_log;
+ -------------- + ------------------ + ------ + ----- +- ----------------- + ----------------------------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------- + ------------------ + ------ + ----- +- ----------------- + ----------------------------- +
| event_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host | mediumtext | NO | | NULL | |
| thread_id | int (11) | NO | | NULL | |
| server_id | int (10) unsigned | NO | | NULL | |
| command_type | varchar (64) | NO | | NULL | |
| argument | mediumtext | NO | | NULL | |
+ -------------- + ------------------ + ------ + ----- +- ----------------- + ----------------------------- +

-View the content of the general query log from the general query log table
[email protected] [(none)]> select thread_id, command_type, argument from mysql.general_log;
+ ----------- + -------------- + ---------------------- ----------------------------------------- +
| thread_id | command_type | argument |
+ ----------- + -------------- + ---------------------- ----------------------------------------- +
| 1 | Query | show variables like ‘log_output’ |
| 1 | Query | select * from tempdb.tb1 |
| 1 | Query | desc mysql.general_log |
| 1 | Query | select thread_id, command_type, argument from mysql.general_log |
+ ----------- + -------------- + ---------------------- ----------------------------------------- +

[email protected] [(none)]> show variables like ‘log_output‘;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| log_output | TABLE |
+ --------------- + ------- +

-Use FILE, TABLE 2 to mix and output common logs
[email protected] [(none)]> set global log_output = ‘file, table’;
Query OK, 0 rows affected (0.00 sec)

[email protected] [(none)]> select @@ global.log_output;
+ --------------------- +
| @@ global.log_output |
+ --------------------- +
| FILE, TABLE |
+ --------------------- +

[email protected] [(none)]> insert into tempdb.tb1 values (2, ‘robinson’);
Query OK, 1 row affected (0.06 sec)

[email protected] [(none)]> commit;
Query OK, 0 rows affected (0.01 sec)

--Verification results, there is a common log record in the table and file
[email protected] [(none)]> system tail /tmp/suse11b.log|grep robinson
141003 17:41:54 2 Query insert into tempdb.tb1 values (2, ‘robinson’)
[email protected] [(none)]> select thread_id, command_type, argument from mysql.general_log
    -> where argument like ‘% robinson%’;
+ ----------- + -------------- + ---------------------- -------------------------------------------------- +
| thread_id | command_type | argument |
+ ----------- + -------------- + ---------------------- -------------------------------------------------- +
| 2 | Query | insert into tempdb.tb1 values (2, ‘robinson’) |
| 2 | Query | select thread_id, command_type, argument from mysql.general_log |
| | | where argument like ‘‘ robinson ’‘ |
+ ----------- + -------------- + ---------------------- -------------------------------------------------- +


d. Close the general query log
--You can close the general query log by setting the system variable general_log, at this time the log output is set to FILE, TABLE
[email protected] [(none)]> show variables like ‘log_output‘;
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| log_output | FILE, TABLE |
+ --------------- + ------------ +

[email protected] [(none)]> set global general_log = off;
Query OK, 0 rows affected (0.01 sec)

[email protected] [(none)]> show variables like ‘% gener%’;
+ ------------------ + ------------------ +
| Variable_name | Value |
+ ------------------ + ------------------ +
| general_log | OFF |
| general_log_file | /tmp/suse11b.log |
+ ------------------ + ------------------ +

[email protected] [(none)]> delete from tempdb.tb1 where id = 2;
Query OK, 1 row affected (0.12 sec)

[email protected] [(none)]> commit;
Query OK, 0 rows affected (0.00 sec)

[email protected] [(none)]> system tail -n 1 /tmp/suse11b.log
141003 17:45:13 2 Query set global general_log = off

[email protected] [(none)]> select thread_id, command_type, argument from mysql.general_log
    -> where argument like ‘% delete%’;
Empty set (0.00 sec)

-From the above demo, we can see that although we set log_output to FILE, TABLE, but general_log is OFF, there is no record of general log

[email protected] [(none)]> set global log_output = none;
Query OK, 0 rows affected (0.00 sec)

[email protected] [(none)]> set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)

[email protected] [(none)]> truncate table tempdb.tb1;
Query OK, 0 rows affected (0.01 sec)

[email protected] [(none)]> system tail -n 1 /tmp/suse11b.log
Time Id Command Argument

--Through the above demonstration, there is no general log output under the clear of log_output = none, general_log = on.  

MySQL Universal Query log (General query logs)