0

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.

  • 5.0 -> 5.6 is a big change. I would upgrade slaves first, made sure they're in sync with the master (pt-table-checksum) let it run for awhile and then do promotion. You need to be able to switch back in case the app doesn't work well with 5.6 – akuzminsky Aug 12 '14 at 19:11
  • and by 'upgrade slaves' you mean install a fresh 5.6, dump and import data and make it a slave to the 5.0 master? – jason Aug 12 '14 at 19:25
0

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'.

  • thank you, your summary makes me feel better about my plan. What about MySQL's documentation indicating you have to follow the path 5.0=>5.1=>5.5=>5.6 ?? That seems safe but very time and resource intensive. It sounds like guys by and large are are not doing that. Does using mySqlDump to move the data negate that warning? – jason Aug 13 '14 at 13:12
  • also do I have reason to be concerned about the interem time I will be replicating from 5.0 to 5.6? – jason Aug 13 '14 at 13:15
  • I think the 5.0=>5.1=>5.5=>5.6 they mention is if you were upgrading your server in place, rather than creating a completely new instance of it. As far as I know MySQLDump should work OK, it's going the other way (5.6 > 5.5 or less) that can be problematic. Also remember to run mysql-upgrade if you include the MySQL tables, as there are structural changes in these. – IGGt Aug 13 '14 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.