MySQL Replication: Primary/Replica (master-slave) and Source/Source (master-master)
Replication copies every change from one MySQL server to another, using the binary log.
This guide builds both classic layouts step by step with GTIDs on MySQL 8.0 / 8.4:
Part 1 One source, one (or more) replicas Copies for backups, reporting and failover
Part 2 Two servers replicating each other Two-way copy; one is normally the active writer
It uses MySQL's current words: "source" (was master) and "replica" (was slave). The old
commands (CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS) still work on 8.0 but were
removed in 8.4. Both layouts are asynchronous: a replica can lag a little behind the source.
Replication is NOT a backup. A bad DELETE replicates instantly. Keep real backups too.
Example servers used below (replace with your own)
db1 10.0.0.11 Source (Part 1) | Server A (Part 2)
db2 10.0.0.12 Replica (Part 1) | Server B (Part 2)
Prerequisites
- Two Linux servers with MySQL installed: the same major version (the replica may be newer, never older)
- A private network between them, and TCP port 3306 open between the two servers only
- Synchronized clocks (chrony or systemd-timesyncd)
- A unique server-id on every server, and a unique server UUID (never clone a data directory without deleting auto.cnf)
- Root or sudo access, and a maintenance window if the source already holds live data
- A recent backup of the source before you start
Firewall (allow only the other server, never the whole internet)
# on db1
sudo ufw allow from 10.0.0.12 to any port 3306 proto tcp
# on db2
sudo ufw allow from 10.0.0.11 to any port 3306 proto tcp
On cloud servers also open 3306 in the security group or network security group, from that one IP only.
Test it: nc -zv 10.0.0.11 3306
How replication works (short version)
1. The source writes every change to its binary log (binlog)
2. The replica's I/O thread connects, reads the binlog and stores it in a relay log
3. The replica's SQL (applier) thread replays the relay log on the replica
GTIDs (global transaction IDs) label each transaction as server-uuid:number, so a replica just says
"continue from what I already have" (SOURCE_AUTO_POSITION=1) and you never juggle file names and positions.
=====================================================================
PART 1: PRIMARY / REPLICA (MASTER-SLAVE)
=====================================================================
Step 1: Configure the source (db1)
Edit the MySQL config (Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnf, RHEL: /etc/my.cnf.d/) under [mysqld]:
[mysqld]
server-id = 1
bind-address = 10.0.0.11 # or 0.0.0.0, but then firewall it
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 604800 # keep binlogs 7 days
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON # (8.0.26 and older name: log_slave_updates)
sync_binlog = 1 # safest, slower
innodb_flush_log_at_trx_commit = 1 # safest, slower
# Optional: replicate only some databases (be careful, see Tips)
# binlog_do_db = shop
Restart it and check:
sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'server_id'; SHOW VARIABLES LIKE 'gtid_mode'; SHOW VARIABLES LIKE 'log_bin';"
On MySQL 8.0 the binary log is on by default. Setting log_bin explicitly keeps the location predictable.
Step 2: Create the replication user (on db1)
mysql -u root -p
CREATE USER 'repl'@'10.0.0.12' IDENTIFIED BY 'A-Long-Random-Password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.12';
FLUSH PRIVILEGES;
Use a dedicated user, limited to the replica's IP, with only the REPLICATION SLAVE privilege.
REQUIRE SSL makes it use TLS. MySQL 8.0 generates TLS certificates automatically, so this works out of the box.
Step 3: Configure the replica (db2)
[mysqld]
server-id = 2
bind-address = 10.0.0.12
relay_log = /var/log/mysql/relay-bin
log_bin = /var/log/mysql/mysql-bin.log # needed for GTID failover and chained replicas
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON
read_only = ON # stops normal users writing to the replica
super_read_only = ON # stops even admin users (except replication threads)
relay_log_recovery = ON # rebuild relay logs after a crash
skip_replica_start = ON # do not auto-start replication until you check it (older: skip_slave_start)
replica_parallel_workers = 4 # apply transactions in parallel (older: slave_parallel_workers)
replica_preserve_commit_order = ON
sudo systemctl restart mysql
Step 4: Copy the existing data to the replica
If the source is brand new and empty, skip this step.
Otherwise take a consistent dump on db1 (InnoDB tables are not locked with --single-transaction):
mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events --set-gtid-purged=ON > seed.sql
scp seed.sql user@10.0.0.12:/tmp/
On db2, load it (super_read_only blocks the load, so switch it off for the import):
mysql -u root -p -e "SET GLOBAL super_read_only=OFF; SET GLOBAL read_only=OFF;"
mysql -u root -p < /tmp/seed.sql
mysql -u root -p -e "SET GLOBAL read_only=ON; SET GLOBAL super_read_only=ON;"
The dump records which GTIDs it contains, so the replica knows exactly where to continue.
For big databases, use a physical backup instead: Percona XtraBackup or MySQL Clone plugin (INSTALL PLUGIN clone ...; CLONE INSTANCE FROM ...).
Step 5: Point the replica at the source and start it (on db2)
mysql -u root -p
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '10.0.0.11',
SOURCE_PORT = 3306,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'A-Long-Random-Password',
SOURCE_AUTO_POSITION = 1,
SOURCE_SSL = 1;
START REPLICA;
Older servers (before 8.0.23) use: CHANGE MASTER TO MASTER_HOST=..., MASTER_AUTO_POSITION=1; START SLAVE;
If you see "Authentication requires secure connection" or a public key error with caching_sha2_password,
keep SOURCE_SSL = 1 (preferred), or for testing add GET_SOURCE_PUBLIC_KEY = 1.
Step 6: Verify (on db2)
SHOW REPLICA STATUS\G
You want to see:
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Last_IO_Error: (empty) Last_SQL_Error: (empty)
Retrieved_Gtid_Set / Executed_Gtid_Set: growing numbers
Then test the whole path:
-- on db1
CREATE DATABASE repl_test; CREATE TABLE repl_test.t (id INT PRIMARY KEY, note VARCHAR(50)); INSERT INTO repl_test.t VALUES (1,'hello from db1');
-- on db2
SELECT * FROM repl_test.t; -- the row should appear within a second
-- clean up on db1
DROP DATABASE repl_test;
Add another replica
Repeat Steps 3 to 6 on the new server with a new server-id (3, 4, ...). Seed it from a dump, a backup of an existing replica, or the clone plugin.
Promote the replica to be the new source (planned switchover)
1. Stop writes on db1: SET GLOBAL super_read_only=ON; SET GLOBAL read_only=ON;
2. Wait until db2 has caught up: SHOW REPLICA STATUS\G (Seconds_Behind_Source: 0, same Executed_Gtid_Set)
3. On db2, stop replicating: STOP REPLICA; RESET REPLICA ALL;
4. On db2, allow writes: SET GLOBAL super_read_only=OFF; SET GLOBAL read_only=OFF;
5. Point your applications at db2 (change the connection string, DNS name or virtual IP)
6. Turn db1 into a replica of db2 with the CHANGE REPLICATION SOURCE TO command from Step 5, then START REPLICA
If db1 died (unplanned failover), do steps 3 to 5 on db2, and rebuild db1 as a replica when it returns.
If db1 had transactions db2 never received, they are lost. Check Executed_Gtid_Set on both if you can still reach db1.
=====================================================================
PART 2: SOURCE / SOURCE (MASTER-MASTER)
=====================================================================
Two servers, each a source AND a replica of the other, so a change on either one appears on both.
Recommended way to run it: ACTIVE / PASSIVE. Applications write to one server only. The other
stays warm and ready, so failover is just switching where applications connect.
Writing to both at once ("active-active") can silently corrupt data: MySQL has no conflict detection.
If two servers update the same row at the same moment, they end up with different values and replication may stop or diverge.
Step 1: Configure both servers
On db1 (Server A), under [mysqld]:
server-id = 1
bind-address = 10.0.0.11
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON
relay_log = /var/log/mysql/relay-bin
relay_log_recovery = ON
auto_increment_increment = 2 # step by 2 so AUTO_INCREMENT keys never collide...
auto_increment_offset = 1 # ...db1 makes odd ids: 1, 3, 5
binlog_expire_logs_seconds = 604800
On db2 (Server B), the same file but:
server-id = 2
bind-address = 10.0.0.12
auto_increment_offset = 2 # db2 makes even ids: 2, 4, 6
Restart both: sudo systemctl restart mysql
The auto_increment settings prevent duplicate primary keys if both servers ever take inserts.
Neither server is read_only here, so either can accept writes. To make one passive, set it read-only later (see below).
Step 2: Create the replication user on BOTH servers
On db1 (lets db2 connect):
CREATE USER 'repl'@'10.0.0.12' IDENTIFIED BY 'A-Long-Random-Password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.12';
On db2 (lets db1 connect):
CREATE USER 'repl'@'10.0.0.11' IDENTIFIED BY 'A-Long-Random-Password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.11';
Open port 3306 in BOTH directions with the firewall commands shown earlier.
Step 3: Seed the second server (only if db1 already has data)
On db1: mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events --set-gtid-purged=ON > seed.sql
Copy it to db2 and load it: mysql -u root -p < seed.sql
If both servers are brand new and empty, skip this step.
Step 4: Make db2 a replica of db1
On db2:
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.11', SOURCE_USER='repl', SOURCE_PASSWORD='A-Long-Random-Password', SOURCE_AUTO_POSITION=1, SOURCE_SSL=1;
START REPLICA;
SHOW REPLICA STATUS\G -- both threads must say Yes
Step 5: Make db1 a replica of db2
On db1:
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.12', SOURCE_USER='repl', SOURCE_PASSWORD='A-Long-Random-Password', SOURCE_AUTO_POSITION=1, SOURCE_SSL=1;
START REPLICA;
SHOW REPLICA STATUS\G -- both threads must say Yes
Now the loop is closed. Each server replicates the other, and GTIDs stop a transaction from being applied twice.
Step 6: Test both directions
-- on db1
CREATE DATABASE mm_test; CREATE TABLE mm_test.t (id INT AUTO_INCREMENT PRIMARY KEY, src VARCHAR(10)); INSERT INTO mm_test.t (src) VALUES ('db1');
-- on db2
SELECT * FROM mm_test.t; -- shows the db1 row (id 1)
INSERT INTO mm_test.t (src) VALUES ('db2'); -- gets an even id
-- back on db1
SELECT * FROM mm_test.t; -- shows both rows
DROP DATABASE mm_test; -- clean up
Run it active / passive (recommended)
On the server that should NOT take writes:
SET PERSIST super_read_only = ON; SET PERSIST read_only = ON;
Replication keeps working (its threads are exempt from read_only). To fail over, flip the two servers:
-- on the old active server
SET PERSIST super_read_only = ON; SET PERSIST read_only = ON;
-- once the other server has caught up (Seconds_Behind_Source: 0)
-- on the new active server
SET PERSIST super_read_only = OFF; SET PERSIST read_only = OFF;
Then point applications at the new active server.
Automate the switch with a floating IP or proxy:
keepalived A virtual IP that moves to the healthy server (VRRP), with a health-check script
HAProxy TCP load balancer with a mysql-check backend (send writes to only one server)
ProxySQL A MySQL-aware proxy: read/write splitting, health checks, seamless failover
MySQL Router Used with InnoDB Cluster (see the alternatives below)
Minimal keepalived idea: a script that checks "mysql -e 'SELECT 1'" and the read_only flag, so the VIP lives on the writable server.
If you really write to both (active-active)
- Keep auto_increment_increment and auto_increment_offset set as above
- Send each application, tenant or table to one server so they never touch the same rows
- Avoid UPDATE and DELETE on the same rows from both sides, avoid non-deterministic statements, and keep binlog_format=ROW
- Run pt-table-checksum regularly to catch drift
- Know that a conflict can stop replication or leave the servers different with no error at all
If you need true multi-writer safety, use Group Replication or a Galera-based cluster instead.
=====================================================================
MONITORING AND MAINTENANCE
=====================================================================
Health check (run on each replica)
SHOW REPLICA STATUS\G
Replica_IO_Running Yes = connected to the source and fetching binlog
Replica_SQL_Running Yes = applying changes
Seconds_Behind_Source Lag in seconds (NULL means the SQL thread is stopped)
Last_IO_Error / Last_SQL_Error The reason when something stops
Retrieved_Gtid_Set What was fetched Executed_Gtid_Set What was applied
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G -- per-thread errors
SELECT @@global.gtid_executed; -- compare between servers: should match when in sync
SHOW BINARY LOGS; SHOW MASTER STATUS; -- 8.4: SHOW BINARY LOG STATUS;
Script it (returns non-zero if broken)
mysql -N -B -e "SHOW REPLICA STATUS\G" | awk '/Replica_(IO|SQL)_Running:/ && $2!="Yes"{bad=1} END{exit bad}' && echo OK || echo BROKEN
Alert on: either thread not Yes, lag above your limit (for example 30 s), disk space on the binlog partition, and gtid_executed drifting apart.
Lag: what to look at
- Big single transactions and long-running writes on the source
- Replica hardware slower than the source (disk I/O is the usual cause)
- replica_parallel_workers set too low (try 4 to 8) with replica_preserve_commit_order=ON
- Missing primary keys on tables (row-based replication then scans whole tables per row)
- Heavy read queries on the replica competing for resources
Accurate lag measurement: pt-heartbeat (Percona Toolkit) writes a timestamp on the source and reads it on the replica.
Check the data really matches
pt-table-checksum --host=10.0.0.11 --user=checksum --ask-pass --databases shop # on the source
pt-table-sync --print --sync-to-source h=10.0.0.12 --databases shop # show fixes, then --execute
Run the checks off-peak: they add load.
Purge and disk space
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;
Never delete binlog files by hand with rm. Never purge a binlog a replica has not yet read.
Set binlog_expire_logs_seconds so old logs clear automatically, longer than your worst replica outage.
Back up from a replica
Take dumps or physical backups from the replica so the source stays fast:
STOP REPLICA SQL_THREAD; -- optional, for a fully quiet copy
mysqldump -u root -p --all-databases --single-transaction --routines --events > /backups/replica.sql
START REPLICA SQL_THREAD;
Check Last_IO_Error, then:
- Firewall or network: nc -zv 10.0.0.11 3306 from the replica
- Wrong user, password or host in the CREATE USER: mysql -h 10.0.0.11 -u repl -p from the replica
- Authentication requires a secure connection: use SOURCE_SSL=1 (or GET_SOURCE_PUBLIC_KEY=1 for a quick test)
- bind-address is 127.0.0.1 on the source: change it and restart
- Duplicate server-id or duplicate server UUID (cloned VM): fix server-id, or delete auto.cnf on the clone and restart
Replica_SQL_Running: No (an error stops the applier)
Read Last_SQL_Error first.
Error 1062 Duplicate entry The row already exists on the replica: someone wrote directly to the replica, or data drifted
Error 1032 Can't find record A row to update or delete is missing on the replica: data drift
Error 1146 Table doesn't exist The replica is missing a table: the seed was incomplete
Do not blindly skip errors. First find out why the servers differ, and repair the data (pt-table-sync) or rebuild the replica.
As a last resort with GTID (skip exactly one failed transaction):
STOP REPLICA;
SET GTID_NEXT = 'SOURCE-UUID:NUMBER'; -- the transaction shown in Last_SQL_Error / Executed_Gtid_Set
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;
Skipping leaves the replica different from the source. Log every skip and verify the data afterwards.
Error 1236: "could not find first log file" or "purged binary logs"
The replica was offline longer than the source kept its binlogs. Re-seed it (dump or clone), then run CHANGE REPLICATION SOURCE TO ... again.
On a rebuilt replica, first run: RESET MASTER; -- 8.4: RESET BINARY LOGS AND GTIDS; then load the dump
"Slave/Replica is not configured" or SHOW REPLICA STATUS is empty
You have not run CHANGE REPLICATION SOURCE TO yet, or you ran RESET REPLICA ALL.
Seconds_Behind_Source keeps growing
See "Lag: what to look at" above. Check top, iostat and SHOW PROCESSLIST on the replica.
GTID errors: "Cannot replicate because the source purged required binary logs" or "GTID_NEXT" conflicts
Compare gtid_executed on both servers. Rebuild the replica from a fresh backup if they have diverged.
Statements that break GTID mode (CREATE TABLE ... SELECT in older versions, mixing transactional and non-transactional tables) are blocked by enforce_gtid_consistency.
Master-master data drift
Symptoms: different row counts, replication stops with 1062/1032, or nothing errors but the servers disagree.
Fix: pick the correct server, take a dump from it, rebuild the other as a replica, and adopt a single-writer rule.
Server will not start after config changes
Read the error log: sudo tail -50 /var/log/mysql/error.log
A wrong option name for your version is the most common cause (log_slave_updates vs log_replica_updates).
Version and product notes
MySQL 8.0.22 and earlier START SLAVE, SHOW SLAVE STATUS, CHANGE MASTER TO, MASTER_* options, log_slave_updates
MySQL 8.0.23+ REPLICA / SOURCE names introduced (the old names still work but are deprecated)
MySQL 8.4 LTS The old MASTER/SLAVE commands are removed. Use the new names only
MariaDB Uses CHANGE MASTER TO ... MASTER_USE_GTID=slave_pos (not SOURCE_AUTO_POSITION) and its own GTID format. Check the MariaDB docs
Percona Server Same as MySQL, with extra tools (XtraBackup, Toolkit)
Alternatives when you need more than two servers or automatic failover
InnoDB Cluster + Group Replication + MySQL Router Built in to MySQL 8: automatic failover, single-primary or multi-primary
Galera (MariaDB Galera, Percona XtraDB Cluster) Synchronous multi-master with conflict detection
Orchestrator, MHA Topology managers for automatic failover of classic replication
Managed services (AWS RDS/Aurora, Azure Database for MySQL, Cloud SQL) Replicas and failover handled for you
Tips
Always use GTIDs (gtid_mode=ON). It makes seeding, failover and re-pointing replicas much simpler than binlog file and position.
Keep replicas read-only (read_only and super_read_only). Writes made directly on a replica are the number one cause of broken replication.
Use row-based binary logs (binlog_format=ROW) and give every table a primary key.
"Master-master" does not mean "write to both". Treat it as active/passive with a fast failover, unless you have designed for conflicts.
Never put replication credentials in shell history or scripts. Restrict the replication user to the other server's IP and require TLS.
Monitor both threads, the lag and the disk space of the binlog volume. Broken replication that nobody notices is worse than none.
Test a failover before you need it: promote the replica on a schedule, and confirm your applications reconnect.
Replication is not a backup. Keep regular dumps or physical backups and practise restoring them, plus binary logs for point-in-time recovery.
Keep every server on the same MySQL version, and upgrade replicas first, the source last.
Avoid replicate-do-db and binlog-do-db filters unless you fully understand them. They break cross-database queries and cause silent data gaps.
