Migrating multi-site shared database data between servers is now easier with Jetendo CMS

Fri, Jul 12, 2013 at 3:55AM

Most web applications are built with database tables that have a single auto incrementing integer for the primary key.  This gives great performance and it's very easy to do with MySQL since it's just a checkbox option in most MySQL admin apps.

When you start storing data for multiple unique web sites in the same table, you need to add a site_id to be able to isolate them all.  For many years, my application has used a site_id like this since I want all of the sites to share one database so they have the best performance, and are easier to manage.

So site A may create blog ID 1, and then site B will create blog ID 2.  All the sites will be incrementing at the same time causing a lot of bigger numbers and data that is harder to separate.

Sometimes you want to be able to migrate one of the web sites to another server or avoid having to migrate all of the web sites at once.  You could write a bunch of queries to do this manually, but if new data is inserted on the new server and the old server, you'd have duplicate key errors that would be tedious to resolve.

How to make migration easier?

For a long time, I have been rewriting the database to support a compound primary key index.

This mean that site_id + table_id are combined to form a unique number.

So now when site A creates a blog article, it will have ID 1, and site B will also have ID 1.

I was able to do this by first disabling the auto_increment feature on the primary key field, and then by writing a MySQL trigger that performs the auto increment logic during an insert operation automatically for all of the tables that have a site_id column.

Here is an example of the trigger currently used on the blog table:

USE `zcore`$$
DROP TRIGGER /*!50032 IF EXISTS */ `blog_auto_inc`$$
CREATE TRIGGER `blog_auto_inc` BEFORE INSERT ON `blog` 
 IF (@zDisableTriggers IS NULL) THEN
 IF (NEW.`blog_id` > 0) THEN
 SET @zLastInsertId = NEW.`blog_id`;
SET @zLastInsertId=(
SELECT IFNULL(MAX(`blog_id`)+1,1)
FROM `zcore`.`blog`
WHERE `blog`.site_id = NEW.site_id
SET NEW.`blog_id`=@zLastInsertId;

Another challenge if you do this is that "select last_insert_id() id" will no longer work because that depends on the auto_increment feature. This is why the trigger is setting a session variable called @zLastInsertId. All insert statements in my application were updated to use "select @zLastInsertId id2, last_insert_id() id" instead. I wrote code that keeps track of which tables have a site_id column, and when they have a site_id column, the "id2" value is used, when they don't, the "id" value is used for determining the inserted id.

I also added the "IF (@zDisableTriggers IS NULL) THEN" logic so that insert triggers can be disabled when you are restoring data using LOAD DATA INFILE. You can use "SET @zDisableTrigger=1" to disable to insert trigger for the current session only, and then "SET @zDisableTrigger=NULL" after your other SQL is complete. Other mysql sessions that are inserting to the table at the same time will still execute the trigger.

This approach has been in use on the test server for several months now while I finished rewriting all the over 1500 queries in Jetendo CMS to use the newest features I made for database queries.

Self-healing is built-in for this now

Because this configuration is uncommon and could be easily broken or forgotten about by developers, I wrote a script that is able to detect mistakes in the database configuration, such as missing or incorrect triggers, and incorrect unique / primary index.  Now to make sure your database is compatible, you can simply run this script from the Jetendo Server Manager and it will fix everything and tell you what to do it if can't.

Automatic migration between same version Jetendo CMS servers is coming soon

Now that this work has been fully completed and applied to the production server, I can more easily handle migration of sites, without having to worry about everything moving all at once.   I will have a 1 click process for syncing the database between two server completed soon.  Update 7/14/2013: I finished building the site backup & import scripts and it is able to create sites that didn't exist yet or update existing ones.


Bookmark & Share