Skip to content

cheatsheetz/mysql

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

1 Commit
.gitattributes
.gitattributes
 
 
.gitignore
.gitignore
 
 
LICENSE.md
LICENSE.md
 
 
README.md
README.md
 
 

Repository files navigation

MySQL Cheat Sheet

Comprehensive reference for MySQL database management system - covering queries, joins, functions, administration, and performance optimization for production environments.

Table of Contents

Installation & Connection

Installation

# Ubuntu/Debian
sudo apt update && sudo apt install mysql-server

# CentOS/RHEL
sudo yum install mysql-server

# macOS (Homebrew)
brew install mysql

# Docker
docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=password -d mysql:8.0

Connection

# Command line
mysql -h hostname -u username -p database_name

# Local connection
mysql -u root -p

# Connection with SSL
mysql -u username -p --ssl-mode=REQUIRED

Connection Parameters

Parameter Description Example
-h Hostname mysql -h localhost
-P Port mysql -P 3306
-u Username mysql -u root
-p Password prompt mysql -p
-D Database mysql -D mydb

Basic Operations

Database Operations

-- Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- List databases
SHOW DATABASES;

-- Use database
USE myapp;

-- Drop database
DROP DATABASE myapp;

-- Show current database
SELECT DATABASE();

Table Operations

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

-- Show tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;
-- or
SHOW COLUMNS FROM users;

-- Alter table
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN username VARCHAR(60);

-- Drop table
DROP TABLE users;

CRUD Operations

-- INSERT
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- Multiple inserts
INSERT INTO users (username, email) VALUES 
    ('jane_doe', 'jane@example.com'),
    ('bob_smith', 'bob@example.com');

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

-- SELECT
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

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

-- DELETE
DELETE FROM users WHERE id = 1;

-- TRUNCATE (faster than DELETE for all rows)
TRUNCATE TABLE users;

Data Types

Numeric Types

Type Size Range Usage
TINYINT 1 byte -128 to 127 Small integers, flags
SMALLINT 2 bytes -32,768 to 32,767 Medium integers
MEDIUMINT 3 bytes -8,388,608 to 8,388,607 Large integers
INT 4 bytes -2B to 2B Standard integers
BIGINT 8 bytes -9E18 to 9E18 Very large integers
DECIMAL(M,D) Variable Exact precision Money, precise calculations
FLOAT 4 bytes Approximate Scientific calculations
DOUBLE 8 bytes Approximate High-precision floats

String Types

Type Max Length Usage
CHAR(M) 255 chars Fixed-length strings
VARCHAR(M) 65,535 chars Variable-length strings
TEXT 65,535 chars Long text
MEDIUMTEXT 16M chars Very long text
LONGTEXT 4GB chars Extremely long text
ENUM 65,535 values Predefined options
SET 64 members Multiple selections

Date/Time 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 PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_date DATE,
    event_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Queries & Filtering

Basic Queries

-- SELECT with conditions
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE username IN ('john', 'jane', 'bob');
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE phone IS NOT NULL;

-- Pattern matching
SELECT * FROM users WHERE username LIKE 'john%';    -- Starts with 'john'
SELECT * FROM users WHERE username LIKE '%doe';     -- Ends with 'doe'
SELECT * FROM users WHERE username LIKE '%john%';   -- Contains 'john'
SELECT * FROM users WHERE username LIKE 'j_hn';     -- 'j' + any char + 'hn'

-- Regular expressions
SELECT * FROM users WHERE username REGEXP '^[A-Za-z]+$';  -- Only letters
SELECT * FROM users WHERE email REGEXP '^[^@]+@[^@]+\.[^@]+$';  -- Email format

Sorting and Limiting

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

-- LIMIT and OFFSET
SELECT * FROM users LIMIT 10;                    -- First 10 records
SELECT * FROM users LIMIT 10 OFFSET 20;          -- Skip 20, take 10
SELECT * FROM users LIMIT 20, 10;                -- Same as above

-- Pagination pattern
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET (@page_number - 1) * 20;

Aggregation

-- Basic aggregation
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;
SELECT AVG(age) FROM users;
SELECT MIN(created_at), MAX(created_at) FROM users;
SELECT SUM(order_total) FROM orders;

-- GROUP BY
SELECT department, COUNT(*) as employee_count 
FROM employees 
GROUP BY department;

