MySQL Replication: A Master-Replica Setup Guide

As a game server or a busy web application grows, a single database struggles to carry all the read and write traffic on its own. MySQL replication lets you spread that load by copying your data from one primary server (master) to one or more copies (replica/slave) in near real time: writes go to the master, while heavy read queries are distributed across the replicas. In this guide you'll build a master-replica setup from scratch, use the modern GTID-based approach, and learn how to monitor the health of your replica.

How does replication work?

At the heart of MySQL replication sits the binary log (binlog). Every data-changing operation on the master (INSERT, UPDATE, DELETE, DDL) is written to the binlog. The replica connects to the master, copies that log into its own relay log, and then replays the events on itself. The process runs with two threads:

• I/O thread: reads the master's binlog and writes it into the replica's relay log.
• SQL (applier) thread: applies the events from the relay log to the replica's data.

Default replication is asynchronous: the master commits its transaction without waiting for the replica to receive the event. This means the replica can fall a few seconds behind the master, which is called replication lag. Being aware of it is critical when you design read distribution.

Configuring the master server

The first step is to enable the binary log on the master and give the server a unique identity. Every replication node must have a network-wide unique server_id. Add this to the [mysqld] section of your my.cnf file:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
bind_address = 0.0.0.0

binlog_format = ROW records row-based changes and is far more reliable than STATEMENT mode. gtid_mode = ON enables modern GTID-based replication, which automates position management. Restart the service after the change:

sudo systemctl restart mysql

Then create a dedicated user for the replica to connect with. Grant it only the REPLICATION SLAVE privilege; don't hand out broad permissions:

CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPassword!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Moving existing data to the replica

Before it can catch up, the replica must start from the same point as the existing data. The cleanest way to take a consistent snapshot is mysqldump. With GTID enabled, the starting GTID set is embedded right into the dump:

mysqldump --all-databases --single-transaction \
  --source-data=2 --triggers --routines --events \
  -u root -p > full-backup.sql

--single-transaction takes a consistent snapshot of InnoDB tables without locking them, and --source-data=2 adds the binlog position as a comment. Copy the file to the replica and import it:

scp full-backup.sql user@replica-ip:/tmp/
# on the replica:
mysql -u root -p < /tmp/full-backup.sql

Connecting the replica server

In the replica's my.cnf give it a different server_id, and consider making it read-only so an accidental write can't break replication:

[mysqld]
server_id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON
relay_log = /var/log/mysql/relay-bin.log

After restarting the service, point the replica at the master. In MySQL 8 the modern commands use the SOURCE terminology:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.1',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'StrongPassword!',
  SOURCE_AUTO_POSITION = 1;

START REPLICA;

Thanks to GTID, SOURCE_AUTO_POSITION = 1 lets the replica continue from the correct point without you typing a binlog file name and position by hand. In older versions the equivalent is CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1 followed by START SLAVE.

Monitoring and troubleshooting replication

To check whether the replica is actually running, query its status:

SHOW REPLICA STATUS\G

Pay special attention to these three fields in the output:

• Replica_IO_Running: Yes and Replica_SQL_Running: Yes — both threads must be running.
• Seconds_Behind_Source — how many seconds the replica is behind the master. If it keeps climbing, the replica can't keep up with the load.
• Last_Error — if it's not empty, the SQL thread got stuck applying an event.

The most common causes of lag are slow disks, missing indexes on the replica, and single-threaded applying. In MySQL 8 you can apply events in parallel by raising replica_parallel_workers:

SET GLOBAL replica_parallel_workers = 4;

If you want stronger consistency guarantees, you can enable semi-synchronous replication; in that mode the master won't acknowledge a commit until at least one replica has written the event to its relay log. You need to install and enable the plugin:

INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

Distributing the read load

The whole point of replication is to split the load. Keep two connection pools in your application: writes and consistency-sensitive queries such as SELECT ... FOR UPDATE go to the master, while heavy reads like reporting and listings go to the replicas. Frameworks like Laravel support this at the configuration level:

'mysql' => [
  'read'  => ['host' => ['10.0.0.2']],
  'write' => ['host' => ['10.0.0.1']],
  'sticky' => true,
],

sticky => true is a crucial detail: if you've performed a write within the same request, it routes subsequent reads to the master too, so you avoid reading data that isn't visible on the replica yet because of lag.

Frequently Asked Questions

Is replication the same as a backup?

No. Because the replica repeats every operation on the master, it also instantly deletes a table you removed by accident. Replication is for high availability and load distribution; for data recovery you still need regular mysqldump or physical backups.

Can I add more than one replica?

Yes. A master can feed several replicas; just give each one a different server_id and repeat the same setup steps. This is the most common way to scale reads horizontally.

What should I do if the replica falls behind the master?

First monitor Seconds_Behind_Source. For persistent lag, speed up the replica's disk (SSD/NVMe), raise the number of parallel applier workers, and add the indexes that are missing on the replica. Splitting heavy bulk writes into smaller batches also reduces lag noticeably.

Don't let the database layer slow you down as you scale your server. If you'd like to design a high-availability MySQL replication architecture for your game server or web project, get in touch with me and we'll set it up together.