Backing up a Drupal Database

......Nick, once again another helpful post. Cheers dude :-)

You could save yourself the hastle and let the backup and migrate do the work for you. Seriously- is there any good reason to not use that well supported module/method?

Not sure I follow you. What "backup and migrate"?

http://drupal.org/project/backup_migrate

Nice script...
On a database of 0.62GB it saved 0.13GB or about 20% :-)

backup & migrate module is only good at small database, if you have node_revision table larger than 100mb, you will understand what I said.

It would be nice if you could
1. add datetime to the name of DBFILE
2. Delete older DBFILES when the count of DBFILES is greater than X. This gives flexibiliy at whatever frequency you want to dump the DB it it will always delete oldest.

You could achieve option 2 using a combination of find, xargs and rm I would imagine. As for option 1, a timestamp could be handy.

Thanks for the script. I was just about to write exactly same thing for one of my sites when I saw your script. I made one small modification to make it a bit more generic:

if [ $# -ne 3 ]
then
  echo "Usage: `basename $0` username password database"
  exit
fi
 
# Define DB Login from the commandline
USER=$1
PASS=$2

# Get the database from the commandline
DB=$3

My version does not use prefixes at all and there is still no error checking. It's quite enough for my purposes…

Nice script.
I have done something similar in Python, (but a bit more sophisticated in deciding what to back up). I generally end up with a backup of around 25% of the size I would have had.
This post is about my whole backup strategy, and the python script is the last attachment: http://drup.org/drupal-backup-script

I created a bunch of helper scripts for managing the database, including dump, erase, restore and merging databases. The dump script is quite similar, but it reads settings.php for the connection settings, and you can set your table preferences in an array in a settings file.

http://drupal.org/project/dbscripts

I created a similar script for similar purposes.

It takes this a step further and takes only a directory as an argument. The directory is the document root for a Drupal multisite install. It then looks for the settings.php files and deduces the database name, user name and password.

get_parms()
{
  CONF=`get_conf`

  grep "^\$db_url" $CONF |
        sed -e 's?^.*//??' -e 's/@localhost//' -e 's/";//' -e "s/';//" -e 's?/?:?' |
        awk -F: '{printf ("DB_USER=%s\nDB_PASS=%s\nDB_NAME=%s\n", $1, $2, $3)}'
  grep "^\$base_url" $CONF |
        sed -e 's?^.*://??' -e "s/';.*$//" -e 's/";.*$//' |
  awk -F: '{printf ("DOMAIN=%s\n", $0)}'
}

eval `get_parms`

It then does what yours does, excludes access_log, cache, watchdog, and the like using these more granular options:

OPTS_DATA="--add-drop-table --add-locks --all --quick --extended-insert --disable-keys --single-transaction"

OPTS_STRUCT="--add-drop-table --add-locks --quick --disable-keys --single-transaction --no-data"

Nice one Khalid!

I'll have to take a look into the 'single transaction', 'disable keys' and 'extended insert' options. They sound like a big performance boost.

Thanks for all the comments guys!

A good idea is maybe to compress the backup file (e.g. a tar.gz), it will decrease the size even more.
If you have a lot of transactions on your db's, it would be a good idea to switch to the InnoDB engine instead of the MyISAM engine in mysql. Then you could add the --single-transaction to the mysqldump command, which will take the backup in a single transaction, without table locking.

Just my 2cts.

Grtz.

Or just use Demo module.

At Krimson we use the drupalsqldump script made by Drumm as a basis for our backup and migration scripts. See: http://cvs.drupal.org/viewvc.py/drupal/contributions/sandbox/drumm/tools/drupalsqldump.sh?revision=1.2&view=markup

In a multisite setup, you can give the multisite name as a parameter, and the script will find the mysql credentials. Very useful!

Because we also upload the dump in our SVN repository, we also sort the data (to limit the number of changed lines)

function SqlDumpData {
        drupalsqldump.sh $1 --no-create-info --skip-opt --set-charset --comments=0 | \
            egrep -v "INSERT INTO \`(accesslog|cache|cache_.*|search_.*|sessions|temp|watchdog)\`" | sort > $2
}
           
function SqlDumpStructure {
        drupalsqldump.sh $1 --no-data > $2
}
[...]
echo Dumping Data to $datafile
SqlDumpData $siteslocation/settings.php $datafile
echo Dumping Structure to $structurefile
SqlDumpStructure $siteslocation/settings.php $structurefile
[...followed by some cleanup and upmload to svn...]

We wrote a (somewhat dated) post about it on http://krimson.be/en/bash-script-backup-your-drupal-site-and-database (the script has evolved and improved since then)

So basically you dump the structure into one file and the data into another...

I like that idea - although I cant imagine a situation where you'd want to restore a database without one or the other. Does the data dump include a "truncate"?

It also looks like that script doesn't take into account DB table prefixing...

There has been some great suggestions here guys - thanks!

I have a very similar script and found out the hard way that it trashes the record for user 0 (the anonymous user). It seems MySQL doesn't like putting a zero into an AUTOINCREMENT field. Ugh. If you've ever done a restore, check to make sure you still have a user 0.

I've posted my scripts, which include a workaround for the problem, at http://industriousone.com/drupal-backup-and-restore (it also lists some other tables to exclude, gets the connection info from settings.php, and adds more flags to reduce the dump size even more).

backing up the database is only part of the state. You also need to capture the files and the current revision of all installed contrib modules/themes/etc.

See my suite of Drupal scripts (includes backup) at http://svn.toddgee.com/drupalScripts

I like the idea about paring off the cache tables tho... might have to lift that one. ;^)