SELECT department, AVG(salary) as avg_salary 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

-- GROUP BY with multiple columns
SELECT department, job_title, COUNT(*) as count
FROM employees 
GROUP BY department, job_title
ORDER BY department, count DESC;

Joins

Inner Join

-- Basic INNER JOIN
SELECT u.username, p.title 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id;

-- Multiple table joins
SELECT u.username, p.title, c.content 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id
INNER JOIN comments c ON p.id = c.post_id;

Outer Joins

-- LEFT JOIN (all records from left table)
SELECT u.username, p.title 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id;

-- RIGHT JOIN (all records from right table)
SELECT u.username, p.title 
FROM users u 
RIGHT JOIN posts p ON u.id = p.user_id;

-- FULL OUTER JOIN (MySQL doesn't support directly, use UNION)
SELECT u.username, p.title 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id
UNION
SELECT u.username, p.title 
FROM users u 
RIGHT JOIN posts p ON u.id = p.user_id;

Self Join

-- Self join example (employee-manager relationship)
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Join Performance Tips

-- Use proper indexes on join columns
CREATE INDEX idx_user_id ON posts(user_id);

-- Avoid joining on functions
-- Bad: WHERE YEAR(created_at) = 2023
-- Good: WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'

-- Use EXPLAIN to analyze join performance
EXPLAIN SELECT u.username, p.title 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id;

Functions

String Functions

-- Common string functions
SELECT CONCAT('Hello', ' ', 'World');                    -- Hello World
SELECT CONCAT_WS('-', 'MySQL', 'is', 'awesome');        -- MySQL-is-awesome
SELECT LEFT('MySQL', 2);                                 -- My
SELECT RIGHT('MySQL', 3);                                -- SQL
SELECT SUBSTRING('MySQL', 3, 3);                         -- SQL
SELECT LENGTH('MySQL');                                  -- 5
SELECT CHAR_LENGTH('MySQL');                             -- 5
SELECT UPPER('mysql');                                   -- MYSQL
SELECT LOWER('MYSQL');                                   -- mysql
SELECT LTRIM('  MySQL  ');                               -- 'MySQL  '
SELECT RTRIM('  MySQL  ');                               -- '  MySQL'
SELECT TRIM('  MySQL  ');                                -- 'MySQL'
SELECT REPLACE('MySQL is great', 'great', 'awesome');    -- MySQL is awesome

-- String functions in queries
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;
SELECT * FROM users WHERE LOWER(email) LIKE '%gmail%';

Numeric Functions

-- Math functions
SELECT ABS(-15);                    -- 15
SELECT CEIL(4.3);                   -- 5
SELECT FLOOR(4.8);                  -- 4
SELECT ROUND(4.567, 2);             -- 4.57
SELECT POWER(2, 3);                 -- 8
SELECT SQRT(16);                    -- 4
SELECT MOD(10, 3);                  -- 1
SELECT RAND();                      -- Random number 0-1
SELECT RAND() * 100;                -- Random number 0-100

-- Aggregate functions
SELECT COUNT(*) FROM orders;
SELECT SUM(amount) FROM orders;
SELECT AVG(amount) FROM orders;
SELECT MIN(order_date) FROM orders;
SELECT MAX(order_date) FROM orders;
SELECT STD(amount) FROM orders;     -- Standard deviation
SELECT VARIANCE(amount) FROM orders;

Date/Time Functions

-- Current date/time
SELECT NOW();                       -- Current datetime
SELECT CURDATE();                   -- Current date
SELECT CURTIME();                   -- Current time
SELECT UNIX_TIMESTAMP();            -- Unix timestamp

-- Date extraction
SELECT YEAR(NOW());                 -- Current year
SELECT MONTH(NOW());                -- Current month
SELECT DAY(NOW());                  -- Current day
SELECT HOUR(NOW());                 -- Current hour
SELECT MINUTE(NOW());               -- Current minute
SELECT SECOND(NOW());               -- Current second
SELECT DAYOFWEEK(NOW());            -- Day of week (1=Sunday)
SELECT DAYNAME(NOW());              -- Day name
SELECT MONTHNAME(NOW());            -- Month name

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);      -- Tomorrow
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);     -- A week ago
SELECT DATEDIFF('2023-12-31', '2023-01-01'); -- Days between dates
SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', NOW());

