currently I am running MySQL 5.0.80 on windows server 2003 (x64), 64gb memory, I have a master and 2 slaves. It's a large 400 gb Database. I am setting up all new hardware (SAN + dedicated MySQL servers with 256 gb memory running windows server 2008 r2. I need to come up with a good plan for moving everything over while minimizing downtime. Is it absolute truth that you cannot go from 5.0 to 5.6 even when using mysqldump to move the data? I was originally hoping to run mysqldump during off hours from master (don't fully trust the slaves data integrity), then make master available to users again while I source the sql files into a new 5.6 server and use it as a slave for a day or so until I knew all was well, then promote to master. Not sure that is feasible because of the jump in versions as well as the replicating (temporarily) from 5.0 to 5.6. Does anyone have suggestions as to a work plan for moving the master and two slaves onto new hardware with a version upgrade? Every time i dump the data it will take 5+ hours and sourcing it in even longer so I am trying to be smart about this since my organization is so sensitive to downtime. thank you.
Mysql – thesql hardware refresh + version upgrade
migrationMySQLupgrade
Best Answer
I think your plan is largely sound, and is how I handled a similar upgarde (albeit 5.0 > 5.5). What I did was to take a Dump from the Master in the quiet hours like you said, and then set up a new Slave on one of the new 2008r2 boxes.
This allows you time to 'play' with the new 5.6 database so you can tweak the config, and make sure the data replicates correctly. Bearing in mind that as akuzminsky mentioned 5.0 > 5.6 is a big change, and a lot of things will have changed (binlog_format and GTID to name two of the big ones), and various settings will have been deprecated, or removed entireley.
In this way you get to keep the original Master and two slaves in production use, whilst you get up to speed with the 5.6 database. You can also repeat the process with a fresh install and different settings if things don't quite work as planned.
Once you are happy with the new slave, then you can set up two additional 5.6 slaves connected to the new 5.6 database giving you a new 5.6 system (master and two slaves), allowing you to do more checks.
Finally, once you are completely happy with the new 5.6 system, you can then simply move your application(s) to look at the new master and slaves at a convenient time.
Also, a bit of advice from someone that's been there, keep an eye on your error logs on the new servers. The later versions are more sensitive to errors, and I suddenly found my error logs filling up very quickly, requiring a bit of time to re-write some of the queries in our applications to make them 'replication safe'.