MySQL Master-Slave Replication Setup Guide (Source-Replica Syntax for 8.4)

Read This First: The Syntax In Most Guides No Longer Runs

If you copy CHANGE MASTER TO or SHOW SLAVE STATUS into a MySQL 8.4 server, you get a syntax error. Not a deprecation warning. An error.

MySQL deprecated the master/slave terminology in 8.0.22 and removed it outright in 8.4 LTS. Since 8.4 is the current long-term release, and since Magento 2.4.8 and a growing number of platforms require it, most replication guides on the internet, including the earlier version of this one, now contain commands that simply fail.

Here is the mapping you actually need:

On MySQL 8.0 the new names all work and the old ones still work with a deprecation warning. On MySQL 8.4 only the new names work. So write the new syntax everywhere: it is correct on both, and it is the only thing that survives the upgrade.

Everything below uses 8.4 syntax.

Why Replication Matters

As traffic grows the database becomes the bottleneck. A single MySQL server handles reads and writes together, and read traffic typically outweighs writes by 10 to 1 or more. Replication moves read queries onto replicas while the source handles writes, which buys you read scaling and a disaster-recovery position at the same time.

Be clear about what it does not buy you. Replication is not a backup. DROP TABLE users executes on the source and replicates faithfully to every replica in under a second. Replication protects you from a server dying. It does nothing about a bad migration, a wrong WHERE clause, or ransomware. You still need real backups with tested restores.

Architecture

Application
    |
    +-- Writes --> Source (MySQL 8.4, 192.168.1.10)
    |                      |
    |                      | binlog (GTID, ROW)
    |                      v
    +-- Reads ---> HAProxy --> Replica 1 (192.168.1.11)
                   :3307   --> Replica 2 (192.168.1.12)
                           --> Replica 3 (192.168.1.13)

HAProxy balances reads across healthy replicas. Writes go straight to the source on 3306. The application must know the difference, which is the part people underestimate, see the replication-lag section below.

Source Configuration

# /etc/mysql/mysql.conf.d/source.cnf
[mysqld]
server-id                = 1
log_bin                  = /var/log/mysql/mysql-bin
binlog_format            = ROW
binlog_row_image         = FULL
gtid_mode                = ON
enforce_gtid_consistency = ON
sync_binlog              = 1
innodb_flush_log_at_trx_commit = 1

# Retain binary logs for 7 days
binlog_expire_logs_seconds = 604800

# Performance tuning: size to your hardware, not to this article
innodb_buffer_pool_size  = 8G
innodb_log_file_size     = 1G
innodb_io_capacity       = 2000
max_connections          = 500

sync_binlog = 1 with innodb_flush_log_at_trx_commit = 1 is the durable combination: every commit is flushed to disk before it is acknowledged. It costs write throughput and it is the right default. Relaxing either one trades data safety for speed, which is a decision to make deliberately with your risk in mind, not to inherit from a config snippet.

innodb_buffer_pool_size = 8G is an example, not a recommendation. Rule of thumb on a dedicated database server: roughly 70 percent of RAM. On a shared box, much less. Copying 8G onto a 4 GB VPS is how you meet the OOM killer.

Create the replication user

CREATE USER 'repl_user'@'192.168.1.%'
  IDENTIFIED WITH caching_sha2_password BY 'strong_password_here';

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;

An oddity worth knowing: the privilege is still spelled REPLICATION SLAVE even in 8.4. The statements were renamed; this grant name was not. It looks like an inconsistency because it is one.

Check the source

SHOW BINARY LOG STATUS\G          -- was SHOW MASTER STATUS
SELECT @@global.gtid_executed;

Replica Configuration

# /etc/mysql/mysql.conf.d/replica.cnf
[mysqld]
server-id                = 2          # MUST be unique across every host
log_bin                  = /var/log/mysql/mysql-bin
binlog_format            = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON
relay_log                = /var/log/mysql/relay-bin
read_only                = ON
super_read_only          = ON
log_replica_updates      = ON         # was log_slave_updates

# Parallel replication
replica_parallel_workers    = 4       # was slave_parallel_workers
replica_parallel_type       = LOGICAL_CLOCK
replica_preserve_commit_order = ON

innodb_buffer_pool_size  = 8G

Three notes that matter more than they look:

