WordPress Database Synchronisation Between Servers – WPQuickies

In this lunchtime #WPQuickies, I talk about WordPress database synchronisation.

This topic is a bit technical so get ready for some information overload.

Let’s tackle the big question everyone wants to know.

The Question You All Want To Know Is…

“How do I synchronise my WordPress staging/development database with the live/production database?”

I see this question posted in forums and groups at least twice every week.

It’s a simple question with a rather complicated answer.

Some Database Related Words And Meanings

Often I find that those asking the question don’t understand the terminologies correctly so let’s cover that quickly.

Copy

A one-time duplicate snapshot of an existing database.  Thereafter, copy can contain different data from the original source database.

Clone

A one-time duplicate snapshot of an existing database.  The terminology suggests that the data is exactly the same as the original database, however, after cloning the copy can contain different data.

Replication

This is a process of frequent cloning from an original database to a new copy. The purpose is to create a distributed database system.  Frequency is determined by the systems manager and can range from every few minutes to hourly, daily, weekly or as needed.

Replication is a one-way process.  Usually termed “master” and “slave” where a single source of truth database is named as the “master”.  The “slave” databases are the result of the replication process.

You can use this system with WordPress using a plugin called hyperDB and you can speed up your site by load-balancing between multiple database copies.

Asynchronous

This is when data is sent out of sequence between two computers. This could mean that data is sent “out of order” or ad-hoc. The receiving computer has to determine how to correctly insert the data into its systems.

Synchronous

This is when data is sent in sequence.  The receiving computer expects the data received to be in the correct order/sequence to insert into its systems.

Synchronisation (Syncing)

Data synchronisation is the process of establishing consistency among data from a source to a target data storage and vice versa and the continuous harmonization of the data over time.

In layman’s terms, data can be added to a single cloned database and the system will ensure that the data is copied and synchronised across all the databases it manages.

Merging

Merging is a process used by synchronisation.

The database system must be able to determine which data is the most recent or “truth” in order to overwrite/update older data to support synchronisation.

This is pretty difficult and can result in conflicts where two or more data cannot be differentiated as to which one is the truth.

Transaction

This is a single database operation; a read, write, delete, update, query, etc.

Transaction Management

A system that looks after the database transactions, making sure they are completed and in the correct order.

Roll Back

Used by the transaction management system to undo one or more transactions (database operations).

Roll Forward

Used by the transaction management system to redo/action one or more transactions (database operations).

Tables

A table is a set of grouped data elements.

A database usually has multiple tables.

Columns

A table column identifies data by name and type: e.g. FirstName Type=text

A table usually contains multiple columns.

Rows

A row is one entry in a table where data is stored in in each column: e.g. FirstName, LastName, DoB, PostCode

Primary Key

This is a special column in a table with a unique identifier.  Usually this is number but it can be anything unique.

The primary key is used to be able to uniquely identify a row of data in a table.

Those are some pretty technical terms but I think it’s important to understand the basic operations of a database before answering the main question.

A Typical WordPress Designer/Developer Scenario

Here’s a typical scenario that WordPress designers and developers will find themselves in.

The client has an existing website.

The designer/developer makes a copy of the live website and database onto another machine to apply the new updates.

The new website and updated database is ready on another machine.

The live client website has received numerous updates during the project timeline and now has new data, not present in the copy.

The designer/developer then asks the question: “How do I synchronise my WordPress staging/development database with the live/production database?”

Files are easy – they can just be uploaded but how do you handle the database updates? 

Unfortunately, MySQL or MariaDB which most WordPress websites use doesn’t have a distributed transactional management system.

Database Table Primary Keys

What’s the problem here?

Do you remember the primary key?

This value must be unique and in the scenario of two databases synchronising together they must match between the database copies.

Here’s a snapshot of the wp_options table from one of my local websites.

The option_id field is the primary key and is an incremental number.

The problem is that the other database, the client’s live website, now has new data with their own primary keys, and some of those numbers may be the same as the data in our newly developed website database.

The two databases are out-of-sync with each other.

There is no transaction management system to determine the correct data; nobody knows which data to add, update, overwrite or delete.

