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