AWS RDS migration guide
Overview
This document will demonstrate how to migrate a database from AWS Relational Database Services (RDS) to PlanetScale. We’ll also address some common issues associated with migrating from RDS and how to resolve them.
Note
This guide assumes you are using MySQL on Amazon RDS. If you are using Amazon Aurora (MySQL compatible) on RDS, follow the Amazon Aurora migration guide. Other database systems (non-MySQL or MariaDB databases) available through RDS will not work with the PlanetScale import tool.
We recommend reading through the Database import documentation to learn how our import tool works before proceeding.
Prerequisites
Before you can perform a migration, you’ll need to make sure the following is configured:
- Your database’s GTID and binlog_format settings are properly configured.
- Your database’s binlog retention settings are properly configured.
You’ll also need to gather the following pieces of information from the AWS Console:
- Endpoint address
- Port number (E.g., 3306)
- Master username and password or the ability to create a new user in the database
The endpoint address and port number are located in the database view under “Connectivity & security”.
The master username can be located under the “Configuration” tab. The master password will be the password used when the database was first created.
Note
If you don’t know the admin password, you can create a new set of credentials using the information on the Import tool user permissions page to create an account that can be used to import your database.
Configuring the RDS security group
In order for PlanetScale to connect to your RDS database, you must allow traffic into the database through the associated security group. The specific IP addresses you will need to allow depend on the region you plan to host your PlanetScale database. Check the Import tool public IP addresses page to determine the IP addresses to allow before continuing. This guide will use the AWS us-east-1 (North Virginia) region so we’ll allow the following addresses:
3.209.149.66 3.215.97.46 34.193.111.15 18.117.23.127 3.131.243.164 3.132.168.252 3.131.252.213 3.132.182.173 3.15.49.114
To allow traffic to your database in AWS, navigate to the “Connectivity & security" tab of the database, and click the link under VPC security groups.
In the lower pane of the next page, select the “Inbound rules” tab, then “Edit inbound rules”.
Click the “Add rule” button, and a new line will be added to the list of rules. Select “MYSQL/Aurora” as the Type and enter the first IP address from the list for your region. AWS will automatically reformat it to x.x.x.x/32
, so you can select that when it appears. You’ll need to repeat this for every IP address you need to allow.
Once you are finished adding all of the necessary IP addresses for the selected region, click “Save rules” to apply the changes.
Importing your database
In the PlanetScale dashboard, click “New database”, then “Import database”.
Complete the form using the information gathered in the previous section. Click “Connect to database” and the import tool will attempt to connect to your RDS instance.
The “Connect to database” button will update with the connection status.
Note
If your database uses foreign key constraints, we will detect them after successfully connecting to your external database and automatically enable foreign key constraint support for your database.
If the connection is successful, click “Begin database import” to migrate your data to PlanetScale.
Warning
If you receive an error, check the Troubleshooting section for information on correcting common configuration issues.
The following view will show you the progress of your data being imported.
Once your database has finished importing, the view will update to show the database that was created in PlanetScale, as well as the option to enable primary mode. This button will make the PlanetScale version of the database the primary replica. Clicking “Enable primary mode” will display a modal where you can confirm that you want to make this change.
After primary mode is enabled, the third card in the flow will update to give you two options:
- Enable replica mode — Reverts the change performed in the previous step, making RDS your primary once again.
- Finish import — Detaches the databases from replication. Future changes to either the PlanetScale database or the RDS database will not be replicated to each other.
Click “Finish import” to complete the import process. Confirm in the modal that will be displayed.
This concludes the process used to import a database into PlanetScale from AWS RDS.
Troubleshooting
Enable binary logging
If binary logging is disabled on your RDS server, you'll receive an error like this from the importer: "External database settings are not compatible with PlanetScale: log_bin must be ON, but found: OFF".
On RDS MySQL servers, binary logging can't be directly enabled by changing a parameter. Instead, to set log_bin
to on, enable automated backups by setting their retention period to any value greater than zero days.
Once that change has been applied, you can verify that log_bin
is on
in a MySQL session:
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+
Configure GTID and binlog settings
Navigate to your RDS instance and check the parameter group in the “Configuration” tab. If you are using the default, you’ll need to create a new parameter group to reconfigure the GTID settings.
To create a parameter group, select “Parameter groups” from the left nav and then “Create parameter group”.
Specify the Parameter group family, Type, Group name, and Description. All fields are required.
When you save, you’ll be brought back to the list of available parameter groups. Select your new parameter group from the list to modify the settings.
Click “Edit parameters” to unlock editing.
Search for “binlog_format” in the search box. Update that setting to match:
- binlog_format: ROW
Search for “gtid” in the search box, and update the following settings to match:
gtid-mode: ON
enforce_gtid_consistency: ON
Click “Save changes” once you are done.
Now you need to associate the parameter group to the database. Select “Databases” from the left nav, check the select box next to your database, and click “Modify” from the toolbar.
Scroll about halfway down the page and find the section titled Additional configuration. Update the DB parameter group to match the new group created earlier in this section. Scroll to the bottom and click “Continue”.
You’ll be presented with options to apply the changes at the next maintenance window or immediately.
If you select Apply during the next scheduled maintenance window, the updated configuration group will be applied during that window.
If you select Apply immediately, the group will be applied immediately but you must manually reboot the database for the changes to take effect.
Once you’ve selected your desired option, click “Modify DB instance” to apply the changes.
If you opted to apply the changes immediately, you can monitor the status on the “Configuration” tab of the database view. The page does not automatically update, so you’ll need to refresh the page to check the status when it updates.
To reboot the database, click the “Modify” button near the top of the screen, then “Reboot”.
Warning
This will disconnect any users who are actively using the database! Proceed with caution.
You’ll be presented with a page to confirm the database you want to reboot. Click "Confirm” and the database will begin rebooting.
You can view the status of the database in the primary list of databases for this account/region. This view does not refresh automatically, click the “Refresh” button to update the view.
Configure binlog retention hours
You may need to specify the number of hours to retain binary logs in your AWS RDS database.
By default, this value is NULL
, which means that binary logs are purged immediately.
You need to specify a period with enough time for replication to occur. For most cases, 48 hours should be sufficient, but you may need to set it higher for large imports.
Warning
Keep in mind, you can go to longer binlog retention periods at the cost of extra disk space on your source database. You should evaluate how large your binlogs are for daily use to determine if you would like to increase the value beyond 48 hours, as there's a chance you may run out of disk space, depending on your configuration. If you need assistance, please reach out to the PlanetScale Support team.
To specify the number of hours for Amazon RDS to retain binary logs on a DB instance, use the mysql.rds_set_configuration()
stored procedure as shown in the following example:
call mysql.rds_set_configuration('binlog retention hours', 48);
Once you call this function, confirm that the value is set correctly by calling this function:
CALL mysql.rds_show_configuration; +------------------------+-------+-----------------------------------------------------------------------------------------------------------+ | name | value | description | +------------------------+-------+-----------------------------------------------------------------------------------------------------------+ | binlog retention hours | 48 | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. | +------------------------+-------+-----------------------------------------------------------------------------------------------------------+
Need help?
Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.