MYSQL命令pager推介
在日常操作MYSQL的工作中，妙用pager设置显示方式，可以很大可能的提高你的效率。例如我们平常有时候通过select查询出的结果非常多，超过好几屏幕，那么前面的结果看不到，整个结果的详细信息没法看全，这时候就可以使用pager调用系统的more或者less等命令显示查询结果了，跟在操作系统上用more和less查看大文件的效果是差不多的。
pager用法: 当设置了pager之后，MYSQL操作的所有命令的输出都会通过pager设置的命令执行，相当于管道符的作用。
当然需要恢复原样的话，利用nopager命令可以取消pager设置，恢复状态；如果你不通过nopager设置的话，那么只能通过重启MYSQL实例才能恢复了。
下面我们介绍几个实际例子:
1、查询大数据表时候，只希望知道查询消耗时间，不需要看到查询结果
mysql> select count(*) from collector_data_num;
+----------+
| count(*) |
+----------+
| 1935236 |
+----------+
1 row in set (0.00 sec)
mysql> pager cat /dev/null; # 实际上等于后面执行的命令|cat /dev/null，这样显示结果就只是执行时间了
PAGER set to 'cat /dev/null'
mysql> select ID from collector_data_num;
1935236 rows in set (3.25 sec)
mysql> select * from collector_data_num limit 1;
1 row in set (0.00 sec)mysql> nopager;
PAGER set to stdout
mysql> select ID from collector_data_num limit 1;
+---------------------+
| ID |
+---------------------+
| 00053owjtxfTP1qK5TF |
+---------------------+
1 row in set (0.00 sec)mysql>2、如果有大量连接，想看有多少Sleep状态
mysql> show processlist;
+---------+------------+---------------------+-----------------+-------------+---------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------------+---------------------+-----------------+-------------+---------+-----------------------------------------------------------------------+------------------+
| 1 | suser | 192.168.0.123:33481 | monitor_db | Sleep | 16 | | NULL |
| 2 | suser | 192.168.0.123:33480 | monitor_db | Sleep | 16 | | NULL |
| 3 | suser | 192.168.0.123:33482 | monitor_db | Sleep | 5 | | NULL |
| 30 | suser | 192.168.0.123:24591 | monitor_db | Sleep | 1763 | | NULL |
| 31 | suser | 192.168.0.123:30559 | monitor_db | Sleep | 4453 | | NULL |
| 1085 | mysqlab | 192.168.0.120:51554 | NULL | Binlog Dump | 1865911 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 957112 | suser | 192.168.0.123:36808 | monitor_db | Sleep | 43 | | NULL |
| 1235647 | suser | 192.168.0.123:55570 | monitor_db | Sleep | 8 | | NULL |
| 1306214 | suser | 192.168.0.123:2004 | monitor_db | Sleep | 13 | | NULL |
| 1315396 | suser | 192.168.0.123:65126 | monitor_db | Sleep | 138 | | NULL |
| 1321128 | suser | 192.168.0.123:7944 | monitor_db | Sleep | 2 | | NULL |
| 1326809 | suser | 192.168.0.123:15931 | monitor_db | Sleep | 7 | | NULL |
| 1329587 | suser | 192.168.0.123:19176 | monitor_db | Sleep | 138 | | NULL |
| 1330894 | suser | 192.168.0.123:20888 | monitor_db | Sleep | 0 | | NULL |
| 1331751 | suser | 192.168.0.123:19211 | monitor_db | Sleep | 3 | | NULL |
| 1331988 | suser | 192.168.0.123:22250 | monitor_db | Sleep | 138 | | NULL |
| 1332087 | root | localhost | NULL | Query | 0 | init | show processlist |
+---------+------------+---------------------+-----------------+-------------+---------+-----------------------------------------------------------------------+------------------+
17 rows in set (0.04 sec)mysql> pager grep Sleep |wc -l;
PAGER set to 'grep Sleep |wc -l'
mysql> show processlist; # 类似于show processlist结果再通过grep Sleep |wc -l显示；下面表示一共有17个连接，其中15个Sleep状态的连接。
15
17 rows in set (0.00 sec)mysql> nopager; # 恢复之前的输出状态
PAGER set to stdout3、通过设置pager查看Slave的特定几个状态值
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.23
Master_User: mysqlab
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000081
Read_Master_Log_Pos: 342258054
Relay_Log_File: mysql-relay-bin.000110
Relay_Log_Pos: 342258217
Relay_Master_Log_File: mysql-bin.000081
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 342258054
Relay_Log_Space: 342258437
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 24
Master_UUID: 5392426b-3e15-11e8-b25b-525400d8821f
Master_Info_File: /data/mysqldata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)mysql> pager cat | egrep -i 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master';
PAGER set to 'cat | egrep -i 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master''
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)mysql> nopager; # 恢复之前状态
PAGER set to stdout本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2018-05-13，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
