Dump / Backup all of your MySQL databases nightly in cron (script)

October 20, 2015 — Leave a comment

You have a number of sites on your server.. how about an easy way to dump them all each night into a directory on the server!

The idea:
The idea of this script is to dump/backup all of your databases each night while you sleep, put them into a directory on the server, then do it again the next night. We’re assuming you have some sort of off-site backup script that will come and grab these files each day before they’re overwritten the next night..

What it does:
1. The first part of the script sets up the various settings like the backup directory, the user, password, hostname..
2. The next part clears out the backup directory from the previous night’s backups
3. the next part does the actual backup of your databases, sleeping 5 minutes after each one in case the load creeps up. There are two parts to this; 1. if you don’t need to specify a root password and 2. if you do need to specify a root password
4. the final part secures the backup directory so only root can see it.

Set it up:
1. Copy the text below and put it somewhere like /usr/local/bin/mysql_backup.sh, make it executable and chmod it so only root can see/run it (chmod 700 /usr/local/bin/mysql_backup.sh)
2. Edit the top portion of the script making sure your DB_BACKUP location exists

Add it to cron:
You can add it to cron by editing root’s crontab and adding the following line:

(this will make it run every night at midnight)

Let us know how it worked for you!

No Comments

Be the first to start the conversation.

Leave a Reply