MySQL Show命令集

MySQL SHOW 命令

1、mysql shell 查看帮助show

(root@localhost) [(none)]> \help show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW {REPLICAS | SLAVE HOSTS}
SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where: {
    LIKE 'pattern'
  | WHERE expr
}

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.

In SHOW statement results, user names and host names are quoted using
backticks (`).

URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

2、查看存储引擎的状态

(root@localhost) [(none)]> SHOW ENGINE INNODB STATUS \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:

Status:
=====================================

2024-06-08 11:54:25 139900802344704 INNODB MONITOR OUTPUT
=====================================

Per second averages calculated from the last 14 seconds
-----------------

BACKGROUND THREAD
-----------------

srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 435284 srv_idle

srv_master_thread log flush and writes: 0
----------

SEMAPHORES
----------

OS WAIT ARRAY INFO: reservation count 89
OS WAIT ARRAY INFO: signal count 87
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------

TRANSACTIONS
------------

Trx id counter 48991
Purge done for trx's n:o < 48986 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421376845869056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845869864, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845868248, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845867440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845866632, not started

0 lock struct(s), heap size 1128, 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 (write thread)
I/O thread 7 state: waiting for completed aio requests (write 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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1168 OS file reads, 1380 OS file writes, 701 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 8 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s
---

LOG
---

Log sequence number          1352792225
Log buffer assigned up to    1352792225
Log buffer completed up to   1352792225
Log written up to            1352792225
Log flushed up to            1352792225
Added dirty pages up to      1352792225
Pages flushed up to          1352792225
Last checkpoint at           1352792225

327 log i/o's done, 0.00 log i/o's/second
----------------------

BUFFER POOL AND MEMORY
----------------------

Total large memory allocated 0
Dictionary memory allocated 550711
Buffer pool size   8192
Free buffers       6892
Database pages     1284
Old database pages 493
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1139, created 145, written 840
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1284, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------

ROW OPERATIONS
--------------

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10299, Main thread ID=139901376005888 , state=sleeping
Number of rows inserted 0, updated 0, deleted 0, read 274
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 2, updated 479, deleted 0, read 12906

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------

END OF INNODB MONITOR OUTPUT
======