Contact

Restoring MySQL DB from on-premise to Amazon RDS

By Vineela Raya
10 mins read

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.

Amazon S3
Step 1: Creating an Amazon S3 Bucket
Let’s start with the setup of an Amazon S3 bucket, a fundamental step for securely storing your database backups. Here's how you can go about it:

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.

Step 2: Creating an IAM User and Policy

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.

Attach Permissions Policies:

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.

Secret keys and access keys:

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.

Creating MySQL DB Backup
Step 1: Configuring AWS CLI for the IAM User

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:


$ aws configure

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


$ aws s3 ls


# Here we can see all the buckets
Step 2: Installing Percona on the Server:
To prepare your server for backup, Percona XtraBackup needs to be installed. Here's how to do it:

 

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm 

 

$ sudo percona-release setup ps57 

 

$ sudo yum install percona-xtrabackup-24 


# Check percona version

$ xtrabackup –version

# If necessary, install the EPEL repository.

$ sudo amazon-linux-extras install epel

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.


$ vi script.sh

 You will be prompted to enter an editor mode. Press i to enter insert mode. 

Add the following contents to your script:


#!/bin/bash

 

# Define AWS S3 Bucket name

S3_BUCKET="chaikin-omni-bucket-for-percona-s3-rds-restore"

 

# Get today's date in the format "YYYY-MM-DD" and store it in the variable "TODAY"

TODAY=$(date +"%Y-%m-%d")

# Get the name of the week and store it in the variable "WEEK_NAME"

WEEK_NAME=$(date +"%A")

 

# Print today's date and the name of the week

echo $TODAY

echo $WEEK_NAME

 

# Remove the entire "dir" directory from "/tmp"

/bin/rm -rf /tmp/dir/

echo "Old 'dir' directory removed."

 

# Wait for 15 seconds to ensure old directory is fully removed

sleep 15

 

# Define a backup directory path with the week name

BACKUP_DIR="/tmp/dir/backup/${WEEK_NAME}_${TODAY}"

 

# Create the new backup directory

/bin/mkdir -p ${BACKUP_DIR}

echo "Backup directory created."

 

# Run the xtrabackup command to create an uncompressed backup of the MySQL database
/bin/xtrabackup --backup --user=root --password=yourpassword --routines --functions --triggers --target-dir=${BACKUP_DIR} --no-timestam

echo "Uncompressed backup created."

 

# Wait for 10 seconds

sleep 10

 

# Change the ownership of the backup directory to user 'dir' and group 'dir'

/bin/chown -R dir:dir ${BACKUP_DIR}

echo "Ownership of backup directory updated."

 

# Upload the CONTENTS of the backup directory (not the directory itself) to the root of the S3 bucket

aws s3 sync ${BACKUP_DIR}/ s3://${S3_BUCKET}/

if [ $? -eq 0 ]; then

    echo "Backup files successfully uploaded to S3."

else

    echo "Failed to upload backup files to S3."

    exit 1

fi

 

# Print a message indicating that the script has been fully executed

echo "Script executed successfully."

Add the following contents to your script:


Save and exit the editor by pressing ESC, followed by :wq


# Make the script executable:

$  chmod +x script.sh

# Run the script in the background using nohup to avoid interruptions:

$ nohup ./script.sh &
After we run this script the dump will be in your S3 bucket.

Restoring MySQL DB backup from Amazon S3 to Amazon RDS

Step 1: Sign in to the AWS RDS Console

  • First, log in to your AWS account and navigate to the AWS RDS Console.

Choose "Restore from Amazon S3"

  • Select the option to "Restore from Amazon S3" to initiate the restoration process
Step 2: Configuration Options :
Now, let's dive into the configuration details:

  • Bucket Selection

Choose the Amazon S3 bucket where your backup was stored.

  • MySQL Engine and Engine Version

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.


  • DB Instance Identifier

Provide a unique DB instance identifier, which will be the name of your new database instance.


  • Credential Settings

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.

  • Storage Type

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.


  • Storage Autoscaling

For development environments, consider not enabling auto scaling as it can be costly.


  • Availability and Durability

Skip creating a standby instance for dev environments unless necessary.


  • Connectivity

Choose IPV4 as the connectivity option and provide VPC details and subnet groups according to your project's requirements.


  • Public Access

Disable public access for enhanced security.


  • Port Configuration

The default port is 3306, but you can modify it if needed.


  • Authentication

Select password authentication for simplicity.

  • Additional Configuration

Specify your database name and parameter group. Decide whether to enable or disable automated backups, and enable encryption for added security.

  • Maintenance

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.

Conclusion :

With Percona XtraBackup and Amazon S3, you can rest easy knowing that your MySQL database backups are both efficient and secure.