By teneighty on

I'm putting together a disaster recovery plan to make sure my site can be restored and/or easily moved to another host. I want to do some form of automated incremental daily DB backup and I was wondering what other people out there do?

I also want to backup the drupal modules, etc, since I modify things a fair bit, and by backing it up, it means I can quickly restore or move the site as need be. Its also useful for making a development site when needed.

So - what's your stategy to keep your site backed-up and ready to restore? Are their particular scripts or tools that you use to do this? Do you burn your backups onto CD, or do you use a script run by crontab to mirror the site somewhere else, or something else altogether?

Rob

Comments

singularo’s picture

singularo
Location Adelaide, AUS
commented

We use a script like this (user/pass changed) to dump all tables except the cache/search/logs tables as below, then rsync the lot over to another server.

#!/bin/sh
#
# table_build.sh - dump all required tables structures, 
# followed by all required table data to a file
#

rm -f ~/drupal.dump
mysql -uuser -ppass drupal -e 'show tables' | grep -v -e "access\|access_log\|search\|statistics\|watchdog\|cache\|url\|Tables_in_drupal" > ~/table.lst

mysqldump -uuser -ppass -d -e -q --add-drop-table drupal >> ~/drupal.dump

for i in `cat ~/table.lst`; do
    echo "Dumping $i"
    mysqldump -uuser -ppass -e -q --add-drop-table drupal $i >> ~/drupal.dump
done

I figure if things are bad enough to be using the backup, then you wont really be worried about losing some stats, and adding those tables back in makes the dump a lot bigger.

You can compress the dump, but then rsync takes longer as the .gzip/.bzip2 version takes more work to synchronise.

I recently tested this by going to a new drupal version on a new server, and it worked fine, except the search engine db didn't get re-created. But since the new search is almost ready?, I just recreated it manually.

The second "--add-drop-table" in the loop isn't really needed.

--
http://singularo.com/

Rick Cogley’s picture

Rick Cogley commented

Here's an alternative script I am using which seems to work well for me. Very similar technique to the above but uses a variable to hold the tables instead of an external file. 

#!/bin/sh
#
# dumpdrupalwithexclusions.sh - grab all tables except grepped out
# and dump to a file
#

# Delete dump
rm -f ~/backups/drupal01.dump

# Dump structure to file
mysqldump -uMySQLadminAccount -pMySQLAdminPassword -d -e -q --single-transaction --add-drop-table DrupalDBname >> ~/backups/drupal01.dump

# Dump tables with data except excluded tables and append to file
for TARGETTBL in $(echo "show tables" | mysql -u MySQLadminAccount -pMySQLAdminPassword DrupalDBname | grep -v -e "access\|access_log\|cache\|search_index\|statistics\|watchdog\|Tables_in_")
		do
			echo "Dumping ${TARGETTBL} ..."
			mysqldump -uMySQLadminAccount -pMySQLAdminPassword -e -q --single-transaction --add-drop-table DrupalDBname ${TARGETTBL} >> ~/backups/drupal01.dump
		done

Some notes:

    The --single-transaction switch tells mysqldump to dump as a snapshot, which is said to be better for integrity. There is apparently a chance that a table being updated during the dump could cause you trouble later.
    The first mysqldump uses -d, which tells it to not get row info. Basically you just get structure. If you run this separately, it yields a 30K file. Removing the -d dumps the rows as well, and that brings the size of the dump up to about 1MB.
    Excluding the various tables brings the size down from 5MB to about 1MB on a fairly new install with just a bunch of quotes (fortunes) loaded and a few pages of content.
    To use, the above (of course) would be saved to a file (I use ~/utils to store scripts) and you need to chmod 755 it so it is executable. I can run it manually from my shell using ./scriptname.sh
    Once the script is working, use cron to schedule it.

Improvements needed -

    need to abstract some of the above into variables
    could name the dump with a date if you are paranoid about your rsync or ftp (to a different server) not working.

Hth.

Rick Cogley :: rick.cogley@esolia.co.jp
Tokyo, Japan

Rick Cogley’s picture

Rick Cogley commented

Added an updated script with variables and the ability to loop through multiple Drupal db's. See this comment in the Drupal handbook -
http://drupal.org/node/434#comment-27052

