Sharing drupal tables between sites - after the sites are already there!

by ekes

It's sometimes really cool to be able to share some of the tables between multiple Drupal sites. Just think your users have the same logins for example. It's pretty standard stuff there's a simple 'how to' here. But what happens when the sites are already up and running, and you hadn't planned this?

Well, I ended up in that situation. Taking a break from writing feed input and output stuff (I will blog about it sometime), I'm implementing an e-mail campaigning tool. It should be really nice as it is integrating into CiviCRM for the people who have sent the e-mails as well as for the people they should send them too. CiviCRM was already on one of the sites, but not the other, and they really want to share a lot of this information.

So. I dumped the two databases.

The first thing to do is to change the database prefixes. The easiest way I found of doing this was in the mysqldump just s/ALTER TABLE `\(.*\)`/ALTER TABLE (prefix_\1)/ and so forth.

Doing the same with both tables.

These can the be put into the database. But watch out! If you are going to share the users table you've just got a whole bunch of wrong uid's on content.

So next task, luckly here there were not so many users.

UPDATE node SET uid='newid' WHERE uid='oldid';

Also the sequences table needs to be shared what ever you are doing. You can't have the id's between shared tables being counted separately! I dumped both tables prefix1_sequences and prefix2_sequences. Then replaced all the insert names with prefix?_ as approprate and put them back into one line to insert back into prefix1_sequences (the shared table).

If anything else comes up I'll add it here!

Comments

Thanks for this

Thanks for this information!Nice stuff.....keep writing.