Technology sharing: an interesting exploration of MySQL client command usage

Author: Chen Yi

preface

This article briefly introduces some tips of "MySQL client" often used in operation and maintenance. These tips will not be used by non professional DBAs. Professional DBAs are necessary. I hope you can use my share.

The built-in commands of MySQL client are as follows, and we will explore 6 of them:

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.

1. pager

pager is similar to Linux's pipe character, and can output to another command as input.

What's powerful is that the command that this pipeline connects is Linux command. We can use familiar linux commands to implement various kinds of operations.

Don't talk much. Let's take a few examples

Turn page

mysql> pager less
PAGER set to 'less'
mysql> show engine innodb status\G
1 row in set (0.00 sec)

The output of innodb status is very long. Follow the Linux command less to turn pages. You can also use more according to your preferences.

Find search

Generally speaking, we want to see which slow SQL is running at present. You can use the following command to query the processlist table in the information u schema, which requires you to be familiar with the metadata table.

mysql> select * from information_schema.PROCESSLIST where COMMAND='Query';
+------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+
| ID   | USER | HOST      | DB                 | COMMAND | TIME | STATE      | INFO                                                               |
+------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+
| 3508 | root | localhost | information_schema | Query   |    0 | executing  | select * from information_schema.PROCESSLIST where COMMAND='Query' |
| 3463 | root | localhost | NULL               | Query   |  233 | User sleep | select sleep(1000)                                                 |
| 3465 | root | localhost | NULL               | Query   |  228 | User sleep | select sleep(2000)                                                 |
| 3439 | root | localhost | NULL               | Query   |  235 | User sleep | select sleep(1000)                                                 |
+------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+
4 rows in set (0.00 sec)

But with pager method, we can get it more efficiently by using grep command of Linux.

mysql> pager grep Query
PAGER set to 'grep Query'
mysql> show processlist;
| 3439 | root        | localhost          | NULL | Query            |    23 | User sleep                                                    | select sleep(1000) |
| 3463 | root        | localhost          | NULL | Query            |    21 | User sleep                                                    | select sleep(1000) |
| 3465 | root        | localhost          | NULL | Query            |    16 | User sleep                                                    | select sleep(2000) |
| 3473 | root        | localhost          | NULL | Query            |     0 | starting                                                      | show processlist   |
17 rows in set (0.00 sec)

You can even count the quantity directly

mysql> pager grep Query |wc -l
PAGER set to 'grep Query |wc -l'
mysql> show processlist;
4               #See here
17 rows in set (0.00 sec)

Four running queries were found in real time.

Close pager

Remember to cancel after using pager. There are three ways to cancel

#Common method, set pager back to the original default value (stdout)
mysql> pager
Default pager wasn't set, using stdout.

#Close pager
mysql> nopager
PAGER set to stdout

#Exit client, reconnect
mysql> quit
Bye

2. tee

The tee command is the same for tee and Linux. When outputting to stdout, you can specify to output to another file at the same time. Three functions can be realized by using it: data guide, audit and record operation

Scenario 1: fast data export

mysql> tee /tmp/general_log
Logging to file '/tmp/general_log'
mysql> select * from general_log where event_time >'2019-11-28 00:00:00';
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                                                          |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+
| 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' |
| 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' |
| 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' |
| 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' |
| 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' |
| 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' |
| 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' |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+
7 rows in set (0.00 sec)

[root@chenyi tmp]# cat general_log 
mysql> select * from general_log where event_time >'2019-11-28 00:00:00';
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                                                          |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+
| 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' |
| 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' |
| 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' |
| 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' |
| 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' |
| 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' |
| 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' |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> \q

Scenario 2: audit

Configure my.cnf

[mysql]
tee=/tmp/tee.log

It can be used for client audit and records all screen output of the client. (of course, this is not a real MySQL audit.)

The client audit log is as follows:

[root@chenyi tmp]# cat /tmp/tee.log 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> nihao;
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
mysql> \q

Remind! Use this move to be careful of misoperation. A large amount of data has been select ed, resulting in the file being full of disk.

Scenario 3: temporary record operation

Go to the customer's side to check the problem. Consider opening tee first, and then watching tee.log to write the fault analysis email after troubleshooting.

Close tee

notee;

So the above mentioned use of tee audit has little effect, because it can be closed!

3. edit

