CheatMySQL client MySQL shell and dump
Light · Postcard
102 × 152 mm · PDF
Dark · Postcard
102 × 152 mm · PDF
Ink saver · Postcard
102 × 152 mm · PDF
Light · A4 landscape
297 × 210 mm · PDF
Dark · A4 landscape
297 × 210 mm · PDF
Ink saver · A4 landscape
297 × 210 mm · PDF
Light · A3 landscape
420 × 297 mm · PDF
Dark · A3 landscape
420 × 297 mm · PDF
Ink saver · A3 landscape
420 × 297 mm · PDF
Start here
mysql -u <user> -p <db>
Connect and pick a database
SHOW DATABASES;List databases
USE <db>;Switch database
SHOW TABLES;List tables
DESCRIBE <table>;
Show columns
\GPrint a result vertically
mysqldump <db> > <file.sql>
Back up a database
\qQuit
Concepts
Server host:3306
Database USE <db>
Table
rows, columns, indexes
Connect
mysql -u <user> -p
Connect to localhost, prompt for password
mysql -u <user> -p -h <host> -P <port>
<db>
Connect to a remote server and database
mysql -e "<sql>" <db>
Run a statement and exit
mysql -S <socket>
Connect through a Unix socket
mysql --protocol=TCP -h 127.0.0.1
Force TCP instead of the socket
With host localhost the client uses the socket and ignores -P;
use 127.0.0.1 or --protocol=TCP.
mysql_config_editor set --login-path=<name> -u
<user> -h <host> -p
Store credentials in ~/.mylogin.cnf
mysql --login-path=<name>
Connect with a stored login path
mysql --ssl-mode=REQUIRED
Demand an encrypted connection
mysql --safe-updates
Refuse UPDATE/DELETE without key or LIMIT
Shell commands
\sShow connection and server status
\u <db>Switch database
\GRun and print vertically
\cCancel the statement being typed
\qQuit
\. <file.sql>
Run SQL from a file
\pPrint the current buffer
\eEdit the statement in $EDITOR
\P <pager>
Pipe output through a pager
\nTurn the pager off
\T <file>Append the session to a file (tee)
\! <cmd>Run a shell command
\R <prompt>
Change the prompt
\WShow warnings after each statement
DELIMITER //Change the statement terminator
Needed when defining stored routines whose body contains
semicolons; reset with DELIMITER ;
Inspect
SHOW DATABASES;List databases
SHOW TABLES;List tables in the current database
DESCRIBE <table>;
Show columns, types and keys
SHOW CREATE TABLE <table>\G
Show the full DDL
SHOW COLUMNS FROM <table>;
Same as DESCRIBE
SHOW INDEX FROM <table>;
List indexes
SHOW TABLE STATUS\G
Engine, row estimate and size per table
SHOW FULL PROCESSLIST;
See running queries
KILL <id>;
Stop a connection's query
SHOW VARIABLES LIKE '<pattern>';
Look up server variables
SHOW STATUS LIKE '<pattern>';
Look up server counters
SHOW WARNINGS;Show warnings of the last statement
SELECT VERSION(), DATABASE(), CURRENT_USER();
Server version, schema, account
Query
SELECT * FROM <table> LIMIT 10;
Peek at rows
SELECT COUNT(*) FROM <table>;
Count rows
EXPLAIN SELECT ...;
Show the query plan
EXPLAIN ANALYZE SELECT ...;
Run and time each plan step
Runs the query for real; available since MySQL 8.0.18 and
MariaDB uses ANALYZE SELECT instead.
EXPLAIN FORMAT=TREE SELECT ...;
Plan as an indented tree
START TRANSACTION;
Begin a transaction
ROLLBACK;Undo the open transaction
COMMIT;Make the transaction permanent
CREATE INDEX <idx> ON <table>
(<col>);
Add an index
ANALYZE TABLE <table>;
Refresh index statistics
| type | Meaning |
|---|---|
const |
one row via key |
ref |
index lookup |
range |
index range scan |
index |
full index scan |
ALL |
full table scan |
Users & grants
CREATE USER '<user>'@'<host>'
IDENTIFIED BY '<pw>';
Create an account
GRANT ALL ON <db>.* TO
'<user>'@'<host>';
Grant on one database
GRANT SELECT, INSERT ON <db>.<table> TO
'<user>'@'<host>';
Grant specific privileges
SHOW GRANTS FOR
'<user>'@'<host>';
List an account's privileges
REVOKE <priv> ON <db>.* FROM
'<user>'@'<host>';
Remove privileges
ALTER USER '<user>'@'<host>'
IDENTIFIED BY '<pw>';
Change a password
DROP USER
'<user>'@'<host>';
Delete an account
SELECT user, host FROM mysql.user;
List accounts
FLUSH PRIVILEGES;
Reload grant tables
Not needed after GRANT, REVOKE or CREATE USER; only after
editing the mysql.* tables directly.
Output
mysql -tTable format (default when interactive)
mysql -B -N -e "<sql>"
Tab-separated rows, no header
mysql -E -e "<sql>"
Vertical output
mysql -sSilent: less chatter
mysql --htmlHTML table output
mysql --xmlXML output
Dump & restore
mysqldump <db> > <file.sql>
Dump one database
mysqldump --single-transaction <db> >
<file.sql>
Consistent InnoDB dump without locks
mysqldump --databases <db1> <db2>
Dump several, with CREATE DATABASE
mysqldump --all-databases
Dump everything
mysqldump <db> <table1> <table2>
Dump chosen tables
mysqldump --no-data <db>
Schema only
mysqldump --no-create-info <db>
Data only
mysqldump --routines --triggers --events <db>
Include stored code
Triggers are on by default; routines and events are not.
mysqldump --where="<cond>" <db>
<table>
Dump matching rows only
mysqldump --result-file=<file.sql> <db>
Write to a file without shell redirect
mysql <db> < <file.sql>
Restore a dump
mysqldump <db> | gzip > <file.sql.gz>
Compressed dump
gunzip -c <file.sql.gz> | mysql <db>
Restore a compressed dump
LOAD DATA LOCAL INFILE '<f>' INTO TABLE
<t>;
Bulk load delimited files
LOCAL needs local_infile enabled on both client and server.
Recipes
CREATE DATABASE <db> CHARACTER SET utf8mb4;CREATE USER '<user>'@'%' IDENTIFIED BY '<pw>';GRANT ALL ON <db>.* TO '<user>'@'%';SHOW GRANTS FOR '<user>'@'%';mysqldump --single-transaction --routines <db> \ | mysql -h <host2> -u <user> -p <db>mysql -B -e "SELECT id, name FROM <table>" <db> \ | tr '\t' ',' > out.csvSTART TRANSACTION;DELETE FROM <table> WHERE <cond>;SELECT ROW_COUNT();# looks right? COMMIT; otherwise ROLLBACK;
