MySQL is one of the most popular open source database management system for the development of interactive Websites. Its is one of the basic components of established website development platforms such as LAMP (Linux-Apache-MySQL-PHP) due to its proven reliability and speed of performance.
If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).
There are several ways to backup MySQL data. In this article we’ll learn how to achieve an automatic backup solution to make the process easier. Starting with the mysqldump utility that comes with MySQL, we will review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file and send confirmation email to your email address.
This is a simple backup solution for people who run their own web server and MySQL database server on a dedicated or VPS server. Since, I manage couple of boxes, here is my own automated solution.
Lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:
sudo apt-get install lftp
It is time to write a shell script that will automate entire procedure:
Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server and also it will send a confirmation email to the system admin. You need to setup correct server details, username,password and email address before using the script:
#!/bin/bash
SCRIPT_STSTUS_FILE="/tmp/mysql_backup_on.txt"
SCRIPT_LOG_FILE="/tmp/mysql_backup_log.txt"
### MySQL Server Login Info ###
MUSER="your-mysql-server-username"
MPASS="your-mysql-server-password"
MHOST="your-mysql-server-hostname-or-ip-address"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
# Make sure that, you have created the following folder - /backup/mysql
BAK="/backup/mysql"
GZIP="$(which gzip)"
### FTP SERVER Login info ###
FTPU="your-ftp-server-username"
FTPP="your-ftp-server-password"
FTPS="your-ftp-server-hostname-or-ip-address"
NOW=$(date +"%d-%m-%Y")
FILE=$SCRIPT_STSTUS_FILE
if [ -f $FILE ];
then
echo "File $FILE exists"
else
echo "Script started" > /tmp/mysql_backup_on.txt
echo " $NOW Script started to backup the database " > $SCRIPT_LOG_FILE
[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*
echo "# Logging to MYSQL Server..."
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=$BAK/$db.$NOW-$(date +"%T").gz
echo "# Starting to dump the database : " $db
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
echo "# Backup completed " $db
done
echo "# All the databases are dumped successfully."
echo "# Logging to the FTP server...."
lftp -u $FTPU,$FTPP -e "mkdir /home/httpdocs/mysql/$NOW;cd /home/httpdocs/mysql/$NOW; mput /backup/mysql/*; quit" $FTPS
echo "# Backup files are transferred to the FTP server successfully."
rm -rf /tmp/mysql_backup_on.txt
#--------------- SENDING EMAIL ---------------------
SUBJECT="***** MIP: Database has been successfully backed up *****"
# Who to send the email to
EMAIL="iyngaran@bcas.lk"
# A file to hold the body of the message which
#is later redirected into mail
EMAILMESSAGE="mysql_backup.txt"
#Echo the body of the message into the file. The first line is
#added with > subsequent lines needs to be added with >>
echo "Sending email to the admin" > $EMAILMESSAGE
#Send an email using /bin/mail
mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
echo " $NOW Database backup has been succfully
completed and confirmation email sent to the admin" > $SCRIPT_LOG_FILE
#--------------------------------------------------
fi
Save script as /home/your-name/mysql.backup.sh file. Setup executable permission:
chmod +x /home/your-name/mysql.backup.sh
Note : Make sure you have created this folder /backup/mysql (must have permission) in your local server, where the mysql server is running. And also make sure you have created this folder /home/httpdocs/mysql in your FTP server.
To run this script (backup MySQL) , enter:
sh /home/your-name/mysql.backup.sh
To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:
crontab -e
Append following cron job:
00 00 * * * ssh /home/your-name/mysql.backup.sh
I hope you found these tips helpful. Be sure to drop a comment if you have any more ideas.