use mysql

show tables;

show create table general_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(21) unsigned NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`command_type` varchar(64) NOT NULL,

`argument` mediumtext NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' |

從上面可以看見使用的是CSV引擎

show create table slow_log;

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(11) NOT NULL,

`rows_examined` int(11) NOT NULL,

`db` varchar(512) NOT NULL,

`last_insert_id` int(11) NOT NULL,

`insert_id` int(11) NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`sql_text` mediumtext NOT NULL,

`thread_id` bigint(21) unsigned NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |

--修改引擎

在mysql資料庫中絕大多數表都不能修改引擎,general_log和slow_log例外；

show engines;

mysql> alter table slow_log engine=myisam;

ERROR 1146 (42S02): Table 'mysql.sow_log' doesn't exist

mysql> show global variables like '%slow%';

+---------------------------+------------------------------------+

| Variable_name | Value |

+---------------------------+------------------------------------+

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /home/data/3306/localhost-slow.log |

+---------------------------+------------------------------------+

5 rows in set (0.00 sec)

mysql> set global slow_query_log =0;

mysql> show global variables like '%slow%';

+---------------------------+------------------------------------+

| Variable_name | Value |

+---------------------------+------------------------------------+

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | /home/data/3306/localhost-slow.log |

+---------------------------+------------------------------------+

5 rows in set (0.00 sec)

mysql> alter table slow_log engine=myisam;

mysql> set global slow_query_log =1;

顯示日誌檔案位置

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

+-----------------------------------------+------------------------------------+

| Variable_name | Value |

+-----------------------------------------+------------------------------------+

| general_log_file | /home/data/3306/localhost.log |

| slow_query_log_file | /home/data/3306/localhost-slow.log |

+-----------------------------------------+------------------------------------+

61 rows in set (0.01 sec)

mysql> show variables like 'log_output';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

1 row in set (0.00 sec)

--general log

set global general_log=0|1

set sql_log_off=0|1

mysql> show variables like 'genera%';

+------------------+-------------------------------+

| Variable_name | Value |

+------------------+-------------------------------+

| general_log | OFF |

| general_log_file | /home/data/3306/localhost.log |

+------------------+-------------------------------+

2 rows in set (0.01 sec)

--開啟general log

mysql> set global general_log=1;

Query OK, 0 rows affected (0.05 sec)

mysql> show variables like 'genera%';

+------------------+-------------------------------+

| Variable_name | Value |

+------------------+-------------------------------+

| general_log | ON |

| general_log_file | /home/data/3306/localhost.log |

+------------------+-------------------------------+

--讓general log記錄到表中

show variables like 'general%';

show variables like '%log_output%';

set global general_log=0;

set global log_output='table';

show variables like '%log_output%';

set global general_log=1;

show variables like 'general%';

開啟slow log

slow_query_log={0|1}

slow_query_log_file #slow log名字

long_query_time #超過多少秒記錄,支援微妙,如果=0,則所有語句都記錄

如下情況不記錄

1) 管理命令不計

2) 沒有用索引不計

3) 從庫上執行的語句

例:

show global variables like 'slow%';

+---------------------+--------------------------------+

| Variable_name | Value |

+---------------------+--------------------------------+

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /home/data/3306/mysql-slow.log |

+---------------------+--------------------------------+

show global variables like 'long_query%';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

show global variables like 'log_output%'; 或 select @@global.log_output;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

set global log_output='table';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | TABLE |

+---------------+-------+

use mysql;

select * from slow_log;

mysql> show create table slow_log\G

*************************** 1. row ***************************

Table: slow_log

Create Table: 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(11) NOT NULL,

`rows_examined` int(11) NOT NULL,

`db` varchar(512) NOT NULL,

`last_insert_id` int(11) NOT NULL,

`insert_id` int(11) NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`sql_text` mediumtext NOT NULL,

`thread_id` bigint(21) unsigned NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'

1 row in set (0.01 sec)

set global log_output='file';

# mysqldumpslow 分析slow log

引數解釋

-s, 是表示按照何種方式排序

c: 訪問計數

l: 鎖定時間

r: 返回記錄

t: 查詢時間

al:平均鎖定時間

ar:平均返回記錄數

at:平均查詢時間

-t, 是top n的意思，即為返回前面多少條的資料；

-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；

基本用法
例：

得到返回記錄集最多的10個SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到訪問次數最多的10個SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照時間排序的前10條裡面含有左連線的查詢語句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建議在使用這些命令時結合 | 和more 使用，否則有可能出現刷屏的情況。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

MySQL的general_log和slow_log

相關文章