12 Essential MySQL Online Commands Every DBA Should Know

1. View MySQL version

SELECT VERSION();

Or: mysql -V Application scenarios: verify version before an upgrade, check compatibility, and confirm support for new features.

2. View current connections

SHOW STATUS LIKE 'Threads_connected';

View maximum connections: SHOW VARIABLES LIKE 'max_connections'; Application: troubleshooting the “Too many connections” problem.

3. View all connections

SHOW PROCESSLIST;

Show full SQL statements: SHOW FULL PROCESSLIST; Sample output: Id User Host DB Command Time State ... Typical scenarios: slow SQL, lock wait, long transaction, deadlock.

4. Kill abnormal connection

KILL 12345;

Example: KILL 1024; Use when a query is stuck (e.g., waiting for a lock).

5. View global status

SHOW GLOBAL STATUS;

Specific metrics:

SHOW GLOBAL STATUS LIKE 'Questions';

SHOW GLOBAL STATUS LIKE 'Uptime';

Common metrics:

Questions – total queries

Connections – total connections

Uptime – server uptime

Slow_queries – number of slow queries

6. View MySQL configuration variables

SHOW VARIABLES;

Specific variables:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SHOW VARIABLES LIKE 'max_connections';

Check buffer‑pool size, connection limits, character set, etc.

7. View lock‑wait information

MySQL 8:

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

MySQL 5.7: SHOW ENGINE INNODB STATUS\G Problem example: “Lock wait timeout exceeded”.

8. View slow‑query settings

SHOW VARIABLES LIKE 'slow_query_log';

Check threshold: SHOW VARIABLES LIKE 'long_query_time'; Log file location: SHOW VARIABLES LIKE 'slow_query_log_file'; Application: locate performance‑bottleneck SQL.

9. View table size

SELECT table_name,
       ROUND(data_length/1024/1024,2) AS data_mb,
       ROUND(index_length/1024/1024,2) AS index_mb
FROM information_schema.tables
WHERE table_schema='your_db';

Use cases: assess disk usage, identify large tables, plan sharding.

10. View index information

SHOW INDEX FROM user;

Check whether an index exists, is used, or duplicated.

11. Analyze SQL execution plan

MySQL 5.7: EXPLAIN SELECT * FROM user WHERE id=1; MySQL 8: EXPLAIN ANALYZE SELECT * FROM user WHERE id=1; Key fields to examine: type, rows, possible_keys, key, Extra.

12. View master‑slave replication status

Master: SHOW MASTER STATUS; Slave (MySQL 5.7) or replica (MySQL 8):

SHOW SLAVE STATUS\G   -- or   SHOW REPLICA STATUS\G

Core metrics:

Replica_IO_Running

Replica_SQL_Running

Seconds_Behind_Source

Normal state: both running = “Yes”, lag = 0.