Update: Added a drupal 7 version of the scripts. Works the same, but uses a different set of patterns to pull the database connection information out of the new settings.php format.
Update: Improved the list of skipped tables, and added support for prefixes.
About a year or so ago I wrote a set of backup and restore scripts for Drupal, running on MySQL. They have seen a lot of use since, with a few tweaks here and there, and I've come to rely on them heavily. I can't imagine developing without them.
Here is my backup script, and my restore script. Skip over the code for some additional explanation and usage hints.
You can download these files using the links at the bottom of this article.
Backup script:
#!/bin/sh # Drupal backup script if [ $# -ne 1 ]; then echo 1>&2 "Usage: $0 backup_file.mysql" exit 1 fi if [ ! -e "settings.php" ] then echo "Must run script in same directory as settings.php" exit fi #------------------------------------------------------------------ # grab necessary values from settings.php USR=`grep ^\\$db_url settings.php | sed -n 's/.*\/\(.*\):.*/\1/p'` PWD=`grep ^\\$db_url settings.php | sed -n 's/.*:\(.*\)@.*/\1/p'` DBN=`grep ^\\$db_url settings.php | sed -n 's/.*\/\(.*\).;$/\1/p'` HST=`grep ^\\$db_url settings.php | sed -n 's/.*@\(.*\)\/.*/\1/p'` PRE=`grep ^\\$db_prefix settings.php | sed -n "s/.*'\(.*\)';/\1/p"` # tables with transient data that should not be backed up SKIP="^${PRE}access$\|${PRE}accesslog\|^${PRE}cache\|${PRE}search_index\|${PRE}sessions\|^${PRE}statistics$\|${PRE}watchdog\|Tables_in" #------------------------------------------------------------------ # remove any existing data rm -f $1 # dump out the structure of all tables mysqldump -h${HST} -u${USR} -p${PWD} \ -d -q --compact --single-transaction \ --add-drop-table ${DBN} > $1 # dump the data, skipping tables indicated in skip list for TBL in $(echo "show tables" | \ mysql -h${HST} -u${USR} -p${PWD} ${DBN} | grep -v -e ${SKIP}) do mysqldump -h${HST} -u${USR} -p${PWD} \ -q -t --compact --skip-extended-insert \ --single-transaction --add-drop-table ${DBN} ${TBL} >> $1 done # MySQL doesn't like zeros in autoincrement columns and will screw # up the anonymous user record echo "UPDATE \`$PRE""users\` SET uid=0 WHERE name='';" >> $1
Restore script:
#!/bin/sh # Drupal restore script if [ $# -ne 1 ]; then echo 1>&2 "Usage: $0 backup_file.mysql" exit 1 fi if [ ! -e "settings.php" ] then echo "Must run script in same directory as settings.php" exit fi #------------------------------------------------------------------ # grab necessary values from settings.php USR=`grep ^\\$db_url settings.php | sed -n 's/.*\/\(.*\):.*/\1/p'` PWD=`grep ^\\$db_url settings.php | sed -n 's/.*:\(.*\)@.*/\1/p'` DBN=`grep ^\\$db_url settings.php | sed -n 's/.*\/\(.*\).;$/\1/p'` HST=`grep ^\\$db_url settings.php | sed -n 's/.*@\(.*\)\/.*/\1/p'` # don't drop these tables SKIP="Tables_in" #------------------------------------------------------------------ # drop all tables, in case something got added since the last backup for TBL in $(echo "show tables" | \ mysql -h${HST} -u${USR} -p${PWD} ${DBN} | grep -v -e ${SKIP}) do echo "drop table ${TBL}" | mysql -h${HST} -u${USR} -p${PWD} ${DBN} done # now I can load the script mysql --default-character-set=utf8 -h${HST} -u${USR} -p${PWD} ${DBN} < $1
I put them in Drupal's scripts directory. Run them from within a site directory, so they can grab your database information from settings.php. Here are some examples:
# make a backup of the site $ cd drupal/sites/mysite.com $ ../../scripts/backup.sh snapshot.mysql # restore a backup of the site $ cd drupal/sites/mysite.com $ ../../scripts/restore.sh snapshot.mysql
Be sure to take your site offline first, to avoid capturing an update in progress.
Once a week I take my site offline, run the backup script, and commit the resulting dump to a Subversion repository. It only takes 30 seconds or so, and provides a great incremental history of the website. And since Subversion only stores the differences between snapshots it uses very little disk space. Eventually I'll automate the whole process and run it every night.
I use the restore script to load the production backup on my development server, so I can test any site changes against the real production data. If I screw something up, it is a simple matter to reload the data and try again. This has saved a ton of time that would have been spent cobbling together test data or recreating sites from scratch. And come deployment time there's real peace of mind in knowing your changes have already been vetted against the production data.
Speaking of deployments, I don't roll out any changes manually. Instead, I have a custom module for each of my websites, and I code everything up in that module's install script. It takes a little longer to develop this way but it is automated and reproducible, which means less down time and no mistakes when I move it into production. It also means I have a record of all changes made to the site, in case I ever need to roll something back. And if I ever need to make the same change somewhere else I can cut-and-paste the code between modules.
The backup script makes this developing these install scripts easier: take a snapshot of the database, manually make the desired change, and then take another snapshot. Run a diff between the two snapshots and you'll see everything that changed in the database, which you can then code up as a series of db_query() calls. To test it, roll back to your last production snapshot and upgrade. If it breaks, fix it and try again.
If you use these scripts, leave a comment and let me know what you think. Especially let me know if you run into problems! Otherwise, I hope you find them as useful as I have. Enjoy!
| Attachment | Size |
|---|---|
| backup-restore-6.x.tar.gz | 1.1 KB |
| backup-restore-7.x.tar.gz | 1.22 KB |
Share
Can you make a screencast of you doing this a test site so we can see how it is done?
Thank you.
I used the backup script. I haven't tried a restore from it yet, but it backed up everything quickly and very nicely. My only comment, so far, is with a few details of your directions. First off, you might want to remind users that they'll have to chmod backup.sh to make it executable (I used chmod 755, but maybe that's not the best setting). Also, in your command-line example, the line for running the script, on my install at least, would be ../../scripts/backup.sh snapshot.mysql (you omitted the "scripts" directory).
Thank you very much for writing these and sharing them!
Thanks for the scripts, I'm successfully using both as part of an upgrade right now.
Somehow I missed the links to the actual scripts and instead copy n' pasted from the post above only to get a formatting error on line two of each script when I ran them. There's html formatting ( <b> ) on line 2 of each example, just delete the <b> on line two and they'll both work fine.
P
http://www.peerside.com
Thanks for the comments, folks. I've integrated your changes into the article.
What kind of site changes are you coding into your custom module install install script? File system changes or just database changes, e.g. new nodes and view modifications? What are some commands you use in there? I'm looking for best practices for deploying between development and production, and your method interests me.
For this site, I have almost nothing in there. I try to keep things pretty much "out of the box" with as few modules and as little configuration as possible. All configuration and validation is done by hand.
For the real production sites I've done in the past, the install script for the site specific module contains setup (enabling and configuring modules) and any subsequent database changes, using the db_query() or update_sql() commands. I also use the simpletest module to verify my changes when moving to production.
I keep the entire site filesystem in source control, usually Subversion, and deploy directly from there. Subversion takes care of any filesystem changes.
Hope that helped!
Thanks for this just came in handy on a clients server. Was going to use the Backup and Migrate module but this is much simpler.
- Sean Bannister
Could the NO_AUTO_VALUE_ON_ZERO mode be used with sql to avoid needing to fix the anonymous user record? I notice that phpMyAdmin offers a checkbox on import presumably for this same problem.
-Tony
That does look like it might do the trick, but I'd want to test the heck out of it before I put it into production. Next time I ramp up a new site I'll give this a try. If anyone else gives it a go in the meantime let me know how it works out for you and I'll update the article.
Thanks for sharing. It's a really nice script, but I had some problems with getting values from settings.php since we're using multiple databases like:
$db_prefix = array(
'default' => 'main_',
'users' => 'shared_',
);
So I changed/added some parts to it:
# grab necessary values from settings.php
MULTIPLE_DB=`grep ^\\$db_url settings.php`
if [[ $MULTIPLE_DB =~ array ]] ; then
USR=`grep ^[[:space:]].\'default\' settings.php | sed -n 's/.*\/\(.*\):.*/\1/p'`
PWD=`grep ^[[:space:]].\'default\' settings.php | sed -n 's/.*:\(.*\)@.*/\1/p'`
DBN=`grep ^[[:space:]].\'default\' settings.php | sed -n 's/.*\/\(.*\).,$/\1/p'`
HST=`grep ^[[:space:]].\'default\' settings.php | sed -n 's/.*@\(.*\)\/.*/\1/p'`
PRE=`grep ^[[:space:]].\'default\' settings.php | sed -n "s/.*'\(.*\)';/\1/p"`
else
USR=`grep ^\\$db_url settings.php | sed -n 's/.*\/\(.*\):.*/\1/p'`
PWD=`grep ^\\$db_url settings.php | sed -n 's/.*:\(.*\)@.*/\1/p'`
DBN=`grep ^\\$db_url settings.php | sed -n 's/.*\/\(.*\).;$/\1/p'`
HST=`grep ^\\$db_url settings.php | sed -n 's/.*@\(.*\)\/.*/\1/p'`
PRE=`grep ^\\$db_prefix settings.php | sed -n "s/.*'\(.*\)';/\1/p"`
fi
Just curious why in your backup file you specify using extended inserts with the -e option and then disable extended inserts with the --skip-extended-insert. Am I missing something here?
mysqldump -h${HST} -u${USR} -p${PWD} \
-e -q -t --compact --skip-extended-insert \
--single-transaction --add-drop-table ${DBN} ${TBL} >> $1
That's a good question; I missed that in the evolution of the script. Let me see if I can remember why I added those flags and sort it out properly.
Okay, I remember now: I turned off the extended inserts because it makes the files easier to diff. This aids with the development of install profiles: dump the database, then make a change to the site. Dump the database again and compare the results.
I also version my database snapshots in Subversion, and this makes it more efficient (smaller deltas).
So you are correct, the "-e" flag can be safely dropped as it is being ignored currently anyway. I will do the next time I make a change to the script.
I was looking over the mysqldump options and found a problem, of sorts. The --single-transaction option seems to be mainly used to get a consistent snapshot backup. However, the command-line for mysqldump mentions that --single-transaction only works with InnoDB tables. All drupal tables are MyISAM.
I just did a quick test and found that it doesn't seem to matter if the --single-transaction option is included or not. In my test, both dump files were identical.
Thank you for the scripts.
If you had to recover from a complete disaster, how would you handle recreating the drupal user and setting grants? Do you also suggest backing up the mysql database and versioning that in subversion?
Jordan
I'm not sure what you mean by "setting grants"? Are you referring to the database user?
To move my site to a new machine (disaster recovery) I simply create a new database (following the instructions in drupal's README), check out my code and latest database backup from Subversion, and restore the backup. Takes maybe 15 minutes total.
Yes, I do keep my database backups in Subversion. Since Subversion is only storing the deltas between each snapshot it doesn't take up much disk space, and it makes it easy to move the snapshots between test and development servers. I've never had to roll back the database to a previous version but it is nice to know that I can.
"I'm not sure what you mean by "setting grants"? Are you referring to the database user?"
Yes, I mean the drupal database user.
For the moment, I've been backing up our site by shutting down mysql and making a copy of /var/lib/mysql. I have also used that method to copy the drupal database between our test and production sites. After copying the database files, I just have to make a few minor changes to the database and it's ready for production use.
Whatever works for you, of course. Personally I prefer a text export I can version and diff. These scripts don't handle the DB permissions, but it would be trivial to write a setup script that did that (and which could then too be versioned).
Hi,
many, many thanks for this script.
I must point out that it doesn't work if the password in the mysql connect string in settings.php has characters that are url encoded.
Cheers,
cousin_itt