Cheatsheets

# Debian / Ubuntu (MariaDB)
sudo apt install mariadb-server mariadb-client
 
# Debian / Ubuntu (MySQL)
sudo apt install mysql-server mysql-client
 
# RHEL / CentOS / Fedora (MariaDB)
sudo dnf install mariadb-server mariadb
 
# Arch Linux
sudo pacman -S mariadb
sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

# MariaDB
sudo systemctl enable --now mariadb
sudo systemctl restart mariadb
sudo systemctl status mariadb
 
# MySQL
sudo systemctl enable --now mysql
sudo systemctl restart mysql
sudo systemctl status mysql

sudo mysql_secure_installation
# Prompts to: set root password, remove anonymous users,
# disallow remote root login, remove test database, reload privileges.

mysql -u root -p                   # Connect as root (prompts for password)
mysql -u myuser -p my_db           # Connect to a specific database as 'myuser'
mysql -h 127.0.0.1 -u root -p     # Connect to a specific host
mysql -u root -p -e "SELECT 1;"   # Execute a query inline and exit

-- Create a new user (localhost only)
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password123';
 
-- Create a user accessible from any host
CREATE USER 'username'@'%' IDENTIFIED BY 'password123';
 
-- Grant all privileges on a specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
 
-- Grant specific privileges only
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';
 
-- Apply privilege changes
FLUSH PRIVILEGES;
 
-- Show grants for a user
SHOW GRANTS FOR 'username'@'localhost';
 
-- Change a user's password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
 
-- Drop a user
DROP USER 'username'@'localhost';

SHOW DATABASES;                    -- List all databases
CREATE DATABASE my_db;             -- Create a database
CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE my_db;                         -- Switch to database 'my_db'
 
SHOW TABLES;                       -- List all tables in current database
DESCRIBE my_table;                 -- Show table structure (columns, types)
SHOW CREATE TABLE my_table;        -- Show the CREATE TABLE statement
DROP TABLE my_table;               -- Delete a table
DROP DATABASE my_db;               -- Delete a database

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

-- Create an index
CREATE INDEX idx_email ON users (email);
 
-- Create a unique index
CREATE UNIQUE INDEX idx_unique_email ON users (email);
 
-- Show indexes on a table
SHOW INDEX FROM users;
 
-- Drop an index
DROP INDEX idx_email ON users;

# Backup a single database
mysqldump -u root -p database_name > backup.sql
 
# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql
 
# Backup only the table structure (no data)
mysqldump -u root -p --no-data database_name > structure.sql
 
# Backup with compression
mysqldump -u root -p database_name | gzip > backup.sql.gz
 
# Restore a database from a file
mysql -u root -p database_name < backup.sql
 
# Restore from compressed backup
gunzip < backup.sql.gz | mysql -u root -p database_name

-- Show running queries
SHOW PROCESSLIST;
 
-- Kill a long-running query by its ID
KILL <process-id>;
 
-- Show query execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
 
-- Show database engine status
SHOW ENGINE INNODB STATUS;
 
-- Show global status variables
SHOW GLOBAL STATUS LIKE 'Threads%';