MySQL CLI Cheat Sheet (`mysql`)¶

A practical cheat sheet for the mysql client: connect, inspect, troubleshoot, and export.

Connect¶

Basic connection:

mysql -h <host> -P 3306 -u <user> -p

Connect to a specific database:

mysql -h <host> -P 3306 -u <user> -p <db>

From a defaults file (avoids putting passwords in shell history):

# ~/.my.cnf
[client]
user=myuser
password=mypassword
host=127.0.0.1
port=3306

Then:

mysql

SELECT VERSION();
SELECT DATABASE();
SHOW DATABASES;
USE <db>;
SHOW TABLES;

Helpful client commands:

\s         -- status
\u <db>    -- use database
\G         -- vertical output (append to a query)

Tables and columns:

SHOW TABLE STATUS;
SHOW COLUMNS FROM <table>;
DESCRIBE <table>;

Show create statement:

SHOW CREATE TABLE <table>\G
SHOW CREATE VIEW <view>\G

Indexes:

SHOW INDEX FROM <table>;

List users:

SELECT user, host FROM mysql.user;

Show grants:

SHOW GRANTS FOR '<user>'@'<host>';

Current running queries:

SHOW FULL PROCESSLIST;

Kill a query:

KILL <process_id>;

Check locks (InnoDB):

SHOW ENGINE INNODB STATUS\G

Explain a query:

EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8+

Export a database with mysqldump:

mysqldump -h <host> -P 3306 -u <user> -p --databases <db> > dump.sql

Export a single table:

mysqldump -h <host> -P 3306 -u <user> -p <db> <table> > table.sql

Import:

mysql -h <host> -P 3306 -u <user> -p <db> < dump.sql

Run a query non-interactively:

mysql -h <host> -P 3306 -u <user> -p -D <db> -e "SELECT NOW();"

Pretty vertical output:

mysql -h <host> -u <user> -p -D <db> -e "SHOW PROCESSLIST\\G"