mysql command-line client cheat sheet

The mysql client is the interactive SQL shell that ships with MySQL. MariaDB ships the same tool as mariadb and keeps mysql as a compatibility name. Options and commands below are from the MySQL 8.4 manual; most work the same in MariaDB.

Connecting

mysql -u appuser -p                         # local server, prompts for password
mysql -u appuser -p -h db1.example.com -P 3306 shop
mysql -u root -p --protocol=TCP -h 127.0.0.1  # force TCP instead of the socket
mysql -u root -p -S /var/run/mysqld/mysqld.sock  # explicit socket file

• -u / --user: account name.
• -p / --password: with no value the client prompts. If you give a value there must be no space: -pSECRET (insecure, visible in process lists and history).
• -h / --host: default localhost, which on Unix means the socket file, not TCP.
• -P / --port: TCP port, default 3306.
• -S / --socket: socket file for localhost connections.
• -D / --database, or a database name as the last argument.

Running SQL from scripts

mysql -u appuser -p shop -e "SELECT COUNT(*) FROM orders"
mysql -u appuser -p -B -N -e "SHOW DATABASES"   # tab-separated, no header
mysql -u appuser -p shop < schema.sql
mysql -u appuser -p -E -e "SHOW REPLICA STATUS"   # vertical output

• -e / --execute: run the statement and quit.
• -B / --batch: tab-separated output, no history file.
• -N / --skip-column-names: no header row.
• -E / --vertical: one line per column, like \G.

Storing credentials safely

Put credentials in an option file instead of on the command line. User files live in ~/.my.cnf; make them readable only by you. MySQL ignores world-writable option files on Unix.

[client]
user=appuser
password="CHANGE_ME"
host=db1.example.com

chmod 600 ~/.my.cnf
mysql --defaults-extra-file=/etc/mysql-backup.cnf -e "SELECT 1"

--defaults-extra-file and --defaults-file must come before other options. MySQL also offers mysql_config_editor, which writes an obfuscated ~/.mylogin.cnf used with --login-path=name.

Built-in client commands

Short forms are case-sensitive and do not take a semicolon.

• \G instead of ;: show the result vertically.
• \s (status): server version, connection, current database, SSL state.
• \u db (use db): change the default database.
• \. file.sql (source file.sql): run a script.
• \c (clear): throw away the statement you are typing.
• \W (warnings): show warnings after every statement.
• \T file (tee): copy everything on screen to a file.
• \P less -S (pager): page wide output (Unix only).
• \e (edit): edit the current statement in $EDITOR (Unix only).
• \q (quit/exit): leave the client.

Everyday SQL

SHOW DATABASES;
USE shop;
SHOW TABLES;
DESCRIBE orders;
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
SELECT CURRENT_USER(), USER(), VERSION();
SHOW GRANTS;
SHOW PROCESSLIST;
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW BINARY LOGS;
SHOW REPLICA STATUS\G

USER() shows the user and host you connected as; CURRENT_USER() shows which account the server actually matched. When they differ (for example an anonymous account matched), that explains many “Access denied” surprises.

Official documentation

• MySQL 8.4: mysql client options
• MySQL 8.4: mysql client commands
• MySQL 8.4: Using option files