Understanding MySQL Replication: Principles, Mechanisms, and Practical Applications

MySQL replication (master‑slave) copies data changes from one MySQL server to one or more replicas, enhancing high availability, scalability and load balancing.

In production, MySQL’s reliance means failures can impact services; replication mitigates data loss and downtime by providing real‑time backup, read‑write separation, and quick failover.

Replication uses binary logs (binlog) that record all changes. Three binlog formats exist: Statement, Row, and Mixed, with Row format now dominant due to its accuracy and completeness.

Binlog events include XID_EVENT, QUERY_EVENT, GTID_EVENT, TABLE_MAP_EVENT, etc., and the binlog lifecycle involves file rotation, indexing, and expiration.

Two positioning methods are supported: File Position and GTID. Example commands:

File: binlog.000001
Position: 381808617

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POSITION=381808617;

Executed_Gtid_Set: e2e0a733-3478-11eb-90fe-b4055d009f6c:1-753

CHANGE MASTER TO MASTER_AUTO_POSITION=1;

The basic asynchronous replication flow starts the I/O thread on the replica, the master’s binlog‑dump thread sends events, the replica writes them to a relay log, and the SQL thread replays them.

Half‑sync replication (available since MySQL 5.5) makes the master wait for an ACK from at least one replica before committing, reducing data loss at the cost of some latency.

Parallel replication, introduced in MySQL 5.7, allows multiple worker threads on the replica to apply transactions concurrently, either based on schema separation or logical‑clock techniques, improving throughput for high‑write workloads.

In vivo’s production environment a one‑master‑two‑replica cluster is used, supplemented by HA components, middleware proxies, remote binlog replication, centralized BinlogServer storage, and optional half‑sync mode to meet 99.99 % availability and 99.9999 % data reliability targets.

Data‑transfer services (DTS) leverage binlog to stream changes to downstream systems such as Elasticsearch or Kafka. A “fake slave” can be registered using MySQL protocol commands; example Go code for the Register‑Slave packet:

data := make([]byte, 4+1+4+1+len(hostname)+1+len(b.cfg.User)+1+len(b.cfg.Password)+2+4+4)

and for the Binlog‑Dump packet: data := make([]byte, 4+1+4+2+4+len(p.Name)) Performance improvements include connection‑pool refactoring and logical‑clock based transaction scheduling, raising throughput from ~7 MB/s to ~13 MB/s in tests.

Overall, MySQL replication not only boosts database availability and reliability but also provides a flexible binlog interface for real‑time data integration across heterogeneous storage systems, with future work focusing on BinlogServer enhancements.