torsdag 17 januari 2013

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!

59 kommentarer:

  1. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, MySql online trainingamong the MySql in Hyderabad. Classroom Training in Hyderabad India

    SvaraRadera
  2. A confirmation of oracle training in chennai thankfulness is all together hadoop training in chennai for sharing the key data and its genuinely informatica training in chennai amazing.

    SvaraRadera
  3. Thanks for sharing amazing information about pega Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with pega. Pega Training in Chennai

    SvaraRadera
  4. Who wants to learn Informatica with real-time corporate professionals. We are providing practical oriented best Informatica training institute in Chennai. Informatica Training in chennai

    SvaraRadera
  5. QTP is a software Testing Tool which helps in Functional and Regression testing of an application. If you are interested in QTP training, our real time working. QTP Training in Chennai

    SvaraRadera
  6. Looking for real-time training institue.Get details now may if share this link visit Oracle Training in chennai

    SvaraRadera
  7. Hey, nice site you have here!We provide world-class Oracle certification and placement training course as i wondered Keep up the excellent work experience!Please visit Greens Technologies located at Chennai Adyar Oracle Training in chennai

    SvaraRadera
  8. Awesome blog if our training additional way as an SQL and PL/SQL trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog Green Technologies In Chennai

    SvaraRadera
  9. Nice site....Please refer this site also nice if Our vision succes!Training are focused on perfect improvement of technical skills for Freshers and working professional. Our Training classes are sure to help the trainee with COMPLETE PRACTICAL TRAINING and Realtime methodologies. Green Technologies In Chennai

    SvaraRadera
  10. Job oriented Hadoop training in Chennai is offered by our institute. Our training is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts. Hadoop Training in Chennai

    SvaraRadera
  11. I also wanted to share few links related to sas training Check this sitete.if share indepth sas training.Go here if you’re looking for information on sas training. SAS Training in Chennai

    SvaraRadera
  12. It was really a wonderful article and I was really impressed by reading this blog. We are giving all software and Database Course Online Training. Oracle Training in Chennai is one of the reputed Training institute in Chennai. They give professional and real time training for all students.
    Oracle Training in chennai

    SvaraRadera
  13. Oracle Training in chennai
    It’s too informative blog and I am getting conglomerations of info’s about Oracle interview questions and answer .Thanks for sharing, I would like to see your updates regularly so keep blogging.

    SvaraRadera
  14. Informatica Training in chennai
    This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    SvaraRadera
  15. Pega Training in Chennai
    Brilliant article. The information I have been searching precisely. It helped me a lot, thanks.
    Keep coming with more such informative article. Would love to follow them.

    SvaraRadera
  16. QTP Training in Chennai,
    Thank you for the informative post. It was thoroughly helpful to me. Keep posting more such articles and enlighten us.

    SvaraRadera
  17. There are lots of information about latest technology and how to get trained in them, like Hadoop Training in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this..

    SvaraRadera
  18. Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me.I get a lot of great information from this blog. Thank you for your sharing this informative blog.
    SAS Training in Chennai

    SvaraRadera
  19. I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Greens Technologies In Chennai

    SvaraRadera
  20. I was looking about the Oracle Training in Chennai for something like this ,Thank you for posting the great content..I found it quiet interesting, hopefully you will keep posting such blogs…
    Greens Technologies In Chennai

    SvaraRadera
  21. The best kits online trainings,thanks for sharing

    http://www.kitsonlinetrainings.com/sql-and-plsql-online-training.html
    http://www.kitsonlinetrainings.com/sql-server-dba-online-training.html
    http://www.kitsonlinetrainings.com/sql-server-developer-online-training.html

    SvaraRadera

  22. if share valuable information about hadoop training courses, certification, online resources, and private training for Developers, Administrators, and Data Analysts may visit Hadoop Training in Chennai

    SvaraRadera
  23. I would recommend the Qlikview to anyone interested in learning Business Intelligence .Absolutely professional and engaging training sessions helped me to appreciate and understand the technology better. thank you very much if our dedicated efforts and valuable insights which made it easy for me to understand the concepts taught and more ... qlikview Training in chennai

    SvaraRadera
  24. Thanks for sharing this useful informative post to our knowledge, Actually OBIEE training will mostly concentrate on real time issues rather than simply teaching you the OBIEE course. This will help you when you join the job and while attending interviews. Obiee Training in chennai

    SvaraRadera
  25. It is really very helpful for us and I have gathered some important information from this blog.
    Oracle Training In Chennai

    SvaraRadera
  26. Oracle Training in Chennai is one of the best oracle training institute in Chennai which offers complete Oracle training in Chennai by well experienced Oracle Consultants having more than 12+ years of IT experience.

    SvaraRadera
  27. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    SvaraRadera
  28. Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision.Informatica Training In Chennai

    SvaraRadera
  29. A Best Pega Training course that is exclusively designed with Basics through Advanced Pega Concepts.With our Pega Training in Chennai you’ll learn concepts in expert level with practical manner.We help the trainees with guidance for Pega System Architect Certification and also provide guidance to get placed in Pega jobs in the industry.

    SvaraRadera
  30. Our HP Quick Test Professional course includes basic to advanced level and our QTP course is designed to get the placement in good MNC companies in chennai as quickly as once you complete the QTP certification training course.

    SvaraRadera
  31. Thanks for sharing this nice useful informative post to our knowledge, Actually SAS used in many companies for their day to day business activities it has great scope in future.

    SvaraRadera
  32. Greens Technologies Training In Chennai Excellent information with unique content and it is very useful to know about the information based on blogs.

    SvaraRadera
  33. Greens Technology offer a wide range of training from ASP.NET , SharePoint, Cognos, OBIEE, Websphere, Oracle, DataStage, Datawarehousing, Tibco, SAS, Sap- all Modules, Database Administration, Java and Core Java, C#, VB.NET, SQL Server and Informatica, Bigdata, Unix Shell, Perl scripting, SalesForce , RedHat Linux and Many more.

    SvaraRadera
  34. if learned in this site.what are the tools using in sql server environment and in warehousing have the solution thank .. msbi training In Chennai

    SvaraRadera
  35. Its really helpful for me to understand. Thanks.If anyone wants to Learn visit this page sybase training In Chennai

    SvaraRadera
  36. i gain the knowledge of Java programs easy to add functionalities play online games, chating with others and industry oriented coaching available from greens technology chennai in Adyar may visit. core java training In Chennai

    SvaraRadera
  37. I have read your blog and I got very useful and knowledgeable information from your blog. It’s really a very nice article spring training In Chennai

    SvaraRadera
  38. fantastic presentation .We are charging very competitive in the market which helps to bring more Microstrategy professionals into this market. may update this blog . microstrategy training In Chennai

    SvaraRadera

  39. hai you have to learned to lot of information about c# .net Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with c#.net.
    C-Net-training-in-chennai

    SvaraRadera

  40. Looking for real-time training institue.Get details now may if share this link visit
    VB-Net-training-in-chennai.html
    oraclechennai.in:

    SvaraRadera


  41. hai If you are interested in asp.net training, our real time working.
    asp.net Training in Chennai.
    Asp-Net-training-in-chennai.html

    SvaraRadera


  42. Amazing blog if our training additional way as an silverlight training trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog
    silverlight-training.html
    greenstechnologies.in:

    SvaraRadera


  43. awesome Job oriented sharepoint training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    if you have more details visit this blog.
    SharePoint-training-in-chennai.html

    SvaraRadera

  44. if share valuable information about cloud computing training courses, certification, online resources, and private training for Developers, Administrators, and Data Analysts may visit
    Cloud-Computing-course-content.html

    SvaraRadera

  45. I also wanted to share few links related to oracle scm training Check this site.if share indepth oracle training.Go here if you’re looking for information on oracle training.
    Oracle-SCM-training-in-Chennai.html

    SvaraRadera
  46. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    Regards,
    Informatica training in chennai|Best Informatica Training In Chennai

    SvaraRadera
  47. This is my first time visit on your site and i have bookmark this for again visit.sas online training

    SvaraRadera
  48. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    informatica training in chennai

    SvaraRadera

  49. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    QTP Training in Chennai

    SvaraRadera

  50. Latest Govt Bank Jobs Recruitment Notification 2016


    Your way of describing the whole thing in this paragraph is actually pleasant, every one be able to effortlessly know it, Thanks a lot.|...........

    SvaraRadera
  51. In database computing, Oracle Real Application Clusters (RAC) — an option for the Oracle Database software produced by Oracle Corporation and introduced in 2001 with Oracle9i — provides software for clustering and high availability in Oracle database environments. Oracle Corporation includes RAC with the Standard Edition, provided the nodes are clustered using Oracle Clusterware.
    Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing clustering.

    In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and redo logs located on disk. The instance comprises the collection of Oracle-related memory and operating system processes that run on a computer system.

    Oracle RAC Training in Chennai

    SvaraRadera
  52. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    hyperion planning training in chennai

    SvaraRadera
  53. I am very impressed with the article I have just read,so nice.......
    jmeter Training in Chennai

    SvaraRadera
  54. Haryana HSSC Steno Typist Recruitment 2016

    Every day I always visit sites to obtain the best information for materials research I was doing...............

    SvaraRadera
  55. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Informatica Training In Chennai
    Hadoop Training In Chennai
    Oracle Training In Chennai
    SAS Training In Chennai

    SvaraRadera