server-id must be unique on every host. Duplicate IDs cause replicas to fight over the connection and disconnect each other in a loop. It is a five-second mistake that produces a baffling hour of debugging.

super_read_only = ON, not just read_only. Plain read_only still lets users with SUPER write, and your admin account has SUPER. Only super_read_only actually stops the 2am "I'll just fix it on the replica" that silently breaks replication forever.

replica_preserve_commit_order = ON is not optional when using parallel workers. Without it, transactions can commit on the replica in a different order than on the source, and your replicas become subtly, silently inconsistent. Parallel replication without commit ordering is faster in the way that skipping the seatbelt is faster.

Initialise replication

mysqldump --all-databases --single-transaction \
  --routines --triggers --events \
  --source-data=2 --set-gtid-purged=ON \
  -u root -p > source_backup.sql

--source-data=2 replaces the removed --master-data=2. --single-transaction is what makes the dump consistent without locking the whole server, and it only works for InnoDB tables. Any MyISAM table in the dump is a silent consistency hole.

For anything past a few tens of gigabytes, mysqldump becomes painful (hours to restore). Use Percona XtraBackup or a filesystem snapshot instead. The GTID position carries across either way.

-- On the replica
SOURCE /tmp/source_backup.sql;

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.10',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='strong_password_here',
  SOURCE_AUTO_POSITION=1,
  SOURCE_SSL=1;

START REPLICA;

SOURCE_AUTO_POSITION=1 is why GTID is worth it: the replica negotiates its own position from its GTID set. No binlog file names, no offsets, no arithmetic during a 3am failover.

Verify

SHOW REPLICA STATUS\G

The fields that matter, in 8.4 spelling:

• Replica_IO_Running: Yes - the receiver thread is connected and pulling binlog
• Replica_SQL_Running: Yes - the applier thread is executing events
• Seconds_Behind_Source: 0 - lag (read the caveat below)
• Last_IO_Error / Last_SQL_Error - empty, and the first place to look when they are not
• Retrieved_Gtid_Set and Executed_Gtid_Set - retrieved is what arrived, executed is what applied. A growing gap means the applier is the bottleneck

The Two Things That Actually Break

Seconds_Behind_Source lies to you

It is the most-monitored field in MySQL replication and the most misunderstood. Two failure modes:

It reads 0 while you are badly behind. The value measures the applier thread against the timestamp of the event it is currently processing. If the receiver thread is stalled or disconnected, there is no event to compare against, and the field can report 0 or NULL on a replica that is hours stale. A replica that is not receiving looks perfectly healthy by this metric.

It jumps around on a busy source. Long transactions distort it because the timestamp is the transaction's start.

So do not alert on it alone. Alert on the combination:

-- Healthy means all three, not just the last one
SELECT
  SERVICE_STATE                                  AS receiver_state,   -- ON
  (SELECT SERVICE_STATE FROM performance_schema.replication_applier_status)
                                                 AS applier_state     -- ON
FROM performance_schema.replication_connection_status;

Performance Schema is the more honest source. replication_connection_status tells you the receiver is genuinely connected, which Seconds_Behind_Source will not.

Reading your own writes

This is the bug that reaches customers. The application writes to the source, immediately reads from a replica, and the row is not there yet. The user submits a form and sees an empty list. It works fine on your laptop with no replication and fails intermittently in production, which makes it a nightmare to reproduce.

Options, roughly in order of effort:

• Route reads after a write to the source for that session or request. Crude, effective, most common.
• Use WAIT_FOR_EXECUTED_GTID_SET() to block until the replica has caught up to a specific GTID before reading.
• Accept it for genuinely eventual-consistent data (product listings, analytics) and never for anything the user just changed.

Decide this deliberately before you split reads, not after the first support ticket.

HAProxy for Read Distribution

# /etc/haproxy/haproxy.cfg
frontend mysql_read
    bind *:3307
    mode tcp
    default_backend mysql_replicas

backend mysql_replicas
    mode tcp
    balance leastconn
    option mysql-check user haproxy_check

    server replica1 192.168.1.11:3306 check inter 5s fall 3 rise 2
    server replica2 192.168.1.12:3306 check inter 5s fall 3 rise 2
    server replica3 192.168.1.13:3306 check inter 5s fall 3 rise 2

Understand the gap here: option mysql-check verifies MySQL answers the handshake. It does not check whether replication is running or how far behind the replica is. A replica whose SQL thread died six hours ago still passes this health check and happily serves stale reads.

