MySQL GTIDs Replication Set Up: 8 Easy Steps

MySQL is one of the widely used Database Management systems that help organizations to store their business data. To avoid any data loss during system failure, companies tend to use MySQL Replication for protecting their data. Are you trying to set up MySQL replication for your database? If that’s the case, you have landed on the right page.

A great way to set up replication for your database is by using MySQL GTIDs. The present article aims at providing a step-by-step guide to help you set up MySQL Replication using GTIDs and help you replicate your MySQL data with ease.

In this article, you will learn about MySQL GTIDs and understand why you should use MySQL GTIDs Replication. Also, you will go through the procedure of how to use MySQL GTIDs for Replication purposes.

Prerequisites

• A MySQL Database.
• A brief idea of Data Replication.
• A brief knowledge of MySQL commands.

What is MySQL?

MySQL is an Open-source Database Management system that is distributed, supported and developed by Oracle Incorporation (INC). MySQL is a relational database that stores the data in the form of tables and views. It supports various database objects such as tables, stored procedures, functions, triggers, views, indexes, and even cursors.

MySQL database server runs comfortably on any laptop or desktop even with various web applications or servers installed on the system. MySQL database server is designed to support large databases which may contain data of many organizations. MySQL database servers support a wide range of functions and multiple web API’s.

It performs exceptionally well & securely accesses various databases on the internet. It ensures connectivity with servers and devices at all times.

What is MySQL Data Replication?

Data Replication is the process of copying data from one server to the another server or cloning a server to make sure that the date remains safe of the primary server fails and ensure data is available to keep business activities running smoothly. This also enables users to perform MySQL Master Slave Replication process.

MySQL Data Replication process involve many stages and consumes much time. It is a one way process where, the data is copied from the primary or the master server and then copied to one or more than one slave servers.

Due to any mishappenig, the master server goes down, then one of the slave server becomes active to provide data and prevents any disruption. You can avoid this tedious Data Replication process with Hevo’s No Code Data Pipeline and automate the Data Replication of your MySQL server.

What are MySQL GTIDs?

MySQL GTIDs stands for global transaction identifier (GTID) which uniquely identifies a transaction committed on the server of origin (master). A unique MySQL GTIDs is created when any transaction occurs. MySQL GTIDs are displayed as a pair of coordinates, separated by a colon character (:), as shown here:

GTID = source_id:transaction_id

The MySQL GTIDs are not just unique to the server on which it originates, but also across the servers in any given replication setup. In other words, each transaction is mapped to MySQL GTIDs.

The source_id is generally the server_uuid of the server and the transaction_id is the sequence number on which the transaction is committed. One example of MySQL GTIDs is given below.

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

MySQL GTIDs Sets

MySQL GTIDs sets are a set of global transaction identifiers that are used in MySQL Server in different ways. GTID is represented as shown below:

gtid_set:
    uuid_set [, uuid_set] ...
    | ''
uuid_set:
    uuid:interval[:interval]...
uuid:
    hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh
h:
    [0-9|A-F]
interval:
    n[-n]
    (n >= 1)

The gtid_executed and gtid_purged system variables are represented as GTID sets. You can always find the source of the transaction on any replica using the binary log.

Understanding MySQL GTIDs Replication & Why You Should Use It

MySQL, starting with version 5.6 and above, allows users to make use of MySQL GTIDs to set up replication for their MySQL database. Each GTID acts as a unique Id that MySQL generates and associates with every transaction that takes place within your database.

It makes use of a unique transaction number and the master server’s UUID to identify each transaction. It thereby ensures a simple failover and recovery process by keeping track of each replication-based transaction between the master and slave server.

When the replication takes place, the slave makes use of the same MySQL GTIDs, irrespective of whether it acts as a master for any other nodes or not. With each transaction replication, the same MySQL GTIDs and transaction numbers also come along from the master and the slave will write these to the binlog if it’s configured to write its data events.

To ensure a smooth, consistent, and fault-tolerant replication, the slave will then inform the master of the MySQL GTIDs that were a part of the execution, which helps the master node identify if any transaction did not take place. The master node then informs the slave to carry out the left-over transactions and thereby ensures that data replication takes place accurately.

This is how MySQL GTIDs Replication provides an efficient way to start replicating your MySQL data.

