Docker MySQL 8 慢查询日志监控详解

最新推荐文章于 2024-08-15 14:24:51 发布
最新推荐文章于 2024-08-15 14:24:51 发布
阅读量2.4k 收藏 8
点赞数 1
分类专栏： 服务端 文章标签： 数据库 mysql Docker 慢查询 slow_query
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zgpeace/article/details/104114000
版权

写作时间：2020-01-30
Docker: 2.1.0.2, MySQL: 8.0.9, MacOS: 10.15.2,

1. 慢查询日志概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

2. 参数说明

  1. slow_query_log ：是否开启慢查询日志，'ON'(1)表示开启，'OFF'(0)表示关闭。
  2. log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  3. slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  4. long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
  5. log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
  6. log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。
    log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
    MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。
    日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

3. 进入MySQL数据库

笔者从Docker启动MySQL容器开始，如需了解Docker如何安装MySQL，请参考：Docker安装MySQL 8 for Mac(图文详解)

% docker start docker-mysql
docker-mysql

进入MySQL容器

% docker exec -it docker-mysql bash

登录MySQL，

root@0d4a5de80d4d:/# mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL
...

4. 设置慢查询日志步骤

查看慢查询相关参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /var/lib/mysql/0d4a5de80d4d-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

设置方法
方法一：全局变量设置
将 slow_query_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON';

查询超过1秒就记录. (注意：此方法需要退出数据库，重新登录才会设置成功。并且容器停止后就会失效。)

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
...

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

方法二：配置文件设置
要想在容器中长期有效，需要退出数据库exit, 在进入MySQL容器里面修改配置文件/etc/mysql/my.cnf，
在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
long_query_time = 1

重启MySQL服务

mysql> exit
Bye
root@0d4a5de80d4d:/# exit
exit
12:09 zgpeace@zgpeaces-MacBook-Pro /Users/zgpeace
% docker restart docker-mysql
docker-mysql
12:09 zgpeace@zgpeaces-MacBook-Pro /Users/zgpeace
% docker exec -it docker-mysql bash
root@0d4a5de80d4d:/# mysql -u root -p123456
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

5. 测试慢查询

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看慢查询日志信息

# 退出sql，进入到容器里面
mysql> exit
Bye
# 慢查询日志记录
root@0d4a5de80d4d:/# cat /var/lib/mysql/0d4a5de80d4d-slow.log 
/usr/sbin/mysqld, Version: 8.0.19 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2020-01-30T01:23:23.342345Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 2.000844  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1580347401;
select sleep(2);

6. 日志的存储方式

log_output 参数是指定日志的存储方式。

  • log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。
  • log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
  • MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。

日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

查询日志存储方式

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

修改日志存储方式

mysql> set global log_output='FILE,TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

执行慢查询

mysql> select sleep(6);
+----------+
| sleep(6) |
+----------+
|        0 |
+----------+
1 row in set (6.04 sec)

查看慢查询记录

mysql> select * from mysql.slow_log;
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+
| start_time                 | user_host                 | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text                         | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+
| 2020-01-30 04:57:08.674648 | root[root] @ localhost [] | 00:00:06.000849 | 00:00:00.000000 |         1 |             1 |    |              0 |         0 |         1 | 0x73656C65637420736C656570283629 |        10 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+
1 row in set (0.01 sec)

7. 未使用索引的查询

系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.01 sec)

mysql> set global log_queries_not_using_indexes=1;
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.01 sec)

8. 管理表语句查询

系统变量log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志。

mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
+---------------------------+-------+
1 row in set (0.00 sec)

9. 统计慢查询总数

查询有多少条慢查询记录，可以使用系统变量Slow_queries。

mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+
1 row in set (0.01 sec)

10. 日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

退出用户登录MySQL到容器里面

mysql> exit
Bye

查看mysqldumpslow的帮助信息：

root@0d4a5de80d4d:/# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

参数解析：

  • -s, 是表示按照何种方式排序，
  1. c: 访问计数
  2. l: 锁定时间
  3. r: 返回记录
  4. t: 查询时间
  5. al:平均锁定时间
  6. ar:平均返回记录数
  7. at:平均查询时间
  • -t, 是top n的意思，即为返回前面多少条的数据；
  • -g, 后边可以写一个正则匹配模式，大小写不敏感的；

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 path/logfile

