Replicating data to a second data center protects you against the loss of an entire site and lets you recover within minutes instead of restoring from backups. The distance between sites adds latency, so the replication strategy you choose decides how much data you can lose and how much slower writes become. In this tutorial you will compare the main strategies, then set up asynchronous MySQL 8.0 replication with GTIDs and TLS between two Ubuntu 24.04 servers in different data centers, replicate application files with rsync, monitor lag and perform a controlled failover.

Prerequisites

  • Two servers running Ubuntu 24.04 LTS in different data centers, for example CubePath servers in two locations. This guide calls them primary (primary_ip) and replica (replica_ip).
  • A non-root user with sudo privileges on both.
  • Network connectivity between them on TCP port 3306 and SSH. A private link or a VPN such as WireGuard is preferable; if the traffic crosses the public Internet, restrict it with a firewall and use TLS as shown below.
  • Enough disk space on the replica for a full copy of the data.

Step 1 - Choosing a replication strategy

Every strategy trades write latency against possible data loss. The round-trip time (RTT) between sites is the key number. Measure it from the primary:

ping -c 10 replica_ip

rtt min/avg/max/mdev = 38.102/38.455/39.013/0.260 ms
StrategyData loss on site failure (RPO)Write latency impactTypical tools
AsynchronousSeconds of transactions in flightNoneMySQL/PostgreSQL native replication, rsync
Semi-synchronousZero or near zero, if the replica acknowledged+1 RTT per commitMySQL semi-sync plugin, PostgreSQL synchronous_commit
Synchronous (quorum)Zero+1 RTT or more per commit, needs 3 sitesGalera, Group Replication, DRBD protocol C
Periodic copyUp to the intervalNoneBackups, snapshots, scheduled rsync

With 38 ms between sites, every synchronous commit would cost at least 38 ms, which most applications cannot absorb. Asynchronous replication is the common choice across data centers, combined with backups for protection against accidental deletions, because a DROP TABLE replicates as fast as any other change.

The rest of this guide implements asynchronous replication for MySQL and scheduled replication for files.

Step 2 - Installing MySQL on both servers

Run on both servers:

sudo apt update
sudo apt install mysql-server

Confirm the version and that the service is running:

mysql --version
systemctl status mysql --no-pager

mysql  Ver 8.0.43-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))
     Active: active (running)

If the primary already runs MySQL with production data, install MySQL only on the replica.

Step 3 - Configuring the primary

Replication needs a unique server ID, the binary log and GTIDs, which let the replica track its position automatically. Edit the MySQL configuration on the primary:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In the [mysqld] section, change bind-address and add the replication settings:

[mysqld]
bind-address             = 0.0.0.0
server_id                = 1
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800
gtid_mode                = ON
enforce_gtid_consistency = ON

binlog_expire_logs_seconds keeps binary logs for 7 days, so the replica can catch up after a long network outage. Instead of 0.0.0.0 you can bind to the private or VPN address of the primary.

Restart MySQL:

sudo systemctl restart mysql

Verify GTID mode is active:

sudo mysql -e "SELECT @@server_id, @@gtid_mode, @@log_bin;"

+-------------+-------------+-----------+
| @@server_id | @@gtid_mode | @@log_bin |
+-------------+-------------+-----------+
|           1 | ON          |         1 |
+-------------+-------------+-----------+

Allow MySQL only from the replica in the firewall:

sudo ufw allow from replica_ip to any port 3306 proto tcp

Step 4 - Creating the replication user

On the primary, create a user that can only connect from the replica, only over TLS, and only has replication rights. Replace your_strong_password with a long random password:

sudo mysql

CREATE USER 'repl'@'replica_ip' IDENTIFIED BY 'your_strong_password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'replica_ip';
EXIT;

MySQL 8.0 generates TLS certificates on first start, so encrypted connections work without extra setup.

From the replica, check that the user can connect over TLS:

mysql -h primary_ip -u repl -p --ssl-mode=REQUIRED -e "SHOW STATUS LIKE 'Ssl_cipher';"

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+

Step 5 - Configuring the replica

On the replica, edit the same file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

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

server_id must differ from the primary's. super_read_only prevents accidental writes, even by administrators, that would make the replica diverge. The binary log on the replica is what lets it become the new primary after a failover. replica_parallel_workers applies transactions in parallel, which helps the replica keep up with a busy primary.

Restart MySQL:

sudo systemctl restart mysql

Step 6 - Copying the initial data

The replica needs a consistent copy of the primary's data before it can start streaming changes. On the primary, create a dump. With GTIDs enabled, mysqldump includes the GTID set of the snapshot, so you do not need to record binary log coordinates:

sudo mysqldump --all-databases --single-transaction --routines --triggers --events \
  --set-gtid-purged=ON | gzip > ~/primary.sql.gz

Copy it to the replica (replace your_user):

scp ~/primary.sql.gz your_user@replica_ip:~

On the replica, clear the local GTID history and import the dump. RESET MASTER deletes the replica's own binary logs, which is safe on a freshly installed server:

sudo mysql -e "RESET MASTER;"
gunzip < ~/primary.sql.gz | sudo mysql

For databases larger than a few tens of GB, a physical copy with Percona XtraBackup or the MySQL Clone plugin is much faster than a logical dump.

Step 7 - Starting replication

On the replica, point it at the primary and start the replication threads:

sudo mysql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary_ip',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'your_strong_password',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;
START REPLICA;

