mysql慢查询日志解析_mysql慢查询日志&&分析

开启 mysql 慢查询[命令开启]

long_query_time

定义慢查询时间

mysql> show variables like 'long_query_time';

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

| Variable_name | Value |

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

| long_query_time | 10.000000 |

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

1 row in set (0.00 sec)

mysql> set long_query_time=0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';

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

| Variable_name | Value |

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

| long_query_time | 0.000000 |

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

1 row in set (0.00 sec)

log_queries_not_using_indexes

将没有使用索引的SQL记录到日志文件

mysql> set log_queries_not_using_indexes=ON;

ERROR 1229 (HY000): Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL

mysql> set global log_queries_not_using_indexes=true;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';

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

| Variable_name | Value |

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

| log_queries_not_using_indexes | ON |

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

1 row in set (0.00 sec)

slow_query_log_file

设置日志文件路径

mysql> set slow_query_log_file='/vagrant/log/mysql/mysql-slow.log';

ERROR 1229 (HY000): Variable 'slow_query_log_file' is a GLOBAL variable and should be set with SET GLOBAL

mysql> set global slow_query_log_file='/vagrant/log/mysql/mysql-slow.log';

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'slow_query_log_file';

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

| Variable_name | Value |

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

| slow_query_log_file | /vagrant/log/mysql/mysql-slow.log |

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

1 row in set (0.00 sec)

slow_query_log

开启慢查询

mysql> show 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 | /usr/local/mysql/data/vagrant-c5-x86_64-slow.log |

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

5 rows in set (0.00 sec)

mysql> set slow_query_log=on;

ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

mysql> set global slow_query_log=on;

Query OK, 0 rows affected (0.09 sec)

mysql> show variables like 'slow_query_log';

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

| Variable_name | Value |

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

| slow_query_log | ON |

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

1 row in set (0.00 sec)

开启 mysql 慢查询[配置文件开启]

修改 mysql 的配置文件 my.cnf

在 [mysqld] 添加配置

long_query_time = 0.1 //100毫秒

log-slow-queries = /vagrant/log/mysql/mysql-slow.log

接着重启mysql

/etc/init.d/mysql restart

分析 mysql 慢查询

日志格式

分析工具

mysql 自带慢查询分析工具 mysqldumpslow

[vagrant@vagrant-c5-x86_64 bin]$ sudo ./mysqldumpslow -s r -t 20 /usr/local/mysql/data/vagrant-c5-x86_64-slow.log