Data Migration and Replication from AWS RDS MySQL to KakaoCloud MySQL

This document describes the process of data migration and replication between AWS RDS MySQL and KakaoCloud MySQL.

Basic Information

• Estimated Time Required: 60 minutes
• Recommended Operating Systems: Windows, MacOS
• Region: kr-central-2

About scenario

In this scenario, we will explain how to migrate data from AWS RDS MySQL to KakaoCloud MySQL and how to replicate data from KakaoCloud MySQL to AWS RDS MySQL. The main topics covered are:

• Data migration using MySQL Workbench and MySQL Dump
  
• Data replication using MySQL Replication

Prerequisites

1. Network environment setup

Before setting up data migration and replication for MySQL on KakaoCloud, you need to configure the network environment. Please refer to the Network Setup Using NAT Instances Across Multiple Availability Zones document to ensure that resources in the private subnet can communicate externally.

2. Security group configuration

Setting up a security group is necessary to block unauthorized external access while allowing required traffic to secure data integrity and facilitate smooth network communication.

Security Group: tutorial-bastion-sg

1. Navigate to KakaoCloud Console > VPC > Security Groups. Refer to the table below to create a security group.

   Name	Description (Optional)
   tutorial-bastion-sg	Security policy for the Bastion host

2. Click the [+ Add] button at the bottom and set the inbound rules as follows, then click the [Apply] button.
   

   Check my public IP

   Click the button below to check your current public IP.
   

   Inbound Policy to Add	Item	Value
   bastion inbound policy 1	Protocol	TCP
   	Source IP	{USER_PUBLIC_IP}/32
   	Port Number	10000-10010
   	Policy Description (Optional)	bastion inbound policy 1
   bastion inbound policy 2	Protocol	TCP
   	Source IP	{USER_PUBLIC_IP}/32
   	Port Number	81
   	Policy Description (Optional)	bastion inbound policy 2
   bastion inbound policy 3	Protocol	TCP
   	Source IP	{USER_PUBLIC_IP}/32
   	Port Number	22
   	Policy Description (Optional)	bastion inbound policy 3

3. Bastion host configuration

The Bastion host serves as a VM for data migration and MySQL access in the cloud environment, acting as a secure gateway for remote access to cloud instances.

1. Navigate to KakaoCloud Console > Beyond Compute Service > Virtual Machine.
2. Refer to the table below for items and values to create a VM instance that will act as the Bastion host.

Category	Item	Setting/Input Value	Note
Basic Information	Name	tutorial-bastion
	Count	1
Image		Ubuntu 20.04
Instance Type		m2a.large
Volume	Root Volume	20
Key Pair		{USER_KEYPAIR}	⚠️ The key pair must be securely stored upon creation. Lost keys cannot be recovered and require reissuance.
Network	VPC	tutorial
	Subnet	main (10.0.0.0/20)
	Security Group	tutorial-bastion-sg

4. MySQL database creation

Create a MySQL instance for data migration. KakaoCloud's MySQL is a fully managed database service, allowing you to provision MySQL instances easily through the console without complex installations. Additionally, it can be used safely in a logically isolated network.

1. Navigate to KakaoCloud Console > Data Store > MySQL.

2. In the Instance Group tab, click the [Create Instance Group] button and refer to the instructions to create an instance group.

   Category	Item	Setting/Input Value	Note
   Basic Settings	Instance Group Name	tutorial-mysql
   	Description	Optional
   MySQL Settings	Engine Version	Use specified value
   	MySQL Username	admin
   	MySQL Password	root1234
   Instance Availability/Count		Single (Primary Instance)
   Instance Type		m2a.large
   Storage Type/Size		100
   Log Storage Type/Size		100
   Network Settings	VPC	tutorial
   	Subnet	{VPC_ID}_sn_5 (10.0.64.0/20)	⚠️ Use the subnet created in the NAT instance tutorial.
   Automatic Backup	Automatic Backup Option	Not used

MySQL data migration

MySQL data migration can be carried out using MySQL Dump or MySQL Workbench.

caution

Data migration occurs over the internet, so internet connectivity must be available between database instances.

1. Using MySQL workbench

