Automated MySQL Datestamp Backup using CRON via shell

Chris-777 · #1 05-05-2007, 07:26 AM

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

Code:

#! /bin/bash
# Automated database datestamp backup
mysqldump --opt -Q -u dbusername -pPassword dbname > /path/to/backups/`date --iso-8601`.sql

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:

Code:

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

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
Ideally this would work well in conjunction with a script on a local box (assuming your site is hosted remotely) that could shell in and download the backups automatically as well. I'll try and update this with exact instructions on how to do that if I can.
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.

Code:

#! /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

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.

Code:

#! /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

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBSEO crashing new server	MentaL	Troubleshooting	20	12-11-2005 07:55 PM

SEO

Automated MySQL Datestamp Backup using CRON via shell

Automated MySQL Datestamp Backup using CRON via shell

Similar Threads