Easy MySQL database backup

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 to protect it from loss. If database is run under Linux OS, the simplest and fastest way is to dump it into a .sql file and ftp it onto a storage protected by RAID. The automation of this process is achieved with shell scripting.

Here's a simple shell script I'm using to backup our company's database. It's designed to send out a report upon successful/unsuccessful database backup to a set of email addresses. It's assumed that email transport is already set up.

Initially, to set up this script, you need to have root access to your system and also access to your database :) Then, you create a .sh file (mine is called backup.sh):

# vi backup.sh

If you are unfamiliar with vi editor, here's a nifty tutorial.

The following is the code I use, with comments explaining each line and with omitted secret data (colored red and with brackets) :) :

#!/bin/sh
#Backup script for  OTRS
echo "From: <backup daemon>@<email domain>" > email.text
echo "To: <address of receiver>@<email domain>" >> email.text
echo "Cc: <cc address>@<email domain>" >> email.text
echo "Subject: OTRS backup" >> email.text
echo "Starting db backup on:" `date` >> email.text

#above is for setting up the header of email report. It's written to a file, that will be deleted after being sent.
 

#dump your database to a file

mysqldump -u root -p<db password> otrs > otrs_`date +%Y-%m-%d`.sql
echo -e "\nBackup file created on:" `date` >> email.text# setting up variables for ftp server. set your host, your user name for ftp and password

HOST='<ip address of ftp host>'

USER='boki' 
PASSWD='<your password>'

# adding some lines for the report
echo -e "\nFTP-ing to backup location started on:" `date` >> email.text

# opening the ftp connection and starting backup
ftp -n -v $HOST << EOT >> email.text
ascii
user $USER $PASSWD
bin
cd otrs_ip
put otrs_`date +%Y-%m-%d`.sql
bye
EOT

# cleaning up after ftp-ing
rm otrs_`date +%Y-%m-%d`.sql
echo -e "\n\nBackup procedure finished. Check above info for errors." >> email.text
echo -e "\n\nFor all the additional info, contact your backup admin." >> email.text
/usr/sbin/sendmail -r "<backup daemon>@<email domain>" -t < email.text
# deleting email file

rm email.text

After successfully editing and saving this script, it's time to make it executable. So, enter as root user following:

# chmod 755 backup.sh

Finally, make your script execute without you needing to intervene - make it execute automatically at a certain time (usually after hours):

# crontab -e

Add a line to the end of your crontab:

00 23 * * * /root/backup.sh

This means that the script will automatically execute at 23:00 every day. Thats it!