MySQL — 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 |