How to Set Up MySQL Replication with GTID on Ubuntu 24.04

MySQL replication copies every change made on one server, the source (formerly called master), to one or more replicas (formerly slaves). Replicas are commonly used to offload read queries, to run backups without touching the primary server and to keep a warm standby for failover. In this tutorial you will set up asynchronous source-replica replication between two Ubuntu 24.04 servers running MySQL 8.0, using global transaction identifiers (GTIDs), seed the replica from a consistent dump of the source and verify that changes flow across.

Prerequisites

To follow this guide you need:

• Two servers running Ubuntu 24.04 LTS, for example two CubePath VPS in the same location, ideally connected over a private network.
• MySQL 8.0 installed on both from the Ubuntu repositories (sudo apt install mysql-server). Both servers should run the same version; check with mysql --version.
• A non-root user with sudo privileges on both servers.
• UFW enabled on both servers, with SSH allowed.

This guide uses these example values. Replace them with your own:

The database being replicated is called appdb.

Note

MariaDB also supports replication, but its GTID implementation and commands (CHANGE MASTER TO ... MASTER_USE_GTID) are different. This guide covers MySQL 8.0 only.

Step 1 - Configuring the source server

The source must write every change to its binary log, have a unique server ID and accept connections from the replica. GTIDs give every transaction a unique ID, so the replica always knows which transactions it has applied and you never have to track binary log file names and positions by hand.

On db-source, open the main MySQL configuration file:

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

By default MySQL only listens on 127.0.0.1. Change bind-address to the source's private IP so the replica can connect:

bind-address = 10.0.0.10

Save the file. Then create a separate file for the replication settings:

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

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

• server_id must be unique across all servers in the replication topology.
• log_bin is enabled by default in MySQL 8.0; setting it explicitly keeps the logs in /var/log/mysql with a predictable name.
• binlog_expire_logs_seconds = 604800 keeps seven days of binary logs. A replica that falls further behind than that has to be rebuilt.
• gtid_mode and enforce_gtid_consistency enable GTID-based replication. enforce_gtid_consistency rejects the few statements that cannot be logged safely with GTIDs, such as CREATE TABLE ... SELECT in older applications.

Restart MySQL and confirm the settings are active:

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

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

Allow MySQL connections only from the replica's IP address:

sudo ufw allow from 10.0.0.11 to any port 3306 proto tcp

Step 2 - Creating the replication user

The replica logs in to the source with a dedicated account that only has the REPLICATION SLAVE privilege (the privilege name has not been renamed in MySQL 8.0). Open the MySQL shell on db-source:

sudo mysql

Create the user, restricted to the replica's IP address. Replace your_strong_password with a strong, unique password:

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

MySQL 8.0 uses the caching_sha2_password authentication plugin, which requires either an encrypted connection or an RSA key exchange. REQUIRE SSL enforces encryption; MySQL on Ubuntu generates self-signed TLS certificates automatically at first start, so no extra setup is needed.

Step 3 - Dumping the source data

The replica needs a starting copy of the data plus the exact set of transactions that copy contains. mysqldump with --single-transaction produces a consistent snapshot without locking InnoDB tables, and with GTIDs enabled it records the executed transaction set in the dump as a SET @@GLOBAL.GTID_PURGED statement.

On db-source, dump the database:

sudo mysqldump --single-transaction --source-data=2 --triggers --routines --events --databases appdb > ~/appdb.sql

MySQL prints a warning when you dump only some databases from a server with GTIDs:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

This is expected: you do want the GTIDs restored, because that is how the replica knows where to start. To replicate every database, use --all-databases instead of --databases appdb.

Confirm the dump contains the GTID set:

grep -m1 "GTID_PURGED" ~/appdb.sql

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3e11fa47-71ca-11e1-9e33-c80aa9429562:1-1542';

Copy the dump to the replica (replace your_user with your sudo user):

scp ~/appdb.sql [email protected]:~/

Step 4 - Configuring the replica server

On db-replica, create the replication configuration file:

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

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

• server_id = 2 differs from the source.
• relay_log sets where the replica stores the events it receives before applying them.
• log_bin on the replica lets you promote it to source later or chain another replica from it.
• read_only = ON stops application users from writing to the replica by mistake. Accounts with the SUPER or CONNECTION_ADMIN privilege, such as root, can still write, which you need for the import in the next step. You will tighten this with super_read_only afterwards.

The replica does not need to accept remote connections for replication itself, so leave its bind-address at 127.0.0.1 unless your applications will read from it over the network. Restart MySQL:

sudo systemctl restart mysql

Step 5 - Importing the dump on the replica

SET @@GLOBAL.GTID_PURGED in the dump fails if the replica already has GTIDs that overlap with the source's. Clear the replica's own GTID history first so the import cannot fail on leftover transactions:

sudo mysql -e "RESET MASTER;"

Warning

RESET MASTER deletes all binary logs and the GTID history of the server you run it on. Run it only on the new replica, never on the source.

