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:
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:
- Use mysqldump to make logical dump of your database.
- Install MySQL Cluster, follow our manual here or follow Andrew Morgans blog to bootstrap a cluster using MCM here.
- 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.
- 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:
- Enable binary logging for MySQL server in production.
- 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.
- 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!