Using replication to make the move from MySQL to MySQL Cluster with minimal downtime!

Last couple of months I've got several questions regarding migrating from normal MySQL (InnoDB or MyISAM) to MySQL Cluster. Migration from normal MySQL to MySQL Cluster can not be done the usual way by "just" changing ENGINE from MyISAM or InnoDB to NDBCluster, the reason for this is because MySQL Cluster is not a traditional Storage engine in MySQL.

If you are new to MySQL Cluster and want to know more, please follow links bellow to get started with MySQL Cluster:

• Compare InnoDB and MySQL Cluster, go here.
• MySQL Cluster Evaluation Guide, go here.

Now to the interesting part - how to actually migrate from normal MySQL to MySQL Cluster?

The normal way to migrate from normal MySQL to MySQL Cluster would be:

1. Use mysqldump to make logical dump of your database.
2. Install MySQL Cluster, follow our manual here or follow Andrew Morgans blog to bootstrap a cluster using MCM here.
3. Import data to cluster using mysql client, there are two options here, either you import data into InnoDB tables and later change table handler to ndbcluster, second option is to first substitute engine in dump-file before loading data. I prefer first option, first import data into InnoDB tables and then change engine to ndbcluster using "alter table" command,  this way you can check constraints and also get arount tuning cluster for bulk loading.
4. Configure application (change IP) to start use MySQL Cluster.

However this procedure is time consuming and will cause downtime for your application.

Also you might want to run some tests before moving production into your new cluster platform.

So how to solve this - MySQL replication!

Before moving over our production to MySQL Cluster we want to:

• Run production load tests on our MySQL Cluster.
• Test our applications against a MySQL Cluster with live data.

And when we are done testing, we want to make the move to our new MySQL Cluster environment with minimal disruption for our clients.

Below you can see the new database architecture after we setup replication between production and MySQL Cluster.

The steps that needs to be done will not be described in detail since most of it is normal MySQL replication, which is straight forward and explained in detail in our manual here.

Procedure to set up replication from production to MySQL Cluster:

1. Enable binary logging for MySQL server in production.
2. Same as step 1-3 above but since we want to set-up replication, when doing step 1) remember to add flag "master-data=2" so mysqldump outputs binary log position into backup file.
3. Start replication from production to new cluster site.

So, by now we have a MySQL Cluster running as a slave to the production environment and getting all updates from production. We are now ready to move over production our load to cluster site at any given time. Run all your test and when you are ready, just move production to your MySQL Cluster installation and you are done.

Happy Clustering!