MariaDB Cheatsheet¶

MariaDB - Open Source Relational Database

MariaDB is a popular open source relational database that was created as a fork of MySQL. It's designed to be a drop-in replacement for MySQL with additional features, better performance, and enhanced security.

Table of Contents¶

Installation
Basic Commands
Database Operations
Table Operations
Data Types
CRUD Operations
Query Operations
Indexes
Views
Stored Procedures
Functions
Triggers
Transactions
User Management
Backup and Restore
Replication
Performance Optimization
Monitoring
Security
Best Practices

Installation¶

Ubuntu/Debian¶

# Update package index
sudo apt update

# Install MariaDB server
sudo apt install mariadb-server mariadb-client

# Secure installation
sudo mysql_secure_installation

# Start MariaDB service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Check status
sudo systemctl status mariadb

# Connect to MariaDB
sudo mysql -u root -p

CentOS/RHEL/Fedora¶

# Install MariaDB
sudo yum install mariadb-server mariadb
# or for newer versions
sudo dnf install mariadb-server mariadb

# Start MariaDB service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Secure installation
sudo mysql_secure_installation

# Connect to MariaDB
mysql -u root -p

macOS¶

# Using Homebrew
brew install mariadb

# Start MariaDB
brew services start mariadb

# Secure installation
mysql_secure_installation

# Connect to MariaDB
mysql -u root -p

Docker¶

# Pull MariaDB image
docker pull mariadb:latest

# Run MariaDB container
docker run --name mariadb-container \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e MYSQL_DATABASE=mydatabase \
  -e MYSQL_USER=myuser \
  -e MYSQL_PASSWORD=mypassword \
  -p 3306:3306 \
  -d mariadb:latest

# Connect to MariaDB in container
docker exec -it mariadb-container mysql -u root -p

# Docker Compose
cat > docker-compose.yml << EOF
version: '3.8'
services:
  mariadb:
    image: mariadb:latest
    container_name: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydatabase
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
    ports:
      - "3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql

volumes:
  mariadb_data:
EOF

docker-compose up -d

Basic Commands¶

Connecting to MariaDB¶

# Connect as root
mysql -u root -p

# Connect to specific database
mysql -u username -p database_name

# Connect to remote server
mysql -h hostname -u username -p database_name

# Connect with specific port
mysql -h hostname -P 3307 -u username -p

# Execute SQL from command line
mysql -u username -p -e "SELECT VERSION();"

# Execute SQL from file
mysql -u username -p database_name < script.sql

Basic Information Commands¶

-- Show MariaDB version
SELECT VERSION();

-- Show current user
SELECT USER();

-- Show current database
SELECT DATABASE();

-- Show current date and time
SELECT NOW();

-- Show databases
SHOW DATABASES;

-- Show tables
SHOW TABLES;

-- Show table structure
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

-- Show create table statement
SHOW CREATE TABLE table_name;

-- Show processes
SHOW PROCESSLIST;

-- Show status
SHOW STATUS;

-- Show variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';

-- Exit MariaDB
EXIT;
QUIT;

Database Operations¶

Creating and Managing Databases¶

-- Create database
CREATE DATABASE mydatabase;
CREATE DATABASE IF NOT EXISTS mydatabase;

-- Create database with character set
CREATE DATABASE mydatabase 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Use database
USE mydatabase;

-- Show current database
SELECT DATABASE();

-- Drop database
DROP DATABASE mydatabase;
DROP DATABASE IF EXISTS mydatabase;

-- Show database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'mydatabase'
GROUP BY table_schema;

Database Information¶

-- Show all databases with sizes
SELECT 
    table_schema AS 'Database',
    COUNT(table_name) AS 'Tables',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

-- Show database character set
SELECT 
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mydatabase';

Table Operations¶

Creating Tables¶

-- Basic table creation
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table with indexes
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT,
    sku VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT idx_search (name, description)
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(255)
);

