How to Migrate Amazon RDS for MySQL to Vultr Managed Databases for MySQL

Managed database services offer scalable, reliable, and secure database solutions without the complexity of self-management. Vultr Managed Databases for MySQL offers a cost-effective solution with predictable pricing, automated backups, and seamless integration with other Vultr services. Amazon RDS for MySQL is a popular managed database service, while Vultr Managed Databases for MySQL offer better functionality with competitive pricing and a developer-friendly interface, enabling you to focus on building applications rather than managing infrastructure.

Follow this guide to migrate Amazon RDS MySQL Databases to Vultr Managed Databases for MySQL with minimal downtime.

Prerequisites

Before you begin, you need to:

• Have access to an existing Amazon RDS MySQL Database.
• Provision a Vultr Managed Databases for MySQL Cluster with the same MySQL version, and a similar or higher size as your source Amazon RDS MySQL Database.
• Install MySQL Workbench or the mysql client tool on your workstation).

Allow Network Connections to the Source Amazon RDS MySQL Database

Amazon RDS Databases only accept VPC connections by default. Follow the steps below to allow network connections from your management workstation's public IP address to avoid connectivity issues during migration.

1. Navigate to the Amazon RDS Console.

   

2. Open your target RDS instance.

3. Under the Connectivity & Security tab, locate the VPC security groups section.

4. Select the security group linked to your RDS instance.

