MySQL / MariaDB Cheatsheet

MySQL and MariaDB are popular open-source relational database management systems. Their CLI commands are largely identical.

Connecting

mysql -u root -p             # Connect as root (prompts for password)
mysql -u myuser -p my_db     # Connect to specific database as 'myuser'
mysql -h 127.0.0.1 -u root -p # Connect to a specific host

User Management

Run these inside the MySQL prompt.

-- Create a new user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password123';
 
-- Grant all privileges on a specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
 
-- Grant privileges for any host (replace localhost with '%')
CREATE USER 'username'@'%' IDENTIFIED BY 'password123';
 
-- Apply changes
FLUSH PRIVILEGES;
 
-- Show grants for a user
SHOW GRANTS FOR 'username'@'localhost';

Database and Table Operations

SHOW DATABASES;              -- List all databases
CREATE DATABASE my_db;       -- Create a database
USE my_db;                   -- Switch to database 'my_db'
 
SHOW TABLES;                 -- List all tables in current database
DESCRIBE my_table;           -- Show table structure
DROP TABLE my_table;         -- Delete a table
DROP DATABASE my_db;         -- Delete a database

Basic CRUD Operations

-- Create (Insert)
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
 
-- Read (Select)
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;
 
-- Update
UPDATE users SET email = '[email protected]' WHERE name = 'Alice';
 
-- Delete
DELETE FROM users WHERE id = 1;

Backups and Restoring

Run these in the standard Linux bash terminal, NOT inside MySQL.