介绍
MySQL自带的客户端是一个简单的SQL shell，通过执行SQL语句，获取需要的数据库里的信息，使用也非常简单。不光在SQL语句方面，做为Shell脚本，也有编辑器一样灵活的用法。
登录MySQL客户端之后，可以通过help命令 或 ？查看支持的所有用法。
mysql> help
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file
For server side help, type 'help contents'
下面介绍又便利，能提高效率的用法。
1.pager
通过PAGER打印查询结果。pager + 任何Linux支持的命令。
配合翻页命令
#配合翻页命令：
mysql> pager less;
PAGER set to 'less'
mysql> show engine innodb status\G
1 row in set (0.01 sec)
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2024-01-04 11:14:29 139798003787520 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 25 srv_active, 0 srv_shutdown, 1450336 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21
OS WAIT ARRAY INFO: signal count 21
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
#退出：
mysql> nopager
PAGER set to stdout
grep过滤命令，通常show engine innodb status信息太多可以通过grep过滤内容：
mysql> pager grep TRANSACTIONS
PAGER set to 'grep TRANSACTIONS'
mysql> show engine innodb status\G
TRANSACTIONS
LIST OF TRANSACTIONS FOR EACH SESSION:
1 row in set (0.01 sec)
#退出 nopager 或 \n：
mysql> nopager
PAGER set to stdout
统计processlist中State的统计：
mysql> pager grep Sleep | wc -l
PAGER set to 'grep Sleep | wc -l'
mysql> show processlist;
3
5 rows in set, 1 warning (0.00 sec)
mysql> pager awk -F '|' '{print $6}' | sort | uniq -c | sort -r
PAGER set to 'awk -F '|' '{print $6}' | sort | uniq -c | sort -r'
mysql> show processlist;
3
2 Sleep
1 Query
1 Daemon
1 Command
4 rows in set, 1 warning (0.00 sec)
#-F和-X选项可以输出适合一个屏幕，以使其退出，不需要滚动时很方便：
mysql> pager less -n -i -S -F -X
2.system
执行系统shell命令。比如：top,iostat,df等命令行，只要操作系统能支持，就可以在终端里执行。这样就没必要退出MySQL操作界面，执行系统命令。
1)top命令用于实时显示进程的动态
mysql> system top
top - 15:35:13 up 87 days, 4:50, 1 user, load average: 0.00, 0.00, 0.00
Tasks: 95 total, 1 running, 94 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.3 us, 0.3 sy, 0.0 ni, 99.0 id, 0.0 wa, 0.3 hi, 0.0 si, 0.0 st
MiB Mem : 1826.3 total, 110.1 free, 722.6 used, 993.5 buff/cache
MiB Swap: 0.0 total, 0.0 free, 0.0 used. 918.8 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
248777 mysql 20 0 1103472 502964 23536 S 0.3 26.9 52:02.29 mysqld
253880 root 10 -10 249260 25724 18204 S 0.3 1.4 82:39.77 Monito
1 root 20 0 176872 7012 4340 S 0.0 0.4 1:19.57 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.94 kthreadd
。。。
2)iostat命令用于监视系统输入/输出设备的加载情况
mysql> system iostat -x -d 1
Linux 4.18.0-193.14.2.el8_2.x86_64 (schouse) 01/04/2024 _x86_64_ (1 CPU)
Device r/s w/s rkB/s wkB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
vda 0.05 0.45 3.91 7.20 0.00 0.03 0.54 6.92 10.94 2.17 0.00 81.00 16.13 0.30 0.01
Device r/s w/s rkB/s wkB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
vda 1.00 0.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 8.00 0.00 1.00 0.10
3)df检查文件系统的磁盘空间占用情况
mysql> system df -HT
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 942M 0 942M 0% /dev
tmpfs tmpfs 958M 472k 958M 1% /run
tmpfs tmpfs 958M 0 958M 0% /sys/fs/cgroup
/dev/vda1 xfs 43G 37G 6.9G 84% /
3.tee
tee功能是将命令的输出不仅发送到标准输出 (终端)，还可以将输出保存到一个外部的文件中。tee命令可以在MySQL命令行客户端中使用，并且可以随时开启或关闭。
#配置外部数据文件：
mysql> tee /tmp/queries.log
Logging to file '/tmp/queries.log'
mysql> show processlist;
+----+--------+-----------+------+---------+---------+-------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+-----------+------+---------+---------+-------------+------------------
| 7 |event_scheduler|localhost|NULL|Daemon|1467451 | Waiting on empty queue | NULL |
| 77 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+--------+-----------+------+---------+---------+-------------+------------------+
2 rows in set, 1 warning (0.00 sec)
#只有退出才能中断 退出：notee或 \t：
mysql> notee;
Outfile disabled.
#退出MySQL客户端，查看文件记录的内容：
shell> ll /tmp/queries.log
-rw-r--r-- 1 root root 737 Jan 4 15:53 /tmp/queries.log
shell> cat /tmp/queries.log
+----+--------+-----------+------+---------+---------+-------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+-----------+------+---------+---------+-------------+------------------
| 7 |event_scheduler|localhost|NULL|Daemon|1467451 | Waiting on empty queue | NULL |
| 77 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+--------+-----------+------+---------+---------+-------------+------------------+
2 rows in set, 1 warning (0.00 sec)
#结合pager 命令一起使用：
mysql> pager cat | tee /dr1/tmp/res.txt
4.edit
edit命令是前一个执行的SQL语句，放到编辑器里面进行编辑, 然后再执行。因为一些长的SQL语句换行后，或长的SQL语句在终端更改麻烦。这个时候edit就可以派上用场。
mysql> select * from employees limit 2;
+----+--------+------------+
| id | name | manager_id |
+----+--------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
+----+--------+------------+
2 rows in set (0.00 sec)
#编辑SQL语句：
mysql> edit
#Enter之后，进入编辑界面，可以随意更改SQL语句：
explain select * from employees limit 3
"/tmp/sqlMU669b" [noeol] 1L, 31C
#之后":wq!"关闭写入，退出之后";"写入结束符号。就会执行sql 语句：
mysql> edit
-> ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
5.prompt
将mysql客户端配置提示符。配置在my.cnf文件下。
下面是显示：用户+ip+具体时间+选择的数据
[mysql]
prompt="\\u@\\h: \R:\m:\s [\d]> "
这样就能知晓，操作的数据库和时间等信息。一目了然。也可以通过里边命令重置：
#登录数据库之后显示内容：
root@localhost: 10:34: [world]>
#进行重置：
root@localhost: 10:34: [world]> prompt
Returning to default PROMPT of mysql>
mysql> prompt (\u@\h) [\d]>\_
PROMPT set to 'prompt (\u@\h) [\d]>\_'
prompt (root@localhost) [world]>
总结
对于DBA来说这些客户端终端命令非常便利。合理使用，能带来意想不到的效果。
参考
https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html
