MySQL 5.6 to 5.7 In-Place Upgrade or Fresh Install?

Question

I'm preparing to migrate a production database server on AWS RDS to 5.7 from version 5.6.

I'm not sure that I trust the automatic upgrade service. Obviously, I would make a backup prior to doing anything, but is there any reason one should choose to just start a new instance and manually export / import the data (and privileges, of course)?

Answer 1

For such major version change like 5.5 to 5.6 or 5.6 to 5.7 I recommend to export/import rather than in-place.

Why do I prefer fresh-install rather than in-place? :

• In MySQL 5.6 you have 29 tables under mysql schema and in 5.7 you have 33 tables under mysql schema.
• If you are using GTID then it is not recommended to go for in-place upgrade.
• Deprecated commands or data types.
• Deprecated UDFs or changed functions.
• Chances of password incompatibility.
• Deprecated stuff will be thrown out as an error during import, thus gives us alarm. Though we don't go through white paper properly.

How do I proceed? :

Let's say in your case 5.6 to 5.7, on your test bed:

1. Take mysqldump or mydumper with master status. A consistent backup.
2. Take all grants out separately into an sql file.
3. Have fresh installation of 5.7.
4. Without performance_schema, mysql, information_schema or any system schema, import the dumped data into 5.7 and apply grants. Ensure you verify root and admin users are removed as it is a different server.
5. Now create slave link and go for down time on 5.6.

6. Now your test architecture will look like:

   APP- > 5.6 SERVERA (Master of) -> 5.7 SERVERB (Master of)  -> 5.7 SERVERC (Slave of SERVERB)
   

7. And now do a consistency check of ServerA, ServerB, ServerC by considering frequently updating tables and take the data in chunk sets and verify if the data checksum is same as per its right state of changes.

But before everything we should ask ourselves what is the reason behind to go for different versions. Of course we can make use of new features. But if we verify conflicts in test bed at least we have a ground confidence to do the same to production.

Answer 2

You can create a read-replica of your MySQL 5.6 instance, and then use the upgrade feature provided by RDS to upgrade the read-replica to MySQL 5.7 - the RDS documentation explains how to do this in a fair amount of detail - look for the section titled 'Upgrading a MySQL Database with Reduced Downtime' http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html

You can then confirm that you are happy with the new instance, and perform a manual failover to the new instance by promoting it to a primary instance, and either renaming the instances or redirecting your application to the new instance.

You must ensure that you stop your application making writes to the original instance before you promote the new instance to be a primary, and also ensure that the read-replica has processed all outstanding binary log statements received from the original primary instance. It is often best to do this during scheduled application downtime - there are ways of doing this without downtime, but they require much more explanation and testing.

Clearly, you should follow this process in your test environment prior to performing the upgrade on the production environment, allowing you to evaluate the compatibility of your application with the new version of MySQL.