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
   

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

   sql Copy

   mysql> SHOW DATABASES;
   

   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;
   

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
   

   • 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>
     

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
   

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

   console Copy

   $ gzip all-databases-backup.sql
   

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 Customer Portal, 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 Customer Portal or MySQL Workbench.

1. Log in to the Vultr Customer Portal.

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
   

   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;
   

   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;
   

   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;
   

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

   sql Copy

   mysql> SHOW TABLES;
   

   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;
   

   Your output should be similar to the one below.

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

7. Exit the MySQL console.

   sql Copy

   mysql> EXIT;
   

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/
     

  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
     

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

     console Copy

     $ sudo nano wp-config.php
     

  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>');
     

     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
       

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

       console Copy

       $ sudo systemctl restart httpd
       

     For Nginx:

     • On all major distributions:

       console Copy

       $ sudo systemctl restart nginx
       

     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
     

  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>"
     

     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
     

  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 Customer Portal 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.