MySQL Slow Query Log and MySQL 8.0.19

2024年 4月 24日 59.9k 0

MySQL Slow Query Log and MySQL 8.0.19

1、需求概述

今天有个客户想访问慢查询日志来寻找查询性能低下的SQL语句,但是他无法访问慢查询日志文件,想从performance_schema中去找,虽然可以找到一些有用的信息,但不如慢查询日志的效率高。所以建议使用慢查询日志表来完成。在调整参数之后,发现sql_text的内容是16进制的,如下:

mysql> select sql_text from mysql.slow_log limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_text |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x73656C656374202A2066726F6D20736274657374312077686572652063206C696B6520272531383532323835363333382D36303732303534383837312D37343337393631323538382527 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

影响中应该是明文的,也觉得很奇怪,不知道哪个版本变更了于是检查了下Release Notes。

2、Check MySQL 8.0.19 Release Notes

链接:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

  • When the mysql client operates in interactive mode, the --binary-as-hex option now is enabled by default. In addition, output from the status (or s) command includes this line when the option is enabled implicitly or explicitly:

    Binary data as: Hexadecimal

    To disable hexadecimal notation, use --skip-binary-as-hex (Bug #24432545, WL #13038)

3、MySQL Cli访问

[root@mydb01 ~]# mysql -e 'select sql_text from mysql.slow_log limit 1'
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_text |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x73656C656374202A2066726F6D20736274657374312077686572652063206C696B6520272531383532323835363333382D36303732303534383837312D37343337393631323538382527 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+

[root@mydb01 ~]# mysql --skip-binary-as-hex -e 'select sql_text from mysql.slow_log limit 1'
+----------------------------------------------------------------------------+
| sql_text |
+----------------------------------------------------------------------------+
| select * from sbtest1 where c like '%18522856338-60720548871-74379612588%' |
+----------------------------------------------------------------------------+

不使用**–skip-binary-as-hex**,我们会发现Binary data as:Hexadecimal

4、MySQL 客户端工具访问

看图示

4.1、MySQL Workbench

4.2、Navicat

5、总结

又是从官网学习的一天。收钱

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论