MySQL的general_log和slow_log

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