Creating Replication between AWS Aurora MySQL and Self-Hosted MySQL

In today’s digital age, having a reliable and robust database is crucial for any business. However, even the most robust databases can have a single point of failure (SPOF) that can disrupt operations and cause significant downtime. One way to mitigate this risk is by creating a replica of your RDS MySQL Aurora on an EC2 instance. In this blog post, we will learn how to create an RDS MySQL Aurora replica on an EC2 instance, and how this will help you to achieve redundancy and eliminate SPOF.

Prerequisite

Overview

  1. Enable bin log in master
  2. Create a user for replication in master
  3. Create a snapshot and restore
  4. Install and configure MySQL server in EC2 Instance
  5. Install mydumper tool in EC2 Instance
  6. Dump schema from the restored instance
  7. Dump data from the restored instance
  8. Dump users from the restored instance
  9. Import Users to EC2 MySQL Instance
  10. Import Schema to EC2 MySQL Instance
  11. Import Data to EC2 MySQL Instance
  12. Start Replication

Steps:

Step 1: Enable bin log in master

Go to the Cluster-level parameter group and make sure binlog_format is set to ROW. If already set skip it, if not update it. This change requires a restart.

To Verify that binlog_format is enabled. Run the below query in the database.

SHOW MASTER LOGS;

it should show you the log name and log size. Otherwise, you will get an error: ERROR 1381 (HY000): You are not using binary logging

Optional:

We also need to ensure a proper binary log retention period. For example, if we expect the initial data export/import to take more then one day, we can set the retention period to something like three days to be on the safe side. This will help ensure we can roll forward the restored data. 

use the below command to check the current retention period.

CALL mysql.rds_show_configuration;

use the below command to update the retention period to 3 days.

call mysql.rds_set_configuration('binlog retention hours', 72);

Note: Don’t forget to change the retention period back to 24 hours. Longer the retention period more space is required.

Step 2: Create a user for replication in master

Create a dedicated user for replication with required permissions only.

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
EXIT;

Step 3: Create a snapshot and restore

The snapshot is the only way to get a consistent backup with the specific binary log position. As Aurora does not allow “super” privileges and we cannot take READ LOCK.

a. Take snapshot

Log into AWS console go to RDS→ databases→ search and select database to take snapshot → select Writer instance → click Action and select Take Snapshot.

b. Restore snapshot

Log into AWS console go to RDS → snapshots → select your snapshot → click restore.

Note: Keep cluster parameter group, VPC and security group same as master.

Next, capture the binary log position for replication. This is done by inspecting the Logs & events section in the console. you’ll see something like this.

Binlog position from crash recovery is mysql-bin-changelog.014824 107977934

take note of the bin file name and log position we’ll need this later.

Step 4: Install and configure MySQL server in EC2 Instance

Launch an EC2 instance with the same VPC as your Aurora Instance. you can select the instance type and storage as per your need.

Run the below commands to install MySQL.

sudo apt-get update
sudo apt-get install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql
sudo systemctl status mysql

You will need to add the following lines to the configuration file to configure the replica. The configuration file is typically located in the /etc/mysql/my.cnf

bind-address=0.0.0.0
server-id=2
binlog_format=ROW
log-bin=mysql-bin
binlog_checksum=NONE
log_bin_trust_function_creators=1
sql_mode="STRICT_TRANS_TABLES"
innodb_flush_method=O_DIRECT

save the file and restart the MySQL server.

sudo systemctl restart mysql
sudo systemctl status mysql

In case of an error check the logs. Generally, logs are located at /var/log/mysql/

Step 5: Install mydumper tool in Metal Instance

I’m using mydumper for the faster dump and restore you can use mysqldump as well.

sudo apt-get install libatomic1
wget https://github.com/mydumper/mydumper/releases/download/v0.12.3-3/mydumper_0.12.3-3.focal_amd64.deb
dpkg -i mydumper_0.12.3-3.focal_amd64.deb