笔者机器举例

root@0d4a5de80d4d:/# mysqldumpslow -s r -t 10 /var/lib/mysql/0d4a5de80d4d-slow.log

Reading mysql slow query log from /var/lib/mysql/0d4a5de80d4d-slow.log
Count: 4  Time=4.25s (17s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 path/logfile

笔者机器举例

root@0d4a5de80d4d:/# mysqldumpslow -s c -t 10 /var/lib/mysql/0d4a5de80d4d-slow.log

Reading mysql slow query log from /var/lib/mysql/0d4a5de80d4d-slow.log
Count: 4  Time=4.25s (17s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” path/logfile

笔者机器举例

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/0d4a5de80d4d-slow.log

Reading mysql slow query log from /var/lib/mysql/0d4a5de80d4d-slow.log
Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 path/logfile | more

笔者机器举例

root@0d4a5de80d4d:/# mysqldumpslow -s r -t 20 /var/lib/mysql/0d4a5de80d4d-slow.log | more

Reading mysql slow query log from /var/lib/mysql/0d4a5de80d4d-slow.log
Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.
Count: 4  Time=4.25s (17s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select sleep(N)

11. 清除慢查询日志表

清除日志的方法，实际上就是删掉老表重新建一个新的.

SET GLOBAL slow_query_log = 'OFF';

ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;

 CREATE TABLE `mysql`.`slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) 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` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

SET GLOBAL slow_query_log = 'ON';
DROP TABLE mysql.slow_log_drop;

12. 参考

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_long_query_time

https://www.cnblogs.com/luyucheng/p/6265594.html

https://www.cnblogs.com/kerrycode/p/5593204.HTML

https://blog.csdn.net/zgpeace/article/details/104100413

https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

https://blog.csdn.net/phker/article/details/83146676

AI架构师易筋
关注
专栏目录
docker 中查看mysql容器的日志
weixin_33138675的博客
08-04 140
我整理的一些关于【Docker】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://edu.51cto.com/surl=QsXoR2如何在 Docker 中查看 MySQL 容器的日志 在今天的文章中，我们将讨论如何查看 Docker 中 MySQL 容器的日志。这对新手开发者尤其重要，因为日志能够提...
Mysql 启动慢查询日志 （不用重启）
cuanxixia1496的博客
02-11 168
Mysql 启动慢查询日志 （不用重启） 1. 查看mysql系统参数 mysql> show variables like "%slow%"; +---------------------------+...
数据库性能定位-慢sql定位、sql优化（docker容器实战）
最新发布
qq_35283902的博客
08-15 937
安装好mysql数据之后，创建的时候，要注意选择 字符集编码。如果没有选择好，你的库表存中文的时候，会字符集乱码。选择utf8mb4.建表的时候，存储引擎mysql5.7及以后数据库，表的默认存储引擎 InnoDB从磁盘的读写速度而言，MyISAM的表 速度要快一些InnoDB存储引擎，存储追求事务的准确性，准确率要比MyISAM要高。数据库自身有很多配置参数，参数可以改变数据库的性能。
docker中mysql开启日志
wangzhneg123的博客
04-01 5424
目录 1.查看安mysql容器状态 2.进入容器内部 3.登录mysql数据库 4.查看binlog日志是否开启，查看后关闭mysql 5.编辑配置文件开启binlog，设置log文件 5.1 先在 /etc 文件夹下查找到mysqld.cnf文件路径 5.2 编辑mysqld.cnf文件 6.退出容器 7.重启mysql容器，且查看是否启动成功 8.查看binlog 日志状态是否开启 9.查看日志 1.查看安mysql容器状态 doc...
Docker Mysql容器--开启binlog日志
热门推荐
Harris的博客
03-27 2万+
        使用Docker容器也有一段时间了，主要是项目中有用到，现有需求开启用Docker容器启动的mysql数据库的binlog，以作日志记录 和数据恢复，我们了解了MySQL的binlog日志的开启方式以及binlog日志的一些原理和常用操作，我们知道，binlog有两大作用，一个是使用binlog恢复数据，另一个就是用来做主从复制。本篇笔记就是来记录如何使用开启binlog日志和做数...
Docker中的mysql开启慢查询日志
hit、run
08-26 1725
Docker中的mysql开启慢查询日志
MySQL慢查询日志slowlog
qq_34595089的博客
01-02 2047
本文介绍MySQL的慢查询日志的定义，参数配置和解析的工具。
Docker mysql 主从配置详解及实例
09-09
docker run --name mysql-slave -v /path/to/my-s.cnf:/etc/mysql/conf.d/my.cnf -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_SLAVE_START_HELPER=true -d mysql ``` 这里我们使用了卷挂载将配置文件映射到...
详解Docker方式实现MySql 主从复制（实践篇）
09-09
在MySQL主从复制中，binlog（binary log，二进制日志）记录了所有的DDL（Data Definition Language）和DML（Data Manipulation Language）语句（除了数据查询语句SELECT和SHOW等）。主服务器会记录binlog，并将这些...
mysql日志监控 zabbix_zabbix 监控mysql日志
weixin_39540023的博客
12-19 472
Zabbix-2.2.2监控MySQL的复制一.zabbix agentd的安装123456789101112131415161718192021groupadd zabbixuseradd-g zabbix -G zabbix -s/sbin/nologin-d/dev...文章科技小能手2017-11-12768浏览量权限问题导致zabbix无法监控mysql说说一个困扰自已两天的问题...
【云原生】docker swarm 使用详解
congge
06-16 8956
docker swarm使用详解
mysql日志监控 zabbix_zabbix监控mysql日志文件
weixin_39622760的博客
12-19 315
Zabbix-2.2.2监控MySQL的复制一.zabbix agentd的安装123456789101112131415161718192021groupadd zabbixuseradd-g zabbix -G zabbix -s/sbin/nologin-d/dev...文章科技小能手2017-11-12768浏览量zabbix之日志文件监控一、日志item介绍下面介绍zabbix另...
docker创建mysql8.0.27容器，并挂载配置文件，数据文件，日志文件
pengpm的博客
11-23 354
docker创建mysql8.0.27容器，并挂载配置文件，数据文件，日志文件
docker mysql容器如何开启慢查询日志
phpstory的博客
07-14 831
一、查看所有容器 二、进入容器 三、输入用户名密码登陆mysql 修改完毕，进行测试select 查询。 四、退出容器，查看慢查询日志 详情参考 https://www.jb51.net/article/241552.htm五、永久开启（数据库服务重启后不失效）修改配置文件my.cnf，在[mysqld]下的下方加入 [mysqld] slow_query_log = ON slow_query_log_file = /var/lib/mysql/tmp_slow.log //linux long
Mysql 慢查询日志入门学习
风雪夜归人的博客
11-27 271
1. Msql 慢查询日志 show variables like '%query%'; slow_query_log ： 表示是否开启慢查询日志记录 （OFF关闭 ON开启） slow_query_log_file：慢查询日志文件存储地址（docker情况下该地址是docker容器中的地址） ...
Docker部署wordpress
wangshui898的专栏
05-18 166
创建数据目录 mkdir -pv /data/wordpress/mysql/{data,conf,logs} chown 999.999 /data/wordpress/mysql/ -R mkdir -pv /data/wordpress/html chown 33.33 /data/wordpress/html -R Mysql配置文件 vim /data/wordpress/mysql/conf/my.cnf [client] port = 3306 socket = /var/run/mysql
MySQL 慢查询及SQL执行时间监控
qq_56754651的博客
08-13 4828
MySQL的慢查询，及mysql优化概念
MYSQL8.0优化
天行++的JAVA空间
01-13 1842
MYSQL8.0优化 tmp_table_size优化 数据库连接突然增多到1000的问题，查看了一下，未有LOCK操作语句。但是明显有好多copy to tmp table的SQL语句，这条语读的时间比较长，且这个表会被加读锁，相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句，会造成更多的语句被阻塞。连接太多造成mysql处理慢。copy to...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值