MySQL log features detailed query, slow query

MySQL log features detailed query, slow query

MySQL log: A large number of IO operations are not recommended to write to a file

Mysql> show global variables like ' innodb% ';mysql> show global variables like '%log% '; General_log | OFF Log | OFF

"Temporarily turn on logging"

mysql> set global log= ' on ';mysql> set global general_log= ' on '; Query OK, 0 rows affected, 1 warning (0.08 sec) General_log | On log | On

"The/mydata/data/pc0003.log log file will be generated after a few random actions"

Mysql> select * from classes; [Email protected] data]# cat/mydata/data/pc0003.log/usr/local/mysql/bin/mysqld, Version:5.5.45-log (Source Distribution). Started with:tcp port:3306 Unix socket:/tmp/mysql.socktime Id Command Argument150911 14:09:39 1 Q Ueryshow Global variables like '%log% ' 150911 14:11:46 1 Queryset Global general_log= ' on ' 150911 14:13:53 1 Queryselec T * from classes

"Temporarily off"

mysql> set global log= ' off ', at this time set relative to the following useless!

 mysql> set global general_log= ' off '; query ok, 0 rows affected  (0.03 sec) mysql> select * from  classes;+---------+----------------+----------+| classid | class           | numofstu |+---------+----------------+----------+|        1 | Shaolin Pai    |        10 | |        2 | Emei Pai        |        7 | |        3 | QingCheng Pai  |        11 | |        4 | Wudang Pai     |        12 | |        5 | riyue shenjiao |       31  | |        6 | Lianshan Pai   |        27 | |        7 | ming jiao      |        27 | |        8 | Xiaoyao Pai    |        15 | |        9 | Liangshan      |        22 | |       10 | taoyuan        |        23 |+---------+----------------+----------+10 rows in  set  (0.00 SEC)

"Take a look at the/mydata/data/pc0003.log log file and only record the time when the setting is closed. 】

[Email protected] data]# cat/mydata/data/pc0003.log/usr/local/mysql/bin/mysqld, Version:5.5.45-log (Source Distribution). Started with:tcp port:3306 Unix socket:/tmp/mysql.socktime Id Command Argument150911 14:09:39 1 Q Ueryshow Global variables like '%log% ' 150911 14:11:46 1 Queryset Global general_log= ' on ' 150911 14:13:53 1 Queryselec T * from classes150911 14:21:49 1 Queryset global general_log= ' off

"Set log output to table"

Mysql> mysql> set global general_log= ' on '; Query OK, 0 rows affected (0.01 sec) mysql> set global log= ' on '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> set global log_output= ' TABLE '; Query OK, 0 rows Affected (0.00 sec)

Implementation

Mysql> show global variables like '%log% ';mysql> select * from Hellodb.classes;

"File View" does not have a previous action record

[Email protected] data]# Cat/mydata/data/pc0003.log

"Table View" is logged to the specified location.

mysql> select * from mysql.general_log;+---------------------+------------------------- --+-----------+-----------+--------------+------------------------------------+| event_time           | user_host                  | thread_id | server_id |  command_type | argument                            |+------------ ---------+---------------------------+-----------+-----------+--------------+---------------------------------- --+| 2015-09-11 14:32:51 | root[root] @ localhost [] |          1 |         1  | query        | show global variables like  '%log% '  | |  2015-09-11 14:33:19 | root[root] @ localhost [] |          1 |         1 |  query        | select * from hellodb.classes       | |  2015-09-11 14:33:20 | root[root] @ localhost [] |          1 |         1 |  query        | select * from hellodb.classes       | |  2015-09-11 14:35:46 | root[root] @ localhost [] |          1 |         1 | query        |  select * from mysql.general_log    |+---------------------+---------------- -----------+-----------+-----------+--------------+------------------------------------+4 rows in  set  (0.00 SEC)

"It is not recommended to enable query logging unless you have special needs"

mysql> set global general_log= ' OFF '; Query OK, 0 rows Affected (0.00 sec) mysql> set global log= ' OFF '; Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> show global variables like ' innodb% ';

Mysql> show global variables like '%log% ';

Query log

Slow query log: Query execution time longer than the specified length of the query, that is, slow query

The error log should be enabled and is not enabled by default.

Binary log: The replication feature relies on this log

Relay LOG:

Transaction log: Commit is not synchronized to file

Random I/O conversion to sequential I/O

ACID Resistance: Persistent

Log file group: There should be at least two log files;

Note: Use small transactions as much as possible to improve the performance of the transaction engine;

Query log:

log={on| OFF}: Log information for all statements is logged in the General query log file (General_log);

log_output={table| file| NONE}

Table and file can appear at the same time, separated by commas;

General_log: Whether query logging is enabled;

General_log_file: Defines a file saved by the General query log

Slow query log: Beyond this time long become slow query!

Mysql> show global variables like ' long% ';

long_query_time:10.000000

slow_query_log={on| OFF}

Sets whether to enable the slow query log; its output location also depends on log_output={table| file| NONE};

Slow_query_log_file=www-slow.log

Define the path and name of the log file;

Log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp _table_on_disk

Log_slow_queries=on

Log_slow_rate_limit=1

Log_slow_verbosity

"Slow Query Demo"

Set Slow Query

Mysql> mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.05 sec) is set to both file and table output mysql> set global log_output= ' file,table '; Query OK, 0 rows Affected (0.00 sec)

"Lock a Table"

mysql> use Hellodb;database changedmysql> lock tables classes write; Query OK, 0 rows Affected (0.00 sec)

"Open a Terminal 2"

mysql> use hellodb;database changedmysql> select * from students;+--- ----+---------------+-----+--------+---------+-----------+| stuid | name           | Age | Gender | ClassID |  teacherid |+-------+---------------+-----+--------+---------+-----------+|     1  | Shi Zhongyu   |  22 | M       |       2 |          3 | |      2 | shi potian    |  22 |  M      |       1 |          7 | Normal display 27 rows in set  (0.00 SEC)

Mysql> select * from classes; "From avoid hitting with cache"

Be blocked ...

"Release lock, View Log"

mysql> unlock tables; query ok, 0 rows affected  (0.00 sec) mysql> select * from  mysql.general_log;+---------------------+---------------------------+-----------+-----------+--------------+-- ----------------------------------+| event_time           | user_host                  | thread_id | server_id | command_type | argument                             |+---------------------+---------------------------+---------- -+-----------+--------------+------------------------------------+| 2015-09-11 14:32:51 |  Root[root] @ localhost [] |         1 |         1 | query         | show global variables like  '%log% '  | |  2015-09-11 14:33:19 | root[root] @ localhost [] |          1 |         1 |  query        | select * from hellodb.classes       | |  2015-09-11 14:33:20 | root[root] @ localhost [] |          1 |         1 |  query        | select * from hellodb.classes       | |  2015-09-11 14:35:46 | root[root] @ localhost [] |         1 |          1 | Query        | select  * from mysql.general_log    | |  2015-09-11 14:38:53 | root[root] @ localhost [] |          1 |         1 |  query        | set global general_log= ' OFF '         |+---------------------+---------------------------+-----------+---------- -+--------------+------------------------------------+5 rows in set  (0.00 sec)

Error log:

Information in the process of server startup and shutdown;

Error messages during server operation;

The information generated when the event scheduler runs an event;

The information that is generated when the server thread is started from the server in the replication schema;

Log_error =/path/to/error_log_file

Log_warnings = {1|0}

Whether to log warning messages in the error log;

MySQL log features detailed query, slow query