Skip to main content

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 OS: 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

Scenario Architecture

Before you start

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.

    NameDescription (Optional)
    tutorial-bastion-sgSecurity 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 AddItemValue
    bastion inbound policy 1ProtocolTCP
    Source IP{USER_PUBLIC_IP}/32
    Port Number10000-10010
    Policy Description (Optional)bastion inbound policy 1
    bastion inbound policy 2ProtocolTCP
    Source IP{USER_PUBLIC_IP}/32
    Port Number81
    Policy Description (Optional)bastion inbound policy 2
    bastion inbound policy 3ProtocolTCP
    Source IP{USER_PUBLIC_IP}/32
    Port Number22
    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.
CategoryItemSetting/Input ValueNote
Basic InformationNametutorial-bastion
Count1
ImageUbuntu 20.04
Instance Typem2a.large
VolumeRoot Volume20
Key Pair{USER_KEYPAIR}⚠️ The key pair must be securely stored upon creation.
Lost keys cannot be recovered and require reissuance.
NetworkVPCtutorial
Subnetmain (10.0.0.0/20)
Security Grouptutorial-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.

    CategoryItemSetting/Input ValueNote
    Basic SettingsInstance Group Nametutorial-mysql
    DescriptionOptional
    MySQL SettingsEngine VersionUse specified value
    MySQL Usernameadmin
    MySQL Passwordroot1234
    Instance Availability/CountSingle (Primary Instance)
    Instance Typem2a.large
    Storage Type/Size100
    Log Storage Type/Size100
    Network SettingsVPCtutorial
    Subnet{VPC_ID}_sn_5 (10.0.64.0/20)⚠️ Use the subnet created in the NAT instance tutorial.
    Automatic BackupAutomatic Backup OptionNot 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. MySQL Workbench

Step 2. Proceed with data migration

  1. Click MySQL Workbench > Database > Migration Wizard to prepare for data migration. Proceeding with Data Migration in MySQL Workbench

    ItemSetting/Input ValueNote
    Source SelectionEnter access information for AWS Database ServerTest Connection for access testing
    Target SelectionEnter access information for KakaoCloud Database ServerTest Connection for access testing
    Schemas SelectionSelect schemas for migrationChoose Library Schemas for this tutorial
    Source ObjectsSelect target tablesProcedures or Functions can be selected
    MigrationGenerate scripts for the migration target
    Target Creation OptionsCreate schemas in the target database and generate SQL script files
    Create SchemasStart creating tables and monitoring logs
    Bulk Data TransferCheck 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}
    ItemDescription
    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.

Scenario Architecture

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.

Primary 승격

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.