Tip: Automated MySQL Datestamp Backup using CRON via shell

I wrote this up a while back for another site, but it's pretty handy and figured I'd throw it up here. I'm sure a lot of seasoned admins do something like this anyway, but for anyone that doesn't, I hope you find this useful.

To automatically make a backup of your database using *nix cron: Requires: Shell access, ability to run bash and add (hah) scripts to cron.xxx.

#! /bin/bash
# Automated database datestamp backup

mysqldump --opt -Q -u dbusername -pPassword dbname > /path/to/backups/`date --iso-8601`.sql 

Code (markup):

Open it up, replace:

• dbusername
• Password
• dbname

With the proper information. Note that there is no space between -pPassword, it's intentional.

Then replace "/path/to/backups/" with the actual path that you want to put them in. Make sure the directory exists.

Put the backup.sh file in the appropriate cron folder. I'm running mine weekly, so mine is in /etc/cron.weekly/. CHMOD it +x (chmod +x backup.sh)

Output of it is a database file, named (the date).sql.

Putting it in cron.weekly will run it every Sunday night at Midnight, and give you a file that looks like this:

[cron.weekly]# sh backup.sh
[cron.weekly]# ls /home/backups/
2006-01-13.sql
[cron.weekly]#

Code (markup):

Note: If you don't have access to /etc on your webserver, but have a crontab for your account at your host, you should use the absolute directory path and save it to a location within your host.

An example would be: /var/www/vhosts/yourdomain.tld/httpdocs/backups

Information on automating the SSH transfer process in general can be found here and is pretty thorough, but I haven't tested it yet.

Combining multiple backups into one single cron script:

If your other databases are all accessible from the same shell prompt and user, you can do this one of two ways. If whatever user you're using for cron has permission to run mysqldump, you can:

Put them all in different folders with the same name, like so. Make sure the target folder exists, it might bark at you if it doesn't. Basically just run the dump command however many times you need to run it, with the respective names/passwords on each line.

I put in a sleep 5 just to give a small pause between operations. All it does is tell the OS to pause for 5 seconds before running the next command. It's probably not necessary, but MySQL might become unhappy if you run one command directly after another - it just gives your CPU/Memory a chance to cycle if necessary.

#! /bin/bash
# Automated database datestamp backup

mysqldump --opt -Q -u dbusername1 -pPassword1 dbname1 > /path/to/backups/1/`date --iso-8601`.sql 
sleep 5
mysqldump --opt -Q -u dbusername2 -pPassword2 dbname2 > /path/to/backups/2/`date --iso-8601`.sql 
sleep 5
mysqldump --opt -Q -u dbusername2 -pPassword3 dbname3 > /path/to/backups/3/`date --iso-8601`.sql 

Code (markup):

Or, you can put them all in the same directory with tags in front of each to name them, like this. I'm just putting databasename_ in front of each as an example, you can put whatever you want in front of the `date text. In this example I'm just using vbulletin_ as the tag.

#! /bin/bash
# Automated database datestamp backup

mysqldump --opt -Q -u dbusername1 -pPassword1 dbname1 > /path/to/backups/vbulletin_`date --iso-8601`.sql 
sleep 5
mysqldump --opt -Q -u dbusername2 -pPassword2 dbname2 > /path/to/backups/wiki_`date --iso-8601`.sql 
sleep 5
mysqldump --opt -Q -u dbusername2 -pPassword3 dbname3 > /path/to/backups/photopost_`date --iso-8601`.sql 

Code (markup):

So if you're only allowed one cron job, just stack as many commands into it as you need.

Cheers.

 

Very helpful post. Rep added to you sir.