-- Create table from query
CREATE TABLE user_summary AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users
GROUP BY DATE(created_at);

Modifying Tables¶

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with position
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) AFTER username;

-- Modify column
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL;

-- Change column name and type
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(60) NOT NULL;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add index
ALTER TABLE users ADD INDEX idx_email (email);

-- Add foreign key
ALTER TABLE orders ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Drop foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;

-- Rename table
ALTER TABLE users RENAME TO customers;
RENAME TABLE customers TO users;

-- Drop table
DROP TABLE temp_data;
DROP TABLE IF EXISTS temp_data;

Table Information¶

-- Show table structure
DESCRIBE users;
SHOW COLUMNS FROM users;

-- Show table creation statement
SHOW CREATE TABLE users;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

-- Show table indexes
SHOW INDEXES FROM users;

-- Show table size
SELECT 
    table_name AS 'Table',
    table_rows AS 'Rows',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

Data Types¶

Numeric Data Types¶

-- Integer types
TINYINT     -- 1 byte, -128 to 127
SMALLINT    -- 2 bytes, -32,768 to 32,767
MEDIUMINT   -- 3 bytes, -8,388,608 to 8,388,607
INT         -- 4 bytes, -2,147,483,648 to 2,147,483,647
BIGINT      -- 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Unsigned integers
INT UNSIGNED        -- 0 to 4,294,967,295
BIGINT UNSIGNED     -- 0 to 18,446,744,073,709,551,615

-- Decimal types
DECIMAL(10,2)       -- Fixed-point number
NUMERIC(10,2)       -- Synonym for DECIMAL
FLOAT               -- Single-precision floating-point
DOUBLE              -- Double-precision floating-point

-- Boolean
BOOLEAN             -- Synonym for TINYINT(1)
BOOL                -- Synonym for TINYINT(1)

String Data Types¶

-- Fixed-length strings
CHAR(10)            -- Fixed-length string, 0-255 characters

-- Variable-length strings
VARCHAR(255)        -- Variable-length string, 0-65,535 characters

-- Text types
TINYTEXT            -- 0-255 characters
TEXT                -- 0-65,535 characters
MEDIUMTEXT          -- 0-16,777,215 characters
LONGTEXT            -- 0-4,294,967,295 characters

-- Binary types
BINARY(10)          -- Fixed-length binary string
VARBINARY(255)      -- Variable-length binary string
TINYBLOB            -- 0-255 bytes
BLOB                -- 0-65,535 bytes
MEDIUMBLOB          -- 0-16,777,215 bytes
LONGBLOB            -- 0-4,294,967,295 bytes

-- Enumeration and Set
ENUM('small', 'medium', 'large')
SET('red', 'green', 'blue')

Date and Time Data Types¶

-- Date and time types
DATE                -- YYYY-MM-DD
TIME                -- HH:MM:SS
DATETIME            -- YYYY-MM-DD HH:MM:SS
TIMESTAMP           -- YYYY-MM-DD HH:MM:SS (with timezone)
YEAR                -- YYYY

-- Examples
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CRUD Operations¶

Insert Operations¶

-- Insert single row
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password) VALUES
    ('alice', 'alice@example.com', 'password1'),
    ('bob', 'bob@example.com', 'password2'),
    ('charlie', 'charlie@example.com', 'password3');

-- Insert with ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john.new@example.com', 'new_password')
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    password = VALUES(password),
    updated_at = CURRENT_TIMESTAMP;

-- Insert from SELECT
INSERT INTO user_backup (username, email, created_at)
SELECT username, email, created_at 
FROM users 
WHERE created_at > '2023-01-01';

-- Insert with IGNORE (skip duplicates)
INSERT IGNORE INTO users (username, email, password)
VALUES ('existing_user', 'existing@example.com', 'password');

Select Operations¶

-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with alias
SELECT username AS user, email AS email_address FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE created_at > '2023-01-01';

