Bash Script to transfer Mysql DB Backup files on AWS S3 Bucket

Bash Script
Bash Script

For system admins, it is always important to have a backup of everything. If anything goes wrong then that will be restored from the backup file.

In this tutorial, we will provide you bash script which automates your MySQL database backups on a daily, weekly, or as per your requirement basis.
Sometimes storing backups files on the same server is also risky because if the same server got crashed then you will lose original data as well as the backup files.
With this kind of situation, we need to store these backup files somewhere else which gives a guarantee of your data.

Here we are going to talk about the Mysql Database Backup script which will help you to copy backup files from the server to the AWS S3 bucket.

I hope you have knowledge of S3 service and you have s3 bucket and credentials which we need to configure on the server.

If you do not have an s3 bucket then refer to the below link,

Read: How to setup an S3 bucket on AWS Cloud?

Once you have done with the S3 bucket configuration, follow the below-provided steps to configure the MySQL database backup script on the server.

Step 1: Install s3cmd command

To transfer or copy a file from the server to the S3 bucket we need an s3cmd command to be installed on the server. for installing the s3 command use below apt-get command.

$ apt-get update

$ apt-get install s3cmd

Step 2: Configure S3 credentials

After s3cmd installation, You need to configure s3 credentials which helps to make the connection between the server and the s3 bucket.

Now you need Access_key and Secret_key of your S3 bucket.

You will get the Access_key & Secret_key of the bucket while creating an s3 bucket on AWS.

$ s3cmd --configure -c ~/.s3cfg

When you have run the above command, it will ask you for the below details,

  • Access Key:
  • Secret Key:
  • Encryption password: [just enter without password]
  • Path to GPG program [/usr/bin/gpg] [Enter]
  • Use HTTPS protocol [No]: [Enter]
  • HTTP Proxy server name: [Enter]
  • Test access with supplied credentials? [Y/n] n
  • Save settings? [y/N] y

That’s it all your s3 configuration going to be stored in ‘/root/.s3cfg’“ file.

Step 3: Create a Directory for script file and backups

Always maintain the standard to store your files on the server. it will help to keep track of files on the server. 

Also if anything goes wrong then you don’t need to download the backup from the S3 bucket. You can use a file from the server for quick recovery.

$ mkdir /opt/scripts [use any location which you want].

$ mkdir /data/DbBackup [use any location which you want]

Step 4: Mysql DB Backup Script

Copy below backup script in your directory “/opt/scripts” and give a name for file “MysqlDbBackup.sh“. I have explained the complete backup script by adding comments on each line of the script file so you will understand the working steps of the script.

vim /opt/scripts/MySqlDBBackup.sh

#!/bin/bash

##Introduction part of script which provides you the info your script like date,version,author etc.

#########################################################
# Title         : MySqlDBBackup.sh                   #
# Description : Backup MYSQL Database Dump            #
# Author        : AuthorName.                       #
# Version       : 0.1v                                 #
# Usage         : ./MySqlDBBackup.sh                    #
#########################################################

##Basic variables which we are using further in script

MYSQLHOST="localhost"      ##Add your Mysql Host Name or IP
MYSQLUSER="user"           ##Username of Mysql who has access to database"
MYSQLPASS='password'       ##Password of User

DOC="/data/DbBackup"       ##To store Backup files
MAIL=$(which mail)         ##Store mail command in variable
MAILTO='emailid'           ##Email-ID for notification

##Timestamp (sortable AND readable)

TIMESTAMP=`date +"%Y%m%d-%H%M"` ## This variable store the current date and time which we attach further with name of backup file

# Below variable "DATABASES" store the available database on the server and grep command will give input of default database to tr command and tr command will skip those databases from
# backup. we do not need default database like information_schema,MySQL etc so we skipped it.

DATABASES=`mysql -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS -e "SHOW DATABASES;" | tr -d "| " | grep -v "(Database|information_schema|mysql|performance_schema)"`

# Below find command search for five days older backup files on the server and delete it. We use this to save storage on the server.

find $DOC/*.sql.gz -mtime +5 -exec rm {} ;

# Here, we execute the for loop because if we have more than one database on the server then for loop will take a dump of first DB, second DB etc save it on the server and also use
# s3 command to transfer or copy files on s3 bucket.if server faces any issue while take dump or backup of the database you will get the notification email for error message
# that mail command mention in if statement.

for db in $DATABASES; do

# Define our filenames
FILENAME="$db-$TIMESTAMP.sql.gz"
# Feedback
echo -e "e[1;34m$dbe[00m"

# Dump and zip
echo -e " creating e[0;35m$FILENAMEe[00m"
mysqldump --single-transaction -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS --databases "$db" | gzip -c > "$DOC/$FILENAME"

#s3 upload
echo -e " Uploading db dump on S3 bucket"
s3cmd put $DOC/$FILENAME s3://bucket-name/bucket-directory/

if [ $? -ne 0 ]; then
echo -e "Hi Sysadmin,nnError in Mysql Dump Live Database" | $MAIL -s "Error : Mysql Dump Error. [`echo DB-ERROR | sed 's: :, :g'`]" $MAILTO
exit 1
fi

done;

# This Mail command send you the notification of completion of database backup.

echo -e "Hi Sysadmin,nn Live Database Backups successfully done" | $MAIL -s "Completed : Database Backup Seccessfully Done. [`echo DB-Done | sed 's: :, :g'`]" $MAILTO

Note: Remove all comments while saving this file on the server.

Step 5: Change Permission & Test Script

Now give the execute permission for the script and run the script with the below command,

$ chmod +x MysqlDBBackup.sh

$ bash -x MySqlDBBackup.sh

Step 6: Check Backup Completed Or Not

Now go to your server backup directory and check the backup file is available in the directory or not. Then check for the same file on the S3 bucket.

Step 7: Setup Backup Cron

After a successful test of the backup script. we will schedule our backup script to execute at a particular time.

Here you can provide a time when less traffic comes to your website.
To schedule a backup script. create the file under the /etc/cron.d/ directory and set your cron.

vim /etc/cron.d/MysqlBackups

#Below cron execute at 2AM IST

00 02 * * * root /bin/bash /opt/scripts/MySqlBackups.sh  > /tmp/MysqlBackups.log 2>&1

If you’re facing any difficulties while configuring MySQL backup script.Please do comment your queries or problem via the comment section, till then stay tuned to Linuxgrow.com for more such valuable articles.

Thank You 🙂

Prashant

Welcome to Linuxgrow. I'm Prashant, a tech-blogger from Mumbai, India. I started Linuxgrow as a passion and to share my knowledge about technologies. Here at Linuxgrow, I write about Linux technologies, Aws Cloud, Wordpress blogging and scripting knowledge. You can read more about me at About us page. Thank You :)

You may also like...

3 Responses

  1. Kalyan B says:

    I Liked IT…………

  2. erotik izle says:

    This is my first time visit at here and i am actually happy to read all at one place. Cicily Dag Keg

Leave a Reply

Your email address will not be published. Required fields are marked *

Facebook