SymmetricDS in dev + prod workflow?

Question

Has anyone leveraged SymmetricDS in their development and production efforts so as to keep the environments synchronized?

We have a number of WordPress sites that need to be kept in synch. In our scenario we have a "master" wordpress project that is developed between a few developers and this master get's duplicated out on new deployments as well as pushed out to update old deployments. We have been considering using ManageWP or the Duplicator plugin, however it just is not optimal in really getting updates out in a tracked fashion that we'd like to see.

Our goal is to use Git + some kind of db synchronization (appears it will be SymmetricDS) in order to synchronize the code and databases while leaving the config files specific to the sites, similar to how Rails devs handle the deployment convention generally.

But with this, I was hoping to find someone who may be using SymmetricDS with some degree of success already in such a scenario who could share some light on how they have it set up so as to handle the custom database components that WordPress will have from site to site and dev site to prod site. It seems to be possible given the customization factor of SymmetricDS, but not yet clear.

Answer 1

I have done SymmetricDS synchronization with various content management sites using the wildcard feature to pick up new tables created by custom components. For example, you can put a trigger on tables named 'wp_*' and it will sync any tables that match. You can read about Using Wildcards to Sync Tables for more detail.

In one setup, we used a staging site where the users made changes, and then the changes synced out to the production sites over night. We changed the push/pull jobs to be a cron expression that ran the jobs overnight, like this:

job.pull.period.time.ms=*/60 * 1-3 * * *

In another setup, the users were editing content from different sites in production, but wanted the data replicated to all other sites. All the tables were in MySQL with primary keys based on auto increment. Rather than deal with conflicts when two users try to save a new article with the same next ID, we altered the auto increment on each site.

Site 1 could use IDs of 1, 101, 201, 301, and so on, like this:

auto_increment_offset=1
auto_increment_increment=100

Site 2 could use IDs of 2, 102, 202, 302, and so on, like this:

auto_increment_offset=2
auto_increment_increment=100

You can read about Options for Auto Increment in the MySQL manual.

Hope that helps.