Backup MySQL database and directly upload to AWS S3 bucket

  Reading Time:

In this post we will learn how to backup MySQL database and upload it to Amazon S3 server. We will be using Ubuntu Server 18.04.

Prerequisites:

  1. Create S3 bucket
  2. MySQL database user name and password
  3. AWS account with access to IAM and S3

Step 1 - Install AWS CLI

sudo apt-get update
sudo apt-get -y install python-pip
sudo pip install awscli

Before we start using AWS CLI tool, we need to configure it by running the following command.

aws configure

This will ask you to provide your

  • AWS Access Key ID
  • AWS Secret Access Key
  • Default region
  • Default output format

It looks like:

AWS Access Key ID [****************PQLL]:
AWS Secret Access Key [****************KMA5]:
Default region name [us-east-1]:
Default output format [json]:

If you have done everything correctly you should be able to see the list of S3 buckets by running below command

aws s3 ls

Note: I recommend you read AWS CLI reference guide to learn how to use AWS CLI with various services.

Step 2 - Create a bash script

We will create a shell script used to back up MySQL database and upload it to S3 bucket.

nano mysql_backup.sh

Copy the following content to the script file. This script uses mysqldump to dump the database to a temporary file, and It uploads the file to S3 by using the AWS CLI

#!/bin/bash
# Database credentials
USER="YourDatabaseUserName"
PASSWORD="YourPassword"
HOST="localhost"
DB_NAME="database that you want to backup"

#Backup_Directory_Locations
BACKUPROOT="/tmp"
TSTAMP=$(date +"%d-%b-%Y")
S3BUCKET="your-s3-bucket"

mysqldump -h$HOST -u$USER $DB_NAME -p$PASSWORD | gzip -9 > $BACKUPROOT/$DB_NAME-$TSTAMP.sql.gz

# Move file from server to your S3 bucket
aws s3 mv $BACKUPROOT/$DB_NAME-$TSTAMP.sql.gz s3://$S3BUCKET/

Step 3 - Let’s run the script

chmod +x mysql_backup.sh
bash mysql_backup.sh

Now check your s3 bucket, you can see a backup file uploaded.

Step 4 - Schedule it with Crontab:

To add a cron job on Ubuntu 18.04 server, run the following command

crontab -e

Add following line at the end of the file

#Run the database backup script every day at 12.30 AM
29 0 * * * /path-to-your-script/mysql_backup.sh

Conclusion

In this post, we learned how to backup MySQL database and directly upload it in to AWS S3 bucket. Just schedule a cron job based on your requirement and you are done. This script will backup only one database. You can easily modify the shell script to backup all databases on your server.

How to enable Basic authentication on Swagger UI using spring security

Enable Basic Authentication on Swagger UI using spring security. Assuming we have already configured Swagger in our project , we shall turn on basic authentication using spring security on spring boot v2...

How to Deploy an Express.js Application to AWS Lambda

In this post, we will learn how to set up a simple Express API application powered by an AWS Lambda function. We will use Serverless Framework...

App & Geek   Never miss a story from App & Geek, get updates in your inbox.