-- Select with multiple conditions
SELECT * FROM users 
WHERE username LIKE 'a%' 
AND created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- Select with ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;

-- Select with LIMIT
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Select DISTINCT
SELECT DISTINCT username FROM users;

-- Select with aggregation
SELECT COUNT(*) FROM users;
SELECT COUNT(*), AVG(id), MIN(created_at), MAX(created_at) FROM users;

-- Select with GROUP BY
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users 
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Select with HAVING
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users 
GROUP BY DATE(created_at)
HAVING COUNT(*) > 5;

Update Operations¶

-- Update single row
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- Update multiple columns
UPDATE users 
SET email = 'john.doe@example.com', 
    updated_at = CURRENT_TIMESTAMP 
WHERE username = 'john_doe';

-- Update with conditions
UPDATE users 
SET password = 'new_hashed_password' 
WHERE created_at < '2023-01-01';

-- Update with JOIN
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.order_date
WHERE o.order_date = (
    SELECT MAX(order_date) 
    FROM orders 
    WHERE user_id = u.id
);

-- Update with LIMIT
UPDATE users 
SET status = 'inactive' 
WHERE last_login < '2022-01-01'
LIMIT 100;

Delete Operations¶

-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM users WHERE created_at < '2022-01-01';

-- Delete with JOIN
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

-- Delete with LIMIT
DELETE FROM users 
WHERE status = 'inactive' 
LIMIT 100;

-- Delete all rows (but keep table structure)
DELETE FROM users;

-- Truncate table (faster than DELETE)
TRUNCATE TABLE users;

Query Operations¶

Joins¶

-- INNER JOIN
SELECT u.username, o.total_amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.username, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.username, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN (using UNION)
SELECT u.username, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN
SELECT u.username, p.name
FROM users u
CROSS JOIN products p;

-- Self JOIN
SELECT u1.username, u2.username AS manager
FROM users u1
LEFT JOIN users u2 ON u1.manager_id = u2.id;

Subqueries¶

-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 100);

-- Subquery in SELECT clause
SELECT 
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- Correlated subquery
SELECT * FROM users u1
WHERE id = (SELECT user_id FROM orders WHERE user_id = u1.id ORDER BY total_amount DESC LIMIT 1);

-- EXISTS subquery
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

-- NOT EXISTS subquery
SELECT * FROM users
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

Window Functions (MariaDB 10.2+)¶

-- ROW_NUMBER
SELECT 
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- RANK and DENSE_RANK
SELECT 
    username,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank
FROM user_spending;

-- PARTITION BY
SELECT 
    username,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Running totals
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

