MySQL command-line cheat sheet for server admins

Most MySQL admin work on a VPS happens in the mysql command-line client. This cheat sheet collects the commands you will reach for again and again, grouped by task, with a short note on what each one does. Everything here works on MySQL 8 and on current MariaDB releases unless a note says otherwise.

Before you start

• A Linux VPS with MySQL or MariaDB installed. If you still need one, see Install MySQL on Ubuntu.
• SSH access as root or a sudo user.
• A database account. The examples use the administrative account and the placeholder database shop.

Log in and out

# Root on Ubuntu/Debian usually authenticates through the system socket
sudo mysql

# A named user, prompting for the password
mysql -u dbadmin -p

# Log in and open a database straight away
mysql -u dbadmin -p shop

# Connect to a server over TCP on a specific host and port
mysql -h 127.0.0.1 -P 3306 -u dbadmin -p

Always use -p on its own so the client prompts for the password. Typing -pPASSWORD puts the password in your shell history and in the process list.

To leave the client, type exit, quit or \q. In MySQL 8, Ctrl + C cancels the statement that is running rather than closing the client. If you started a transaction with START TRANSACTION, finish it with COMMIT; or ROLLBACK; before you exit, so you know what was saved.

Find your way around

SHOW DATABASES;              -- list all databases you can see
USE shop;                    -- switch to a database
SELECT DATABASE();           -- show which database is selected
SHOW TABLES;                 -- list tables in the current database
DESCRIBE orders;             -- columns, types and keys of a table
SHOW CREATE TABLE orders\G   -- the full CREATE statement
SELECT VERSION();            -- server version
STATUS;                      -- connection details, uptime, charset (short form: \s)

Ending a statement with \G instead of ; prints each row vertically. That is much easier to read for wide results.

Users and privileges

SELECT user, host FROM mysql.user;                -- all accounts
SHOW GRANTS FOR 'dbadmin'@'localhost';            -- what an account may do
SHOW GRANTS;                                      -- your own privileges
SELECT CURRENT_USER();                            -- the account you matched
ALTER USER 'dbadmin'@'localhost' IDENTIFIED BY 'REPLACE_WITH_YOUR_OWN_PASSWORD';

The password above is a placeholder. Pick your own strong password. Creating accounts and granting rights is covered in Create MySQL users and grant privileges.

See what is running

SHOW FULL PROCESSLIST;

Each row is a connection. The useful columns are Id, User, db, Command, Time (seconds in the current state) and Info (the query text). To list only the long-running queries, filter through information_schema:

SELECT id, user, db, time, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep' AND time > 30
ORDER BY time DESC;

Stop a stuck query

KILL QUERY 1234;   -- stop the running statement, keep the connection
KILL 1234;         -- close the whole connection

Replace 1234 with the Id from the processlist.

Variables and status

Variables are settings. Status counters describe what the server has done since it started.

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE '%timeout%';

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

You can change many variables at runtime with SET GLOBAL, but the change is lost on restart. Make lasting changes in the config file; see MySQL and MariaDB config file locations.

Useful shell commands

These run from the Linux shell, not inside the client:

sudo systemctl status mysql          # service state (use mariadb on MariaDB)
sudo systemctl restart mysql         # restart the server
mysqladmin -u root -p status         # one-line health summary
mysqladmin -u root -p processlist    # processlist without opening the client
mysql -u dbadmin -p shop < file.sql  # run a SQL file against a database
mysql -u dbadmin -p -e "SHOW DATABASES;"   # run one statement and exit

On AlmaLinux and Rocky Linux the MySQL service is called mysqld. MariaDB uses mariadb on all of them.

Check it works

• STATUS; shows the server version, your user and the uptime.
• SHOW FULL PROCESSLIST; lists at least your own session.
• SHOW GLOBAL STATUS LIKE 'Uptime'; returns a number of seconds that matches how long the service has been running.

Troubleshooting

• "Access denied for user 'root'@'localhost'": on Ubuntu and Debian, root uses socket authentication. Run sudo mysql instead of mysql -u root -p.
• "Can't connect to local MySQL server through socket": the service is stopped. Check it with sudo systemctl status mysql and read the error log.
• A killed query stays in the processlist: it is rolling back. Wait, and watch Time grow until it disappears.
• "ERROR 1046: No database selected": run USE dbname; first, or prefix table names, as in shop.orders.
• The prompt shows -> and does nothing: the client is waiting for the end of the statement. Type ; and press Enter, or \c to discard it.

Next steps

• Find out which databases and tables use the most space: Check database and table sizes in MySQL.