MySQL Replication Overview, Configuration, and Common Issues

MySQL's native replication feature enables data distribution and high‑availability by copying changes from a single master server to one or more slave servers; the master records updates in a binary log and slaves retrieve and replay these events.

Three replication formats are supported: statement‑based replication (default, executes the same SQL on slaves), row‑based replication (records row changes directly), and mixed mode (automatically switches when statements cannot be safely replicated).

Replication addresses data distribution, load balancing, backup, and failover, allowing read‑heavy workloads to be offloaded to slaves while keeping data synchronized.

The replication process consists of three steps: (1) the master writes changes to the binary log, (2) the slave copies the binary log events to its relay log, and (3) the slave replays the relay log to apply the changes locally.

Basic master‑slave setup requires enabling the binary log on the master, assigning unique server-id values, creating a replication user with REPLICATION SLAVE privilege, and configuring the slave with log_bin , relay_log , log_slave_updates , and read_only options.

Typical SQL commands used during configuration are:

GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO 'backup'@'10.100.0.200' IDENTIFIED BY '1234';

SET PASSWORD FOR 'backup'@'10.100.0.200' = OLD_PASSWORD('1234');

CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='repl', MASTER_PASSWORD='p4ssword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;

START SLAVE;

SHOW SLAVE STATUS\G

Common topologies include a single master with multiple slaves, active‑active master‑master, active‑passive master‑master, cascaded replication (master‑slaves‑slaves), and dual‑master with slaves, each offering different trade‑offs in redundancy, read scalability, and write bottlenecks.

Typical replication errors and their remedies are illustrated, such as connection failures, attempting operations while tables are locked, mismatched server-id values, binlog position mismatches after a master restart, duplicate‑key conflicts, and missing rows; solutions involve correcting configuration, restarting the slave, adjusting sync_binlog , or skipping problematic events with SET GLOBAL sql_slave_skip_counter=1; .

Additional files involved in replication include mysql-bin.index (tracks binary log files), relay-log.info (stores slave's relay log state), master.info (records master connection info), and relay-log.info (holds current relay log details).