How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Question

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option --single-transaction. For production, I am looking for way where I can upgrade mysql prod DB to 5.6 with minimal downtime.

We are using a different server, but the problem is how I can manage ongoing transaction on production DB, because while taking mysqldump and restoring to new server their will be many transactions performed on my existing production server. How can I sync this prod-DB updated transactions with new 5.6 mysql-server ?

I am thinking if we can run mysqldump with --single-transaction --flush-logs and after restored this dump, restore remaining transaction by applying new binary logs that created after mysqldump.

I am also planning to enable GTID replication with our new 5.6 mysql production.

Answer 1

We used replication (no GTID) to replicate from our production 5.5 servers to our new 5.6 servers so to minimize downtime. Basically, set up your new 5.6 server to replicate from the 5.5 (cannot use GTID at this time). Dump 5.5 and restore to 5.6, start the replication.

When you move production to the 5.6 server, stop the replication from the 5.5 server . Reconfigure 5.6 server to use GTID (need to issue RESET MASTER and RESET SLAVE on the 5.6). Then configure your downstream 5.6 slaves.