--- title: "How to Set Up Cross-Datacenter MySQL Replication" description: "Pick a cross-datacenter replication strategy, set up encrypted MySQL 8.0 GTID replication between two sites on Ubuntu 24.04, sync files and fail over safely." category: "Migration and Disaster Recovery" language: en source: https://cubepath.com/docs/migration-and-disaster-recovery/cross-datacenter-replication-strategies updated: 2026-09-25 --- # How to Set Up Cross-Datacenter MySQL Replication 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: ```bash ping -c 10 replica_ip ``` ```output rtt min/avg/max/mdev = 38.102/38.455/39.013/0.260 ms ``` | Strategy | Data loss on site failure (RPO) | Write latency impact | Typical tools | |----------|--------------------------------|----------------------|---------------| | Asynchronous | Seconds of transactions in flight | None | MySQL/PostgreSQL native replication, rsync | | Semi-synchronous | Zero or near zero, if the replica acknowledged | +1 RTT per commit | MySQL semi-sync plugin, PostgreSQL `synchronous_commit` | | Synchronous (quorum) | Zero | +1 RTT or more per commit, needs 3 sites | Galera, Group Replication, DRBD protocol C | | Periodic copy | Up to the interval | None | Backups, 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: ```bash sudo apt update sudo apt install mysql-server ``` Confirm the version and that the service is running: ```bash mysql --version systemctl status mysql --no-pager ``` ```output 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**: ```bash sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` In the `[mysqld]` section, change `bind-address` and add the replication settings: ```ini [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: ```bash sudo systemctl restart mysql ``` Verify GTID mode is active: ```bash sudo mysql -e "SELECT @@server_id, @@gtid_mode, @@log_bin;" ``` ```output +-------------+-------------+-----------+ | @@server_id | @@gtid_mode | @@log_bin | +-------------+-------------+-----------+ | 1 | ON | 1 | +-------------+-------------+-----------+ ``` > Note: On a primary that already has data and GTIDs disabled, `gtid_mode` cannot jump straight from `OFF` to `ON` online without the intermediate steps described in the MySQL manual. A restart with the settings above works if you can afford a short downtime. Allow MySQL only from the replica in the firewall: ```bash 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: ```bash sudo mysql ``` ```sql 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: ```bash mysql -h primary_ip -u repl -p --ssl-mode=REQUIRED -e "SHOW STATUS LIKE 'Ssl_cipher';" ``` ```output +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | Ssl_cipher | TLS_AES_256_GCM_SHA384 | +---------------+------------------------+ ``` ## Step 5 - Configuring the replica On the **replica**, edit the same file: ```bash sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` ```ini [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: ```bash 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: ```bash 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`): ```bash 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: ```bash 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: ```bash sudo mysql ``` ```sql 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: ```sql SHOW REPLICA STATUS\G ``` The important fields are: ```output 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**: ```bash sudo mysql -e "CREATE DATABASE repl_test;" ``` And checking that it appears on the **replica** within a second or two: ```bash sudo mysql -e "SHOW DATABASES LIKE 'repl_test';" ``` ```output +----------------------+ | 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`): ```bash 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: ```bash sudo nano /etc/systemd/system/files-sync.service ``` ```ini [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: ```bash sudo nano /etc/systemd/system/files-sync.timer ``` ```ini [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: ```bash 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 ``` > Warning: `--delete` mirrors deletions too. Keep regular backups of the files, since an accidental deletion on the primary is replicated on the next run. 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. ```bash 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: ```bash sudo mysql ``` ```sql STOP REPLICA; RESET REPLICA ALL; SET GLOBAL super_read_only = OFF; SET GLOBAL read_only = OFF; ``` 3. **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. 4. **Redirect the application** to the new primary: update the application configuration, a DNS record with a low TTL, or a floating IP. 5. **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.