Tutorial _ MySQL

This article describes how to start and use a common query log instance in MySQL, including examples of basic operations such as backup and shutdown. For more information, see Enable general Log Query
Generally, general query logs are not enabled for performance purposes. Slow log can locate SQL statements with performance problems, while general log records all SQL statements.

For MySQL, if you want to enable slow log and general log, you need to restart. from MySQL 5.1.6, general query log and slow query log support writing to files or database tables, in addition, logs can be dynamically modified at the Global level when they are enabled and output.

mysql>select version();

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

Set the log output mode to a file (if log_output = table is set, the log results will be recorded in the table named gengera_log, and the default engine of this table is CSV ):

mysql>set global log_output=file;

Query OK, 0 rows affected (0.00 sec)

Set the log file path for general log:

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

Query OK, 0 rows affected (0.00 sec)

Enable general log:

mysql>set global general_log=on;

Query OK, 0 rows affected (0.02 sec)

After a while, close the general log:

mysql>set global general_log=off;

Query OK, 0 rows affected (0.02 sec)

Common system variables for Log Query

Log_output = [none | file | table | file, table] # common query log output format

General_log = [on | off] # enable general query log general_log_file [= filename] # General query log location and name

Backup of general query logs

In Linux or Unix, you can use the following command to rename the file

And create a new file:

shell> mv hostname.log hostname-old.logshell> mysqladmin flush-logsshell> cp hostname-old.log to-backup-directoryshell> rm hostname-old.log

In Windows, the log file cannot be renamed when the server opens the log file. You must stop the server and rename the log file. Restart the server to create a new log file.

Demonstrate the use of common query logs

A. enable common query logs

-- Demo environment root @ localhost [(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 the system variable root @ localhost [(none)]> show variables like '% general % ';

+------------------+----------------------------+| Variable_name | Value  |+------------------+----------------------------+| general_log | OFF  || general_log_file | /var/lib/mysql/suse11b.log |+------------------+----------------------------+

-- View the current general log and display the 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 enable the general query log root @ localhost [(none)]> set @ global. general_log = 1;

Query OK, 0 rows affected (0.00 sec)

-- Check that the common log file already exists. root @ localhost [(none)]> system ls/var/lib/mysql/suse11b. log/var/lib/mysql/suse11b. logroot @ localhost [(none)]> select * from tempdb. tb1; -- execute the query

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

-- View the contents of a common log file root @ localhost [(none)]> system more/var/lib/mysql/suse11b. log/usr/sbin/mysqld, Version: 5.5.39-log (MySQL Community Server (GPL )). started:

Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime  Id Command Argument141003 16:18:12 4 Query show variables like '%general%'141003 16:18:55 4 Query select * from tempdb.tb1

B. change the log location of a common query

root@localhost[(none)]> exit

Byesuse11b:~ # service mysql stopShutting down MySQL...   donesuse11b:~ # mysqld --general_log_file=/tmp/suse11b.log --user=mysql &[1] 47009suse11b:~ # ps -ef|grep mysql|grep -v grepmysql 47009 44514 1 16:22 pts/0 00:00:00 mysqld --general_log_file=/tmp/suse11b.log --user=mysqlroot 47053 44514 0 16:22 pts/0 00:00:00 grep mysqlsuse11b:~ # 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)

-- Now we can see from the system variables that the general 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 the contents of the common log file root @ localhost [(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.sockTime  Id Command Argument141003 16:30:03 1 Query show variables like '%gener%'141003 16:30:09 1 Query select count(*) from tempdb.tb1

C. General log output query method

-- The output can be a FILE, a TABLE, or no output, that is, TABLE, FILE, NONE -- system variable log_outputroot @ localhost [(none)]> show variables like 'log _ output ';

+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output | FILE |+---------------+-------+

-- The following is a TABLE output method: 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 socket:/var/lib/mysql. sockTime Id Command Argument141003 16:30:03 1 Query show variables like '% gener %' 141003 16:30:09 1 Query select count (*) from tempdb. tb1141003 16:31:00 1 Query show variables like 'log _ output' 141003 17:00:48 1 Query set global log_output = 'table' # General Query log output to file only records modifications to global 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(10) unsigned | NO | | NULL  |  || command_type | varchar(64) | NO | | NULL  |  || argument | mediumtext | NO | | NULL  |  |+--------------+------------------+------+-----+-------------------+-----------------------------+

-- View the contents of the general query log from the general query log table 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 |+---------------+-------+

-- Use FILE and TABLE 2 to output common logs. 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)

-- Verification result: common log records exist in 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. disable common query logs.

-- You can disable the general query log by setting the system variable general_log. at this time, 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)
-- As shown in the preceding example, although we set log_output to FILE and TABLE, general_log to OFF, and general logs are not generated with any records.

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 -- in the preceding demonstration, there is no general log output in the cleanup of log_output = none and general_log = on.