MySQL/MariaDB Cheatsheet
Quick reference for common MySQL and MariaDB command-line administration tasks
MySQL and MariaDB share the same client syntax for most day-to-day administration. This cheatsheet covers common commands for connecting, managing databases and users, running backups, and checking server state.
Connect and Exit
Open a SQL session and disconnect safely.
| Command | Description |
|---|---|
mysql -u root -p | Connect as root (prompt for password) |
mysql -u user -p -h 127.0.0.1 | Connect to specific host |
mysql -u user -p -P 3306 | Connect on custom port |
sudo mysql | Login using Unix socket auth (common on Debian/Ubuntu) |
exit | Leave MySQL/MariaDB shell |
Basic SQL Checks
Run quick checks after connecting.
| SQL | Description |
|---|---|
SELECT VERSION(); | Show server version |
SHOW DATABASES; | List all databases |
USE db_name; | Switch active database |
SHOW TABLES; | List tables in current database |
SHOW GRANTS FOR 'user'@'host'; | Show user privileges |
Database Management
Create, inspect, and remove databases.
| SQL | Description |
|---|---|
CREATE DATABASE db_name; | Create a database |
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | Create DB with charset/collation |
SHOW DATABASES LIKE 'db_name'; | Check if DB exists |
DROP DATABASE db_name; | Delete a database |
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA; | List schemas via information schema |
User Management
Create users and manage authentication.
| SQL | Description |
|---|---|
CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong_password'; | Create local user |
CREATE USER 'app'@'%' IDENTIFIED BY 'strong_password'; | Create remote-capable user |
ALTER USER 'app'@'localhost' IDENTIFIED BY 'new_password'; | Change password |
DROP USER 'app'@'localhost'; | Delete user |
SELECT user, host FROM mysql.user; | List users and hosts |
Privileges
Grant, review, and revoke access.
| SQL | Description |
|---|---|
GRANT ALL PRIVILEGES ON db_name.* TO 'app'@'localhost'; | Grant full DB access |
GRANT SELECT,INSERT,UPDATE,DELETE ON db_name.* TO 'app'@'localhost'; | Grant specific privileges |
REVOKE ALL PRIVILEGES ON db_name.* FROM 'app'@'localhost'; | Remove DB privileges |
SHOW GRANTS FOR 'app'@'localhost'; | Review granted privileges |
FLUSH PRIVILEGES; | Reload grant tables |
Backup and Restore
Use mysqldump for exports and imports.
| Command | Description |
|---|---|
mysqldump -u root -p db_name > db_name.sql | Backup one database |
mysqldump -u root -p --databases db1 db2 > multi.sql | Backup multiple databases |
mysqldump -u root -p --all-databases > all.sql | Backup all databases |
mysql -u root -p db_name < db_name.sql | Restore into existing database |
gunzip < db_name.sql.gz | mysql -u root -p db_name | Restore compressed SQL dump |
Import and Export Data
Common import/export patterns.
| Command | Description |
|---|---|
mysql -u root -p -e "SHOW DATABASES;" | Run one-off SQL from shell |
mysql -u root -p db_name -e "SOURCE /path/file.sql" | Import SQL file via client |
mysql -u root -p -Nse "SELECT NOW();" | Non-interactive query output |
mysqldump -u root -p db_name table_name > table.sql | Export one table |
mysql -u root -p db_name < table.sql | Import one table dump |
Service and Health Checks
Verify daemon state and open ports.
| Command | Description |
|---|---|
systemctl status mysql | Check MySQL service status |
systemctl status mariadb | Check MariaDB service status |
sudo systemctl restart mysql | Restart MySQL service |
sudo systemctl restart mariadb | Restart MariaDB service |
ss -tulpn | grep 3306 | Confirm server is listening |
Common Troubleshooting
Quick checks for frequent connection and auth problems.
| Issue | Check |
|---|---|
Access denied for user | Verify user/host pair and password, then check SHOW GRANTS |
Can't connect to local MySQL server through socket | Check service status and socket path in config |
Can't connect to MySQL server on 'host' | Confirm host/port reachability and firewall rules |
Unknown database | Verify database name with SHOW DATABASES; |
| Restore fails on collation/charset | Ensure server supports source collation and set utf8mb4 where needed |
Related Guides
Use these articles for detailed MySQL/MariaDB workflows.
| Guide | Description |
|---|---|
How to Manage MySQL Databases and Users from the Command Line | End-to-end admin workflow |
How to Create MySQL Users Accounts and Grant Privileges | User and privilege setup |
How to Back Up and Restore MySQL Databases with Mysqldump | Backup and restore patterns |
How to Show a List of All Databases in MySQL | Database discovery commands |
List (Show) Tables in a MySQL Database | Table listing commands |
How to Check the MySQL Version | Version checks and verification |