Mastering MySQL: A Practical Knowledge Map of Deployment Scenarios

MySQL is widely used, and topics such as performance optimization, high availability, strong consistency, security, backup, clustering, horizontal and vertical scaling, load balancing, and read/write separation are abundant. Although many alternatives exist, MySQL remains the first choice for low‑cost and easy maintenance. This article organizes MySQL technical points by application scenarios into a knowledge map for deeper learning and summarization.

1. Single Master

Common for personal sites, startups, and small internal systems where a single database instance suffices. The focus is on four key points shown in the diagram, with backup being the most critical.

Cold backup: shut down the server and copy physical files (InnoDB engine files, shared tablespace files, individual tablespace files, redo log files, my.cnf). Restore by copying files back to the appropriate directory.

Hot backup: use Ibbackup or XtraBackup to record redo log checkpoints (LSN), copy shared and individual tablespace files without blocking, and record the LSN after copying.

Warm backup:

Use mysqldump with --single-transaction to ensure consistency; avoid DDL during backup. Restore with mysql -uroot -p <file.sql>.

Binary semi‑synchronous replication for incremental master‑slave copying.

Restore using mysqlbinlog.

2. One Master One Slave

This architecture targets performance and high availability. In addition to backup, it requires performance optimization, read/write separation, and load balancing.

Performance optimization is driven by service metrics (e.g., handling requests within 3 seconds, allocating ~1.5 seconds to the database). Priorities: index optimization → table design optimization → database configuration optimization → hardware optimization.

Read/write separation and load balancing are relatively simple; the author’s current system lacks read/write separation and implements load balancing via PHP code, which is suboptimal.

3. One Master N Slaves

When a system demands high availability, consistency, or performance, a one‑master‑multiple‑slaves setup is considered. However, MySQL configuration and built‑in middleware alone cannot fully address availability and consistency challenges.

4. Horizontal Cluster

Applicable when sharding is needed. After exhaustive performance tuning, the system can be split into sub‑systems, each with its own database, and data synchronization mechanisms share core data to avoid cross‑database joins. Large transactions are broken into smaller ones with proper isolation and synchronization.

5. Vertical Cluster

Focuses on scaling challenges. The author’s example uses 256 shards distributed across two master‑two‑slave clusters, with no current scaling demand. Future scaling could group shards into larger clusters (e.g., four clusters of 64 shards each). Routing logic is currently implemented in PHP, affecting business code clarity.

6. Mixed Mode

The mixed mode combines the previous five scenarios, addressing all 17 identified problem points in a layered manner.