Migrating databases...?

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
hortitude's picture

I was looking into best practices for migrating databases from development to production and ran across this: http://drupal.org/node/205366

I was just surprised to find a "deprecated" tag near the top. Any ideas why this is deprecated?

Apologies if this isn't the best group for this post..

Thanks!

Comments

Migrating datbases..?

hortitude's picture

Is this the preferred mechanism? http://www.dave-cohen.com/node/1066
Seems like patching core isn't really the best thing to do.....

Does anyone have any ideas on best practices for this?

Backup Migrate module?

cleanthes's picture

I've had great success recently with the Backup Migrate module. It will export your test server to a SQL dump or even better to an Amazon S3 dump, and then on the live site you simply restore from the same file / S3 bucket. Easy Peasy. It also gives you automated scheduled backups as an extra bonus.

I did have one minor issue where a View didn't transfer properly, but I think that was probably my fault not the module's. Up until that it has worked flawlessly.

The only thing I've had to make sure of is that the /files/ path used on the test production and production sites is the same. In a single-site setup this should be easy, but in a multi-site setup I've just been creating a symlink from the actual /files/ folder to a 'shorthand' version which I can share across the test and production environments:
Test site: siteX.mytestserver.com
Production site: www.siteX.com
(Assuming these are on separate servers)

On Test, my multi-site setup is
/usr/share/drupal6/sites/siteX.mytestserver.com
I have a symlink:
/usr/share/drupal6/sites/siteX -> /usr/share/drupal6/sites/siteX.mytestserver.com

On Production my multi-site setup is
/usr/share/drupal6/sites/www.siteX.com
I have a symlink:
/usr/share/drupal6/sites/siteX -> /usr/share/drupal6/sites/www.siteX.com

Hence I can set the same 'files' directory (/sites/siteX/files) for both sites, and when I transfer from test to production the images / files etc are still loaded OK.

And of course

cleanthes's picture

... Backup Migrate module allows you to go back the other way. If your customer has added a load of content, and now wants a whizzy new feature added, you can restore your test environment from a live Backup, make the change and restore back the other way (ensuring of course your customer makes no changes in the meantime, and with the caveat that some stats/webform submissions might be lost, so you'd want this to be as small-a-window as possible - still, safer than developing on the production site)

The only thing I've had to

bleen's picture

The only thing I've had to make sure of is that the /files/ path used on the test production and production sites is the same. In a single-site setup this should be easy, but in a multi-site setup I've just been creating a symlink from the actual /files/ folder to a 'shorthand' version which I can share across the test and production environments

It's worth noting that by sharing the same files folder you get all the benefits described by cleanthes, but you also end up with testing cruft in your production files folder. Example: lets say you are creating a bunch of photo galleries to test newfeatureX on your test environment. Now all those jpgs are in the files folder used by your production site. Not the end of the world, but worth considering.

We usually just rsync our files folder from prod down to testing.

SUMMARY
Cleanthes's method is faster but IMO cruftier ... my method adds an extra step but keeps the files directory cleaner. Both valid.

Well, actually...!

cleanthes's picture

I agree about the cruft, if you sync files from Test->Production, but that wasn't really what I meant (I would generally do the same as you to get files to/from Production)

The main point was about the naming of the files folder. In a multi-site environment, you'll (probably, in my case at least) want each site to have its own files folder name, and you'll need this to be within the right site folder. When you sync the database (using backup migrate, or using SQL dump), it will include the site's 'file' folder name in its settings. Therefore the one on Test needs to be the same as the one on Production, otherwise you'll find that files on Production will not load.

Test structure = sites/siteX.mydevelopmentserver.com/ 'Files' folder in Drupal admin is set to 'sites/siteX.mydeveloper.com/files/'
Production structure = sites/www.siteX.com/ 'Files' folder in Drupal admin is set to 'sites/www.sitex.com/files/'

When you sync using Backup & Migrate to Production, the site will work, but all files will try to load from sites/siteX.mydeveloper.com/files NOT from sites/www.siteX.com/.

The way to avoid this is to do what I suggested, and ensure both Test and Production site have a sites//files folder which is the same name. Because of the way multi-site works, you'll need to do this with symlinks (Although actually I just realised you could put your files folder outside of the site folder..., meaning you don't need symlinks)

Backup / Migrate module

hortitude's picture

The module just seems to me like it does well for for production --> dev but not really the other way around.

It doesn't seem to really make sense to take down my site while I import a DB from dev.

What is really needed is to be able to have a couple different db profiles:

  1. Base install (modules enabled etc)
  2. #1 plus configuration (e.g. created a node for organic groups and created content types)
  3. #2 plus instance data (e.g. here are the three groups that I will have configured on my site)
  4. #3 plus user data (now I have 5 users that are members of a group)

Ideally when we want to add a new module we would be able to install it at level 1 and have it propagate to the other levels.

we've been having a lengthy

bleen's picture

we've been having a lengthy discussion about this in the NYC Group on g.d.o: http://groups.drupal.org/node/43320

See Drupal handbook

AFowle's picture

There are many posts on migrating Drupal on the internet in general and all over drupal.org. I found them incomplete, so I added an entry to the handbook about two weeks ago. See http://drupal.org/node/679054. Seems the logical place to me. It covers all the issues I had, but I'm not boasting, there may well be other things to consider - so go edit it!

To things

emptyvoid's picture

Table wizard and migrate modules.

Robert Foley Jr
Solutions Architect
http://www.robertfoleyjr.com

Table Wizard and Migrate

rootdownmedia's picture

Table Wizard and Migrate modules looks amazing. At first glance, seems like it solves the development > production database migration process by sync'ing database changes for you.

How does it play with the Features module? Do you even need Features when you have these two modules?

--
ROOT DOWN!
Design & dev specializing in music industry
Brooklyn, NY