Rick Cogley :: rick.cogley@esolia.co.jp
Tokyo, Japan

Rick Cogley’s picture

Rick Cogley commented

Very nice perl script for doing mysql backups, with table exception and many other features.

http://worldcommunity.com/opensource/utilities/mysql_backup.html

Rick Cogley :: rick.cogley@esolia.co.jp
Tokyo, Japan

kbahey’s picture

kbahey commented

I wrote a few scripts to automate the backup process for drupal, and recreate it on my home server (all this is Linux based, but the concept can be applied to Windows with some work).

From cron on my hosting web site, I run a dbdump script. All this does is does a mysqldump of the entire database, which is for several domains, using db prefixes. Then it bzip2 compresses them, and sends an email that it was successful or not. The scripts goes and extracts the database name, the user name, the password from the conf.php file so as not to hard code them.

Another script on my home server goes and downloads the above file and deletes it from the hosting server.

Another cron job does a tar bzip2 compress of the hosting public_html (or www or whatever its name is) to get all the drupal scripts, images, ...etc.

Then another script on my home server downloads and deletes this tar bzip2 file, similar to the other one.

On my home server, I have a script that cleans up the test database, and then loads the database from the dbdump backup. The script copies a special version of .htaccess, and several conf.php files that are specific to the home machine.

Once this is done, I am good to go with the testing domain names.

If I need to move to another host, or recover from a disaster, then it is a matter of uploading the tar backup, bunzip'ing it, uploading the dbdump backup, bunzip'ing it, then going to the phpmyadmin and doing a load of the database.

Hope this helps.

--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba

patatti’s picture

patatti commented

RAID for local harddrive data protection. For offsite storage, just dump the database with a cron job, then rsync the database dump plus the other files you want to another machine at the same site (with RAID as well) or to an offsite data backup server (RAIDed of course). If you do all of that, you will be protected from single harddrive crash, single machine crash, and single site crash. I've never done it, but that would be my maximum paranoia approach. It is even better when you have hot-swappable harddrives if you can afford those machines, because you won't need to put a machine out of service while you replace the harddrive.

There is no "if" in "when a harddrive crashes".

javanaut’s picture

javanaut commented

I use mysqldump for all sites hosted on the server, then use rsync to make an offsite backup. For "important" sites, I version each day's updates (db and code) using RCS's 'ci' command. This is done whether the code is finished or not.

For sites requiring very quick recovery (using an almost mirror copy of the live database), I use MySQL's replication feature and pull a slave feed off of the production server onto a live backup server. Not quite realtime failover, but close enough for most of the work I do.

media girl’s picture

media girl commented

There might be demand for a sort of "poor man's backup" that works like poor man's chron plus the database module, with default settings, date/time tagging, to back up to a designated location, online (with ability to set default logon permissions) or local (for locally dedicated hosting.

Talk is cheap. This is only an idea.

--
mediagirl.org

robertdouglass’s picture

robertdouglass commented

the dba module already offers the code to create the backup. Seems like a cron hook would be easy to add. So making the db backup would be taken care of. Moving it off location, though, is another issue, and would probably have to be done with a script as discussed above.

- Robert Douglass

-----
www.robshouse.net
www.webs4.com

Rick Cogley’s picture

Rick Cogley commented

Hi - Hmm, interesting. I have not tried out the DBA module, but if the backup URL is relatively simple (i.e. no session info required etc) and you can specify somehow that the backup should be stored in /some/folder then you can make cron run it using the "wget" command.

Rick Cogley :: rick.cogley@esolia.co.jp
Tokyo, Japan

green monkey’s picture

green monkey commented

I’ve seen a different CMS that had a Backup hook in the Admin section that was a manual feature and also displayed the last time a Backup was made, as a reminder. Maybe there could be a toggle for Cron / Manual. This also had a restore from: [Browse] feature.

With 4.6 coming up soon, the timing couldn’t be better.

Subtle module request. :)

thanks
Jeff

kbahey’s picture

kbahey commented

I think that a cron hook would be more appropriate in the backup module itself.

The backup module would then call the dba functions necessary to do the backup.

--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba