Set up data replication between Amazon RDS for MySQL and MySQL on Amazon EC2 using GTID - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

Set up data replication between Amazon RDS for MySQL and MySQL on Amazon EC2 using GTID

Created by Rajesh Madiwale (AWS)

Environment: PoC or pilot

Technologies: Databases

Workload: Open-source

Summary

This pattern describes how to set up data replication on the Amazon Web Services (AWS) Cloud between an Amazon Relational Database Service (Amazon RDS) for MySQL DB instance and a MySQL database on an Amazon Elastic Compute Cloud (Amazon EC2) instance by using MySQL native global transaction identifier (GTID) replication.

With GTIDs, transactions are identified and tracked when they are committed on the originating server and applied by replicas. You don’t need to refer to log files when starting a new replica during failover.

Prerequisites and limitations

Prerequisites

  •  An active AWS account

  • An Amazon Linux instance deployed

Restrictions

  • This setup requires an internal team to run the read-only queries.

  • The source and target MySQL versions must be the same.

  • Replication is set up in the same AWS Region and virtual private cloud (VPC).

Product versions

  • Amazon RDS versions 5.7.23 and later, which are the versions that support GTID

Architecture

Source technology stack

  • Amazon RDS for MySQL

Target technology stack

  • MySQL on Amazon EC2

Target architecture

GTID replication from an RDS for MySQL database to MySQL on Amazon EC2 in the same private subnet.

Tools

AWS services

Other services

  • Global transaction identifiers (GTIDs) are unique identifiers generated for committed MySQL transactions.

  • mysqldump is a client utility for performing logical backups by producing SQL statements that can be run to reproduce the source database object definitions and table data.

  • mysql is the command-line client for MySQL.

Epics

TaskDescriptionSkills required

Create the RDS for MySQL instance.

To create the RDS for MySQL instance, follow the steps in the Amazon RDS documentation, using the parameter values that are covered in the next task.

DBA, DevOps engineer

Enable GTID-related settings in the DB parameter group.

Enable the following parameters in the Amazon RDS for MySQL DB parameter group.

Set enforce_gtid_consistency to on, and set gtid-mode to on.

 

DBA

Reboot the Amazon RDS for MySQL instance.

A reboot is required for the parameter changes to take effect.

DBA

Create a user and grant it replication permissions.

To install MySQL, use the following commands.

CREATE USER 'repl'@'%' IDENTIFIED BY 'xxxx';
GRANT REPLICATION slave ON *.* TO 'repl'@'%' ;
FLUSH PRIVILEGES;

 

DBA
TaskDescriptionSkills required

Install MySQL on Amazon Linux.

To install MySQL, use the following commands.

sudo yum update
sudo wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm 
sudo yum install mysql-community-server
sudo systemctl start mysqld
DBA

Log in to MySQL on the EC2 instance and create the database.

The database name should be the same as the database name in Amazon RDS for MySQL. In the following example, the database name is replication.

create database replication;
DBA

Edit the MySQL config file, and restart the database.

Edit the my.conf file that is located in /etc/ by adding the following parameters.

server-id=3
gtid_mode=ON
enforce_gtid_consistency=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin

Then restart the mysqld service.

systemctl mysqld restart
DBA
TaskDescriptionSkills required

Export the data dump from the Amazon RDS for MySQL database.

To export the dump from Amazon RDS for MySQL, use the following command.

mysqldump --single-transaction  -h mydb.xxxxxxx.amazonaws.com  -uadmin -p --databases replication > replication-db.sql
DBA

Restore the .sql dump file in the MySQL database on Amazon EC2.

To import the dump to the MySQL database on Amazon EC2, use the following command.

mysql -D replication -uroot -p  < replication-db.sql
DBA

Configure the MySQL database on Amazon EC2 as a replica.

To start the replication and to check the replication status, log in to the MySQL database on Amazon EC2, and use the following command.

CHANGE MASTER TO MASTER_HOST="mydb.xxxxxxx.amazonaws.com", MASTER_USER="repl", MASTER_PASSWORD="rep123", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G
DBA

Related resources