Using MySQL GTIDs for Replication

You can set up MySQL GTIDs Replication using the following steps to replicate your MySQL data:

• Step 1: Synchronizing Master & Slave Servers
• Step 2: Stopping Master & Slave Servers
• Step 3: Configuring the Master Server
• Step 4: Creating a Replication User
• Step 5: Using mysqldump to Back up Master Server
• Step 6: Configuring the Master Server
• Step 7: Executing the Change Master
• Step 8: Verifying the Replication Process

Step 1: Synchronizing Master & Slave Servers

Synchronize both servers by setting them to read-only if the replication is running already by using the following command:

mysql> SET @@GLOBAL.read_only = ON;

You must allow all ongoing transactions to commit or rollback first. Then, you may allow the slave to catch up with the master.

Step 2: Stopping Master & Slave Server

Both the Master and Slave Servers should be stopped by using the following command: 

sudo service mysqld stop

Step 3: Configuring the Master Server

The Master server needs to be started with GTID mode enabled by setting the gtid_mode variable to ON. It is also essential that the enforce_gtid_consistency variable is enabled to make sure that only the statements which are safe for MySQL GTIDs Replication are logged. These changes can be made by accessing the given file:

vi /etc/my.cnf

The following parameters should be added under the [mysqld] section of my.cnf file

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

The next step is to start the mysqld server process,  using the following command:

sudo service mysqld start

Step 4: Creating a Replication User

You must now create a replication user for the slave server through the given command:

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';
Grant replication slave on *.* to 'repl_user'@'%';

The user must be created with a required user name, password, and permissions.

Use the following command to get the master binary log coordinates:

mysql> show master status ;

mysql> show global variables like 'gtid_executed';

5: Using mysqldump to Back up Master Server

In this example, we will be using mysqldump to back up the Master server. You can do this by using the following command:

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events -hex-blob --host=54.89.xx.xx --port=3306 --user=root  --password=XXXXXXXX > mysqlbackup_dump.sql
# head -n30 mysqlbackup_dump.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: 54.89.242.211    Database:
-- ------------------------------------------------------
-- Server version       5.7.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--SET @@GLOBAL.GTID_PURGED='82e9927e-7645-11e9-8e8d-0e651b9198f4:1-5';

Instead of using manual method of using mysqldump for backup, you can also automate this process to backup the Master server using automated Data Pipeline tools such as Hevo, that will also save your time and efforts.

Step 6: Configuring the Master Server

Once the Master has been configured, the next step is to configure the Slave server: The following parameters must be added under the [mysqld] section of my.cnf file:

server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
relay-log = relay-log-server
read-only = ON
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

Additionally, you can also start slaves with the –skip-slave-start option before configuring the slave settings.

Now you may start the mysqld server process using the given command:

sudo service mysqld start

Next step is to load the mysqldump in slave server:

mysql> show global variables like 'gtid_executed';

mysql> source mysqlbackup_dump.sql ;
mysql> show global variables like 'gtid_executed';

Step 7: Executing the Change Master

The slave should be configured to use the master with GTID based transactions as the source for data replication and to use GTID-based auto-positioning rather than file-based positioning.

CHANGE MASTER TO
MASTER_HOST = '54.89.xx.xx',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXX',
MASTER_AUTO_POSITION = 1;

It is now time to begin the slave process:

start slave;

Step 8: Verifying the Replication Process

The final step is the check your replication process using the given command:

mysql> show slave status G

You must also disable the read-only mode if it is enabled:

mysql> SET @@GLOBAL.read_only = OFF;

This is how you can set up MySQL GTIDs Replication with ease.

Conclusion

In this blog post, you have learned how to set up MySQL GTIDs Replication using GTID. However, if you are tired of making compromises and want a simple, easy-to-use, and completely managed MySQL database replication experience, you should have a look at what Hevo offers. However, when it comes to working with replication, you need to be an expert at MySQL to set up servers from scratch and manually configure several details. Moreover, most of the time, the data is not available in the right format and you will need data engineering and MySQL administration skills to transform the data.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 150+ data sources (including 60+ free sources) and can seamlessly perform MySQL Replication in real-time. Furthermore, Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your MySQL data. Using Hevo will make your life easier and make Data Replication hassle-free.

Want to take Hevo for a spin? Sign up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

FAQs