MySQL Workbench is an open-source tool that provides SQL development and management within a single integrated development environment for visual database design. This tutorial is based on a Windows instance.

Step 1. Connect to the VM for data migration and MySQL access

1. Refer to the Connect to Windows Instance to RDP into the VM instance and install MySQL Workbench.

2. Download and install from the MySQL Workbench Official Download Page.

Step 2. Proceed with data migration

1. Click MySQL Workbench > Database > Migration Wizard to prepare for data migration.

   Item	Setting/Input Value	Note
   Source Selection	Enter access information for AWS Database Server	Test Connection for access testing
   Target Selection	Enter access information for KakaoCloud Database Server	Test Connection for access testing
   Schemas Selection	Select schemas for migration	Choose Library Schemas for this tutorial
   Source Objects	Select target tables	Procedures or Functions can be selected
   Migration	Generate scripts for the migration target
   Target Creation Options	Create schemas in the target database and generate SQL script files
   Create Schemas	Start creating tables and monitoring logs
   Bulk Data Transfer	Check final data migration completion logs

info

The above items are mandatory settings; other items can be set to default values.

Step 3. Verify migration data in KakaoCloud MySQL

1. To connect to the MySQL instance group from the Bastion instance configured in the preparation stage, execute the command to connect to the MySQL instance from the virtual machine instance using the MySQL Client.

   # MySQL instance connection command
   mysql --user={USER} --password={USER_PASSWORD} --host {ENDPOINT} --port {PORT}

   Item	Description
   user	{USER}: Database username - The name of the DB admin account that connects to the MySQL instance.
   password	{USER_PASSWORD}: Initial password for the admin account connecting to the MySQL instance - The password entered during MySQL creation.
   host	{ENDPOINT}: MySQL endpoint information - The endpoint associated with the MySQL instance.
   port	{PORT}: The port number set when creating the instance group.

2. Check the data integrity of the book table in the library schema in KakaoCloud MySQL.

# MySQL instance command
mysql> use library;
Database changed

mysql> select * from book;

2. Utilizing MySQL dump

MySQL Dump is a utility and command used to back up or migrate the data of a database.

Step 1. Dumping AWS RDS data

Use the mysqldump command from the KakaoCloud VM to perform a dump on the AWS RDS MySQL Replica DB. Stop the Replica before executing the dump.

# 1. Connect to KakaoCloud VM
Access the Bastion Server instance.

# 2. Connect to the AWS Read-Only Replica
shell> mysql --user=admin --password=admin1234 --host {aws-rds-read-replica}.ap-northeast-2.rds.amazonaws.com --port 3306

# 3. Stop the replication on the Read-Only Replica
mysql> CALL mysql.rds_stop_replication;
+-------------------------------+
| Message                       |
+-------------------------------+
| Slave is now down or disabled |
+-------------------------------+
1 row in set (3.01 sec)

Query OK, 0 rows affected (3.01 sec)

mysql> exit
Bye

# 4. Dump the Read-Only Replica
shell> mysqldump -h {aws-read-replica}.ap-northeast-2.rds.amazonaws.com \
    -u admin \
    -p \
    --port=3306 \
    --routines \
    --triggers \
    --databases {dump_DATABASE} > aws_source.sql
Enter password: {Enter admin password}
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.
mysqldump: Got error: 1049: Unknown database 'erin-database' when selecting the database

# 5. Confirm the dump file creation despite warning messages
shell> ls
aws_source.sql

Step 2. Importing data into KakaoCloud MySQL and setting up replication

Use the dump file created in the KakaoCloud VM to import data into KakaoCloud MySQL.

# Connect to KakaoCloud MySQL
shell> mysql --user={USER} --password={USER_PASSWORD} --host {KAKAOCLOUD-MySQL-ENDPOINT} --port 3306

# Grant permissions
mysql> grant SYSTEM_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN on *.* to 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

# Import the dump file into KakaoCloud MySQL
shell> mysql --user=admin --password=admin1234 --host {kc-mysql-endpoint} --port 3306 < aws_source.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
# Check if the imported data from the dump file is present in KakaoCloud MySQL

MySQL data replication

This guide introduces how to migrate and replicate data from KakaoCloud MySQL to AWS RDS MySQL.

