MySQL

-- Database & table management
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE myapp;

CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, role ENUM('user','admin','moderator') DEFAULT 'user', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_email (email), INDEX idx_role_created (role, created_at) );

-- Common queries SELECT id, name, email FROM users WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT 20; INSERT INTO users (name, email, password) VALUES ('Sufi', 's@example.com', SHA2('password', 256)); UPDATE users SET role = 'admin' WHERE email = 's@example.com'; DELETE FROM users WHERE id = 5; ```

-- One-to-Many: users → posts
CREATE TABLE posts (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    title      VARCHAR(255) NOT NULL,
    body       TEXT,
    published  BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FULLTEXT INDEX ft_title_body (title, body)  -- full-text search
);

-- Many-to-Many: users ↔ tags CREATE TABLE tags (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) UNIQUE); CREATE TABLE user_tags ( user_id BIGINT UNSIGNED, tag_id BIGINT UNSIGNED, PRIMARY KEY (user_id, tag_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (tag_id) REFERENCES tags(id) );

-- Complex JOIN SELECT u.name, COUNT(p.id) AS post_count, GROUP_CONCAT(t.name) AS tags FROM users u LEFT JOIN posts p ON u.id = p.user_id AND p.published = TRUE LEFT JOIN user_tags ut ON u.id = ut.user_id LEFT JOIN tags t ON ut.tag_id = t.id GROUP BY u.id HAVING post_count > 0 ORDER BY post_count DESC; ```

DELIMITER $$

-- Stored procedure CREATE PROCEDURE GetUserStats(IN userId BIGINT, OUT postCount INT, OUT commentCount INT) BEGIN SELECT COUNT(*) INTO postCount FROM posts WHERE user_id = userId; SELECT COUNT(*) INTO commentCount FROM comments WHERE user_id = userId; END$$

-- Function CREATE FUNCTION FullName(first_name VARCHAR(50), last_name VARCHAR(50)) RETURNS VARCHAR(100) DETERMINISTIC RETURN CONCAT(first_name, ' ', last_name)$$

-- Trigger CREATE TRIGGER after_user_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO audit_log (event, user_id, created_at) VALUES ('user_deleted', OLD.id, NOW()); END$$

DELIMITER ;

-- Call procedure CALL GetUserStats(1, @posts, @comments); SELECT @posts, @comments; ```

-- EXPLAIN to analyze query plan
EXPLAIN SELECT * FROM users WHERE email = 's@example.com';
EXPLAIN ANALYZE SELECT u.*, COUNT(p.id) FROM users u JOIN posts p ON u.id = p.user_id GROUP BY u.id;

-- Index types CREATE INDEX idx_name ON users(name); -- B-tree (default) CREATE UNIQUE INDEX idx_email ON users(email); -- unique CREATE INDEX idx_created ON users(created_at DESC); -- descending CREATE INDEX idx_composite ON posts(user_id, created_at); -- composite (order matters!) CREATE FULLTEXT INDEX ft_search ON posts(title, body); -- full-text

-- Full-text search SELECT * FROM posts WHERE MATCH(title, body) AGAINST ('MySQL performance' IN BOOLEAN MODE);

-- Query optimization tips: -- 1. Leftmost prefix rule for composite indexes -- 2. Avoid functions on indexed columns in WHERE -- 3. Use covering indexes (include all columns needed) -- 4. Use LIMIT with ORDER BY on indexed column -- 5. Avoid SELECT * in production queries ```

-- Transactions ACID compliance
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 1000);

COMMIT; -- make permanent -- or ROLLBACK; -- undo all changes

-- Isolation levels SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), SERIALIZABLE

-- Locking SELECT * FROM inventory WHERE product_id = 5 FOR UPDATE; -- exclusive lock SELECT * FROM products WHERE id = 10 FOR SHARE; -- shared lock

-- Deadlock prevention: always acquire locks in same order -- MySQL auto-detects deadlocks and rolls back the smaller transaction ```

Primary-Replica Replication MySQL's built-in replication asynchronously copies writes from primary to one or more replicas.

-- On primary: enable binary log (my.cnf)
-- [mysqld] log_bin = mysql-bin, server_id = 1

-- Create replication user on primary CREATE USER 'replicator'@'replica_host' IDENTIFIED BY 'strong_password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'replica_host';

-- On replica: configure source CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'primary_host', SOURCE_USER = 'replicator', SOURCE_PASSWORD = 'strong_password', SOURCE_AUTO_POSITION = 1; START REPLICA; SHOW REPLICA STATUS\G ```

High Availability Options
• MySQL InnoDB Cluster: built-in HA with automatic failover (uses Group Replication)
• ProxySQL: connection pooling, query routing, read/write splitting
• MySQL Router: client-side load balancing for InnoDB Cluster
• Percona XtraDB Cluster: synchronous multi-primary replication

Backup Strategies
• mysqldump: logical backup, slow for large datasets
• Percona XtraBackup: hot physical backup, fastest restore
• Binary log: point-in-time recovery