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 thestatus
(ors
) 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、总结
又是从官网学习的一天。收钱