mysql小技巧，mysql客户端操作技巧
转自：https://mp.weixin.qq.com/s/dM_Kr23h-yXo61uSf8uPNQ
引言
作为一个MySQL数据库从业者，我们最常用的工具就是mysql了，然而如何让它变的更好用，你了解吗？
mysql Client Commands
作为连接MySQL数据库的工具，mysql其实有很多非常有用的命令设置，有一些是我们日常使用的，比如\g、\G、\q，也有我们不太常用的\P、\T。今天分享一下我对这些设置的理解，希望能对大家在日常的工作中，有所帮助。
支持哪些设置
mysql提供了help命令，用以说明支持哪些命令的设置，我们先看一下
GreatDB Cluster[(none)]> help ;
For information about Percona products and services, visit:
http://www.percona.com/
Percona Server manual: http://www.percona.com/doc/percona-server/8.0/
For the MySQL Reference Manual: http://dev.mysql.com/
To buy Percona support, training, or other products, visit:
https://www.percona.com/
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.
source_decrypt Execute an encrypted script file. Takes file name, decrypt key as arguments.
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.
For server side help, type 'help contents'
?
显示帮助信息，列出所有支持的操作命令，这个最简单，就不做进一步说明了。
clear(\c)
MySQL手册介绍为清除当前输入的SQL语句，我个人更愿意理解为撤销已输入SQL的执行。看下面的例子：
GreatDB Cluster[test]> select * from tt1;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)
GreatDB Cluster[test]> delete from tt1
-> where id = 5
-> and c1 is not null
->
当我输入完上面的SQL时，发现逻辑未考虑全，不想执行这个SQL了，怎么办呢？这里有好几种解决办法：你可以ctrl+c 终止命令的执行，也可以关闭当前的mysql客户端，甚至关闭操作命令的电脑。然后有一种标准且简单的方式，就是在命令的最后加上\c，就可以实现：
GreatDB Cluster[test]> delete from tt1
-> where id = 5
-> and c1 is not null
-> \c
GreatDB Cluster[test]>
可以看到，在命令窗口最后输入\c后，SQL不会被执行，并且重新启动新的命令行接收客户端输入。需要注意的是：\c和需要取消的SQL间，不能有分隔符(默认为分号;)，否则会先执行分隔符前面的SQL，然后再执行\c，这时就达不到取消已输入SQL执行的效果了。
connect(\r)
重新连接数据库服务端，支持重连过程中，指定database名字和连接主机。
这个功能看起来好像没什么新奇的，客户端执行了SQL，就算连接超时了，默认情况下，自动就会重新连接服务端。
但是在MGR、主从复制场景下，如果所有实例的维护账号都具有相同的用户名、密码、端口，那么通过\r就很方便的切换多个实例进行维护，而不需要重新输入其他连接信息
GreatDB Cluster[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.82 |
+---------------+
1 row in set (0.00 sec)
GreatDB Cluster[test]> \r test 172.16.50.81
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 911698
Current database: test
GreatDB Cluster[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.81 |
+---------------+
1 row in set (0.00 sec)
GreatDB Cluster[test]>
重连信息中的ip地址，也可以是在/etc/hosts中配置的主机名
GreatDB Cluster[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.81 |
+---------------+
1 row in set (0.00 sec)
GreatDB Cluster[test]> \r test greatdb82
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 2460607
Current database: test
GreatDB Cluster[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.82 |
+---------------+
1 row in set (0.00 sec)
GreatDB Cluster[test]>
delimiter(\d)
自定义分隔符，在创建、修改存储过程、函数、触发器、事件、视图都会用到，替换用于替换默认的分号分隔符。
【常用】edit(\e) vi编辑，保留原格式编辑上一行SQL
官网解释说命令用于编辑当前输入SQL命令，默认的编辑器是vi，也可以通过设置环境变量EDITOR来改变成其他的编辑器，比如调整为vim编辑器export EDITOR=$(which vim)。
有这个命令后，输出错误的SQL，就不需要再用\c终止了，而是直接在其后加上\e进行编辑，修改成正确的后，再执行。
比如我实际上想要执行的命令中tt2表不存在，那么只需要通过\e更新SQL语句中的表名字就可以继续执行，不需要再重新编辑整条SQL。
GreatDB Cluster[test]> select * from test.tt2 \e
vi中替换表名字的操作就不再演示了，编辑后的执行情况如下：
GreatDB Cluster[test]> select * from test.tt2 \e
-> ;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)
GreatDB Cluster[test]>
另外在测试过程中，还发现\e可以对上一次执行的SQL进行编辑，即单独执行\e时，其实是对上次执行的SQL命令做编辑。
GreatDB Cluster[test]> \e
select * from test.tt1
这里有人就会想到，我按向上的方向键，也能编辑上一条SQL呀，何必这么麻烦，这里有下面的SQL情况，有多次换行，或者结构更复杂的SQL
GreatDB Cluster[test]> select * from test.z1
-> join test.z2
-> using(id)
-> limit 3;
+------+------+------+
| id | name | name |
+------+------+------+
| 1 | 11 | 11 |
| 2 | 22 | 22 |
| 11 | 11 | 11 |
+------+------+------+
3 rows in set (0.00 sec)
GreatDB Cluster[test]>
如果按上的方向键，整个SQL语句结构就发生了变化，本来规整的SQL语句，变成了很长的一行，很难调整，但是使用\e就不会有这种情况，会保持之前的输入结构，更方便语句的调整
GreatDB Cluster[test]> select * from test.z1
-> join test.z2
-> using(id)
-> limit 3;
+------+------+------+
| id | name | name |
+------+------+------+
| 1 | 11 | 11 |
| 2 | 22 | 22 |
| 11 | 11 | 11 |
+------+------+------+
3 rows in set (0.00 sec)
GreatDB Cluster[test]> \e
select * from test.z1
join test.z2
using(id)
limit 3
另外，有同学经常苦恼，我昨天才输入的SQL命令，如果需要重新执行，又得输入一遍，其实这里也有一个小技巧，通过快捷键CTRL+R，输入SQL中的关键信息(比如表名字)，就能快速翻出对应的SQL，如果匹配出来的行不是想要的SQL，可以继续按CTRL+R继续上翻，直到查找到需要的SQL，当然，也有可能需要的SQL已经被清理出历史记录中，这种情况是无法被找到的。
【常用】ego(\G)
提交SQL语句到服务器，并且将返回的数据列式显示。
exit(\q)
退出mysql客户端连接。
go(\g)
提交SQL语句到服务器。
【常用】pager(\P) \P grep -Ev "Sleep"
设置pager规则，对查询结果执行pager规则后，再输出结果。这也是一个非常有用的设置，我们常常因为processlist结果太多而不方便查看，需要通过单行grep或者查询information_schema.processlist来实现，有这个工具后，我们看看效果
GreatDB Cluster[test]> show processlist;
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| 6 | event_scheduler | localhost | NULL | Daemon | 5019248 | Waiting on empty queue | NULL | 5019247326 | 0 | 0 |
| 26 | system user | | NULL | Connect | 5018577 | waiting for handler commit | Group replication applier module | 5018576436 | 0 | 0 |
| 29 | system user | | NULL | Query | 1010 | Slave has read all relay log; waiting for more updates | NULL | 1010045 | 0 | 0 |
| 30 | system user | | NULL | Query | 1010 | Waiting for an event from Coordinator | NULL | 1010045 | 0 | 0 |
| 31 | system user | | NULL | Query | 100958 | Waiting for an event from Coordinator | NULL | 100956966 | 0 | 0 |
| 32 | system user | | NULL | Query | 100958 | Waiting for an event from Coordinator | NULL | 100956966 | 0 | 0 |
| 33 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576419 | 0 | 0 |
| 34 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576418 | 0 | 0 |
| 35 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576417 | 0 | 0 |
| 36 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576415 | 0 | 0 |
| 37 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576413 | 0 | 0 |
| 31568 | greatdb.sys | localhost | NULL | Sleep | 4861529 | NULL | PLUGIN | 4861528879 | 0 | 0 |
| 2460607 | greatdb | 172.16.50.81:59062 | test | Query | 0 | init | show processlist | 0 | 0 | 0 |
| 2466518 | greatdb.sys | localhost | NULL | Sleep | 1 | NULL | PLUGIN | 947 | 0 | 1 |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
14 rows in set (0.00 sec)
GreatDB Cluster[test]> \P grep -vE 'system user|Sleep'
PAGER set to 'grep -vE 'system user|Sleep''
GreatDB Cluster[test]> show processlist;
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| 6 | event_scheduler | localhost | NULL | Daemon | 5019255 | Waiting on empty queue | NULL | 5019255045 | 0 | 0 |
| 2460607 | greatdb | 172.16.50.81:59062 | test | Query | 0 | init | show processlist | 0 | 0 | 0 |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
14 rows in set (0.01 sec)
GreatDB Cluster[test]>
pager后面可以跟很多的shell命令，比如awk、grep、wc ，对结果集的处理，当SQL不方便处理而shell方便处理时，不用再使用-e参数每次都进行连接，然后处理，比如主从结构对Slave_IO_Running、Slave_SQL_Running的监控，可设置pager grep -E 'Slave_IO_Running|Slave_SQL_Running'。
pager的另外一个用途：经常有同学问，一些字段中是否有包含某些特定字符串，正常的SQL处理是需要写成col1 like '%abc%' or col2 like '%abc%'...，写出来后SQL结构相对复杂，通过pager设置pager grep -i abc; select * from tab即可方便查看。
nopager(\n)
pager的设置是整个session生命周期内都生效，通过执行nopager进行关闭设置
prompt(\R)
修改mysql客户端的命令行提示信息，支持显示的提示信息非常多，具体可以参见MySQL官网介绍。修改mysql客户端的命令行提示信息，也有好几种方式：
1、设置操作系统环境变量MYSQL_PS1 export MYSQL_PS1= " \D_\h_\p_\u > "，格式为"时间_主机_端口_用户名"
2、通过客户端的命令prompt修改 \R \D_\h_\p_\u >
3、通过my.cnf的[mysql]域进行配置
[mysql]
prompt="\R \D_\h_\p_\u > "
4、如果session中通过\R xxx 临时修改了命令行提示信息，可以通过单独执行\R来恢复默认设置。
quit(\q)
退出当前session连接。
auto-rehash(\#)
在使用mysql客户端连接服务器时，默认情况下会自动收集一些元数据信息，在后续输入SQL命令时可以通过tab键补齐命令，比如补齐表名字、列名字。
GreatDB Cluster[test]> select * from tt(此时按tab键)
tt1 tt1.c1 tt1.c2 tt1.dd tt1.id ttt ttt.id
但是如果在session中新建了表，或者给表上新增了字段，是无法通过tab键补齐的，这时通过\#命令刷新元数据信息，之后就能对新的DDL结构进行补齐
GreatDB Cluster[test]> create table tt2 as select * from tt1;
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
GreatDB Cluster[test]> select * from tt(此时按tab键)
tt1 tt1.c1 tt1.c2 tt1.dd tt1.id ttt ttt.id
GreatDB Cluster[test]> \#
GreatDB Cluster[test]> select * from tt(此时按tab键)
tt1 tt1.c1 tt1.c2 tt1.dd tt1.id tt2 tt2.c1 tt2.c2 tt2.dd tt2.id ttt ttt.id
GreatDB Cluster[test]> select * from tt
source(\.)
命令后需要跟一个文件名，\. filename会对filename文件中的内容按标准SQL进行解析执行。
status(\s)
输出本次连接的相关信息及服务器的一些信息，如果连接时指定了--safe-updates，还会输出查询限制相关的信息，\s输出了很多有用的信息，可仔细阅读。
[#8#root@greatdb81 ~ 20:26:13]8 m5 3306 --safe-updates
greatsql: [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 916197
Server version: 8.0.25-15-greatdbcluster5.1.8-RC-6 GreatDB Cluster, Release RC-6, Revision 60ab7f36025
Copyright (c) 2009-2021 BEIJING GREAT OPENSOURCE SOFTWARE TECHNOLOGY CO.,LTD.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
GreatDB Cluster[(none)]> \s
--------------
/greatdb/svr/greatdb/bin/greatsql Ver 8.0.25-15-greatdbcluster5.1.8-RC-6 for Linux on x86_64 (GreatDB Cluster, Release RC-6, Revision 60ab7f36025)
Connection id: 916197
Current database:
Current user: greatdb@127.0.0.1
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.25-15-greatdbcluster5.1.8-RC-6 GreatDB Cluster, Release RC-6, Revision 60ab7f36025
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 51 days 9 hours 5 min 13 sec
Threads: 16 Questions: 4354604 Slow queries: 339 Opens: 19188 Flush tables: 3 Open tables: 6095 Queries per second avg: 0.980
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the command.)
SELECT has an automatic 'LIMIT 1000' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000
--------------
GreatDB Cluster[(none)]>【常用】system(\!) 执行操作系统命令
使用默认命令解释器执行给定命令，简单的说就是返回到操作系统执行\!之后的命令，比如下面
GreatDB Cluster[(none)]> \! date
2022年 11月 16日 星期三 20:32:34 CST
GreatDB Cluster[(none)]> \! pwd
/root
GreatDB Cluster[(none)]> \! cd /greatdb
GreatDB Cluster[(none)]> \! vmstat -w
procs -----------------------memory---------------------- ---swap-- -----io---- -system-- --------cpu--------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 358508 0 3117236 0 0 486 20 0 0 5 3 92 0 0
GreatDB Cluster[(none)]>
在8.0.19之前，只支持在unix系统中使用该命令，到8.0.19后，在windows中也支持了该命令。
tee(\T)
将所有执行的SQL命令及输出结果保存到指定文件中。这在调测、生产维护过程中，都是非常有用的一个功能，特别是一些安全要求高的环境中，控制台只能显示几十行命令时，想要查找之前执行的命令及执行的结果比较难，此时就能用上\T了。
GreatDB Cluster[(none)]> \T /root/a.log
Logging to file '/root/a.log'
GreatDB Cluster[(none)]> select * from test.tt1;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)
GreatDB Cluster[(none)]> \! cat /root/a.log
GreatDB Cluster[(none)]> select * from test.tt1;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)
GreatDB Cluster[(none)]>
如果想要记录每个人登录数据库，做了哪些操作，由于tee不支持根据当前时间动态产生日志文件名，我们可以这样设置
## 首先创建一个log目录
mkdir -p /greatdb/logs/client/
## 然后设置环境变量，为了连接安全，建议使用--login-path的方式进行登录，我这里使用的是gdb1登录。
echo "alias mlogin='mysql --login-path=gdb1 --tee /greatdb/logs/client/\$(date +%Y-%m-%d_%H-%M-%S).log'" >> ~/.bashrc; source ~/.bashrc
[#15#root@greatdb81 /greatdb/logs/client 20:48:53]15 echo "alias mlogin='mysql --login-path=gdb1 --tee /greatdb/logs/client/$(date +\"%Y-%m-%d_%H-%M\").log'" >> ~/.bashrc; source ~/.bashrc
## 通过设置的alias登录数据库
[#16#root@greatdb81 /greatdb/logs/client 20:49:43]16 mlogin
Logging to file '/greatdb/logs/client/2022-11-16_20-49.log'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 916482
Server version: 8.0.25-15-greatdbcluster5.1.8-RC-6 GreatDB Cluster, Release RC-6, Revision 60ab7f36025
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables from tpcc;
+------------------+
| Tables_in_tpcc |
+------------------+
| bmsql_config |
| bmsql_customer |
| bmsql_district |
| bmsql_history |
| bmsql_item |
| bmsql_new_order |
| bmsql_oorder |
| bmsql_order_line |
| bmsql_stock |
| bmsql_warehouse |
| datatypes1 |
| datatypes10 |
| datatypes11 |
| datatypes2 |
| datatypes3 |
| datatypes4 |
| datatypes5 |
| datatypes6 |
| datatypes7 |
| datatypes8 |
| datatypes9 |
+------------------+
21 rows in set (0.00 sec)
mysql>
我们发现指定的日志目录中，已经有了日志文件，并且记录了所有执行的SQL及输出结果
[#9#root@greatdb81 ~ 20:51:43]9 cd /greatdb/logs/client/
[#10#root@greatdb81 /greatdb/logs/client 20:51:48]10 ll
总用量 8
-rw-r--r-- 1 root root 627 11月 16 20:48 2022-11-16_20-48.log
-rw-r--r-- 1 root root 3214 11月 16 20:50 2022-11-16_20-49.log
[#11#root@greatdb81 /greatdb/logs/client 20:51:48]11
notee(\t)
取消\T设置，不再记录操作信息到日志文件中。
use(\u)
切换当前连接的database。
【常用】warnings(\W) 执行完SQL语句后立即显示告警信息（需要执行SQL前就给这个会话设置）
在执行完SQL语句后，立即显示warning信息，不需要再手动执行show warnings;了。
nowarnings(\w)
在执行完SQL语句后，不立即显示warning信息，需要手动执行show warnings;才会显示warning信息。
resetconnection(\x)
以新连接的状态重新连接到服务器，并且进行一些信息的清理及复位，不需要再次进行权限验证。主要影响如下信息：
- 回滚所有活动的事务，并重置自动提交模式。
- 所有DML锁均已释放。
- 所有TEMPORARY table 均已关闭(并删除)。
- 会话系统变量将重新初始化。
- 用户变量设置丢失。
- 准备好的语句被释放。
- HANDLER关闭。
- LAST_INSERT_ID 置为 0。
- 用 GET_LOCK 释放。
可以参考8.0.26的代码libmysql.cc中4429~4444行
int STDCALL mysql_reset_connection(MYSQL *mysql) {
DBUG_TRACE;
if (simple_command(mysql, COM_RESET_CONNECTION, nullptr, 0, 0))
return 1;
else {
mysql_detach_stmt_list(&mysql->stmts, "mysql_reset_connection");
/* reset some of the members in mysql */
mysql->insert_id = 0;
mysql->affected_rows = ~(uint64_t)0;
free_old_query(mysql);
mysql->status = MYSQL_STATUS_READY;
mysql_extension_bind_free(MYSQL_EXTENSION_PTR(mysql));
return 0;
}
}
query_attributes
通过query_attributes var1 value1 var2 value2来设置变量，然后通过mysql_query_attribute_string('var1')返回变量值value1，目前来看，并没有发现特殊的使用方法，毕竟我们也可以通过set @var1='value1'来设置。
使用体验
mysql Client Commands在某些场景下，可以极大的提高我们的操作效率，相对于完整的命令ego、pager、edit等，我更喜欢使用他们的简写命令\G、\P、\e，因为有时候完整命令不确定如何正确使用，会导致不生效，但是简写命令，是一定会生效的。