-- Date formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');         -- 2023-01-01 12:30:45
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');             -- Monday, January 01, 2023

Conditional Functions

-- CASE statement
SELECT 
    username,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age >= 18 AND age < 65 THEN 'Adult'
        ELSE 'Senior'
    END as age_group
FROM users;

-- IF function
SELECT username, IF(age >= 18, 'Adult', 'Minor') as status FROM users;

-- IFNULL and COALESCE
SELECT username, IFNULL(phone, 'No phone') as contact FROM users;
SELECT username, COALESCE(phone, email, 'No contact') as contact FROM users;

-- NULLIF
SELECT NULLIF(division_result, 0) FROM calculations;  -- Returns NULL if 0

Indexes & Performance

Creating Indexes

-- Primary key (automatically creates unique index)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

-- Single column index
CREATE INDEX idx_username ON users(username);

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

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Full-text index
CREATE FULLTEXT INDEX idx_content ON posts(title, content);

-- Partial index (MySQL 8.0+)
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

Index Management

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_username ON users;

-- Analyze table (update index statistics)
ANALYZE TABLE users;

-- Check index usage
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Query Optimization

-- Use EXPLAIN to understand query execution
EXPLAIN FORMAT=JSON SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- Optimization techniques
-- 1. Use indexes on WHERE clauses
SELECT * FROM users WHERE username = 'john_doe';  -- Index on username

-- 2. Use indexes on ORDER BY
SELECT * FROM posts ORDER BY created_at DESC;     -- Index on created_at

-- 3. Use covering indexes
CREATE INDEX idx_covering ON posts(user_id, created_at, title);
SELECT user_id, created_at, title FROM posts WHERE user_id = 1;

-- 4. Avoid SELECT *
SELECT id, username, email FROM users;  -- Better than SELECT *

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

Performance Monitoring

-- Show slow queries
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Log queries taking > 2 seconds

-- Show processlist
SHOW PROCESSLIST;

-- 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;

Administration

User Management

-- Create user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'password';  -- Any host

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'localhost';
GRANT USAGE ON *.* TO 'readonly'@'%';

-- Show grants
SHOW GRANTS FOR 'appuser'@'localhost';

-- Revoke privileges
REVOKE INSERT, UPDATE, DELETE ON myapp.* FROM 'appuser'@'localhost';

-- Change password
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'newpassword';

-- Drop user
DROP USER 'appuser'@'localhost';

-- Flush privileges (apply changes)
FLUSH PRIVILEGES;

Configuration

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

-- Set global variables
SET GLOBAL max_connections = 500;
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB

-- Set session variables
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

Database Maintenance

-- Check table integrity
CHECK TABLE users;

-- Repair table
REPAIR TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Analyze table statistics
ANALYZE TABLE users;

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

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

Stored Procedures & Triggers

Stored Procedures

-- Create stored procedure
DELIMITER //
CREATE PROCEDURE GetUserPosts(IN user_id INT)
BEGIN
    SELECT p.id, p.title, p.created_at
    FROM posts p
    WHERE p.user_id = user_id
    ORDER BY p.created_at DESC;
END //
DELIMITER ;

-- Call procedure
CALL GetUserPosts(1);

-- Procedure with output parameter
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END //
DELIMITER ;

-- Call with output
CALL GetUserCount(@count);
SELECT @count;

-- Drop procedure
DROP PROCEDURE GetUserPosts;

Functions

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

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

-- Drop function
DROP FUNCTION CalculateAge;

Triggers

-- Create trigger
CREATE TRIGGER update_modified_time 
BEFORE UPDATE ON users
FOR EACH ROW 
SET NEW.updated_at = NOW();

-- Audit trigger
CREATE TABLE user_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(10),
    old_values JSON,
    new_values JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER user_audit_trigger
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_values, new_values)
    VALUES (NEW.id, 'UPDATE', 
            JSON_OBJECT('username', OLD.username, 'email', OLD.email),
            JSON_OBJECT('username', NEW.username, 'email', NEW.email));
END //
DELIMITER ;

-- Show triggers
SHOW TRIGGERS;

-- Drop trigger
DROP TRIGGER update_modified_time;

Backup & Recovery

Backup Commands

# Full database backup
mysqldump -u root -p myapp > myapp_backup.sql

# Multiple databases
mysqldump -u root -p --databases db1 db2 > multiple_backup.sql

