The use of pager commands in mysql _ MySQL

The use of pager commands in mysql bitsCN.com

The use of pager commands in mysql

In mysql, if you use the pager command in linux, the work efficiency will be greatly improved. The following describes several examples:

1. First, let's look at the basic usage.

Mysql> pager less

PAGER set to 'less'

Mysql> show engine innodb status/G

At this time, you can start to use the less Mode. you can use spaces to go to the next page and quit;

You can even directly execute scripts in linux, such as lock_waits.sh in/tmp /.

You can:

Mysql> pager/tmp/lock_waits

PAGER set to '/tmp/lock_waits'

Execute the/tmp/lock_waits script.

2. to process a large number of datasets, if you only want to care about the results, you can do this:

Mysql> pager cat>/dev/null

PAGER set to 'Cat>/dev/Null'

# For example, execute a series of lengthy execution plan statements, ignore the intermediate process output, and only display the time consumption.

Mysql> SELECT...

1000 rows in set (0.91 sec)

Mysql> SELECT...

1000 rows in set (1.63 sec)

3. for example, if you are optimizing SQL statements, a large amount of results will be generated.

Mysql> SELECT...

[...]

989 rows in set (0.42 sec)

You can use checksum to compare whether the results of each adjusted SQL statement are the same

Mysql> pager md5sum

PAGER set to 'md5sum'

# Original query

Mysql> SELECT...

32a1894d773c9b85172969c659175d2d-

1 row in set (0.40 sec)

# Rewritten query-wrong

Mysql> SELECT...

Fdb94521558684afedc8148ca724f578-

1 row in set (0.16 sec)

Here, checksum is different, so there is a problem with the rewritten SQL statement.

4. if there are a large number of connections, it will be inconvenient to use show processlist. for example, it is not convenient to know which current connections are sleeping or dead. you can do this:

Mysql> pager grep Sleep | wc-l

PAGER set to 'grep Sleep | wc-L'

Mysql> show processlist;

337

346 rows in set (0.00 sec)

The number of sleep connections is displayed immediately;

Further, you need to know the connection status of each status, which can be as follows:

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;

309 Sleep

3

2 Query

2 Binlog Dump

1 Command

Of course, you can also use SQL query:

Mysql> select count (*) FROM INFORMATION_SCHEMA.PROCESSLIST where command = 'sleep ';

Select command, COUNT (*) total from INFORMATION_SCHEMA.PROCESSLIST group by command order by total desc;

BitsCN.com