Operations¶

Scope

Production deployment, replication, backup/recovery, performance tuning, and monitoring.

Replication Topologies¶

Pattern	Consistency	Failover	Use Case
Async replication	Eventual	Manual or MHA	Standard HA
Semi-sync replication	Near-strong	Automated	Low data loss tolerance
Group Replication	Strong (Paxos)	Automatic	Multi-primary or single-primary
InnoDB Cluster	Strong	MySQL Router	Official HA solution
InnoDB ReplicaSet	Eventual	Manual	Simple async HA

Performance Tuning¶

InnoDB Configuration¶

# my.cnf production settings (32Gi RAM server)
[mysqld]
innodb_buffer_pool_size = 24G          # 75% of RAM
innodb_buffer_pool_instances = 8       # 1 per GB of pool size (max 64)
innodb_log_file_size = 2G              # Larger = better write perf
innodb_flush_log_at_trx_commit = 1     # ACID compliance (2 for perf)
innodb_flush_method = O_DIRECT         # Skip OS cache
innodb_io_capacity = 2000              # SSD: 2000+, HDD: 200
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

Query Optimization¶

-- Find slow queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Check buffer pool hit ratio (should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
SELECT (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio;

Backup & Recovery¶

# Physical backup with xtrabackup
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full

# Logical backup
mysqldump --single-transaction --routines --triggers --all-databases > full.sql

# Point-in-time recovery
mysqlbinlog --start-datetime="2026-04-12 10:00:00" binlog.000042 | mysql

Common Issues¶

Issue	Diagnosis	Fix
Replication lag	`SHOW SLAVE STATUS\G`	Check I/O thread, SQL thread
Table locks	`SHOW PROCESSLIST`	Kill long queries, optimize index
Deadlocks	`SHOW ENGINE INNODB STATUS`	Reorder transactions, add indexes
Buffer pool thrashing	`SHOW STATUS LIKE 'Innodb_buffer%'`	Increase buffer pool size
Disk full	`SHOW BINARY LOGS`	Purge old binlogs

Commands & Recipes¶

Connection & Basics¶

# Connect
mysql -h localhost -u root -p

# Create database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Import
mysql -u root -p mydb < dump.sql

# Dump
mysqldump -u root -p --single-transaction mydb > backup.sql

Essential Queries¶

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

-- Active connections
SHOW PROCESSLIST;

-- Kill long query
KILL <process_id>;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Index usage
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Performance Tuning¶

# my.cnf essentials
[mysqld]
innodb_buffer_pool_size = 4G        # 70-80% of RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1  # ACID compliance
innodb_flush_method = O_DIRECT
max_connections = 200

Group Replication Setup¶

-- On each node
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SET SQL_LOG_BIN=1;

CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='password'
  FOR CHANNEL 'group_replication_recovery';

-- Bootstrap first node
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- Join other nodes
START GROUP_REPLICATION;

Sources¶

MySQL Reference