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!

Over 20 participants on our first MySQL user group event in Stockholm!

Last week we had our first Swedish MySQL User Meeting "SMUG" in Stockholm. The event was at Oracle engineering office at Söder and 20 users attended this first event!

I presented a talk on news from MySQL connect, it was a blend of MySQL 5.6 RC and MySQL Cluster 7.3DM, you can see slides on slideshare

Look out for our next SMUG event ;)

MySQL user group meeting in Stockholm 21st of November

MySQL User Group meeting in Sweden coming up!

Agenda is not finalized yet but we are going to talk about:
- News from MySQL Connect - MySQL 5.6 RC
- MySQL partitioning
- Handling character set and collations

Location:
Munchen Bryggeriet Soder Malarstrand 57B (6th floor)
Stockholm, 118 25

Time: 15:00 - 18:00

You can register for event here

See you there!

Finally we have a MySQL User Group in Sweden!

I have always found it strange that we do not have a MySQL user group in Sweden - this is the country where the MySQL saga started.

Therefore I am delighted to announce that since today we have a user group in Sweden and I hope it will be a active one!

If you want to join our user group in Sweden, join the group here!

High Availability Solutions for MySQL

Join us for our High Availability Solutions for MySQL seminar in Helsinki on Thursday May 10th 2012 to better understand how to achieve High Availability with MySQL.

During this free Oracle seminar we will review the various options and technologies at your disposal to implement highly available & highly scalable MySQL infrastructures, as well as best practices in terms of architectures.

We will examine the new High Availability features in MySQL 5.5, MySQL Replication, MySQL Cluster and more, including the upcoming Oracle VM Template for MySQL Enterprise Edition as well as what’s new in the development releases MySQL 5.6 and MySQL Cluster 7.2.

Read more and register here

Want to become MySQL certified - join our upcomming training!

Upcoming MySQL Training - join the MySQL trainig at Oracle University!

MySQL for Database Administrators:
6 Februari, 19 Februari, 27 Februari, 12 Mars, 19 Mars, 16 April

MySQL Performance Tuning:
24 Januari, 14 Februari, 27 Mars, 29 May, 26 June

Click here to register!!

Oracle Breakfast Seminar: High Availability Solutions for MySQL in Stockholm on Thursday February 23rd 2012

Free Oracle Breakfast Seminar: High Availability Solutions for MySQL Stockholm, 23rd February 2012 Web-based and business critical applications must typically be available 24/7. In addition to being very costly due to lost revenue opportunities, downtime can be extremely detrimental to customer loyalty, and present regulatory issues if data is compromised. Join us for our High Availability Solutions for MySQL seminar in Stockholm on Thursday February 23rd 2012 to better understand how to achieve High Availability with MySQL. During this free Oracle seminar we will review the various options and technologies at your disposal to implement highly available & highly scalable MySQL infrastructures, as well as best practices in terms of architectures. We will examine the new High Availability features in MySQL 5.5, MySQL Replication, MySQL Cluster…and more, including the upcoming Oracle VM Template for MySQL Enterprise Edition as well as what's new in the development releases MySQL 5.6 and MySQL Cluster 7.2. MySQL powers 9 of the world's top 10 Web Properties. By attending this event you will gain insights into real-world HA architectures, and better understand how to determine and implement the MySQL High Availability infrastructure best matching your requirements. Space is limited so Register Now!