Import the dump:

sudo mysql < ~/appdb.sql

Verify that the database exists and that the GTID set now matches the one from the dump:

sudo mysql -e "SHOW DATABASES LIKE 'appdb'; SELECT @@GLOBAL.gtid_executed;"

+------------------+
| Database (appdb) |
+------------------+
| appdb            |
+------------------+
+----------------------------------------------+
| @@GLOBAL.gtid_executed                       |
+----------------------------------------------+
| 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-1542  |
+----------------------------------------------+

Step 6 - Starting replication

Point the replica at the source. With SOURCE_AUTO_POSITION = 1, the replica sends the source its GTID set and receives every transaction it is missing, so no binary log file or position is needed. Open the MySQL shell on db-replica:

sudo mysql

Configure the connection and start the replication threads:

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

CHANGE REPLICATION SOURCE TO and START REPLICA are the MySQL 8.0.23+ names for the older CHANGE MASTER TO and START SLAVE, which still work but are deprecated. Ubuntu 24.04 ships MySQL 8.0.4x, so the new syntax is available.

Now make the replica fully read-only, so that not even administrative accounts can write to it by accident. SET PERSIST also saves the value across restarts:

SET PERSIST super_read_only = ON;

The replication threads are not affected by super_read_only.

Step 7 - Verifying replication

Check the replica's status, still in the MySQL shell on db-replica:

SHOW REPLICA STATUS\G

The important fields are:

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 10.0.0.10
                  Source_User: replicator
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
                Last_IO_Error:
               Last_SQL_Error:
        Seconds_Behind_Source: 0
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1543-1550
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-1550
                Auto_Position: 1
           Source_SSL_Allowed: Yes

• Replica_IO_Running: Yes means the replica is connected to the source and receiving events.
• Replica_SQL_Running: Yes means it is applying them.
• Seconds_Behind_Source shows the replication lag.
• Last_IO_Error and Last_SQL_Error explain what went wrong when either thread says No.

Now test it with real data. On db-source, create a table and insert a row:

sudo mysql appdb -e "CREATE TABLE replication_test (id INT PRIMARY KEY, note VARCHAR(50)); INSERT INTO replication_test VALUES (1, 'hello from source');"

On db-replica, read it back:

sudo mysql appdb -e "SELECT * FROM replication_test;"

+----+-------------------+
| id | note              |
+----+-------------------+
|  1 | hello from source |
+----+-------------------+

Confirm the replica rejects writes. On db-replica, run:

sudo mysql appdb -e "INSERT INTO replication_test VALUES (2, 'should fail');"

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

Finally, clean up on db-source. The DROP TABLE replicates too:

sudo mysql appdb -e "DROP TABLE replication_test;"

Step 8 - Monitoring replication

Replication can stop without the application noticing, and then the replica silently serves old data. Check the two thread states and the lag regularly. From the shell on the replica:

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

For per-worker detail, including the last error with a timestamp, query Performance Schema:

sudo mysql -e "SELECT WORKER_ID, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE FROM performance_schema.replication_applier_status_by_worker;"

Feed these values into your monitoring system and alert when either thread is not Yes or when Seconds_Behind_Source stays above a threshold, for example 60 seconds. Tools such as Prometheus with mysqld_exporter expose them as metrics.

To add more replicas, repeat Steps 3 to 7 on each new server with a unique server_id, a UFW rule on the source for its IP and a replication user for its address (or a single user for the whole private subnet, such as 'replicator'@'10.0.0.%').

Troubleshooting

• Replica_IO_Running: Connecting with error connecting to source: the replica cannot reach port 3306 on the source. Check bind-address on the source, the UFW rule and that you can connect by hand with mysql -h 10.0.0.10 -u replicator -p --ssl-mode=REQUIRED from the replica.
• Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection: the replica is connecting without TLS. Make sure you used SOURCE_SSL = 1, or add GET_SOURCE_PUBLIC_KEY = 1 to CHANGE REPLICATION SOURCE TO.
• Last_IO_Error: ... Got fatal error 1236 from source ... purged binary logs containing GTIDs that the replica requires: the source already deleted binary logs the replica still needs, typically because the replica was offline longer than binlog_expire_logs_seconds. Rebuild the replica from a fresh dump (Steps 3 to 6).
• Last_SQL_Error with error 1062 (duplicate key) or 1032 (row not found): the replica's data diverged from the source, usually because something wrote to the replica directly. Rebuilding the replica from a fresh dump is the safe fix; skipping transactions hides the inconsistency instead of fixing it.
• ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed during the import: the replica already had GTIDs. Run RESET MASTER; on the replica and import again.

Conclusion

You now have a MySQL 8.0 replica that receives every change from the source over an encrypted connection, tracks its position with GTIDs and refuses direct writes. From here, point read-only queries or backup jobs at the replica, add alerts on the replication status, and document how you would promote the replica (stop replication, disable super_read_only, point the application at it) so failover is a procedure rather than an improvisation.