MySQL/MariaDB Cheatsheet
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 |
