MySQL auto backup on every hour or every 30 min is that really cool right ? Yes now its most important to take backup of the database even every minute if possible. The cyber security is most important now days and lot of new script kiddies and pro hackers out there. The website security is at most risk when the internet grows. The only way to keep safe is updated software and do the backup of your code , database   and even infrastructure .

Its been a long time since I wrote a single article in my blog , I think I have to start again for the better tomorrow.In this article I just show you guys how can we take an auto backup of your MySQL databases on your scheduled times, from every minute to once in a day. or the frequency you like.

Here we are using the  mysqldump   feature for the auto backup, So make sure the mysqldump is available on your server.

How are are going to do the MySQL auto backup on every hour is create a shell script and execute it with cron jobs.

Jump to the Code

Create a file called mysql_dump.sh and put it in your preferred location in the server, The file content will be as follows.

#!/bin/sh
# This script will help to create MySQL auto backup on every hour
# Database Details goes here
db_host=""
db_name=""
db_username=""
db_password=''
backup_location=/home/backup/mysql
today=`date +%Y-%m-%d`
sql_file=$backup_location/$today/$db_name-`date +%H%M`.sql
tar_file=$backup_location/$today/$db_name-`date +%H%M`.tar.gz
if [ ! -d $backup_location/$today ]
then
mkdir -p $backup_location/$today
/usr/bin/mysqldump -h $db_host -u $db_username -p$db_password $db_name > $sql_file
tar zcf $tar_file $sql_file
rm $sql_file
else
/usr/bin/mysqldump -h $db_host -u $db_username -p$db_password $db_name > $sql_file
tar zcf $tar_file $sql_file
rm $sql_file
fi

The above code will create a backup of MySQL database to an .sql file then it compress the .sql into tar.gz for saving the disk space then remove the .sql file.

Now the shell script file is ready next thing you have to do is make the file executable

chmod +x /path to the file
//eg: chmod +x /home/admin/scripts/mysql_dump.sh

Now you can configure the Cron jobs in your server if you have a Cpanel you can create a cron job in there. and put the path to this file. If you’re setting that from Control panel make sure the user have access to take the dump and write in the folder . basically Sudo.

0 * * * * /home/admin/scripts/mysql_dump.sh

If you don’t have any control panel then just add the same on the crontab-e file. use your favorite editor and save the content mentioned as above. There is no need to restart the cron service it will auto reload once you made the changes.

What  if the MySQL dumps are too many ? like if you set it on every hour suppose you may wish to remove some old files , say 7 days old files can be removed, Yep that is good idea . just add the below codes at the end of the shell script file.

expiry=7
find $backup_location -type d -mtime +$expiry | xargs rm -Rf

Yes its easy to make MySQL auto backup on every hour or every 30 minutes or what ever frequency you want, It is important to take backup on production servers with less frequency.

I hope this little article will help you guys MySQL auto backup job easier.

Happy coding 🙂  stay safe 🙂