For a real check, use an HTTP agent (xinetd or a small service) that queries SHOW REPLICA STATUS and returns unhealthy when lag exceeds your threshold or either thread is stopped, then point HAProxy at it with option httpchk. Otherwise your load balancer is confirming the server is alive, not that its data is correct.

Monitoring

#!/bin/bash
# /usr/local/bin/check_replication.sh
# Checks BOTH threads and lag, because lag alone is not enough.
THRESHOLD=30
HOST=192.168.1.11

STATUS=$(mysql -u monitor -p"$MONITOR_PASS" -h "$HOST" -e "SHOW REPLICA STATUS\G")

IO=$(echo "$STATUS"  | awk '/Replica_IO_Running:/  {print $2}')
SQL=$(echo "$STATUS" | awk '/Replica_SQL_Running:/ {print $2}')
LAG=$(echo "$STATUS" | awk '/Seconds_Behind_Source:/ {print $2}')

if [ "$IO" != "Yes" ] || [ "$SQL" != "Yes" ]; then
  echo "CRITICAL: replication threads stopped (IO=$IO SQL=$SQL)"
  exit 2
elif [ "$LAG" = "NULL" ]; then
  echo "CRITICAL: replication not reporting lag"
  exit 2
elif [ "$LAG" -gt "$THRESHOLD" ]; then
  echo "WARNING: lag ${LAG}s (threshold ${THRESHOLD}s)"
  exit 1
fi
echo "OK: lag ${LAG}s"
exit 0

Note the password comes from the environment, not the command line. A password in mysql -p'secret' is visible to every user on the box via ps.

For Prometheus, mysqld_exporter exposes replication metrics. Be aware its metric names still use the old terminology even against 8.4 servers, because the exporter's names are its own compatibility surface:

- alert: MySQLReplicationLag
  expr: mysql_slave_status_seconds_behind_master > 30
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "MySQL replication lag on {{ $labels.instance }}"

- alert: MySQLReplicationStopped
  expr: mysql_slave_status_slave_sql_running == 0
        or mysql_slave_status_slave_io_running == 0
  for: 1m
  labels:
    severity: critical
  annotations:
    summary: "MySQL replication thread stopped on {{ $labels.instance }}"

Check your exporter version's actual metric names rather than trusting either naming convention.

Failover

-- On the most up-to-date replica
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- On every remaining replica, repoint at the new source
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.11',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

"The most up-to-date replica" is doing heavy lifting in that sentence. Compare Executed_Gtid_Set across replicas and promote the one furthest ahead. Promote the wrong one and you have silently discarded the transactions it never received.

This is manual failover, which means a human is awake, has the runbook, and is not panicking. If you need failover to happen without that, use a tool built for it: MySQL Group Replication, InnoDB Cluster, or Orchestrator. Hand-rolled automatic failover is how you get split brain, where two servers both believe they are the source and both accept writes.

Best Practices

• Write 8.4 syntax everywhere, even on 8.0. It works on both and survives the upgrade.
• Use GTID (SOURCE_AUTO_POSITION=1). Binlog file and position arithmetic during an incident is a mistake generator.
• super_read_only on every replica, not plain read_only.
• replica_preserve_commit_order = ON whenever parallel workers are on.
• Alert on both replication threads plus lag, never lag alone.
• Test failover in staging on a schedule. An untested runbook is fiction.
• Keep 7 days of binlog minimum, more if a replica may be offline for maintenance.
• Use TLS for replication (SOURCE_SSL=1) across any network you do not fully control.
• Consider semi-synchronous replication if losing the last few transactions on a source failure is unacceptable.
• And again: replication is not a backup.

Properly configured replication turns a single point of failure into a scalable, resilient database tier. Configured from a 2019 blog post, it turns into a syntax error on 8.4 or a silent consistency bug on any version. For complex or multi-region topologies, our infrastructure management and servers management teams design and run these in production.

Sources

• MySQL 8.4: what is new and what was removed
• MySQL 8.4: CHANGE REPLICATION SOURCE TO
• MySQL 8.4: SHOW REPLICA STATUS
• MySQL 8.4: replica server options and variables
• MySQL: replication with GTIDs
• MySQL: WAIT_FOR_EXECUTED_GTID_SET