MYSQL命令pager推介

in 推介 with 0 comment  访问: 173 次

在日常操作MYSQL的工作中,妙用pager设置显示方式,可以很大可能的提高你的效率。例如我们平常有时候通过select查询出的结果非常多,超过好几屏幕,那么前面的结果看不到,整个结果的详细信息没法看全,这时候就可以使用pager调用系统的more或者less等命令显示查询结果了,跟在操作系统上用moreless查看大文件的效果是差不多的。

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 stdout

3、通过设置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
WeZan
Responses