Restoring MySQL DB from on-premise to Amazon RDS
When considering a migration of your MySQL database to AWS, especially from an on-premises setup, this article will dive into a specific technique: leveraging Percona XtraBackup. This tool offers an efficient way to backup and restore your MySQL database on AWS using Amazon S3.
Sign in to AWS Console: Log in to your AWS Management Console.
Navigate to Amazon S3: Find the Amazon S3 service in the AWS Console.
Create a New S3 Bucket: Click "Create bucket," choose a unique name, select a region, and configure properties like permissions and versioning.
Review and Create the Bucket: Review your settings and create the S3 bucket.
With our S3 bucket ready, it's time to set up an IAM (Identity and Access Management) user and policy for secure access. Here's how you can do it:
Navigate to IAM in AWS Console: Find the IAM service in the AWS Console
Create a New IAM User: Specify the user name and choose programmatic access.
Click on the user you created.
In the "Permissions" tab, click "Add permissions" and attach the policies you require. For S3 access, the "AmazonS3FullAccess" was chosen for tutorial purposes, but it's good practice to create a custom policy with the least privilege necessary.
Review and Create User: Review the user configuration, then create the IAM user.
Now for this testuser we need to create secret keys and access keys for configuring it in aws cli.
Retrieve the access key ID and secret access key for the IAM user. Keep them secure.
Now, let's configure the AWS Command Line Interface (CLI) on your on-premises server to utilize the IAM user's credentials:
Open a terminal on your server.
Run the following command:
You will be prompted to enter the following information:
AWS Access Key ID: Enter the access key ID for the IAM user you created.
AWS Secret Access Key: Enter the secret access key for the IAM user.
Default region name: Specify your preferred AWS region.
After entering this information, your AWS CLI will be configured to use the IAM user's credentials for S3 access.
Now we can use access S3 from server
To confirm it give command
Step 3: Creating a Backup Script:
Now, we'll create a script that will automate the backup process and store it in your S3 bucket. Let's dive into the script creation.
Add the following contents to your script:
Save and exit the editor by pressing ESC, followed by :wq
Restoring MySQL DB backup from Amazon S3 to Amazon RDS
Choose "Restore from Amazon S3"
Choose the Amazon S3 bucket where your backup was stored.
Select your desired MySQL Engine and Engine Version based on your project specifications.
IAM Role and Instance Class
Pick an IAM role and an instance class that align with your project requirements.
Provide a unique DB instance identifier, which will be the name of your new database instance.
Specify the Master user and Password. Make sure to securely save and store the password since it will be the main user for your database.
For storage, we recommend using gp3, the latest and cost-effective option at the time of writing this document. If your database requires high IOPs, you can opt for provisioned IOPS.
For development environments, consider not enabling auto scaling as it can be costly.
Skip creating a standby instance for dev environments unless necessary.
Choose IPV4 as the connectivity option and provide VPC details and subnet groups according to your project's requirements.
Disable public access for enhanced security.
The default port is 3306, but you can modify it if needed.
Select password authentication for simplicity.
Specify your database name and parameter group. Decide whether to enable or disable automated backups, and enable encryption for added security.
You can choose to enable auto minor version upgrades and enable delete protection for extra security.
Step 3: Review and Create Database
Before proceeding, carefully review your configuration settings. Once you are satisfied that everything is set up correctly, click "Create Database" to start the restoration process.
With Percona XtraBackup and Amazon S3, you can rest easy knowing that your MySQL database backups are both efficient and secure.