It is equivalent to using vi command to edit SQL statement in MySQL. This function is relatively weak, even for the vi party, the efficiency has not improved much. When you open edit by default, the last SQL command is edited. After you exit vi, enter ";" and enter enter to execute the SQL edited in vi.

mysql> select * from information_schema.PROCESSLIST where COMMAND='Query';
+------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+
| ID   | USER | HOST      | DB                 | COMMAND | TIME | STATE      | INFO                                                               |
+------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+
| 3508 | root | localhost | information_schema | Query   |    0 | executing  | select * from information_schema.PROCESSLIST where COMMAND='Query' |
| 3463 | root | localhost | NULL               | Query   |  233 | User sleep | select sleep(1000)                                                 |
| 3465 | root | localhost | NULL               | Query   |  228 | User sleep | select sleep(2000)                                                 |
| 3439 | root | localhost | NULL               | Query   |  235 | User sleep | select sleep(1000)                                                 |
+------+------+-----------+--------------------+---------+------+------------+--------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> edit

However, it is interesting to use edit to hide the client operation records and realize "hacker operation". Let's take a look at:

mysql> edit
    -> ;
PAGER set to 'grep -v I am a hacker. >>/tmp/1.log'
mysql> edit
    -> ;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> edit
    -> ;
6 rows in set (0.00 sec)

mysql> \q

The above is the SQL command I executed on the console. I'm sure you don't know what I executed. And the next user can only see the following four command lines when logging in with my mysql client:

edit;
edit;
edit;
\q

This hides my SQL command line operations.

When we open the "client audit log", we can see the following:

[root@chenyi tmp]# cat /tmp/tee.log 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> edit
    -> ;
PAGER set to 'grep -v I am a hacker. >>/tmp/1.log'
mysql> edit
    -> ;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> edit
    -> ;
+------------------+---------------+
| user             | host          |
+------------------+---------------+
| heike            | %             |
| root             | 10.168.65.%   |
| mysql.session    | localhost     |
| mysql.sys        | localhost     |
| root             | localhost     |
| chenyi           | localhost     |
+------------------+---------------+
6 rows in set (0.00 sec)


mysql> \q

In this log, we can find that I have a pager operation, and the query results are output after the last edit. However, we have no way to know the actual operations in the three edits. The query result output after the last edit shows that "tee audit mode" ignores pager's filtering function. The original output is audited, but the original SQL command executed evades the audit and is hidden.

Now, let me reveal:

#First edit
pager grep -v I am a hacker. >>/tmp/1.log

#Third edit
select user,host from mysql.user;

Second, we don't know what the operation is. Of course, we really need to check. We can try to parse binlog and try our luck to see if it is a write operation

If installed mcafee We can also see the audit plug-in

{
  "msg-type": "activity",
  "date": "1574932159871",
  "thread-id": "9",
  "query-id": "129",
  "user": "root",
  "priv_user": "root",
  "ip": "",
  "host": "localhost",
  "connect_attrs": {
    "_os": "linux-glibc2.12",
    "_client_name": "libmysql",
    "_pid": "6004",
    "_client_version": "5.7.27",
    "_platform": "x86_64",
    "program_name": "mysql"
  },
  "pid": "6004",
  "os_user": "root",
  "appname": "/usr/local/mysql/bin/mysql",
  "status": "0",
  "cmd": "create_user",
  "query": "create user heike@'%' identified by '***'"
}
{
  "msg-type": "activity",
  "date": "1574932159874",
  "thread-id": "9",
  "query-id": "130",
  "user": "root",
  "priv_user": "root",
  "ip": "",
  "host": "localhost",
  "connect_attrs": {
    "_os": "linux-glibc2.12",
    "_client_name": "libmysql",
    "_pid": "6004",
    "_client_version": "5.7.27",
    "_platform": "x86_64",
    "program_name": "mysql"
  },
  "pid": "6004",
  "os_user": "root",
  "appname": "/usr/local/mysql/bin/mysql",
  "status": "0",
  "cmd": "grant",
  "query": "grant all on *.* to heike@'%'"
}

Similarly, the third edit, due to the select operation, will also be recorded by the audit plug-in.

