MySQL Tracing and logging

After MySQL opens the general log, all query statements are recorded in the general log file, the file is read-only, but the general log file is very large, so the default is closed.

However, sometimes it is necessary to check for errors and other reasons, temporarily open the general log.

Open method:

Show global variables like '%general% ';

Set global general_log = on; Open it

Set global general_log = off; Shut down

The general log is recorded to a text file by default, but can be changed to a database by modifying log_output= ' table ', and a table general_log will be added to MySQL db.

By looking at the table structure, it is an external CSV file.

Show global variables like '%log_output% ';

Set global log_output = ' TABLE ';

MySQL Event Tracker, MySQL does not need to restart the service tracking SQL, or configure the log

The first step is to create the following two log tables

  CREATE TABLE ' Slow_log ' (   ' start_time ' timestamp not NULL DEFAULT current_timestamp on                           UPDATE Current_timestamp,   ' user_host ' mediumtext not NULL,   ' query_time ' time not NULL,   ' lock_time ' time not NULL,   ' rows_sent ' int (one) not null,   ' rows_examined ' int (one) not NULL,   ' db ' varchar (+) NOT NULL,   ' last_insert_id ' int (one) not NU LL,   ' insert_id ' int (one) not null,   ' server_id ' int (ten) unsigned not NULL,   ' Sql_text ' Mediumtext not null,< c14/> ' thread_id ' bigint (+) unsigned not NULL  ) engine=csv DEFAULT charset=utf8 comment= ' Slow log '

  CREATE TABLE ' General_log ' (   ' event_time ' timestamp not NULL DEFAULT current_timestamp on                          UPDATE Current_ TIMESTAMP,   ' user_host ' mediumtext not NULL,   ' thread_id ' bigint (+) unsigned not null,   ' server_id ' int (10) unsigned not NULL,   ' command_type ' varchar ($) NOT null,   ' argument ' mediumtext not null  ) Engine=csv DEFAULT C Harset=utf8 comment= ' General log '

The second step is to open the query log on the database

SET Global general_log = 1; SET Global log_output = ' table ';

Step three query log

SELECT * FROM Mysql.general_log

Step fourth closes the query log on the database

SET global general_log = 0;

Fifth step clear the database log

TRUNCATE TABLE Mysql.general_log;

MySQL Tracing and logging