MySQL数据库之慢查询日志
一、开启慢查询日志
通过show global variables like '%slow%' #查看MySQL慢查询日志是否开启
[root@mysqlmaster01 ~]# mysql --login-path=mysql57
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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 | ON |
| slow_query_log_file | slow.log |
+---------------------------+----------+
5 rows in set (0.01 sec)
show variables like "long_query_time"; #查看MySQL慢查询时间设置，默认2秒（mysql5.7）
mysql> show global variables like 'long%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
- log-queries-not-using-indexes = on #记录未使用索引的查询
mysql> show global variables like '%queri%';
+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
+----------------------------------------+-------+
2 rows in set (0.00 sec)
mysql> show global variables like 'log%slow%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
+---------------------------+-------+
2 rows in set (0.00 sec)
二） 安装mysqlsla分析慢查询日志
[root@mysqlmaster01 ~]#yum install wget perl perl-DBI perl-DBD-MySQL mysql perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan perl-Time-HiRes
[root@mysqlmaster01 ~]# wget http://soft.51yuki.cn/mysqlsla-2.03.tar
[root@mysqlmaster01 ~]# tar xf mysqlsla-2.03.tar
[root@mysqlmaster01 ~]# cd mysqlsla-2.03
[root@mysqlmaster01 mysqlsla-2.03]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla
[root@mysqlmaster01 mysqlsla-2.03]# make && make install
案例：
#查询记录最多的20个sql语句，并写到select.log中去 mysqlsla -lt slow --sort t_sum --top 20 /data/mysql/127-slow.log >/tmp/select.log #统计慢查询文件为/data/mysql/127-slow.log的所有select的慢查询sql，并显示执行时间最长的100条sql，并写到sql_select.log中去 mysqlsla -lt slow -sf "+select" -top 100 /data/mysql/127-slow.log >/tmp/sql_select.log #统计慢查询文件为/data/mysql/127-slow.log的数据库为mydata的所有select和update的慢查询sql，并查询次数最多的100条sql，并写到sql_num.sql中去 mysqlsla -lt slow -sf "+select,update" -top 100 -sort c_sum -db mydata /data/mysql/127-slow.log >/tmp/sql_num.log
最新文章
- 设置二级域名共享一级域名Cookie和删除共享Cookie
- BizTalk动手实验（一）安装BizTalk Server 2010开发环境
- Creater中选择一行的方法
- Resource is out of sync with the file system的解决办法
- oracle中的dual表详解
- __FILE__,__LINE__,FUNCTION__
- 2016 ACM/ICPC Asia Regional Qingdao Online 1002 Cure
- HTML最基础的入门(上)
- GoWorld – 用Golang写一个分布式可扩展、可热更的游戏服务器
- Python 导入模块
- java中的notify和notifyAll有什么区别？
- day12函数，三元表达式 ，列表推导式 ，字典推导式，函数对象，名称空间与作用域，函数的嵌套定义
- http摘要认证
- gentoo qt-creator no examples
- python导入requests库一直报错原因总结 （文件名与库名冲突）
- MFS故障测试及维护总结
- PAT A1029 Median （25 分）——队列
- 基于nopCommerce的开发框架（附源码）
- 【LOJ】#2230. 「BJOI2014」大融合
- Win10 C盘根目录权限