MySQL客户端指令用法的探索

数据运维 2023-08-26 法医 手机阅读

对于数据库的操作可以用图形界面，也可以用指令，但很多运维同学和技术高手更喜欢指令操作，敲键盘更有感觉。

更重要的是，虽然图形界面封装了各种指令，操作可视化，学习成本低，但是当出现问题，由于中间多了一层往往不容易定位问题，会对问题诊断，产生一些混淆。

技术社群的这篇文章《技术分享 | 一次有趣的 MySQL 客户端命令用法的探索》简单介绍了一些运维时MySQL客户端中经常使用的一些小技巧。这些小技巧非专业DBA基本不会用到，专业的DBA必备。

MySQL客户端的内置命令有以下这些，我们会探索其中6个，

  1. List of all MySQL commands:

  2. Note that all text commands must be first on line and end with ';'

  3. ? (?) Synonym for `help'.

  4. clear (c) Clear the current input statement.

  5. connect (r) Reconnect to the server. Optional arguments are db and host.

  6. delimiter (d) Set statement delimiter.

  7. edit (e) Edit command with $EDITOR.

  8. ego (G) Send command to mysql server, display result vertically.

  9. exit (q) Exit mysql. Same as quit.

  10. go (g) Send command to mysql server.

  11. help (h) Display this help.

  12. nopager (n) Disable pager, print to stdout.

  13. notee (t) Don't write into outfile.

  14. pager (P) Set PAGER [to_pager]. Print the query results via PAGER.

  15. print (p) Print current command.

  16. prompt (R) Change your mysql prompt.

  17. quit (q) Quit mysql.

  18. rehash (#) Rebuild completion hash.

  19. source (.) Execute an SQL script file. Takes a file name as an argument.

  20. status (s) Get status information from the server.

  21. system (!) Execute a system shell command.

  22. tee (T) Set outfile [to_outfile]. Append everything into given outfile.

  23. use (u) Use another database. Takes database name as argument.

  24. charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

  25. warnings (W) Show warnings after every statement.

  26. nowarning (w) Don't show warnings after every statement.

  27. resetconnection(x) Clean session context.

1. pagerpager的作用类似于Linux的管道符，可以把输出给另外一个命令作为输入。强大之处在于这个管道符接的命令是Linux命令，我们可以利用我们熟悉的Linux命令实现各种骚操作。话不多说，直接来几个例子。

翻页

  1. mysql> pager less

  2. PAGER set to 'less'

  3. mysql> show engine innodb statusG

  4. 1 row in set (0.00 sec)

innodb status的输出很长，接Linux命令less实现翻页，同样地根据您个人喜好，也可以用more。

查找搜索

一般来说我们想查看目前有哪些正在跑的慢SQL，可以用以下命令查询information_schema中的processlist表，这要求你熟悉元数据表。

  1. mysql> select * from information_schema.PROCESSLIST where COMMAND='Query';

  2. +------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+

  3. | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |

  4. +------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+

  5. | 3508 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.PROCESSLIST where COMMAND='Query' |

  6. | 3463 | root | localhost | NULL | Query | 233 | User sleep | select sleep(1000) |

  7. | 3465 | root | localhost | NULL | Query | 228 | User sleep | select sleep(2000) |

  8. | 3439 | root | localhost | NULL | Query | 235 | User sleep | select sleep(1000) |

  9. +------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+

  10. 4 rows in set (0.00 sec)

但用pager方法的话，我们可以利用Linux的grep命令，更高效地获取。

  1. mysql> pager grep Query

  2. PAGER set to 'grep Query'

  3. mysql> show processlist;

  4. | 3439 | root | localhost | NULL | Query | 23 | User sleep | select sleep(1000) |

  5. | 3463 | root | localhost | NULL | Query | 21 | User sleep | select sleep(1000) |

  6. | 3465 | root | localhost | NULL | Query | 16 | User sleep | select sleep(2000) |

  7. | 3473 | root | localhost | NULL | Query | 0 | starting | show processlist |

  8. 17 rows in set (0.00 sec)

甚至可以直接统计数量。

  1. mysql> pager grep Query |wc -l

  2. PAGER set to 'grep Query |wc -l'

  3. mysql> show processlist;

  4. 4 # pager

  5. Default pager wasn't set, using stdout.

  7. #关闭pager

  8. mysql> nopager

  9. PAGER set to stdout

  11. #退出客户端，重新连接

  12. mysql> quit

  13. Bye

2. teetee和Linux的tee命令是一样的。在输出到stdout同时可以指定同时输出到另外一个文件。使用他主要可以实现三个功能: 导数据、审计、记录操作。

场景一：快速导出数据

  1. mysql> tee tmp/general_log

  2. Logging to file '/tmp/general_log'

  3. mysql> select * from general_log where event_time >'2019-11-28 00:00:00';

  4. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+

  5. | event_time | user_host | thread_id | server_id | command_type | argument |

  6. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+

  7. | 2019-11-28 16:49:15.459116 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  8. | 2019-11-28 16:49:18.604167 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  9. | 2019-11-28 16:49:19.299166 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  10. | 2019-11-28 16:49:20.283979 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  11. | 2019-11-28 16:49:20.844283 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  12. | 2019-11-28 16:49:21.289261 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  13. | 2019-11-28 16:49:49.164062 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  14. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+

  15. 7 rows in set (0.00 sec)

  1. [root@chenyi tmp]# cat general_log

  2. mysql> select * from general_log where event_time >'2019-11-28 00:00:00';

  3. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+

  4. | event_time | user_host | thread_id | server_id | command_type | argument |

  5. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+

  6. | 2019-11-28 16:49:15.459116 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  7. | 2019-11-28 16:49:18.604167 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  8. | 2019-11-28 16:49:19.299166 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  9. | 2019-11-28 16:49:20.283979 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  10. | 2019-11-28 16:49:20.844283 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  11. | 2019-11-28 16:49:21.289261 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  12. | 2019-11-28 16:49:49.164062 | root[root] @ localhost [] | 5 | 153307 | Query | select * from general_log where event_time >'2019-11-28 00:00:00' |

  13. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+

  14. 7 rows in set (0.00 sec)

  16. mysql> q

场景二：审计

配置my.cnf

  1. [mysql]

  2. tee=/tmp/tee.log

可以当客户端审计用,记录了客户端所有屏幕输出。(当然，这不是真正意义上的MySQL审计)"客户端审计日志"如下，

  1. [root@chenyi tmp]# cat tmp/tee.log

  2. Welcome to the MySQL monitor. Commands end with ; or g.

  3. Your MySQL connection id is 6

  4. Server version: 5.7.27-log MySQL Community Server (GPL)

  6. Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

  8. Oracle is a registered trademark of Oracle Corporation and/or its

  9. affiliates. Other names may be trademarks of their respective

  10. owners.

  12. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

  14. mysql> nihao;

  15. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nihao' at line 1

  16. mysql> q

提醒！使用这招要小心有人误操作，select 了大量的数据，导致文件写满磁盘。

场景三：临时记录操作

去客户那边排查问题，可以考虑先开tee，然后排查故障完毕后，看着tee.log去编写故障分析邮件。

关闭tee

  1. notee;

所以刚才上面说的用tee审计作用不大，因为可以关闭！

3. edit相当于在MySQL中使用vi命令来编辑SQL语句。这个功能比较鸡肋，即使对于vi党来说，效率也没有多少提升。默认打开edit时，是编辑上一条SQL命令，退出vi后，输入“；”后回车就会执行在vi中编辑的SQL。

  1. mysql> select * from information_schema.PROCESSLIST where COMMAND='Query';

  2. +------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+

  3. | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |

  4. +------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+

  5. | 3508 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.PROCESSLIST where COMMAND='Query' |

  6. | 3463 | root | localhost | NULL | Query | 233 | User sleep | select sleep(1000) |

  7. | 3465 | root | localhost | NULL | Query | 228 | User sleep | select sleep(2000) |

  8. | 3439 | root | localhost | NULL | Query | 235 | User sleep | select sleep(1000) |

  9. +------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+

  10. 4 rows in set (0.00 sec)

  11. mysql> edit

不过有趣的是，使用edit可以隐藏客户端操作记录，实现“黑客操作”，下面我们来看看，

  1. mysql> edit

  2. -> ;

  3. PAGER set to 'grep -v 我是黑客 >>/tmp/1.log'

  4. mysql> edit

  5. -> ;

  6. Query OK, 0 rows affected (0.00 sec)

  8. Query OK, 0 rows affected (0.00 sec)

  10. mysql> edit

  11. -> ;

  12. 6 rows in set (0.00 sec)

  14. mysql> q

上面是我在控制台执行的SQL命令，相信大家都不知道我执行了什么。并且下一个用户使用我的MySQL客户端登录时只能看到以下四条命令行，

  1. edit;

  2. edit;

  3. edit;

  4. q

这就隐藏了我的SQL命令行操作了。当我们开启了前面我们说的"客户端审计日志"，我们可以看到以下内容，

  1. [root@chenyi tmp]# cat /tmp/tee.log

  2. Welcome to the MySQL monitor. Commands end with ; or g.

  3. Your MySQL connection id is 9

  4. Server version: 5.7.27-log MySQL Community Server (GPL)

  6. Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

  8. Oracle is a registered trademark of Oracle Corporation and/or its

  9. affiliates. Other names may be trademarks of their respective

  10. owners.

  12. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

  14. mysql> edit

  15. -> ;

  16. PAGER set to 'grep -v 我是黑客 >>/tmp/1.log'

  17. mysql> edit

  18. -> ;

  19. Query OK, 0 rows affected (0.00 sec)

  21. Query OK, 0 rows affected (0.00 sec)

  23. mysql> edit

  24. -> ;

  25. +------------------+---------------+

  26. | user | host |

  27. +------------------+---------------+

  28. | heike | % |

  29. | root | 10.168.65.% |

  30. | mysql.session | localhost |

  31. | mysql.sys | localhost |

  32. | root | localhost |

  33. | chenyi | localhost |

  34. +------------------+---------------+

  35. 6 rows in set (0.00 sec)

  38. mysql> q

这个日志，可以发现我有一个pager操作，并且最后一个edit后有查询结果输出，但具体三个edit里的实际操作，我们都无从得知。最后一个edit后有查询结果输出说明了"tee 审计方式"会忽略pager的过滤作用，原输出被审计下来了，但执行的原SQL命令躲过了审计，被隐藏起来了。现在，我揭晓一下，

  1. #第一个edit

  2. pager grep -v 我是黑客 >>/tmp/1.log

  4. #第三个edit

  5. select user,host from mysql.user;

第二个edit我们目前还不知道是什么操作。当然我们实在要排查，可以尝试解析binlog碰碰运气，看是否是写入操作。如果安装了mcafee的审计插件，我们在审计插件也可以看到。

mcafee：https://bintray.com/mcafee/mysql-audit-plugin/release

  1. {

  2. "msg-type": "activity",

  3. "date": "1574932159871",

  4. "thread-id": "9",

  5. "query-id": "129",

  6. "user": "root",

  7. "priv_user": "root",

  8. "ip": "",

  9. "host": "localhost",

  10. "connect_attrs": {

  11. "_os": "linux-glibc2.12",

  12. "_client_name": "libmysql",

  13. "_pid": "6004",

  14. "_client_version": "5.7.27",

  15. "_platform": "x86_64",

  16. "program_name": "mysql"

  17. },

  18. "pid": "6004",

  19. "os_user": "root",

  20. "appname": "/usr/local/mysql/bin/mysql",

  21. "status": "0",

  22. "cmd": "create_user",

  23. "query": "create user heike@'%' identified by '***'"

  24. }

  25. {

  26. "msg-type": "activity",

  27. "date": "1574932159874",

  28. "thread-id": "9",

  29. "query-id": "130",

  30. "user": "root",

  31. "priv_user": "root",

  32. "ip": "",

  33. "host": "localhost",

  34. "connect_attrs": {

  35. "_os": "linux-glibc2.12",

  36. "_client_name": "libmysql",

  37. "_pid": "6004",

  38. "_client_version": "5.7.27",

  39. "_platform": "x86_64",

  40. "program_name": "mysql"

  41. },

  42. "pid": "6004",

  43. "os_user": "root",

  44. "appname": "/usr/local/mysql/bin/mysql",

  45. "status": "0",

  46. "cmd": "grant",

  47. "query": "grant all on *.* to heike@'%'"

  48. }

同样的，第三个edit，由于是select操作，也会被审计插件记录到。

  1. {

  2. "msg-type": "activity",

  3. "date": "1574932192709",

  4. "thread-id": "9",

  5. "query-id": "131",

  6. "user": "root",

  7. "priv_user": "root",

  8. "ip": "",

  9. "host": "localhost",

  10. "connect_attrs": {

  11. "_os": "linux-glibc2.12",

  12. "_client_name": "libmysql",

  13. "_pid": "6004",

  14. "_client_version": "5.7.27",

  15. "_platform": "x86_64",

  16. "program_name": "mysql"

  17. },

  18. "pid": "6004",

  19. "os_user": "root",

  20. "appname": "/usr/local/mysql/bin/mysql",

  21. "rows": "35",

  22. "status": "0",

  23. "cmd": "select",

  24. "objects": [

  25. {

  26. "db": "mysql",

  27. "name": "user",

  28. "obj_type": "TABLE"

  29. }

  30. ],

  31. "query": "select user,host from mysql.user"

  32. }

可以看出，审计插件的审计功能可以审计到服务器真实执行的SQL，这是tee审计方式不可比拟的。但审计插件并没有发现我的pager操作，所以并不知道我导出了数据，只有tee审计方式发现了我导出了数据。

  • 前面例子，我们可以看到，审计插件的审计日志里，密码是不显示的。

  • 而我们知道binlog里，密码也是加密的。

  • MySQL客户端的历史记录里，是不会记录带  identified by 'xxx'  语句的。

所以，以上方式都不会泄露密码。

唯一会泄露明文密码的地方，是"tee审计方式"。而经过测试，结论是使用edit可以让明文密码绝不泄露。所以，edit操作可以隐藏密码。最后，我揭晓一下，我第二edit操作是，

  1. create user heike@'%' identified by 'Heike@2019';

  2. grant all on *.* to heike@'%';

4. system不退出MySQL客户端情况下执行Linux命令。

查看服务器IP

我一般用来确认IP地址。

  1. mysql> system ip a

  2. 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000

  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

  4. inet 127.0.0.1/8 scope host lo

  5. valid_lft forever preferred_lft forever

  6. inet6 ::1/128 scope host

  7. valid_lft forever preferred_lft forever

  8. 2: eth0: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

  9. link/ether 02:00:0a:ba:41:0f brd ff:ff:ff:ff:ff:ff

  10. inet 10.186.65.15/24 brd 10.186.65.255 scope global eth0

  11. valid_lft forever preferred_lft forever

  12. inet6 fe80::aff:feba:410f/64 scope link

  13. valid_lft forever preferred_lft forever

5. status查看MySQL服务器状态。

  1. mysql> status

  2. --------------

  3. /usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.27, for linux-glibc2.12 (x86_64) using EditLine wrapper

  5. Connection id: 11

  6. Current database:

  7. Current user: root@localhost

  8. SSL: Not in use

  9. Current pager: stdout

  10. Using outfile: '/tmp/tee.log'

  11. Using delimiter: ;

  12. Server version: 5.7.27-log MySQL Community Server (GPL)

  13. Protocol version: 10

  14. Connection: Localhost via UNIX socket

  15. Server characterset: utf8mb4

  16. Db characterset: utf8mb4

  17. Client characterset: utf8

  18. Conn. characterset: utf8

  19. UNIX socket: /tmp/mysql3307.sock

  20. Uptime: 1 hour 15 min 32 sec

  22. Threads: 1 Questions: 145 Slow queries: 0 Opens: 195 Flush tables: 1 Open tables: 188 Queries per second avg: 0.031

  23. --------------

基本上去客户那处理问题，登录MySQL后第一个执行的命令行就是这个了。一般用s这个快捷命令。这里可以获取大量想要的信息。

  • MySQL连接的客户端是5.7.27

  • MySQLServer 的版本是5.7.27 社区版

  • 开启了"客户端审计日志"，输出到/tmp/tee.log

  • 我连接数据库用的是sock方式

  • 一般来说不能获取连接的数据库端口信息，但这里的命名我甚至获取了端口信息！

  • 我pager没有设置，用的默认stdout，标准输出到屏幕

  • 数据库开机运行时间1小时15分钟，数据库被重启过了？

  • 数据库连接线程为1个，没有程序或人连数据库，只有我

  • Questions数145个。

  • Slow queries为0，没有慢查询

  • Opens数195，没有快达到65536的上限

  • Open tables数188，没有快达到65536的上限

  • Queries per second avg，这个是QPS，但他的算法是除以uptime时间，所以并不能反映现在服务器的负荷，没什么用

这里我要特别说明两个信息的获取：1. 连接数如果我只想知道服务器连接有没有打满，那么我并不需要  show processlist ，直接  s ，就知道了。2. QPS我这里说的QPS指的是Questions per second。方法一从status命令获取

  1. s select sleep(1); s

瞬时服务器真实QPS等于两次  s  输出的Questions差值再减4，因为  s  本身会造成3个Questions数，而  select sleep(1) ；会造成1个Questions数。方法二show global status获取

  1. show global status like 'Questions';select sleep(1);show global status like 'Questions';

瞬时服务器真实QPS等于两次  show global status like 'Questions' ；输出的差值再减2，因为  show global status like 'Questions' ；本身会造成1个Questions数，而   select sleep(1) ；会造成1个Questions数。方法三最佳实践，因为平时观察QPS并不是看瞬时的一个点，我们需要持续看，所以用mysqladmin方法是合适的。

  1. [root@chanyi tmp]# mysqladmin -uroot -proot -P3307 -S /tmp/mysql3307.sock -r -i 1 ext |grep -i 'question'

  2. mysqladmin: [Warning] Using a password on the command line interface can be insecure.

  3. | Questions | 162 |

  4. | Questions | 1 |

  5. | Questions | 1 |

  6. | Questions | 1 |

  7. | Questions | 1 |

  8. | Questions | 1 |

  9. | Questions | 1 |

  10. | Questions | 1 |

  11. | Questions | 1 |

  12. ^C

这个方法实际上也采用  show global status 。

瞬时服务器真实QPS其实是0，这个数字1来自于每秒一次的  show global status 。

6. prompt

修改MySQL提示登录提示符。

我一般会在两个情况使用它，

临时标记主从或ip地址

  1. #主库上

  2. mysql> prompt master> ;

  3. PROMPT set to 'master> '

  4. master>

  6. #从库上

  7. mysql> prompt slave> ;

  8. PROMPT set to 'slave> '

  9. slave>

让提示符更丰富

修改/etc/my.cnf配置文件

  1. [mysql]

  2. prompt=\U [\d]>

修改后的效果，

  1. root@localhost [(none)]>use test

  2. Reading table information for completion of table and column names

  3. You can turn off this feature to get a quicker startup with -A

  5. Database changed

  6. root@localhost [test]>

现在，MySQL客户端登录后可以方便清楚是哪个用户登录，切换到哪个数据库了。

最佳实践

修改/etc/my.cnf配置文件

  1. [mysql]

  2. prompt=\u@\h:\p \R:\m:\s [\d]>

修改后的效果，

  1. root@127.0.0.1:3308 01:42:58 [(none)]>use test

  2. Reading table information for completion of table and column names

  3. You can turn off this feature to get a quicker startup with -A

  5. Database changed

  6. root@127.0.0.1:3308 01:43:04 [test]>

经过这么设置，我们可以通过提示符就知道我们登录的是哪个数据库实例，还可以记录下时间。如果再配合前面所说的"客户端审计日志"的话，能记录下登录的数据库实例以及SQL的执行时间，简直完美。

  1. [root@chenyi tmp]# cat /tmp/tee.log

  2. Welcome to the MySQL monitor. Commands end with ; or g.

  3. Your MySQL connection id is 9

  4. Server version: 5.7.27-log MySQL Community Server (GPL)

  6. Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

  8. Oracle is a registered trademark of Oracle Corporation and/or its

  9. affiliates. Other names may be trademarks of their respective

  10. owners.

  12. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

  14. root@127.0.0.1:3308 11:42:58 [(none)]>use test

  15. Reading table information for completion of table and column names

  16. You can turn off this feature to get a quicker startup with -A

  18. Database changed

  19. root@127.0.0.1:3308 11:43:04 [test]>

  21. mysql> q

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：《MySQL运行时的可观测性》
《MySQL和MariaDB版本管理的历史背景及差异了解》《MySQL数据页损坏问题的场景》《MySQL导入导出数据表容量的一个问题场景》《查询字段的数量对查询效率的影响》
《定位磁盘性能问题的武器》
近期的热文：《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引：《公众号1200篇文章分类和索引》