Step 1. Port forwarding

To set up KakaoCloud MySQL replication (Chained Replication) on AWS RDS Replica-2, you need to configure a port forwarding environment using a VM for data migration.

Pre-checks before port forwarding

• External access port of KakaoCloud VM (e.g., 4306)
• Internal access port via NAT instance (e.g., 3306)
  • When connecting via port 4306 externally, it is forwarded to port 3306 through the NAT instance, and data is sent to KakaoCloud MySQL. You can set the external access port to any desired port.
• Public IP and Private IP of KakaoCloud VM

1. Check the Endpoint IP of the KakaoCloud MySQL VM.

   shell> nslookup {KC_MySQL_ENDPOINT}
   ...
   Address: {MySQL_PRIVATE_IP}
   
   

2. Access the KakaoCloud VM.

   shell> ssh -i {PATH_TO_PRIVATE_KEY} {USERNAME}@{INSTANCE_IP}

3. (For Ubuntu) Use the iptables command to install and start iptables-persistent.

   shell> sudo apt update
   shell> sudo apt install iptables-persistent
   shell> sudo systemctl start iptables
   shell> sudo systemctl status iptables
   # Confirm Active status

4. Configure the ssh environment.

   shell> sudo vi /etc/ssh/sshd_config
   AllowTcpForwarding yes
   GatewayPorts yes
   # Change the above two to yes and restart
   shell> sudo /etc/init.d/ssh restart
   Restarting ssh (via systemctl): ssh.service.

5. Set up port forwarding in the kernel.

   shell> sudo vi /etc/sysctl.conf
   net.ipv4.ip_forward=1
   shell> sudo sysctl -p /etc/sysctl.conf
   net.ipv4.ip_forward = 1 # Output value

6. Configure port forwarding in the NAT instance.

   # 4306 - This is the port accessed externally. Here, we are using 4306.
   shell> sudo iptables -t nat -A PREROUTING -p TCP --dport 4306 -j DNAT --to-destination {KC_MySQL_ENDPOINT_IP}:3306
   shell> sudo iptables -t nat -A POSTROUTING -p tcp -d {KC_MySQL_ENDPOINT_IP} --dport 3306 -j SNAT --to-source {KC_VM_PRIVATE_IP}
   

7. Verify the configured values.

   shell> sudo iptables -t nat -nL --line-numbers
   Chain PREROUTING (policy ACCEPT)
   num  target     prot opt source               destination         
   1    DNAT       tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:4306 to:{KC_MySQL_ENDPOINT_IP}:3306 <-- Check
   
   Chain INPUT (policy ACCEPT)
   num  target     prot opt source               destination         
   
   Chain OUTPUT (policy ACCEPT)
   num  target     prot opt source               destination         
   
   Chain POSTROUTING (policy ACCEPT)
   num  target     prot opt source               destination         
   1    SNAT       tcp  --  0.0.0.0/0            172.16.16.133        tcp dpt:3306 to:{KC_MySQL_ENDPOINT_IP} <-- Check

Step 2. KakaoCloud MySQL data dump and replication setup

1. Perform a database dump of KakaoCloud MySQL using the mysqldump command on the KakaoCloud VM.

   shell> mysqldump -h {KC_MySQL_ENDPOINT} \
       -u admin \
       -p \
       --port=3306 \
       --single-transaction \
       --routines \
       --triggers \
       --databases {dump_DATABASE} > kc_replica.sql
   shell> Enter password: {ADMIN PASSWORD}
   Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
   # Confirm that the file was created successfully despite the warning message

2. Use the generated dump file on the KakaoCloud VM to import data into AWS RDS.

   # Connect to AWS RDS
   shell> mysql --user={USER} --password={USER_PASSWORD} --host {AWS_RDS_REPLICA_2_ENDPOINT} --port 3306
   
   # Grant permissions
   mysql> grant SYSTEM_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN on *.* to 'admin'@'%';
   Query OK, 0 rows affected (0.01 sec)
   
   mysql>  FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> exit
   Bye
   
   # Import data
   mysql> mysql --user=admin --password=admin1234 --host {aws-rds-replica-2-endpoint} < kc_replica.sql
   mysql: [Warning] Using a password on the command line interface can be insecure.
   

