2026 MySQL DBA Command Reference: 9 Essential Commands Explained

Complete MySQL DBA Command Reference

MySQL is the core of large-scale architectures. This guide details nine essential DBA commands for database administration and performance troubleshooting.

1. SHOW DATABASES — List Databases

View all databases in the current MySQL instance.

SHOW DATABASES;

2. SHOW TABLE STATUS — View Table Details

Retrieve detailed information for each table, including:

Table size

Row count

Storage engine

Creation time

Update time

Data length

Index length

Can be scoped to a specific database:

SHOW TABLE STATUS FROM shop;

3. SHOW FULL PROCESSLIST — Inspect Active Connections

A critical command for diagnosing MySQL issues. Key columns to monitor:

Id — Connection identifier

User — Connected user

Host — Client host

db — Current database

Command — Type of command (Query, Sleep, etc.)

Time — Seconds in current state

State — Thread state (e.g., Locked, Waiting for table metadata lock, Sending data)

Info — The SQL statement being executed

Long-running states like Locked, Waiting for table metadata lock, or Sending data warrant further investigation.

4. SHOW ENGINE INNODB STATUS — InnoDB Internals

Core diagnostic command for InnoDB. Use \G for vertical output: SHOW ENGINE INNODB STATUS\G Provides visibility into:

Transactions

Locks

Deadlocks

Buffer Pool

I/O

Logs

Semaphores

Especially valuable for analyzing InnoDB deadlocks.

5. SHOW VARIABLES — View Configuration

Display current MySQL configuration settings. Filter with LIKE: SHOW VARIABLES LIKE 'max_connections'; Check maximum allowed connections. SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; Inspect InnoDB buffer pool size. SHOW VARIABLES LIKE 'slow_query_log'; Verify whether the slow query log is enabled.

6. SHOW STATUS — Runtime Metrics

Show server status counters. Examples: SHOW STATUS LIKE 'Threads_connected'; Current connection count. SHOW STATUS LIKE 'Threads_running'; Number of actively executing threads. SHOW STATUS LIKE 'Questions'; Total queries received since startup.

7. EXPLAIN — Analyze Query Execution Plans

The primary tool for SQL performance optimization:

EXPLAIN SELECT * FROM user WHERE name = '张三';

Focus on these columns: type — Access type (e.g., ALL = full table scan) possible_keys — Indexes that could be used key — Index actually chosen rows — Estimated rows examined Extra — Additional info (Using where, Using index, etc.)

A type = ALL typically indicates a full table scan, prompting index review.

MySQL 8.0 adds EXPLAIN ANALYZE for actual execution statistics:

EXPLAIN ANALYZE SELECT ...

8. SHOW INDEX — Examine Index Definitions

List indexes for a table: SHOW INDEX FROM user; Returns:

Index name

Indexed columns

Index type (BTREE, HASH, etc.)

Uniqueness

Cardinality (index cardinality)

Column order (A = ascending)

Filter for a specific index:

SHOW INDEX FROM user WHERE Key_name = 'idx_name';

Essential for diagnosing slow queries, index inefficiency, and design issues.

9. SHOW CREATE TABLE — Full Table Definition

Outputs the complete CREATE TABLE statement. Use \G for readability: SHOW CREATE TABLE user\G Compared to DESC, this reveals:

Columns

Primary key

Indexes

Unique constraints

Foreign keys

Storage engine

Character set

Collation

Also works without \G: SHOW CREATE TABLE user; Indispensable for troubleshooting schema, index, and character set problems.