-- LAG and LEAD
SELECT 
    order_date,
    total_amount,
    LAG(total_amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(total_amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM orders;

Common Table Expressions (CTE) - MariaDB 10.2+¶

-- Basic CTE
WITH young_users AS (
    SELECT * FROM users WHERE YEAR(created_at) >= 2023
)
SELECT * FROM young_users WHERE username LIKE 'a%';

-- Recursive CTE
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n+1, next_fib_n, fib_n + next_fib_n 
    FROM fibonacci 
    WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;

-- Multiple CTEs
WITH 
high_value_orders AS (
    SELECT * FROM orders WHERE total_amount > 1000
),
premium_users AS (
    SELECT DISTINCT user_id FROM high_value_orders
)
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
JOIN premium_users pu ON u.id = pu.user_id
JOIN high_value_orders o ON u.id = o.user_id
GROUP BY u.username;

Indexes¶

Creating Indexes¶

-- Simple index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_users_username_email ON users(username, email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username_unique ON users(username);

-- Partial index (with WHERE clause)
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Functional index
CREATE INDEX idx_users_email_lower ON users((LOWER(email)));

-- Full-text index
CREATE FULLTEXT INDEX idx_products_search ON products(name, description);

-- Spatial index (for geometry columns)
CREATE SPATIAL INDEX idx_locations_point ON locations(coordinates);

Managing Indexes¶

-- Show indexes for a table
SHOW INDEXES FROM users;

-- Show index usage
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_users_email ON users;
ALTER TABLE users DROP INDEX idx_users_email;

-- Add index to existing table
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD UNIQUE INDEX idx_username_unique (username);

-- Analyze table to update index statistics
ANALYZE TABLE users;

-- Check index cardinality
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME;

Views¶

Creating Views¶

-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE status = 'active';

-- Complex view with joins
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- Updatable view
CREATE VIEW user_profiles AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- View with CHECK OPTION
CREATE VIEW premium_users AS
SELECT id, username, email, membership_level
FROM users
WHERE membership_level = 'premium'
WITH CHECK OPTION;

Managing Views¶

-- Show views
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Show view definition
SHOW CREATE VIEW active_users;

-- Update view
ALTER VIEW active_users AS
SELECT id, username, email, created_at
FROM users 
WHERE status = 'active';

-- Drop view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

-- Update data through view (if updatable)
UPDATE user_profiles 
SET email = 'newemail@example.com' 
WHERE id = 1;

Stored Procedures¶

Creating Stored Procedures¶

-- Simple stored procedure
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS user_count FROM users;
END //
DELIMITER ;

-- Procedure with parameters
DELIMITER //
CREATE PROCEDURE GetUsersByStatus(IN user_status VARCHAR(20))
BEGIN
    SELECT * FROM users WHERE status = user_status;
END //
DELIMITER ;

-- Procedure with IN, OUT, and INOUT parameters
DELIMITER //
CREATE PROCEDURE GetUserStats(
    IN user_status VARCHAR(20),
    OUT user_count INT,
    OUT avg_age DECIMAL(5,2)
)
BEGIN
    SELECT COUNT(*), AVG(age) 
    INTO user_count, avg_age
    FROM users 
    WHERE status = user_status;
END //
DELIMITER ;

-- Procedure with conditional logic
DELIMITER //
CREATE PROCEDURE UpdateUserStatus(
    IN user_id INT,
    IN new_status VARCHAR(20)
)
BEGIN
    DECLARE user_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO user_exists 
    FROM users 
    WHERE id = user_id;

    IF user_exists > 0 THEN
        UPDATE users 
        SET status = new_status, updated_at = NOW() 
        WHERE id = user_id;
        SELECT 'User updated successfully' AS message;
    ELSE
        SELECT 'User not found' AS message;
    END IF;
END //
DELIMITER ;

Calling Stored Procedures¶

-- Call simple procedure
CALL GetUserCount();

-- Call procedure with parameters
CALL GetUsersByStatus('active');

-- Call procedure with OUT parameters
CALL GetUserStats('active', @count, @avg_age);
SELECT @count, @avg_age;

-- Call procedure with error handling
CALL UpdateUserStatus(1, 'inactive');

Managing Stored Procedures¶

-- Show stored procedures
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Show procedure definition
SHOW CREATE PROCEDURE GetUserCount;

-- Drop stored procedure
DROP PROCEDURE GetUserCount;
DROP PROCEDURE IF EXISTS GetUserCount;

Functions¶

Creating Functions¶

-- Simple function
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN YEAR(CURDATE()) - YEAR(birth_date);
END //
DELIMITER ;

-- Function with conditional logic
DELIMITER //
CREATE FUNCTION GetUserLevel(total_spent DECIMAL(10,2))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(20);

    IF total_spent >= 10000 THEN
        SET level = 'platinum';
    ELSEIF total_spent >= 5000 THEN
        SET level = 'gold';
    ELSEIF total_spent >= 1000 THEN
        SET level = 'silver';
    ELSE
        SET level = 'bronze';
    END IF;

    RETURN level;
END //
DELIMITER ;

Using Functions¶

-- Use function in SELECT
SELECT 
    username,
    birth_date,
    CalculateAge(birth_date) AS age
FROM users;

-- Use function in WHERE clause
SELECT * FROM users 
WHERE CalculateAge(birth_date) >= 18;

-- Use function with aggregation
SELECT 
    GetUserLevel(SUM(total_amount)) AS level,
    COUNT(*) AS user_count
FROM orders
GROUP BY user_id;

Managing Functions¶

-- Show functions
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- Show function definition
SHOW CREATE FUNCTION CalculateAge;

-- Drop function
DROP FUNCTION CalculateAge;
DROP FUNCTION IF EXISTS CalculateAge;

Triggers¶

Creating Triggers¶

-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- AFTER INSERT trigger
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, timestamp)
    VALUES (NEW.id, 'INSERT', NOW());
END //
DELIMITER ;

-- BEFORE UPDATE trigger
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- AFTER UPDATE trigger with conditional logic
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO user_status_history (user_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END //
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users (original_id, username, email, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, NOW());
END //
DELIMITER ;

Managing Triggers¶

-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users';

-- Show trigger definition
SHOW CREATE TRIGGER before_user_insert;

-- Drop trigger
DROP TRIGGER before_user_insert;
DROP TRIGGER IF EXISTS before_user_insert;

Transactions¶

Basic Transactions¶

-- Start transaction
START TRANSACTION;
-- or
BEGIN;

-- Perform operations
INSERT INTO users (username, email) VALUES ('test_user', 'test@example.com');
UPDATE users SET status = 'active' WHERE username = 'test_user';

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

Transaction with Savepoints¶

START TRANSACTION;

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');

SAVEPOINT sp2;

INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');

-- Rollback to savepoint
ROLLBACK TO sp1;

-- Commit remaining changes
COMMIT;

Transaction Isolation Levels¶

-- Show current isolation level
SELECT @@transaction_isolation;

-- Set isolation level for session
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set isolation level for next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Locking¶

-- Shared lock (read lock)
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (write lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- Table-level locks
LOCK TABLES users READ;
LOCK TABLES users WRITE;
UNLOCK TABLES;

User Management¶

Creating Users¶

-- Create user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

-- Create user with specific host
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'password';

-- Create user if not exists
CREATE USER IF NOT EXISTS 'newuser'@'localhost' IDENTIFIED BY 'password';

Granting Privileges¶

-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

-- Grant specific privileges on specific database
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'appuser'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, UPDATE ON mydatabase.users TO 'readonly'@'localhost';

-- Grant specific column privileges
GRANT SELECT (username, email), UPDATE (email) ON mydatabase.users TO 'limited'@'localhost';

-- Grant execute privilege for stored procedures
GRANT EXECUTE ON mydatabase.* TO 'appuser'@'localhost';

-- Grant with grant option
GRANT SELECT ON mydatabase.* TO 'manager'@'localhost' WITH GRANT OPTION;

-- Apply changes
FLUSH PRIVILEGES;

Managing User Privileges¶

-- Show user privileges
SHOW GRANTS FOR 'username'@'localhost';
SHOW GRANTS FOR CURRENT_USER();

-- Revoke privileges
REVOKE INSERT, UPDATE ON mydatabase.* FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

-- Change user password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');

-- Rename user
RENAME USER 'oldname'@'localhost' TO 'newname'@'localhost';

-- Drop user
DROP USER 'username'@'localhost';
DROP USER IF EXISTS 'username'@'localhost';

-- Show all users
SELECT User, Host FROM mysql.user;

User Account Management¶

-- Lock user account
ALTER USER 'username'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

-- Set password expiration
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Require SSL
ALTER USER 'username'@'localhost' REQUIRE SSL;

-- Set resource limits
ALTER USER 'username'@'localhost' 
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 100
     MAX_CONNECTIONS_PER_HOUR 10;

Backup and Restore¶

mysqldump Backup¶

# Backup single database
mysqldump -u username -p database_name > backup.sql

# Backup multiple databases
mysqldump -u username -p --databases db1 db2 db3 > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases > all_databases.sql

# Backup specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Backup with compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

# Backup with additional options
mysqldump -u username -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  database_name > backup.sql

# Backup structure only (no data)
mysqldump -u username -p --no-data database_name > structure.sql

# Backup data only (no structure)
mysqldump -u username -p --no-create-info database_name > data.sql

Restore from Backup¶

# Restore database
mysql -u username -p database_name < backup.sql

# Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

# Restore all databases
mysql -u username -p < all_databases.sql

# Create database and restore
mysql -u username -p -e "CREATE DATABASE newdatabase;"
mysql -u username -p newdatabase < backup.sql

Binary Log Backup¶

-- Show binary log status
SHOW MASTER STATUS;

-- Show binary logs
SHOW BINARY LOGS;

-- Flush logs
FLUSH LOGS;

-- Purge old logs
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

Point-in-Time Recovery¶

# Extract SQL from binary log
mysqlbinlog mysql-bin.000001 > binlog.sql

# Extract SQL for specific time range
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
           --stop-datetime="2023-01-01 23:59:59" \
           mysql-bin.000001 > recovery.sql

# Apply recovery
mysql -u username -p database_name < recovery.sql

Replication¶

Master Configuration¶

-- Enable binary logging (in my.cnf)
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-format=ROW

-- Create replication user
CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;

Slave Configuration¶

-- Configure slave (in my.cnf)
[mysqld]
server-id=2
relay-log=relay-bin
read-only=1

-- Configure replication
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Stop slave
STOP SLAVE;

Replication Monitoring¶

-- Check replication status
SHOW SLAVE STATUS\G

-- Check master status
SHOW MASTER STATUS;

-- Show processlist
SHOW PROCESSLIST;

-- Check replication lag
SELECT 
    UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag_seconds
FROM (
    SELECT FROM_UNIXTIME(variable_value) AS ts
    FROM information_schema.global_status
    WHERE variable_name = 'SLAVE_SQL_RUNNING_STATE'
) AS subquery;

Performance Optimization¶

Query Optimization¶

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'john@example.com';

-- Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);

-- Use ANALYZE TABLE to update statistics
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Check table for errors
CHECK TABLE users;

-- Repair table
REPAIR TABLE users;

Configuration Optimization¶

-- Show current configuration
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';

-- Key buffer size (for MyISAM)
SET GLOBAL key_buffer_size = 256M;

-- InnoDB buffer pool size
SET GLOBAL innodb_buffer_pool_size = 1G;

-- Query cache (deprecated in MariaDB 10.1.7+)
SET GLOBAL query_cache_size = 64M;
SET GLOBAL query_cache_type = ON;

-- Connection settings
SET GLOBAL max_connections = 200;
SET GLOBAL connect_timeout = 10;

-- Temporary table settings
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

Slow Query Log¶

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

-- Show slow query log settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Analyze slow queries (from command line)
mysqldumpslow /var/log/mysql/slow.log

Performance Monitoring¶

-- Show process list
SHOW PROCESSLIST;

-- Show status variables
SHOW STATUS;
SHOW STATUS LIKE 'Innodb%';

-- Show engine status
SHOW ENGINE INNODB STATUS;

-- Performance schema queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Show table sizes
SELECT 
    table_name,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

Monitoring¶

System Monitoring¶

-- Show server status
SHOW STATUS;

-- Show global variables
SHOW GLOBAL VARIABLES;

-- Show process list
SHOW PROCESSLIST;

-- Show open tables
SHOW OPEN TABLES;

-- Show table locks
SHOW TABLE STATUS;

-- Show engine status
SHOW ENGINE INNODB STATUS;

Performance Monitoring¶

-- Connection statistics
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';

-- Query statistics
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Queries';
SHOW STATUS LIKE 'Slow_queries';

-- InnoDB statistics
SHOW STATUS LIKE 'Innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_rows%';

-- Table cache statistics
SHOW STATUS LIKE 'Open%';
SHOW STATUS LIKE 'Opened_tables';

Log Monitoring¶

# Monitor error log
tail -f /var/log/mysql/error.log

# Monitor slow query log
tail -f /var/log/mysql/slow.log

# Monitor general log
tail -f /var/log/mysql/mysql.log

# Analyze slow queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Security¶

Basic Security¶

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Remove test database
DROP DATABASE IF EXISTS test;

-- Set root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';

-- Disable remote root login
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Flush privileges
FLUSH PRIVILEGES;

SSL Configuration¶

# Generate SSL certificates (in my.cnf)
[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

# Require SSL for user
ALTER USER 'username'@'localhost' REQUIRE SSL;

# Connect with SSL
mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u username -p

Audit Plugin¶

-- Install audit plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- Configure audit logging
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

-- Show audit status
SHOW VARIABLES LIKE 'server_audit%';

Best Practices¶

Schema Design Best Practices¶

-- Use appropriate data types
-- Use INT for IDs, not VARCHAR
-- Use DECIMAL for money, not FLOAT
-- Use appropriate VARCHAR lengths

-- Normalize appropriately
-- Don't over-normalize (3NF is usually sufficient)
-- Denormalize for performance when needed

-- Use constraints
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount > 0),
    status ENUM('pending', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Use indexes wisely
-- Index foreign keys
-- Index columns used in WHERE, ORDER BY, GROUP BY
-- Don't over-index (impacts INSERT/UPDATE performance)

Query Best Practices¶

-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- Use specific columns instead of SELECT *
SELECT id, username, email FROM users;

-- Use EXISTS instead of IN for subqueries
SELECT * FROM users 
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

-- Use UNION ALL instead of UNION when duplicates are acceptable
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

-- Use prepared statements to prevent SQL injection
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @user_id = 1;
EXECUTE stmt USING @user_id;

Performance Best Practices¶

-- Regular maintenance
ANALYZE TABLE users;
OPTIMIZE TABLE users;

-- Monitor slow queries
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- Use connection pooling in applications
-- Configure appropriate buffer sizes
-- Use read replicas for read-heavy workloads
-- Partition large tables when appropriate

-- Example partitioning
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Security Best Practices¶

-- Use least privilege principle
-- Create specific users for applications
CREATE USER 'app_read'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON myapp.* TO 'app_read'@'localhost';

CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write'@'localhost';

-- Regular security maintenance
-- Update MariaDB regularly
-- Monitor access logs
-- Use SSL for connections
-- Backup regularly and test restores
-- Use strong passwords
-- Limit network access

Summary¶

MariaDB is a powerful, open-source relational database management system that offers excellent performance, reliability, and compatibility with MySQL. This cheatsheet provides comprehensive coverage of MariaDB operations from basic administration to advanced features.

Key Strengths: - MySQL Compatibility: Drop-in replacement for MySQL with enhanced features - Performance: Improved query optimizer and storage engines - Open Source: Truly open source with active community development - Advanced Features: Window functions, CTEs, JSON support, and more - Storage Engines: Multiple storage engines including InnoDB, MyISAM, and Aria

Best Use Cases: - Web applications requiring ACID compliance - Data warehousing and analytics - E-commerce platforms - Content management systems - Applications requiring high availability and replication

Important Considerations: - Proper indexing strategy is crucial for performance - Regular maintenance (ANALYZE, OPTIMIZE) improves performance - Backup and recovery procedures must be tested regularly - Security configuration requires careful attention - Monitoring and alerting are essential for production systems

By following the practices and techniques outlined in this cheatsheet, you can effectively design, implement, and maintain MariaDB databases that are secure, performant, and scalable for enterprise applications.