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 :
About The Author
Techthings
Welcome to TechThings. I'm Prashant Thorat, a tech-blogger from Mumbai, India. I started Techthings as a passion and to share my knowledge about technologies. Here at Techthings I write about Linux technologies, Aws Cloud, Wordpress blogging and scripting knowledge. You can read more about me at About us page. Thank You :)
This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! Keep up the good work.
Managed Network Services Dallas
Thank you.
Hi,
cmd: aws s3 sync /var/backups/ s3://bucket/amazon/backup
How to run above command using crontab to make backup from linux to s3bukcet.
All important backups are stored under backups folder. I need the script to copy the files from machine to s3 bucket. Could you please…
Thanks
Thanks for your backup script. It helped me a ton! I modified it a bit and had to add some changes to fix a few things. I also used awscli and the “aws s3api put-object” command.
For example:
(grep -v wasn’t working because I had to add the “\” in the beginning of the “(” and after the db name)
DATABASES=`mysql -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS -e “SHOW DATABASES;” | tr -d “| ” | grep -v “\(Database\|information_schema\|cphulkd\|leechprotect\|modsec\|mysql\|performance_schema\)”
(had to add a “\” before the semi-colon)
find $DOC/*.sql.gz -mtime +5 -exec rm {} \;
(had to modify the format of the sendmail command for both parts. I used $MAIL=’/usr/sbin/sendmail’)
echo -e “Hi Sysadmin,\n\nError in Mysql Dump Live Database. \n\nError : Mysql Dump Error. [`echo DB-ERROR | sed ‘s: :, :g’`] \n\nFrom: blahblah.com” | $MAIL -v $MAILTO