5. Edit the Inbound rules and allow access from your public IP (0.0.0.0/0 for unrestricted access, or specify your workstation's IP).

Back Up the Source Amazon RDS MySQL Database

Before migrating your MySQL database from Amazon RDS to Vultr, create a backup of your Amazon RDS database. This ensures you have a restore point in case of any errors during migration. You can back up your Amazon RDS MySQL database using the mysqldump command-line utility or the AWS Management Console. Follow the steps below to back up your source Amazon RDS database using the mysqldump utility that allows you to export the database schema and data into a SQL file.

1. Log in to your source Amazon RDS MySQL database using the mysql connection string. Enter your RDS password when prompted.

   CONSOLE

   Copy

   $ mysql -h <aws-rds-endpoint> -u <aws-rds-username> -p

   Explain Code

   Powered by Vultr AgentBeta

2. List all available databases and note the target databases to migrate.

   SQL

   Copy

   mysql> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Your output should be similar to the one below.

   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | sys                |
   | testmigrationdb    |
   +--------------------+
   5 rows in set (0.21 sec)

3. Exit the MySQL console.

   SQL

   Copy

   mysql> EXIT;

   Explain Code

   Powered by Vultr AgentBeta

4. Back up all databases in your source Amazon RDS instance to a single all-databases-backup.sql file. Replace <aws-rds-endpoint> and <username> with your actual Amazon RDS instance details.

   CONSOLE

   Copy

   $ mysqldump -h  <aws-rds-endpoint> \
   -u <username> -p --all-databases --set-gtid-purged=OFF \
   --single-transaction --triggers > all-databases-backup.sql

   Explain Code

   Powered by Vultr AgentBeta

   • Use the following command to backup a specific database to a single .sql file.

     CONSOLE

     Copy

     $ mysqldump -h  <aws-rds-endpoint> \
     -u <username> -p <database-name> --set-gtid-purged=OFF \
     --single-transaction --triggers > <backup-name.sql> 

     Explain Code

     Powered by Vultr AgentBeta

5. List files in your working directory and verify that the all-databases-backup.sql is available when the backup is successful.

   CONSOLE

   Copy

   $ ls -lh all-databases-backup.sql

   Explain Code

   Powered by Vultr AgentBeta

6. Compress the backup file with gzip for improved file transfer.

   CONSOLE

   Copy

   $ gzip all-databases-backup.sql

   Explain Code

   Powered by Vultr AgentBeta

Migrate the Amazon RDS MySQL Database to Vultr Managed Databases for MySQL

You can migrate the source Amazon RDS MySQL database to your destination Vultr Managed Databases for MySQL using two methods, live replication and backup restoration. The Vultr Console, and MySQL Workbench to perform a live migration of all databases to your Vultr Managed Databases for MySQL cluster. Follow the steps below to migrate the source Amazon RDS MySQL using the Vultr Console or MySQL Workbench.

1. Log in to the Vultr Console.

2. Click Products and select Databases.

3. Click your target Vultr Managed Databases for MySQL to open its management page.

4. Verify the access credentials within the Connection Details section to use when migrating to the Vultr Managed Databases for MySQL cluster.

   

Test the Vultr Managed Databases for MySQL

It's important to verify the migrated data on your destination Vultr Managed Databases for MySQL and transfer any missing databases from your source Amazon RDS Databases. This ensures that all databases are migrated and your destination cluster is functional before cutting over existing applications. Follow the steps below to test the Vultr Managed Databases for MySQL cluster and verify that the migration is successful.

1. Log in to your Vultr Managed Databases for MySQL using the mysql connection string and enter your password when prompted.

   CONSOLE

   Copy

   $ mysql -h <vultr-database-host> -P <vultr-database-port> -u vultradmin -p

   Explain Code

   Powered by Vultr AgentBeta

   Replace the following example entries in the above command:

   • <vultr-database-host> with your Vultr Managed Databases for MySQL hostname.
   • <vultr-database-port> with your Vultr Managed Databases for MySQL port.
   • vultradmin with your Vultr Managed Databases for MySQL username.

2. Check the MySQL database version and verify that it matches your source Amazon RDS database version.

   SQL

   Copy

   mysql> SELECT @@version;

   Explain Code

   Powered by Vultr AgentBeta

   Your output should be similar to the one below.

   +-----------+
   | @@version |
   +-----------+
   | 8.0.35    |
   +-----------+
   1 row in set (0.17 sec)

3. List all available databases and verify that they match with your source Amazon RDS databases.

   SQL

   Copy

   mysql> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Your output should be similar to the one below.

   +--------------------+
   | Database           |
   +--------------------+
   | defaultdb          |
   | information_schema |
   | mysql              |
   | performance_schema |
   | sys                |
   | testmigrationdb    |
   +--------------------+
   6 rows in set (0.13 sec)

4. Switch to any of the migrated databases, such as testmigrationdb.

   SQL

   Copy

   mysql> USE testmigrationdb;

   Explain Code

   Powered by Vultr AgentBeta

5. List all tables in the database and verify the schema structure.

   SQL

   Copy

   mysql> SHOW TABLES;

   Explain Code

   Powered by Vultr AgentBeta

   Your output should be similar to the one below.

   +---------------------------+
   | Tables_in_testmigrationdb |
   +---------------------------+
   | employees                 |
   +---------------------------+

6. Run the following query to count the number of rows in a table and verify that they match with your source RDS database. Replace employees with your actual table name.

   SQL

   Copy

   mysql> SELECT COUNT(*) FROM employees;

   Explain Code

   Powered by Vultr AgentBeta

   Your output should be similar to the one below.

   +----------+
   | COUNT(*) |
   +----------+
   |    10000 |
   +----------+

7. Exit the MySQL console.

   SQL

   Copy

   mysql> EXIT;

   Explain Code

   Powered by Vultr AgentBeta

Cutover Applications to the Vultr Managed Databases for MySQL

Follow the recommendations below when cutting over applications to your Vultr Managed Databases for MySQL cluster.

• Cutover WordPress Applications:

  1. Navigate to the WordPress installation directory.

     CONSOLE

     Copy

     $ cd /path/to/wordpress/directory/

     Explain Code

     Powered by Vultr AgentBeta

  2. Create a backup of the configuration file to ensure safe rollback in case of any issues.

     CONSOLE

     Copy

     $ sudo cp wp-config.php wp-config.ORIG

     Explain Code

     Powered by Vultr AgentBeta

  3. Open the configuration file using a text editor such as nano.

     CONSOLE

     Copy

     $ sudo nano wp-config.php

     Explain Code

     Powered by Vultr AgentBeta

  4. Update database credentials. Locate the following section and replace the values with your Vultr Managed Databases for MySQL credentials.

     PHP

     Copy

     /** The name of the database for WordPress */
     define('DB_NAME', '<example-wordpress-db>');
     
     /** MySQL database username */
     define('DB_USER', '<example-user>');
     
     /** MySQL database password */
     define('DB_PASSWORD', '<example-password>');
     
     /** MySQL hostname */
     define( 'DB_HOST', '<vultr-database-host>');
     
     /** MySQL port */
     define( 'DB_PORT', '<vultr-database-port>');

     Explain Code

     Powered by Vultr AgentBeta

     Replace the following values:

     • <example-wordpress-db> with the database name in your Vultr Managed Databases cluster.
     • <example-user> with the appropriate database username.
     • <example-password> with the password of your database user.
     • <vultr-database-host> with the host of your Vultr Managed Databases for MySQL cluster.
     • <vultr-database-port> with the Vultr Managed Databases for MySQL cluster port.

     Save and close the file.

  5. Restart the web server to apply the configuration changes. The command may vary depending on the web server and Linux distribution in use.

     For Apache:

     • On Debian-based systems (Ubuntu, Debian):

       CONSOLE

       Copy

       $ sudo systemctl restart apache2

       Explain Code

       Powered by Vultr AgentBeta

     • On RHEL-based systems (CentOS, Rocky Linux, AlmaLinux):

       CONSOLE

       Copy

       $ sudo systemctl restart httpd

       Explain Code

       Powered by Vultr AgentBeta

     For Nginx:

     • On all major distributions:

       CONSOLE

       Copy

       $ sudo systemctl restart nginx

       Explain Code

       Powered by Vultr AgentBeta

     If your system uses an alternative init system, such as SysVinit or OpenRC, use the appropriate restart command. Refer to your distribution’s documentation for more information.

• Dockerize the application cutover:

  If your application runs within a Docker container, you'll need to update the database connection settings in the Docker Compose file.

  1. Open your docker-compose.yaml file.

     CONSOLE

     Copy

     $ sudo nano docker-compose.yaml

     Explain Code

     Powered by Vultr AgentBeta

  2. Locate the environment variables that store database connection details and update them with your Vultr Managed Databases for MySQL credentials.

  3. Update the database connection variables with your Vultr Managed Databases for MySQL credentials.

     YAML

     Copy

     services:
         your-app:
             environment:
                 MYSQL_HOST: "<vultr-database-host>"
                 MYSQL_USER: "<example-user>"
                 MYSQL_PASSWORD: "<example-password>"
                 MYSQL_DATABASE: "<example-wordpress-db>"
                 MYSQL_PORT: "<vultr-database-port>"

     Explain Code

     Powered by Vultr AgentBeta

     Save and close the file.

  4. Restart the Docker containers to apply the new database settings:

     CONSOLE

     Copy

     $ sudo docker-compose down && docker-compose up -d

     Explain Code

     Powered by Vultr AgentBeta

  For applications built with Node.js, Python, or PHP, visit the respective documentation resources to update the database connection strings using environment variables or configuration files.

Conclusion

You have migrated your MySQL database from Amazon RDS to a Vultr Managed Databases for MySQL cluster. You migrated the databases with live replication using the Vultr Console and direct backup transfers with MySQL Workbench. Migrating your source database lets you configure existing application to connect to the new Vultr Managed Databases for MySQL cluster. Delete the old Amazon RDS instance to prevent additional costs when the migration to your Vultr Managed Databases for MySQL is successful. Visit the Vultr Managed Databases for MySQL documentation for more information.