Because WordPress writes so much mixed data to the database tables, specifically wp_posts and wp_options, it makes it nearly impossible to synchronise changes between out-of-sync databases.

There are distributed database transaction management systems available for MySQL, but the cost is out of reach for a typical freelancer.  Usually reserved for big enterprises.

Some Have Tried, Failed and Sort-of Succeeded

The WordPress company Delicious Brains who author the popular plugin WP Migrate DB Pro tried to create a service called Mergebot to tackle this problem.

They failed. Not because they are not very good developers but because the problem of synchronising MySQL databases is technically very challenging.

You can read about their journey and the issues in their blog post at https://deliciousbrains.com/syncing-wordpress-database-changes-merging/ 

WP Stagecoach claims to offer a database merging service, but merging data is very different from synchronising data, and the merging system itself has known issues https://wpstagecoach.com/kb/known-incompatibilities/. 

VersionPress https://versionpress.com/ also claim to have a solution based on storing database transactional data in Git, a popular development versioning tool.

However, their plugin is a “developer preview”.

The documentation clearly states it should not be used in production https://docs.versionpress.net/en/getting-started/about-eap/ Some big issues still there, I think, and the last update to the plugin was 2 years ago, suggesting development has been abandoned.

The service offered by WPMerge https://wpmerge.io/ seems to offer a solution.

However, their service needs to be installed and activated before any changes are made to either development or production.  It doesn’t work already out-of-sync databases.

You would need to install the service on the production server first before copying the site over to development.

The client probably isn’t going to be keen on you installing untested and resource heavy services on their live website.

It works by recording transactions triggered by the WordPress WP_Query class, which means it won’t store any data updated by native SQL calls that many plugins still use.

None of these systems handle conflict resolution either.

Solutions To WordPress Database “Synchronisation”

That’s enough about the complexity of synchronising databases – let’s look at some solutions to the problem.

Solution 1

Ask the client to put a hold on any updated to the live website.

This may not be possible for ecommerce or user generated content websites but it’s the simplest solution.

Solution 2

Limit and record the number of updates.

This is kind of like creating your own transactional record of what’s been updated on the live website.

You can ask the client to add items to a website.

You will need to add this content into your development website before pushing it up to the live website.

This can work well for small business websites.

It can also work for e-commerce websites as long as you have a way of exporting and importing the relevant data

The plugin WP All Import does a great job of exporting WooCommerce data and during importing, keeping the association between the data.

https://www.wpallimport.com/

Solution 3

Record and apply database updates like unit tests.

This is not going to work for non-developers.

The idea here is to update the database with scripts, rather than use the WordPress user interface.

This way allows you to apply those same updates to the other database, ensuring that the same data is present.

Although the primary keys may be different, this is not a synchronisation method.

Conclusion

There doesn’t seem to be a magic bullet solution for keeping two WordPress databases completely synchronised between two different environments.

The best you can do is to halt updates on the production site during your project and replace it with your new site when complete.

Or, try to record and merge new data in the production site into your development database copy just prior to launching.

If you have any other solutions that work for you, please let me know in the comments below.

#WPQuickies

Join me every Thursday at 1 pm Sydney time for some more WPQuickies – WordPress tips and tricks in thirty minutes or less.

Broadcasting live on YouTube and Facebook.

Suggest a #WPQuickies Topic

If you have an WordPress topic you’d like to see explained in 30 mins or under, fill out the form below.

https://forms.gle/mMWCNd3L2cyDFBA57

Was this article helpful?
YesNo

Leave a Comment

Your email address will not be published. Required fields are marked *

Keep In Touch With My Weekly Blog Email

A once-per-week daily digest of my posts from the week. No sales, no spam and I will not share your details with anyone else.

Wil

Wil is a dad, WordPress consultant, WordPress developer, business coach and mentor. He co-organizes the WordPress Sydney meetup group and has been on the organising committee for WordCamp Sydney since 2014. He speaks at many special events and contributes to the WordPress open source project. His likes are chillies, craft beer and electrogravitics.

Leave a Comment

Your email address will not be published. Required fields are marked *