Some tips on pager and other commands in mysql
one。 Pager less or pager more
Description:
Less mode, you can use spaces to the next page, Q exit
More mode, like the linux more command, displays to the next page by space
For example:
Mysql > pager less
PAGER set to 'less'
> show engine innodb status\ G
* * 1. Row *
Type: InnoDB
Name:
Status:
= =
2019-06-25 09:58:27 0x7f326c3fb700 INNODB MONITOR OUTPUT
= =
Per second averages calculated from the last 7 seconds
-
BACKGROUND THREAD
-
Srv_master_thread loops: 1644 srv_active, 0 srv_shutdown, 6045651 srv_idle
Srv_master_thread log flush and writes: 6047099
-
SEMAPHORES
-
OS WAIT ARRAY INFO: reservation count 19543
OS WAIT ARRAY INFO: signal count 18271
RW-shared spins 0, rounds 1971, OS waits 762
RW-excl spins 0, rounds 15377, OS waits 200
RW-sx spins 365, rounds 7423, OS waits 99
Spin rounds per wait: 1971.00 RW-shared, 15377.00 RW-excl, 20.34 RW-sx
-
TRANSACTIONS
-
Trx id counter 3264932
Purge done for trx's n:o
< 3264932 undo n:o < 0 state: running but idle History list length 32 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421335447628512, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421335447627600, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] , : 按回车继续显示，按q退出。 二.忽略中间过程输出,只显示执行结果： mysql>Pager cat > / dev/null
PAGER set to 'cat > / dev/null'
Mysql > select * from test.test
101000 rows in set (0.33 sec)
III. Show processlist formatted output
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
three
1 Query
1 Command
1 Binlog Dump
134 Sleep
136 rows in set (0.00 sec)
IV. Checksum usage:
Checksum is used to compare whether the SQL results are the same:
Mysql > pager md5sum
PAGER set to 'md5sum'
Mysql > select count (*) from test.test
009e5c78cbf36ce635cc26a4711edf6b-
1 row in set (0.11 sec)
After deleting part of the data:
Mysql > select count (*) from test.test
B092d86b9dad1070f9cd56786d1ac99a-
1 row in set (0.00 sec)
Note: the checksum value of the SQL statement is different before and after deleting the data.
V. Edit usage
Root@localhost: (none) 10:32:56 > 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: test10:32:57 > show databases
+-+
| | Database |
+-+
| | information_schema |
| | S121318 | |
| | S122036 | |
| | S122206 | |
| | S122443 |
| | S122501 | |
| | S383 |
| | U47032 |
| | dsf |
| | impl |
| | monitor |
| | mysql |
| | performance_schema |
| | slow_query_log |
| | sys |
| | test |
| | test_tb |
| | yqht |
| | yqms2 |
+-+
19 rows in set (0.00 sec)
Root@localhost: test10:33:02 > edit
/ / hit enter
(edit in the open vi, finish editing and then x exit)
>; / / click on the semicolon after quitting. To execute this edited statement.
Show tables
~
~
~
~
~
~
~
~
~
"/ tmp/sqlizwJXA" 1L, 12C written
->
+-+
| | Tables_in_test |
+-+
| | aaa |
| | dsf |
| | dsf_old |
| | peihy |
| | sq_prebycollecttime |
| | t |
| | T1 |
| | test |
+-+
8 rows in set (0.01 sec)
six。 Tee command usage
The tee command outputs the results to a file:
Root@localhost: test10:36:25 > tee / tmp/aaa.txt
Logging to file'/ tmp/aaa.txt'
Root@localhost: test10:36:31 > select * from t
+-+ +
| | id | name |
+-+ +
| | 1 | dsf |
| | 2 | dsf |
| | 5 | dsf |
| | 6 | liu |
| | 7 | pei |
+-+ +
5 rows in set (0.00 sec)
Root@localhost: test10:36:34 > notee
Outfile disabled.
/ tmp/aaa.txt is as follows:
# cat / tmp/aaa.txt
Mysql > select * from t
+-+ +
| | id | name |
+-+ +
| | 1 | dsf |
| | 2 | dsf |
| | 5 | dsf |
| | 6 | liu |
| | 7 | pei |
+-+ +
5 rows in set (0.00 sec)
Mysql > notee
7. Echo command usage:
# echo "select * from t;" | mysql test
Id name
1 dsf
2 dsf
5 dsf
6 liu
7 pei
eight。 Do not display the column headers of the table:
# mysql-- skip-column-names-e "select * from test.user limit 10;"
+-- +
| | 1 | Xiaoming | 1 |
| | 2 | Xiao Hong | 1 |
| | 3 | culvert | 2 |
| | 4 | BBfSaxkHIuXDbvXA | 7394002 | |
| | 5 | hBlAVc rgIWKMELT | 2230353 | |
| | 6 | yGNWtciFFlmDgWpH | 3941883 | |
| | 7 | aRlDlsfzghrkbAAd | 7363753 | |
| | 8 | pWOiwGVJInoGrNP | 7648385 | |
| | 9 | uJldIgGPfefqmltm | 866603 | |
| | 10 | KnjeWwrsOUdIgGMS | 555015 | |
+-- +
# mysql-- skip-column-names-e "select * from test.user limit 10;" | cat-n
1 1 Xiaoming 1
2 2 Xiaohong 1
3 3 culvert 2
4 4 BBfSaxkHIuXDbvXA 7394002
5 5 hBlAVc rgIWKMELT 2230353
6 6 yGNWtciFFlmDgWpH 3941883
7 7 aRlDlsfzghrkbAAd 7363753
8 8 pWOiwGVJInoGrNP 7648385
9 9 uJldIgGPfefqmltm 866603
10 10 KnjeWwrsOUdIgGMS 555015