{
  "msg-type": "activity",
  "date": "1574932192709",
  "thread-id": "9",
  "query-id": "131",
  "user": "root",
  "priv_user": "root",
  "ip": "",
  "host": "localhost",
  "connect_attrs": {
    "_os": "linux-glibc2.12",
    "_client_name": "libmysql",
    "_pid": "6004",
    "_client_version": "5.7.27",
    "_platform": "x86_64",
    "program_name": "mysql"
  },
  "pid": "6004",
  "os_user": "root",
  "appname": "/usr/local/mysql/bin/mysql",
  "rows": "35",
  "status": "0",
  "cmd": "select",
  "objects": [
    {
      "db": "mysql",
      "name": "user",
      "obj_type": "TABLE"
    }
  ],
  "query": "select user,host from mysql.user"
}

It can be seen that the audit function of the audit plug-in can audit the SQL actually executed by the server, which is incomparable to the tee audit mode. However, the audit plug-in did not find my pager operation, so it did not know that I exported data. Only the tee audit mode found that I exported data.

In the previous example, we can see that the password is not displayed in the audit log of the audit plug-in.
We know that in binlog, the password is also encrypted.
In the history of mysql client, the statement with identified by 'xxx' will not be recorded.

Therefore, none of the above methods will disclose the password.

The only place where the plaintext password will be revealed is "tee audit method". After testing, the conclusion is that using edit can make the plaintext password never leak.

Therefore, the edit operation can hide the password.

Finally, I will reveal that my second edit operation is:

create user heike@'%' identified by 'Heike@2019';
grant all on *.* to heike@'%';

4. system

Execute Linux command without exiting mysql client

View server IP

I usually use it to confirm the IP address

mysql> system ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 02:00:0a:ba:41:0f brd ff:ff:ff:ff:ff:ff
    inet 10.186.65.15/24 brd 10.186.65.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::aff:feba:410f/64 scope link 
       valid_lft forever preferred_lft forever

5. status

View MySQL server status

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

Connection id:		11
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		'/tmp/tee.log'
Using delimiter:	;
Server version:		5.7.27-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql3307.sock
Uptime:			1 hour 15 min 32 sec

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

Basically, the customer handles the problem. This is the first command line to execute after logging in MySQL. Usually use the shortcut command \ s. Here you can get a lot of information you want.

The client of mysql connection is 5.7.27
MySQL Server version is 5.7.27 community version
Open "client audit log", output to / tmp/tee.log
I connect to the database in the way of sock
Generally speaking, I can't get the port information of the connected database, but I even get the port information for the naming here!
My pager is not set. It uses the default stdout, and the standard output is to the screen
The database has been started for 1 hour and 15 minutes. Has the database been restarted?
Database connection thread is 1. There is no program or human connection to the database, only me
There are 145 Questions.
Slow queries is 0, no slow queries
Number of Opens195, not reaching the upper limit of 65536
188 Open tables, not reaching the upper limit of 65536
Queries per second avg, this is QPS, but its algorithm is divided by uptime time, so it doesn't reflect the current server load, it's useless

Here I would like to highlight two aspects of information acquisition:

Number of connections

If I just want to know if the server connection is full, then I don't need to show processlist, just \ s.

QPS QPS here refers to Questions per second.

Method 1

Get from status command

\s select sleep(1); \s

The real QPS of the instantaneous server is equal to the difference between the two \ s output Questions, which is further reduced by 4, Because \ s itself will cause 3 Questions, while select sleep(1); will cause 1 Questions.

Method 2

show global status get

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

The real QPS of instantaneous server is equal to show global status like 'Questions' twice; the difference of output is further reduced by 2, Because show global status like 'Questions'; itself will cause 1 question number, while select sleep(1); will cause 1 question number

Method 3

Best practice, because usually observing QPS is not a instantaneous point, we need to keep looking, so it is appropriate to use mysqladmin method.

[root@chanyi tmp]# mysqladmin -uroot -proot -P3307 -S /tmp/mysql3307.sock -r -i 1 ext |grep -i 'question'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Questions                                     | 162                                              |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
^C

This method also uses show global status

The real QPS of instantaneous server is actually 0. The number 1 comes from show global status once a second

6. prompt

Modify the mysql prompt.

I usually use it in two situations:

Temporarily mark master-slave or ip address

#On the main warehouse
mysql> prompt master> ;
PROMPT set to 'master> '
master> 

#From library
mysql> prompt slave> ;
PROMPT set to 'slave> '
slave>

Make prompts richer

Modify the / etc/my.cnf configuration file

[mysql]
prompt=\\U [\\d]>

Modified effect:

root@localhost [(none)]>use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [test]>

Now, after the MySQL client logs in, it is convenient to know which user logs in and which database to switch to.

Best practices