I am running several Drupal 7 multi-sites that share same code base as well as the same database with prefixed tables per multi-site (some tables are shared too).

I chose this setup, because i need several sites, each representing a region. They share the same code but not the same content. (This is why i didn't chose Domain Access). The reason, why all multi-sites share the same database instance with prefixed tables is because i need cross-multi-site queries to aggregate global information. A context-switch within one database (by changing the table prefix whith db_set_active) is much "cheaper" than initializing a new database connection for every of the N regions.

Now I need to sync the data of one multi-site with the data of another multi-site within the same database. I thought Drush could easily do the job with:

drush sql-sync [from multi-site1] [to multi-site2] 

But i recognized, that the sync is done per database and not based on prefixed tables... That sounds logical!

So the command above would sync *[same_database]* with *[same_database]*, but what i need is a script that runs like this:

sync [all prefixed1_ tables] with [all prefixed2_ tables] within same database

Now my question: Has anyone worked on synchronization of multi-sites within the same database before, or has anyone a hint on best practice for my synchronization-needs?

Edit: I found out, what my needs really are. Drush's sync is not a real "sync" it is more like a "export from and import to". So it is quite more simple than a complete synchronization-task with "insert", "merge" and "delete". So i am going to write my own drush's script to fulfill my needs.

  • "Best" is a weasel word. Best for what? Do you want to keep many branches of one devel codebase together? Keep testing site in the same database you use for production? What's your reason to have multiple sites with same codebase and content?
    – Mołot
    May 29, 2013 at 12:23
  • Sorry for my vague formulation on best practice, i edited it. The reason for my setup is well evaluated and i can't change it anymore. May 29, 2013 at 12:30
  • I'm not accusing you bad organisation and I do not advise to change it. It's just that without knowledge what those sites are to each other no one will be able to reliably answer about practices. For example, if one of the pages is production, and second is preproduction for testing content, and you need to update 1, 2 nodes at time, solution will be totally different than if one page is devel branch X and the other devel branch Y and you regularly delete, create and synchronize 10k nodes.
    – Mołot
    May 29, 2013 at 12:34
  • Sorry for that, i didn't meant to offend you with my short comment. I go and explain myself and edit my question once again. May 29, 2013 at 12:38
  • You did not offend me. I'm just trying to understand what you need to try to provide you with answers.
    – Mołot
    May 29, 2013 at 12:40

2 Answers 2


sql-sync does not support prefixed database tables. There is work going on in the issue queue to support removal of cache tables via wildcard expressions. Perhaps this code could be adapted to support prefixed tables as well, if a motivated contributor would write and submit a patch.

  • Same question was already discussed on drush's project page as well: drupal.org/node/625870. Anyway, i accept this answer, because i have to do it myself ;) May 29, 2013 at 14:27

Your safest, if not fastest, bet is to use Feeds. You can export data that needs to get synchronized from one site using Views, maybe Views Data Export. This little tool will create XML. It can be created on cron run and stored on your server's HDD. Then Feeds can use it to import nodes that needs importing.

Direct database manipulation is faster, but you are asking for trouble with node IDs and revisions, unless all you want is 1:1 content copy.

  • Thanks for your advise on Feeds, I am going to use it for cross-region content exchange as well... but this happens on demand, triggered by content-authors, who can import content from other regions to their own region. Beside that, yes, i need a 1:1 content copy that ... however... fulfills the needs of drush's regular sql-sync job. I am going to edit my question again... May 29, 2013 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.