The use of pager commands in mysql

The use of pager commands in mysql is in mysql. in linux, the use of pager commands will greatly improve work efficiency. The following describes several examples: 1. First, let's take a look at the basic usage of www.2cto.com mysql> pager less PAGER set to 'less' mysql> show engine innodb status \ G. Then we can start using the less mode, you can use spaces to go to the next page and quit. You can even directly execute the script in linux. For example, if there is a script in lock_waits.sh under/tmp/, you can: mysql> pager/tmp/lock_waits PAGER set to '/tmp/lock_waits' will execute the/tmp/lock_waits script www.2cto.com 2. to process a large number of datasets, if you only want to care about the results, mysql> pager cat>/dev/null PAGER set to 'cat>/dev/null' # For example, execute a series of lengthy execution plan statements and ignore intermediate process output, only mysql> SELECT... 1000 rows in set (0.91 sec) mysql> SELECT... 1000 rows in set (1.63 sec) 3. For example, if you perform SQL optimization, a large number of results are generated for mysql> SELECT... [...] 989 rows in set (0.42 sec) you can use checksum to compare whether the results produced by 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 is not convenient 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) now you can see the number of sleep connections currently; www.2cto.com further, you need to know the connection status of each status, you can do this: 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;