How to Configure MySQL Gtid-based Replication for Automatic Failover on Ubuntu

ByAkensai

How to Configure MySQL GTID-Based Replication for Automatic Failover on Ubuntu is one of the most valuable skills a database administrator can have. GTID (Global Transaction Identifier) replication makes failover predictable and reliable. Without it, promoting a replica to a primary server is a manual, error-prone process. With GTID replication, MySQL tracks every transaction with a unique identifier. This means replicas can automatically sync to the correct position after a failover event. In this tutorial, you will set up a primary and replica MySQL server on Ubuntu, enable GTID mode, and configure automatic failover using MySQL Shell and MySQL Router. By the end, you’ll have a production-ready replication setup that handles server failures gracefully. This guide targets Ubuntu 20.04 or 22.04 and MySQL 8.0.

Prerequisites for Configuring MySQL GTID-Based Replication

Before you start, make sure you have the following in place.

Required setup:

  • Two Ubuntu servers (20.04 or 22.04) , one primary, one replica
  • MySQL 8.0 installed on both servers
  • Root or sudo access on both machines
  • Both servers can communicate over the network on port 3306
  • Unique server IDs assigned to each MySQL instance

Assumed knowledge:

  • Basic Linux command-line usage
  • Familiarity with MySQL and SQL queries
  • Understanding of what database replication does

Estimated time: 45–60 minutes

You should also review the official MySQL GTID replication documentation before proceeding. It covers edge cases that may apply to your specific environment.

Step-by-Step Guide to MySQL GTID Replication and Automatic Failover

Related tutorial: How to Install and Configure Docker on Ubuntu Server 24.04 Lts

Follow these steps carefully on both servers unless a step specifies otherwise.

Step 1: Update both servers and verify MySQL version

Run this on both the primary and replica:

sudo apt update && sudo apt upgrade -y
mysql --version

You need MySQL 8.0.x. If MySQL isn’t installed, run:

sudo apt install mysql-server -y
sudo systemctl enable mysql
sudo systemctl start mysql

Step 2: Configure the primary server’s MySQL settings

Open the MySQL configuration file on your primary server:

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

Add or update these lines under the [mysqld] section:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_format = ROW

Save the file and restart MySQL:

sudo systemctl restart mysql

Step 3: Configure the replica server’s MySQL settings

Open the same config file on your replica server:

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

Use a different server-id value:

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_format = ROW
read_only = ON

Restart MySQL on the replica:

sudo systemctl restart mysql

Step 4: Create a replication user on the primary

Log into MySQL on your primary server:

sudo mysql -u root -p

Create a dedicated replication user:

CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON . TO 'replicator'@'%';
FLUSH PRIVILEGES;

Don’t use your root account for replication. A dedicated user is safer and easier to manage.

Step 5: Connect the replica to the primary

Log into MySQL on your replica server. Run the following command, replacing the IP and credentials with your own:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='replicator',
  SOURCE_PASSWORD='StrongPassword123!',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

The SOURCE_AUTO_POSITION=1 flag is what enables GTID-based positioning. It tells the replica to use GTIDs instead of binary log file positions.

Step 6: Verify replication is working

Still on the replica, run:

SHOW REPLICA STATUSG

Look for these two lines in the output:

Replica_IO_Running: Yes
Replica_SQL_Running: Yes

Both must say Yes. If either says No or Connecting, check your firewall rules and credentials.

Step 7: Install MySQL Shell for automatic failover

MySQL Shell handles InnoDB Cluster management. Install it on both servers:

sudo apt install mysql-shell -y

Then launch MySQL Shell on the primary:

mysqlsh root@localhost

Create an InnoDB Cluster:

var cluster = dba.createCluster('MyCluster');
cluster.addInstance('[email protected]:3306');

MySQL Shell will configure Group Replication automatically. This is the layer that enables true automatic failover.

Step 8: Install and configure MySQL Router

MySQL Router sits between your application and the cluster. It routes traffic to the current primary automatically. Install it on your application server:

sudo apt install mysql-router -y
mysqlrouter --bootstrap [email protected]:3306 --directory /etc/mysqlrouter
sudo systemctl start mysqlrouter

Your application now connects to MySQL Router on port 6446. Router handles failover redirection without any code changes. You can read more about this in the MySQL Router 8.0 documentation.

Troubleshooting Common MySQL Replication Issues

Even careful setups run into problems. Here are the most common ones.

Problem: Replica_IO_Running shows “Connecting”

This usually means a firewall is blocking port 3306. Check with:

sudo ufw status
sudo ufw allow from 192.168.1.101 to any port 3306

Also verify the primary’s MySQL bind address. It should not be set to 127.0.0.1. Change it to 0.0.0.0 or the server’s actual IP in mysqld.cnf.

Problem: GTID errors after a failover

If you see errors like Error 1236, the GTID sets may be out of sync. Run this on the replica to reset:

STOP REPLICA;
RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1;
START REPLICA;

Problem: Cluster member shows “RECOVERING” state

This happens when a node rejoins after a crash. Give it a few minutes. If it stays stuck, rejoin it manually:

cluster.rejoinInstance('[email protected]:3306');

Warning: Never run SET GLOBAL gtid_purged on a replica that’s already part of an active cluster. It will break GTID consistency and require a full resync.

Conclusion: Your MySQL GTID Replication Setup Is Ready

You’ve now learned how to configure MySQL GTID-based replication for automatic failover on Ubuntu from scratch. Your setup includes a primary server, a replica, InnoDB Cluster management through MySQL Shell, and MySQL Router for transparent failover. When the primary server goes down, the cluster promotes a replica automatically. Your application reconnects through MySQL Router without manual intervention.

From here, consider adding a third server to your cluster for better fault tolerance. You should also set up monitoring with tools like Percona Monitoring and Management to track replication lag and cluster health. Understanding how to configure MySQL GTID-based replication for automatic failover on Ubuntu gives your infrastructure a real edge in uptime and reliability.

Similar Posts