Example tutorials for opening and using common query logs in Mysql _mysql

Open a common query log
the general log will not open for performance reasons. Slow log can locate some performance-related SQL, and general log logs all of the SQL.

mysql5.0 version, if you want to open slow log, general log, need to restart, starting from the MySQL5.1.6 version, general query log and slow query log to support the writing to the file or database table two ways, and the log is open, Changes to the output method can be modified dynamically at the global level.

Mysql>select version ();

+ ———— +
| version () |
+ ———— +
| 5.1.37-log |
+ ———— +
1 row in Set (0.02 sec)

Set the log output to a file (if you set log_output=table, the log results are recorded in a table named Gengera_log, which has a default engine of CSV):

Mysql>set Global log_output=file;

Query OK, 0 rows Affected (0.00 sec)

Set log file path for general log:

Mysql>set global general_log_file= '/tmp/general.log ';

Query OK, 0 rows Affected (0.00 sec)

Open General log:

Mysql>set Global General_log=on;

Query OK, 0 rows affected (0.02 sec)

After a period of time, close the general log:

Mysql>set Global General_log=off;

Query OK, 0 rows affected (0.02 sec)

System variables for common query logs

Log_output=[none|file|table|file,table] #通用查询日志输出格式

General_log=[on|off]      #是否启用通用查询日志

general_log_file[=filename]    #通用查询日志位置及名字

Backup of common query logs

In Linux or UNIX, you can rename the file by following the command

and create a new file:

shell> mv Hostname.log hostname-old.log

shell> mysqladmin flush-logs

CP shell> To-backup-directory

shell> RM hostname-old.log

In Windows, the log file cannot be renamed during the server's open log file. You must first stop the server and then rename the log file. Then restart the server to create a new log file.

Demonstrating the use of common query logs

A, enable the common query log

--Demo Environment
root@localhost[(none)]> show variables like '%version% ';

+-------------------------+------------------------------+
| Variable_name  | Value  |
+-------------------------+------------------------------+
| innodb_version | 5.5.39  |
| protocol_ Version | slave_type_conversions | | | | | 5.5.39-log | | |
version_comment | MySQL Community Server (GPL) |
| Version_compile_machine | x86_64  |
| version_compile_os | Linux  |
+-------------------------+------------------------------+

--View System Variables
root@localhost[(none)]> show variables like '%general% ';

+------------------+----------------------------+
| variable_name | Value  |
+------------------+----------------------------+
| general_log | Off  | |
general_log_file |/var/lib/mysql/suse11b.log
| +------------------+----------------------------+

