Backup script for mysql database to amazon s3 bucket

Backup Mysql Database To AWS S3 Bucket

If you're storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data protect it from loss.
In this tutorial, we will provide you bash script which automates your MySQL database backups on daily,weekly or as per your requirement basis.
sometimes storing your backups on the same server is also risky because if the same server exhaust then with you original data you loose backup as well.
for this kind of situation we need to store this backup files somewhere else which give a guarantee of your data. Here we are going to use AWS S3 bucket service to store our daily data backups.

I hope you have aware of S3 service and you have s3 bucket and credentials which we need to configure on the server. If you do not create s3 bucket then refer below link

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

Step 1 : Install s3cmd Command

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

  • apt-get update
  • apt-get install s3cmd

Step 2 : Configure S3cmd command

Once installation of s3cmd command done. you need to configure s3 which helps to make the connection between server and s3 bucket.
For configuring s3 on server you need Access_key and Secret_key of your S3 bucket. You get the Access_key & Secret_key of the bucket while creating an s3 bucket on aws.

  • s3cmd --configure -c ~/.s3cfg

When you have run above command it will ask you for 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 "Configuration saved to '/root/.s3cfg'".

Step 3 : Create a directory for the backup script and backup file.

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

  • mkdir /opt/scripts [use any location which you want].
  • mkdir /data/DbBackup [use any location which you want]

Step 4 : Backup Script

Copy below backup script in your directory "/opt/scripts" and give name for file "MysqlDbBackup.sh". I have explained complete backup script by adding comments on each line of script file so you will understand 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        : PRASHANT THORAT                                                      # 

# Date           : 9th July 2016                                                                  #
# 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 your                                                        database"
MYSQLPASS='password'      ##Password of User

DOC="/data/DbBackup"       ##Provide directory details where to store backup                                                 files
MAIL=$(which mail)           ##This variable store the mail command which is                                                    use to send emails
MAILTO='emailid'             ##Add your email id for notification on backup                                                         complete or errors"

##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

# This variable 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 Backup Script

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

  • chmod +x MysqlDBBackup.sh
  • bash -x MySqlDBBackup.sh

Step 6 : Check Backup complete 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 S3 bucket.

Step 7 : Schedule Backup cron

After a successful test of backup script. we will schedule our backup script to execute at a perticular time. Here you can provide a time when less traffic comes on your website.
To schedule backup script. create 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 techthings.org for more such valuable articles.

Related Posts :

Latest Comments
  1. vicky May 22, 2017

Leave a Reply

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