Check the status:

SHOW REPLICA STATUS\G

The important fields are:

             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
          Seconds_Behind_Source: 0
                  Last_IO_Error:
                 Last_SQL_Error:

Both threads must say Yes. Test end to end by creating a database on the primary:

sudo mysql -e "CREATE DATABASE repl_test;"

And checking that it appears on the replica within a second or two:

sudo mysql -e "SHOW DATABASES LIKE 'repl_test';"

+----------------------+
| Database (repl_test) |
+----------------------+
| repl_test            |
+----------------------+

Remove it on the primary with sudo mysql -e "DROP DATABASE repl_test;", and the drop replicates too.

Step 8 - Replicating application files

Uploaded files and other data outside the database also need to reach the second site. For most applications, a scheduled rsync over SSH gives an RPO of a few minutes with very little complexity. The following runs on the primary and pushes /var/www/uploads to the replica.

Create an SSH key for root and authorize it on the replica for a user that owns the destination directory (/var/www/uploads must exist on the replica and be writable by your_user):

sudo ssh-keygen -t ed25519 -N "" -f /root/.ssh/id_ed25519_sync
sudo ssh-copy-id -i /root/.ssh/id_ed25519_sync.pub your_user@replica_ip

Create a service unit:

sudo nano /etc/systemd/system/files-sync.service

[Unit]
Description=Replicate uploads to the secondary data center
Wants=network-online.target
After=network-online.target

[Service]
Type=oneshot
ExecStart=/usr/bin/rsync -az --delete --bwlimit=50m -e "ssh -i /root/.ssh/id_ed25519_sync" /var/www/uploads/ your_user@replica_ip:/var/www/uploads/

And a timer that runs it every 5 minutes:

sudo nano /etc/systemd/system/files-sync.timer

[Unit]
Description=Run files-sync every 5 minutes

[Timer]
OnBootSec=2min
OnUnitActiveSec=5min

[Install]
WantedBy=timers.target

A oneshot service never runs twice at the same time, so a slow sync cannot pile up. --bwlimit=50m caps the transfer at 50 MB/s so it does not saturate the link. Enable the timer and check the first run:

sudo systemctl daemon-reload
sudo systemctl enable --now files-sync.timer
sudo systemctl start files-sync.service
journalctl -u files-sync.service -n 20 --no-pager

If you need block-level synchronous replication or near real-time file sync, look at DRBD or object storage with built-in replication instead, but take the latency costs from Step 1 into account.

Step 9 - Monitoring replication

Replication that silently stopped is worse than no replication, because you believe you are protected. Check two things on the replica: both threads running and lag under your RPO.

sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E 'Replica_(IO|SQL)_Running:|Seconds_Behind_Source|Last_(IO|SQL)_Error:'

Integrate this into your monitoring system rather than a cron script: the Prometheus mysqld_exporter, Zabbix and most other tools have ready-made replication checks. Alert when either thread is not Yes for more than a minute, or when Seconds_Behind_Source stays above your RPO. For files, alert when the last successful run of files-sync.service is older than 15 minutes.

Step 10 - Failing over to the replica

Practice this procedure before you need it. When the primary site is down or you are doing a planned switch:

  1. Stop writes on the primary if it is still reachable, so no transactions are lost. For a planned switch, stop the application and run sudo mysql -e "SET GLOBAL super_read_only = ON;" on the primary, then wait until Seconds_Behind_Source is 0 on the replica.

  2. Promote the replica. On the replica:

sudo mysql

STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

  1. Make the change permanent by removing read_only and super_read_only from /etc/mysql/mysql.conf.d/mysqld.cnf on the new primary, so a restart does not bring it back as read-only.

  2. Redirect the application to the new primary: update the application configuration, a DNS record with a low TTL, or a floating IP.

  3. Verify by writing a test row and checking the application.

When the old primary comes back, do not simply start it: it may contain transactions that never reached the replica, and two writable primaries will diverge. Rebuild it as a replica of the new primary, repeating Steps 5 to 7 with the roles reversed.

Automating failover (with tools such as Orchestrator or MySQL InnoDB Cluster) reduces downtime, but across data centers it needs a third site or witness to avoid split brain, where both sides believe they are the primary. Start with a well-rehearsed manual procedure.

Troubleshooting

  • Replica_IO_Running: Connecting: the replica cannot reach the primary. Check the firewall rule, bind-address, and the credentials with the test in Step 4. Last_IO_Error has the details.
  • Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection: SOURCE_SSL = 1 is missing from CHANGE REPLICATION SOURCE TO.
  • @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty during the import: run RESET MASTER; on the replica before importing, as in Step 6.
  • Last_SQL_Error with a duplicate key or missing row: the replica has diverged, usually because something wrote to it. Do not skip errors; rebuild the replica from a fresh dump.
  • Seconds_Behind_Source keeps growing: the replica cannot apply changes as fast as the primary produces them. Increase replica_parallel_workers, check disk I/O on the replica, and make sure it has hardware similar to the primary.

Conclusion

You chose a replication strategy based on the latency between your sites, set up encrypted GTID-based MySQL replication to a second data center, replicated application files with a systemd timer, and documented a failover procedure. Replication protects against site loss, not against mistakes, so keep versioned backups alongside it. As next steps, add replication checks to your monitoring and rehearse the failover in a maintenance window, measuring how long it takes against your RTO.