I want to automate MySQL database dump in a production environment which includes the below features.
- The dump should be created automatically everyday at 8 PM.
- Dump should be stored in /opt/dump
- Dump should be stored as bzip2 to save disk space and easy network transfer.
- The gz archive should be stored with the Date and time when it was created.
- All old dump should be deleted except last three.
- Send email to me, after successful dump creation.
Automating a backup process, not only saves you from the repetitive task (which I personally find boring), but also helps in minimizing human error and save time. Here is what I did to automate my MySQL database schema on all servers in production. Save the below content to a file say mysql-dump-automate.sh
# Written by Avishek Kumar
# Define variable Date
# Variables that need to be edited as required
# create directory if not exist
mkdir -p $path
# Take the mysql dump in one go. Though it is unsafe to use the password on the command line interface.
/usr/bin/mysqldump --user=$username --password=$password -h $host -P $port $db >/dev/null 2>&1 | gzip -c > "$path""$Date".sql.gz && \
## Delete all except top 3
cd $path && rm -rf "$(ls -1t | tail -n -3)" && \
## Email on Success
echo "Database Backup on Sever 220.127.116.11 was successful " | mail -s "Database Backup Created" firstname.lastname@example.org -aFrom:email@example.com
For more details on sending emails in Linux Terminal refers How can i send emails from my Linux box?
Make the script executable
$ chmod 755 mysql-dump-automate.sh
To run the above automatically everyday at 8 PM, you may schedule a cron job simply as
- Edit crontab
- Add the below line at the end of the crontab file. Save and exit.
$ crontab -e
0 20 * * * sh /home/avi/test/test/mysql-dump-automate.sh
If everything goes as suggested in the write-up, you should now be able to automate any mysql database like a cakewalk.
Look at ‘automysqlbackup’, see: https://sourceforge.net/projects/automysqlbackup/
It will do everything you want… And more!
It is part of most linux distributions like Debian and Ubuntu. You can use their package managers to install it. The configuration file is extensively documented.. Look around and have fun!