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.
Table of Contents
Prerequisites
- A MySQL Database.
- A brief idea of Data Replication.
- A brief knowledge of MySQL commands.
What is MySQL?
Image Source
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.
For further information, you can check the official MySQL website here.
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.
Hevo Data, an Automated No Code Data Pipeline, helps you replicate data from 150+ Data Sources (including 40+ free sources) such as MySQL to Data Warehouses, Business Intelligence Tools, or any other destination of your choice in a completely hassle-free manner. Our end-to-end Data Management supports Data Ingestion from MySQL servers via Binary logs (BinLog). Hevo utilizes the Binary logs to perform your MySQL Data Replication in real-time.
Get Started with Hevo for Free
Hevo’s fault-tolerant Data Pipeline offers you a secure option to unify data from multiple data sources and store it in a Data Warehouse of your choice. Hevo is fully managed and completely automates the process of not only replicating data from MySQL but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.
Use Hevo’s No-Code Data Pipeline to replicate data from MySQL to a destination of your choice in a seamless and automated way. Try our 14-day full feature access free trial!
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
Image Source
You can set up MySQL GTIDs Replication using the following steps to replicate your MySQL data:
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 ;
Image Source: Self
mysql> show global variables like 'gtid_executed';
Image Source: Self
Performing the MySQL Replication process manually consumes time and generates errors. To keep your application live all the time, the organization can use Hevo, an automated No Code Data Pipelining solution that not only helps the replicate MySQL server but also automate their ETL process without writing a single line of code.
Hevo’s No-Code Data Pipeline seamlessly connects with your MySQL server or 150+ Data Sources (including 40+ free data sources) and uses its blazing-fast Data Pipelines to extract, transform and load the data to other MySQL servers or destinations of your choice such as Data Warehouses, Databases in a matter of minutes.
Check out how Hevo can make your life easier:
- Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the format of incoming MySQL data and replicates it to the destination schema. You can also choose between Full & Incremental Mappings to suit your Data Replication requirements.
- One-Stop Solution: Hevo offers a robust all-in-one solution for replicating data from Amazon Aurora MySQL Amazon RDS MySQL, Azure MySQL, and many more. This way, you don’t have to seek multiple solutions for your MySQL Data and can rely on Hevo’s Pipeline to ensure a smooth Data Replication from all of your MySQL sources. To learn more, check out Hevo’s documentation for MySQL Replication.
- Transformations: Hevo provides preload transformations to make your incoming data fit for the chosen destination. You can also use drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few.
- Live Support: The Hevo team is available round the clock to extend exceptional support for your convenience through chat, email, and support calls.
Want to take Hevo for a spin? Sign up here for a 14-day free trial and experience the feature-rich Hevo.
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';
Image Source: Self
mysql> source mysqlbackup_dump.sql ;
mysql> show global variables like 'gtid_executed';
Image Source: Self
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 40+ 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.
Visit our Website to Explore Hevo
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.
Have any further queries? Get in touch with us in the comments section below.
With an analytical perspective and a problem-solving approach, Shruti has extensive experience in solving business problems of data teams by delivering thoroughly researched content pivotal for data practitioners.