Step 6: Dump schema from the restored instance

I’m dumping data and schema separately so that I have the flexibility to change anything in the schema.

log in to EC2 Instance and create a configuration file with the login details.

tee aurora.cnf <<EOF
[client]
user=<username>
password=<password>
host=<your-restored-database.rds.amazonaws.com>
EOF
chmod 400 aurora.cnf

Don’t forget the change the placeholder with the correct values.

Run the below command to take a dump.

mydumper --no-data \
    --verbose 3 \
    --no-locks \
    --outputdir ./schema \
    --logfile mydumper-schema.log \
    --regex '^(?!(mysql|test|performance_schema|information_schema|sys))' \
    --defaults-file aurora.cnf

Above command will take schema dump of all the database excluding mysql,test,performance_schema,information_schema,sys.

Step 7: Dump data from the restored instance

Run the below command to take the dump of data.

mydumper --threads 2 \
    --set-names="utf8"
    --no-schemas \
    --compress \
    --rows="10000000" \
    --verbose 3 \
    --long-query-guard 999999 \
    --no-locks \
    --outputdir ./data \
    --logfile mydumper-data.log \
    --regex '^(?!(mysql|test|performance_schema|information_schema|sys))' \
    --defaults-file aurora.cnf

If the data size is big then I suggest running the data dump command in the screen, by using the screen you can run the dump data command in the background.

screen manual: https://www.gnu.org/software/screen/manual/screen.html#toc-Overview-1)

you can increase the number of threads as per your instance number of CPU.

Step 8: Dump users from the restored instance

To dump users I’m using percona-toolkit.

To install, run the below command.

sudo apt install percona-toolkit -y

To dump users run below command.

pt-show-grants --ask-pass --host your-restored-database.rds.amazonaws.com --user <username> > users.sql

Step 9: Import Users to EC2 MySQL Instance

To import users, run the below command.

mysql -u root -p < users.sql

Step 10: Import Schema to EC2 MySQL Instance

To Import schema, run the below command.

myloader \
  --directory ./schema \
  --verbose 3 \
  --logfile ./myloader-schema.log \
  --host localhost \
  --user root \
  --ask-password

Step 11: Import Data to EC2 MySQL Instance

Before importing the data, I would again recommend running this inside a screen session. As this is going to take time depending on the size of the database.

myloader \
  --threads 8 \
  --directory ./data \
  --queries-per-transaction 10000 \
  --verbose 3 \
  --logfile ./myloader-data.log \
  --host localhost \
  --user root \
  --ask-password

Step 12: Start Replication

Once the above steps are executed successfully we can start the replication.

First, log in to the EC2 MySQL server using the below command.

mysql -u root -p

To start replication, run the below commands.

CHANGE MASTER TO MASTER_HOST='<your-rds-hostname>', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE = '<mysql-bin-changelog-file-name>', MASTER_LOG_POS =<position>;
start slave;

Note: you will get the mysql-bin-changelog-file-name and position from step 3.

To check slave status, run the below command.

show slave status \G

Now you have successfully set up replication between MySQL Aurora and EC2 hosted MySQL instance.

Let me know your thoughts in the comment section.

Bonus

1. If you face the error “Error ‘Unknown or incorrect time zone: ‘UTC” on query”. To resolve this issue run the below command.

first, stop the slave using the below query.

mysql -u root -p
stop slave;
exit;

second, loads the time zone tables in the mysql database

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

Above command may show some Warnings, you can ignore them.

final, start slave.

mysql -u root -p
start slave;
show slave status \G
exit;

2. There is a collate-related issue With Aurora 5.7 collate utf8mb4_0900_ai_ci is not available in MySQL 5.7, use utf8mb4_general_ci instead.

MySql 5.7 documentation link: https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

To resolve this issue. Replace replace utf8mb4_0900_ai_ci in dumped schema file with utf8mb4_general_ci.

you can use the below command to find and replace collate.

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' *.sql
Views: 384

Tagged
0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments