Master MySQL: From Beginner Basics to Advanced High‑Availability and Backup Strategies

Most people relax by playing the mobile game "Honor of Kings"; the author uses this analogy to introduce MySQL learning levels, dividing MySQL knowledge into four major modules: architecture, backup & recovery, high‑availability clusters, and optimization.

Part 1: Beginner (Bronze) – Getting Started

Newcomers should learn common MySQL commands, version differences from 5.1 to 5.7, and how to install MySQL.

Common command summary :

Typical initialization differences between MySQL 5.6 and MySQL 5.7 are illustrated:

MySQL 5.6 : initialize data by entering the script directory under the home folder and executing the init script (password is empty).

MySQL 5.7 : initialize data by entering the bin directory under the home folder; the old mysql_install_db command is deprecated. Use --initialize (generates a password) or --initialize-insecure (empty password).

Part 2: Silver – MySQL Architecture and Storage Engines

The MySQL architecture consists of two layers: the MySQL server layer and the storage engine layer.

A SQL statement passes through eight sub‑components; steps 1‑6 belong to the server layer, step 7 belongs to the storage engine layer.

Two most used storage engines :

InnoDB – supports transactions, row‑level locking, caches both indexes and data, stores tables in a single .ibd file, and provides adaptive hash index, double‑write buffer, and insert buffer.

MyISAM – does not support transactions, uses table‑level locking, caches only index files, stores data in .MYD and index files in .MYI, and keeps a total row count for fast COUNT(*) queries.

Part 3: Gold – Memory, Logging, and Transaction Isolation

InnoDB memory modules include data buffer, index buffer, insert buffer, redo‑log buffer, and double‑writer buffer.

Key InnoDB features : double write (protects against page corruption), adaptive hash index (creates hash indexes when beneficial), and insert buffer (converts random I/O to sequential I/O for secondary indexes).

Log handling :

Binlog cache → binlog file : controlled by sync_binlog. sync_binlog=0 offers best performance but highest risk; sync_binlog=1 is safest with some performance loss.

Redo log buffer → redo log : controlled by innodb_flush_log_at_trx_commit (0, 1, 2).

Dirty page flushing : controlled by innodb_max_dirty_pages_pct (recommended 25‑50%).

InnoDB I/O threads: write thread, read thread, insert‑buffer thread, redo‑log thread, plus a master thread and page‑cleaner thread.

Transaction isolation levels (ACID) :

Read Uncommitted (RU)

Read Committed (RC)

Repeatable Read (RR)

Serializable

Recommendation: use RR for transaction‑heavy systems, RC for portal‑type sites.

Part 4: Platinum – Backup and Recovery

Backup methods are divided into cold backup (offline) and hot backup (online). Hot backup includes logical backup (mysqldump, mydumper, mysqlpump) and physical backup (Percona XtraBackup).

a. mysqldump
b. mydumper
c. mysqlpump (MySQL 5.7+)

Physical backup copies raw files using percona‑xtrabackup.

Backup types: full backup and incremental backup. Common tools in production are mysqldump and xtrabackup.

InnoDB maintains a redo log; during crash recovery it replays committed transactions and rolls back uncommitted ones.

XtraBackup copies InnoDB pages without locking tables and monitors the transaction log to copy changed pages.

Part 5: Diamond – High‑Availability Architectures

Common MySQL HA solutions:

Master‑slave replication with keepalived dual‑master (M‑M) or MHA.

Galera‑based clustering (PXC).

M‑M keepalived provides fast failover; scripts must be robust, and nodes should be configured in non‑preemptive backup mode to avoid split‑brain.

MHA automatically selects the most up‑to‑date slave for promotion, supports binlog servers, and can work with MySQL 5.7 semi‑sync.

PXC (Percona XtraDB Cluster) offers multi‑node synchronous replication with near‑zero latency and full MySQL compatibility.

Part 6: King – Advanced Optimization

Advanced optimization can be approached from four angles: application design, system configuration, database tuning, and hardware.

Refer to the author's blog "MySQL Optimization – The Eighteen Dragon Palms" for detailed techniques.

Original article from 51CTO Blog Author: superZS Link: http://blog.51cto.com/sumongodb/1950839