How To Migrate Database From RDS To DigitalOcean

How To Migrate Database From RDS Snapshot To DigitalOcean Managed Database

Step 1: Restore RDS Snapshot

1. Go to AWS RDS Console
2. Click on Snapshots
3. Select the snapshot you want to restore

4. Click on Actions > Restore Snapshot
5. Fill in the details Important fields:
   • Make it publicly accessible: Yes (if you want to access the database from outside the VPC)
   • DB Instance Class: db.r5.large
   • Multi-AZ Deployment: No
6. Click on Restore DB Instance
7. Wait for the process to complete
8. Once the process is complete, you will see the new DB instance in the list of DB instances
9. Update the master password of the DB instance

Step 2: Create EC2 Instance To Dump Data

1. Go to AWS EC2 Console
2. Launch a new EC2 instance
   • Choose an Ubuntu AMI
   • Choose the same VPC as the RDS instance
   • Choose an instance type with enough resources to dump the data
   • Add a new security group with inbound rules to allow SSH
3. Connect to the EC2 instance using SSH

Step 3: Connect EC2 Instance To RDS Instance

1. Go to the RDS Cluster
2. Click Set up EC2 Connection

3. Choose the EC2 instance you created in the previous step

4. Follow the steps until finished
5. Once the process is complete, you will have the connection details to connect to the RDS instance from the EC2 instance

Step 4: Dump Data From RDS Instance

1. Connect to the EC2 instance using SSH
2. Install MySQL client

sudo apt update
sudo apt install mysql-client

1. Dump the data from the RDS instance

mysqldump -h <rds-endpoint> -u <username> -p --single-transaction --routines --triggers <database-name> | gzip > /path/to/your/local/backup.sql.gz

Example:

mysqldump -h db2-download.c2i8lks3rhzq.ap-southeast-1.rds.amazonaws.com -u root -p'e6duz3zzN8NRmUVGKQOS' --set-gtid-purged=OFF --single-transaction --routines --triggers --quick booking_production | gzip > ~/backup2.sql.gz

1. If there are errors related to GTID, you can try to add --set-gtid-purged=OFF to the command
2. If there are errors related to table or view you can ignore them using --ignore-table=<database-name>.<table-name> Example:

mysqldump -h db2-download.c2i8lks3rhzq.ap-southeast-1.rds.amazonaws.com -u root -p'e6duz3zzN8NRmUVGKQOS' --set-gtid-purged=OFF --single-transaction --routines --triggers --quick --ignore-table=booking_production.reservation_pageview_views booking_production | gzip > ~/backup.sql.gz

1. Wait for the process to complete
2. Once the process is complete, you will have a backup file in the specified path
3. Copy the backup file to your local machine

scp -i /path/to/your/key.pem ec2-user@your-ec2-instance-public-dns:/path/to/remote/file /path/to/local/directory

Example:

scp -i "ec2-db-migrate-ballbot-luthfi.pem" ubuntu@ec2-52-221-234-217.ap-southeast-1.compute.amazonaws.com:/home/ubuntu/backup.sql.gz ~/backup.sql.gz

Note: If your internet connection is slow, you can create k8s pod in our staging cluster and download the backup file to the pod.

Step 5: Restore Data To DigitalOcean Managed Database

1. Create a new Database in DigitalOcean Managed Database

CREATE DATABASE `hh-production-clone` DEFAULT CHARACTER SET = 'utf8mb4';

1. Install pv to monitor the progress of the data import

sudo apt install pv

1. Restore the data to the DigitalOcean Managed Database

pv ~/backup.sql.gz | gunzip | mysql -h <do-db-endpoint> -P 25060 -u <username> -p'<password>' <database-name> 1>/dev/null 2>/root/restore_errors.log

Example:

pv ~/backup.sql.gz | gunzip | mysql -h db-mysql-staging-do-user-123-0.b.db.ondigitalocean.com --compress --force -P 25060 -u doadmin -p'xxx' hh-production-clone 1>/dev/null 2>/root/restore_errors.log

1. Wait for the process to complete

2. Once the process is complete, you will have restored the data to the DigitalOcean Managed Database
3. Verify the data in the DigitalOcean Managed Database
4. Update the connection details in your application to point to the DigitalOcean Managed Database
5. Test the application to ensure that it is working as expected

Step 6: Clean Up

1. Terminate the EC2 instance
2. Terminate the RDS instance (if you no longer need it)
3. Delete the security groups associated with the EC2 and RDS instances

How To Change Database Staging Configuration

1. Go AWS System Manager
2. Go to Parameter Store
3. Search for /digitalocean/hh-ballbot-db

4. Edit the production > database value to the new database name

production:
  <<: *default
  database: hh-production-clone

1. Save the changes
2. Trigger the deployment pipeline to apply the changes