2

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)?

  • Does AWS keep the 5.6 instance until you say you are happy with the 5.7? – Rick James Jun 2 '17 at 13:59
  • @RickJames They allow you to create a snapshot and restore the snapshot to a new 5.7 instance directly. Once you are satisfied, they will perform the actual upgrade on the real instance. – 1234567 Jun 2 '17 at 14:08
  • I suggest that AWS has designed the "best" way for their Cloud. (No, I cannot directly vouch for them.) – Rick James Jun 2 '17 at 21:57
  • @RickJames - right, my concern is just whether to trust it or not. I've never liked doing operating system upgrades... always preferred to reinstall from scratch. Correction to my previous statement: once you finish testing the process on a snapshot, you can perform the upgrade normally on the regular instance (they don't automate this "testing" process for you). – 1234567 Jun 3 '17 at 3:07
3

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.

  • 1
    Note that the question is about MySQL on Amazon RDS. – Michael - sqlbot Jun 3 '17 at 4:49
  • Agreed corrected. Approach remains the same – Mannoj Jun 3 '17 at 5:13
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.

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.