🐬
MySQL / MariaDB cheat sheet
Commands for the mysql client and related tools. SQL statements end with a semicolon; end a query with \G instead to see rows vertically.
Click any command to copy it.
Connect and explore
| Command | What it does |
|---|---|
mysql -u user -p | Connect and prompt for the password. |
mysql -u user -p -h host database | Connect to a remote host and database. |
SHOW DATABASES; | List databases. |
USE dbname; | Select a database. |
SHOW TABLES; | List tables. |
DESCRIBE tablename; | Show a table's columns. |
SHOW CREATE TABLE tablename\G | Show the full CREATE statement. |
SHOW PROCESSLIST; | Show running queries and connections. |
Backup and restore
| Command | What it does |
|---|---|
mysqldump -u user -p dbname > dump.sql | Dump one database. |
mysqldump -u user -p --single-transaction dbname > dump.sql | Consistent InnoDB dump without locking tables. |
mysqldump -u user -p --all-databases > all.sql | Dump everything. |
mysql -u user -p dbname < dump.sql | Restore a dump (the database must exist). |
mysqldump -u user -p dbname | gzip > dump.sql.gz | Compress the dump on the fly. |
Users and privileges
| Command | What it does |
|---|---|
CREATE DATABASE appdb CHARACTER SET utf8mb4; | Create a database with full Unicode support. |
CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret'; | Create a user. |
GRANT ALL ON appdb.* TO 'app'@'localhost'; | Grant access to one database. |
SHOW GRANTS FOR 'app'@'localhost'; | Show a user's privileges. |
DROP USER 'app'@'localhost'; | Destructive: Delete a user. |
DROP DATABASE appdb; | Destructive: Permanently delete a database. |
Diagnostics
| Command | What it does |
|---|---|
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) AS mb FROM information_schema.tables GROUP BY table_schema; | Size of each database in MB. |
SHOW VARIABLES LIKE 'max_connections'; | Show a configuration variable. |
SHOW STATUS LIKE 'Threads_connected'; | Show a status counter. |
EXPLAIN SELECT ...; | Show a query's execution plan. |
KILL 1234; | Terminate a connection by ID from SHOW PROCESSLIST. |
mysqlcheck -u user -p --all-databases | Check all tables for problems. |
Frequently asked questions
How do I see which queries are running?
Run SHOW FULL PROCESSLIST; to see each connection's current statement and how long it has been running.
Why use --single-transaction?
With InnoDB tables it takes a consistent snapshot without locking tables, so your site keeps working during the dump.