# All databases
mysqldump -u root -p --all-databases > all_databases.sql

# Structure only
mysqldump -u root -p --no-data myapp > structure_only.sql

# Data only
mysqldump -u root -p --no-create-info myapp > data_only.sql

# With compression
mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

# Binary log backup
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

Restore Commands

# Restore database
mysql -u root -p myapp < myapp_backup.sql

# Restore compressed backup
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

# Restore specific table
mysql -u root -p myapp -e "source table_backup.sql"

Point-in-Time Recovery

# 1. Restore from last full backup
mysql -u root -p < full_backup.sql

# 2. Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2023-01-01 12:30:00" mysql-bin.000001 | mysql -u root -p

# 3. Skip problematic transaction and continue
mysqlbinlog --start-position=12345 mysql-bin.000001 | mysql -u root -p

Monitoring & Optimization

Performance Monitoring

-- Connection status
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Query performance
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Uptime';

-- InnoDB status
SHOW ENGINE INNODB STATUS;

-- Buffer pool status
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Lock status
SHOW STATUS LIKE 'Innodb_row_lock%';

Key Performance Metrics

-- Query performance schema
SELECT schema_name, 
       ROUND(SUM(sum_timer_wait)/1000000000000,2) AS total_latency
FROM performance_schema.events_statements_summary_by_digest pssbd
JOIN performance_schema.events_statements_current psc USING (digest)
GROUP BY schema_name;

-- Top slow queries
SELECT digest_text,
       count_star,
       ROUND(avg_timer_wait/1000000000000,2) AS avg_seconds,
       ROUND(sum_timer_wait/1000000000000,2) AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Table I/O statistics
SELECT object_name,
       count_read,
       count_write,
       count_fetch,
       count_insert,
       count_update,
       count_delete
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'myapp'
ORDER BY count_read + count_write DESC;

Configuration Tuning

# my.cnf optimization settings
[mysqld]
# Connection settings
max_connections = 500
max_connect_errors = 100

# Buffer settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Query cache (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 128M

# Thread settings
thread_cache_size = 16
table_open_cache = 2000

# Slow query log
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1

# Binary logging
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

Integration Patterns

Connection Pooling

# Python with mysql-connector-python
import mysql.connector.pooling

config = {
    'user': 'username',
    'password': 'password',
    'host': 'localhost',
    'database': 'myapp',
    'pool_name': 'mypool',
    'pool_size': 10,
    'pool_reset_session': True
}

pool = mysql.connector.pooling.MySQLConnectionPool(**config)

def get_user(user_id):
    conn = pool.get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result

Application Integration

// Node.js with mysql2
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'myapp',
    connectionLimit: 10,
    queueLimit: 0
});

async function getUser(userId) {
    const [rows] = await pool.execute(
        'SELECT * FROM users WHERE id = ?',
        [userId]
    );
    return rows[0];
}

// Transaction example
async function transferFunds(fromAccount, toAccount, amount) {
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    
    try {
        await connection.execute(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, fromAccount]
        );
        
        await connection.execute(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toAccount]
        );
        
        await connection.commit();
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
}

Replication Setup

-- Master configuration
-- Add to my.cnf:
[mysqld]
log-bin = mysql-bin
server-id = 1
binlog-do-db = myapp

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

-- Get master status
SHOW MASTER STATUS;

-- Slave configuration
-- Add to my.cnf:
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

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

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

Performance Tuning Tips

  1. Use appropriate indexes: Create indexes on columns used in WHERE, ORDER BY, and JOIN clauses
  2. **Avoid SELECT ***: Only select columns you need
  3. Use LIMIT: Limit result sets for large queries
  4. Optimize JOINs: Ensure proper indexes on join columns
  5. Use connection pooling: Reuse database connections
  6. Monitor slow queries: Enable slow query log and optimize problematic queries
  7. Partition large tables: Use table partitioning for very large datasets
  8. Use read replicas: Distribute read load across multiple servers
  9. Optimize configuration: Tune MySQL configuration for your workload
  10. Regular maintenance: Run ANALYZE TABLE and OPTIMIZE TABLE regularly

Resources

Comprehensive MySQL reference for production database management. Contributions welcome!

About

MySQL queries, joins, functions, database administration, and performance optimization

github.com/cheatsheetz/mysql

Resources

Readme

License

WTFPL license
Activity
Custom properties

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published