MySQL replication copies changes from a source (formerly “master”) to one or more replicas (formerly “slaves”) using the binary log. Replicas serve read queries, act as hot standbys and make backups possible without loading the source. With GTIDs (global transaction identifiers), setting up and repointing replicas is much simpler than with log file positions.

This guide uses MySQL 8.x (8.0.23+ syntax). MariaDB uses similar concepts but different GTID syntax (CHANGE MASTER TO … master_use_gtid=slave_pos).

Servers: source 10.0.0.31, replica 10.0.0.32, same MySQL version, on a private network or VPN.

1. Source configuration

/etc/mysql/mysql.conf.d/replication.cnf:

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
bind-address = 10.0.0.31
binlog_expire_logs_seconds = 604800   # keep 7 days of binlogs
sudo systemctl restart mysql

(MySQL 8 enables binary logging by default; setting it explicitly documents the intent.)

2. Replication user

CREATE USER 'repl'@'10.0.0.32' IDENTIFIED BY 'a-long-random-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.32';

Firewall: allow 3306 from the replica only:

sudo ufw allow from 10.0.0.32 to any port 3306 proto tcp

3. Replica configuration

[mysqld]
server_id = 2
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON
relay_log = relay-bin

super_read_only prevents accidental writes even by admin users. Restart MySQL.

4. Seed the replica with the source’s data

The replica must start from a consistent copy. For small and medium databases, a dump that includes GTID information:

On the source:

mysqldump --single-transaction --routines --triggers --events \
  --source-data=2 --set-gtid-purged=ON --all-databases | gzip > seed.sql.gz

(--source-data is --master-data on older versions.) Copy it to the replica and import:

mysql -e "RESET BINARY LOGS AND GTIDS;"    # 8.4+; on 8.0: RESET MASTER;
gunzip < seed.sql.gz | mysql

For large databases, use a physical copy with Percona XtraBackup or MySQL’s CLONE plugin (CLONE INSTANCE FROM …), which is much faster.

5. Start replication

On the replica:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.31',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'a-long-random-password',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;

SOURCE_AUTO_POSITION = 1 uses GTIDs to find the right starting point automatically. GET_SOURCE_PUBLIC_KEY is needed with caching_sha2_password when the connection isn’t using TLS (better: enable TLS with SOURCE_SSL = 1).

6. Check status

SHOW REPLICA STATUS\G

Look for:

  • Replica_IO_Running: Yes
  • Replica_SQL_Running: Yes
  • Seconds_Behind_Source: 0 (or small)
  • Last_IO_Error / Last_SQL_Error empty

Test: create a table on the source and check it appears on the replica.

7. Using replicas

  • Send read-only queries (reports, analytics, search) to replicas. Remember replication lag: don’t read your own just-written data from a replica.
  • Take backups from a replica to avoid load on the source.
  • Frameworks support read/write splitting (Laravel read/write connections, Django database routers), or use a proxy like ProxySQL.

8. Failover

If the source fails:

  1. Make sure the old source can’t accept writes (stop it, firewall it).
  2. On the most up-to-date replica: STOP REPLICA; RESET REPLICA ALL; SET GLOBAL super_read_only = OFF; SET GLOBAL read_only = OFF;
  3. Point the application to it.
  4. Repoint other replicas with CHANGE REPLICATION SOURCE TO SOURCE_HOST='new-source', SOURCE_AUTO_POSITION=1; — GTIDs make this straightforward.

For automatic failover, look at MySQL InnoDB Cluster/Group Replication, Orchestrator, or MariaDB with Galera — see MariaDB Galera Cluster.

Common problems

  • Duplicate entry / SQL thread stopped — someone wrote to the replica; keep super_read_only on. Fix the data, then restart replication.
  • Replica falling behind — single-threaded apply on a busy source; enable parallel replication (replica_parallel_workers, default 4 in 8.0.27+), use faster storage.
  • Disk filling with binlogs — set binlog_expire_logs_seconds.

Replication is not a backup: DROP TABLE replicates instantly. Keep backups — see mysqldump and database backup automation.

  • MySQL
  • Replication
  • High Availability