What Is MySQL Replication?

MySQL replication is a built-in feature that asynchronously ships changes from one server (Master/Source) to another (Slave/Replica) so both stay in sync. The core mechanism is the binary log (binlog). The Master records every write event in the binlog, and the Replica receives those events and re-executes them until it reaches the same state.

Read load distribution: Send read queries to Replicas
High availability (HA): Promote a Replica if the Master fails
Backup offloading: Take dumps on a Replica to spare the Master

Starting with MySQL 8.0, the official terms changed from Master/Slave to Source/Replica. This guide uses both names for compatibility with older versions.

Replication Topology and Data Flow

CODE

[Master] --(binlog write)--> binlog
 | Dump Thread
   v
[Replica] -- IO Thread --> relay log -- SQL Thread -->

Thread	Location	Role
Binlog Dump Thread	Master	Sends binlog events to the Replica
IO Thread	Replica	Receives events and writes them to the relay log
SQL Thread	Replica	Reads the relay log and applies the events

There are three binlog formats: ROW (recommended), STATEMENT, and MIXED. Use ROW for the strongest data consistency.

1. Master Configuration

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf).

INI

[mysqld]
#
server-id = 1

#
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# GTID
gtid_mode = ON
enforce_gtid_consistency = ON

# binlog — 7
binlog_expire_logs_seconds = 604800

# DB when
# binlog_do_db = appdb

After a restart, create a dedicated replication account.

SQL

-- Master run
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED WITH caching_sha2_password BY 'StrongReplPass!2026';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- binlog check(-GTID method )
SHOW MASTER STATUS\G

Example output:

CODE

File: mysql-bin.000004
Position: 1547

If the Master is already in production, take a consistent snapshot with mysqldump --single-transaction --master-data=2 --source-data=2 and load it onto the Replica.

2. Replica Configuration

INI

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON

# Replica dedicated ( super_read_only)
read_only = ON
super_read_only = ON

# after save
relay_log_recovery = ON

Starting GTID-based replication (MySQL 8.0)

SQL

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.1.10',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'StrongReplPass!2026',
  SOURCE_PORT = 3306,
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;

Coordinate (binlog position) method — older versions / MariaDB compatibility

SQL

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'StrongReplPass!2026',
  MASTER_LOG_FILE = 'mysql-bin.000004',
  MASTER_LOG_POS = 1547;

START SLAVE;

3. Diagnosing Replication Status

SQL

-- MySQL 8.0
SHOW REPLICA STATUS\G
-- /MariaDB
SHOW SLAVE STATUS\G

Key fields to check:

Field	Healthy value	Meaning
Replica_IO_Running	Yes	Connected to the Master and receiving events
Replica_SQL_Running	Yes	Applying the relay log normally
Seconds_Behind_Source	0	Replication lag (seconds)
Last_IO_Error	(empty)	Network/authentication errors
Last_SQL_Error	(empty)	SQL errors during apply

Replication is healthy when both the IO and SQL threads show Yes and Seconds_Behind_Source is 0 or a low value.

4. Semi-Synchronous Replication (Semi-Sync)

With asynchronous replication, the Master does not wait for Replicas to receive events, so failover can lose data. Semi-sync finishes a commit only after at least one Replica has acknowledged that it wrote the event to its relay log.

SQL

-- Master
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 1000; -- ms, when

-- Replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;
STOP REPLICA IO_THREAD; START REPLICA IO_THREAD;

After the timeout it automatically falls back to asynchronous mode, so semi-sync is not “zero data loss.” If you truly need no data loss, look at Group Replication / InnoDB Cluster.

5. Replication Lag: Causes and Remedies

Symptom	Cause	Remedy
SQL Thread apply lag	Single-threaded apply	Parallel apply with `replica_parallel_workers`
Sudden spike at a point in time	Large batch jobs / ALTER	Nightly batches, pt-online-schema-change
IO Thread lag	Network bandwidth	Compression (`SOURCE_COMPRESSION_ALGORITHMS`)

SQL

--
STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_preserve_commit_order = ON;
START REPLICA SQL_THREAD;

6. Recovering Broken Replication

If the SQL Thread has stopped because of a data conflict, identify the cause and skip the event at GTID granularity.

SQL

-- GTID check after,
SET GTID_NEXT = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:42';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

Skipping an event leaves data inconsistency behind. Confirm the root cause, and if consistency is in doubt, compare Master and Replica with pt-table-checksum.

7. Failover (Manual Promotion)

This is the basic procedure for promoting a Replica to the new Master when the Master fails.

SQL

-- 1) Replica
STOP REPLICA;
RESET REPLICA ALL;

-- 2) dedicated
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

Then point the application’s DB endpoint at the new Master. If you need automation, use tools such as Orchestrator, MHA, ProxySQL, or MySQL Router.

Summary

Item	Recommended setting
binlog format	ROW
Replication coordinates	GTID (`SOURCE_AUTO_POSITION=1`)
Replica protection	`super_read_only = ON`
Preventing data loss	Semi-sync + a short timeout
Apply performance	Parallel replication (`LOGICAL_CLOCK`)
Consistency checks	Run `pt-table-checksum` regularly
Automatic failover	Orchestrator / MHA / Router

Once you clearly understand the binlog → Dump → IO → SQL flow, diagnosis becomes straightforward. Combining GTID, semi-sync, and parallel replication lets you run read scaling and HA reliably.

#mysql#mariadb#replication#ha#binlog

Editorial note

AI tools may assist drafting. Check the evidence in each guide for whether commands were run and which environment was used. Confirm the current docs for your version before applying instructions, and email us about errors. email us

Topic-related official docs (auto-linked · not per-claim sources)MySQL manual ↗

How we write and review →Editorial policy →

Questions & answers

Ask a question about this guide. We'll review and reply.

MySQL Replication: Setting Up Source-Replica (Master-Slave) HA, Diagnosing Lag, and Failing Over