--View the current common log, showing no log file
root@localhost[(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 generic query log
root@localhost[(none)]> set @ @global. general_log=1;

Query OK, 0 rows Affected (0.00 sec)

--View the common log file already exists
root@localhost[(none)]> system Ls/var/lib/mysql/suse11b.log 
/var/lib/mysql/ Suse11b.log
root@localhost[(None)]> select * from TEMPDB.TB1;--Execute Query

+------+------+
| id | val
| +------+------+
| 1 | jack |
+------+------+

--View Common log file content
root@localhost[(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 VA Riables like '%general% '
141003 16:18:55 4 Query select * FROM TEMPDB.TB1

b, change the common query log location

root@localhost[(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
47009 44514 1 16:22 pts/0 00:00:00 mysqld--general_log_file=/tmp/suse11b.log--user=my SQL
root 47053 44514 0 16:22 pts/0 00:00:00 grep mysql
suse11b:~ # mysql

root@localhost[(None)]> system Ls/tmp/suse11b.log

Ls:cannot access/tmp/suse11b.log:no such file or directory

root@localhost[(none)]> show variables like '%gener% ';

+------------------+------------------+
| variable_name | Value |
+------------------+------------------+
| general_log | Off  | |
general_log_file |/tmp/suse11b.log
| +------------------+------------------+

root@localhost[(None)]> set global general_log=on;

Query OK, 0 rows affected (0.01 sec)

-At this point from the system variables, the common log has been to the/tmp directory
root@localhost[(none)]> show variables like '%gener% ';

+------------------+------------------+
| variable_name | Value |
+------------------+------------------+
| general_log | On  |
| general_log_file |/tmp/suse11b.log
| +------------------+------------------+

--Publish Query
root@localhost[(None)]> select COUNT (*) from TEMPDB.TB1;

+----------+
| count (*) |
+----------+
| 1 |
+----------+

--View Common log file content
root@localhost[(none)]> system More/tmp/suse11b.log

Mysqld, Version:5.5.39-log (MySQL Community Server (GPL)). Started with:
TCP port:3306 Unix sockets:/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, common query log output mode

--can be output as a file, a table and not output, that is, Table,file,none
-system variable log_output
root@localhost[(NONE)]> show variables like ' Log_ Output ';

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

--The following is modified to output as table mode
root@localhost[(none)]> set global log_output= ' table ';

Query OK, 0 rows Affected (0.00 sec)

root@localhost[(none)]> show variables like ' log_output ';

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+

--Publish Query
root@localhost[(none)]> select * from TEMPDB.TB1;

+------+------+
| id | val
| +------+------+
| 1 | jack |
+------+------+

root@localhost[(None)]> system More/tmp/suse11b.log

Mysqld, Version:5.5.39-log (MySQL Community Server (GPL)). Started with:
TCP port:3306 Unix sockets:/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:3 1:00 1 Query show variables like ' log_output '
141003 17:00:48 1 Query set global log_output= ' TABLE ' #通用查询日志输出到文件仅仅记录到全 Modification of the local variables

--mysql.general_log records the information of the General query log
root@localhost[(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 (a) unsigned | NO | | NULL  | | |  command_type |
varchar (64) | NO | | NULL  | | |
| argument | mediumtext | NO | | NULL  |  |
+--------------+------------------+------+-----+-------------------+-----------------------------+

--View the contents of the generic query log from the common query log
root@localhost[(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 |
+-----------+--------------+---------------------------------------------------------------+

root@localhost[(none)]> show variables like ' log_output ';

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+

--Using file,table 2 mixed output Common log
root@localhost[(none)]> set global log_output= ' file,table ';

Query OK, 0 rows Affected (0.00 sec)

root@localhost[(None)]> SELECT @ @global. log_output;

+---------------------+
| @ @global. log_output |
+---------------------+
| file,table |
+---------------------+

root@localhost[(none)]> insert INTO TEMPDB.TB1 values (2, ' Robinson ');

Query OK, 1 row affected (0.06 sec)

root@localhost[(none)]> commit;

Query OK, 0 rows affected (0.01 sec)

--Verify the results, there are common logging inside tables and files
root@localhost[(none)]> system Tail/tmp/suse11b.log|grep Robinson

141003 17:41:54 2 Query insert INTO TEMPDB.TB1 values (2, ' Robinson ')

root@localhost[(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, turn off the common query log

--You can turn off the common query log by setting the system variable General_log, at which point the log output is set to File,table

root@localhost[(none)]> show variables like ' log_output ';

+---------------+------------+
| variable_name | Value |
+---------------+------------+
| log_output | file,table |
+---------------+------------+

root@localhost[(None)]> set global General_log=off;

Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)]> show variables like '%gener% ';

+------------------+------------------+
| variable_name | Value |
+------------------+------------------+
| general_log | Off  | |
general_log_file |/tmp/suse11b.log
| +------------------+------------------+

root@localhost[(none)]> Delete from tempdb.tb1 where id=2;

Query OK, 1 row affected (0.12 sec)

root@localhost[(none)]> commit;

Query OK, 0 rows Affected (0.00 sec)

root@localhost[(None)]> system Tail-n 1/tmp/suse11b.log

141003 17:45:13 2 Query set global General_log=off

root@localhost[(None)]> Select Thread_id,command_type,argument from Mysql.general_log
 -> where argument Like '%delete% ';

Empty Set (0.00 sec)
--From the above demo, although we set the Log_output as file,table, but General_log is off, the common log has no record of any

root@localhost[(None)]> set global log_output=none;

Query OK, 0 rows Affected (0.00 sec)

root@localhost[(None)]> set global general_log=1;

Query OK, 0 rows Affected (0.00 sec)

root@localhost[(none)]> truncate TABLE tempdb.tb1;

Query OK, 0 rows affected (0.01 sec)

root@localhost[(None)]> system Tail-n 1/tmp/suse11b.log

Time  Id Command Argument

--Through the above demo, there is no common log output under Log_output=none,general_log=on.