3. Configure replication from KakaoCloud MySQL instance to AWS RDS.

     # Connect to AWS RDS
   shell> mysql --user=admin --password=admin1234 --host {aws-rds-replica-2-endpoint} --port 3306
   
   # Set up replication
   mysql> CALL mysql.rds_set_external_master_with_auto_position (
   '{KC_VM_PUBLIC_IP}'  # <- KC VM Public IP
   , '4306'          # <- External port for KC VM Public
   , 'admin'         # <- RDS administrator account
   , 'admin1234'     # <- RDS administrator password
   , 0               # <- Use of SSL (0 means not used)
   , 0               # <- Delay time
   );
   Query OK, 0 rows affected (0.04 sec)
   
   # Switch AWS RDS MySQL replication setting to auto GTID mode
   mysql> CALL mysql.rds_set_master_auto_position (1);
   +-----------------------------------------+
   | Message                                 |
   +-----------------------------------------+
   | Master Auto Position has been set to 1. |
   +-----------------------------------------+
   1 row in set (2.02 sec)
   
   +---------------------------+
   | Message                   |
   +---------------------------+
   | Slave is running normally |
   +---------------------------+
   1 row in set (2.02 sec)
   
   Query OK, 0 rows affected (2.02 sec)
   
   # Check replication status
   mysql> show replica status\G;
   *************************** 1. row ***************************
   ...
   ERROR: 
   No query specified
   

Step 3. Data replication test

Create a database and table on the KakaoCloud MySQL instance and input data, then check if the data has been replicated correctly to the AWS RDS Replica-2 instance.

Changing DB endpoint and promoting to primary

This section outlines how to promote the Amazon RDS Primary instance to KakaoCloud MySQL instance step by step.

Step 1. Check DB replication transaction status

1. Stop incoming transactions to the AWS RDS Primary DB service to minimize downtime and block transaction entries in the database. Execute the command to view the current process list.

   mysql> show processlist;
   +----------+----------+---------------------+--------+------------------+----------+---------------------------------------------------------------+------------------+
   | Id       | User     | Host                | db     | Command          | Time     | State                                                         | Info             |
   +----------+----------+---------------------+--------+------------------+----------+---------------------------------------------------------------+------------------+
   |        6 | repluser | 1.1.1.1:10000       | NULL   | Binlog Dump GTID | 59192344 | Master has sent all binlog to slave; waiting for more updates | NULL             |
   .....
   | 45942707 | root     | localhost           | NULL   | Query            |        0 | starting                                                      | show processlist |
   +----------+----------+---------------------+--------+------------------+----------+---------------------------------------------------------------+------------------+

2. Check the synchronization of KakaoCloud's Replica MySQL DB to ensure that Seconds_Behind_Source is 0, indicating no replication lag.

   mysql> show replica status\G;
   ...
   Seconds_Behind_Source : 0

3. Similarly, check the replication status on AWS RDS to ensure that Seconds_Behind_Source is 0.

   mysql> show replica status\G;
   ...
   Seconds_Behind_Source : 0

Step 2. Change MySQL DB endpoint for applications

1. Update the database endpoint for all applications connected to the existing AWS RDS Primary DB, directing them to KakaoCloud MySQL.
2. Simultaneously, disable the read_only setting on KakaoCloud MySQL DB by running the following command:

   mysql> set global read_only=0;
3. To block any transactions on the AWS RDS Primary DB, set the read_only parameter as follows:

   mysql> set global read_only=1;
4. Verify that connections to KakaoCloud MySQL are working correctly, and then perform a test transaction to confirm that the functions operate as expected.

Step 3. Detach AWS RDS primary and promote KakaoCloud MySQL instance to primary

1. Confirm once again that there are no transactions replicating from AWS RDS to KakaoCloud MySQL.

   mysql> show replica status\G;
   ...
   Seconds_Behind_Source : 0 # <- Confirm
2. To stop replication and detach the AWS RDS Primary, promoting KakaoCloud MySQL to Primary, perform the following steps:

   mysql> stop replica;
   mysql> reset replica all;
3. Verify that all changes have been applied correctly and conduct a final check on the service status.