Is it possible to downgrade a AWS RDS from mysql 5.7 to lower version (say 5.6)

Question

This is something i need to figure out, my company runs a number of prod RDS on AWS. Some of the mysql RDS run with 5.7 , i need to downgrade the mysql to 5.6 or 5.5 . Is this functionality provided by AWS.

Scenario: A mysql server already up and running with mysql version 5.7, Downgrade this to 5.6 

        -> If this is possible then what are the possible ways ?
        -> How to do this ?

Solution

This is not something that AWS provides out of the box, however it can be solved with below 2 approaches depending on your database size and downtime that you can accept.

It might worth considering fixing application compatibility instead of downgrading DB which is more risky operation.

1. Dump, restore and switch method

Dump your currently running database with mysqldump utility. Start a new RDS instance with downgraded engine, load your dumped data into it. Switch your application to use RDS instance with downgraded engine.

2. Dump, restore, replicate & switch method

Dump your currently running database with mysqldump utility. Start a new RDS instance with downgraded MySQL engine, load your dumped data into it. Set the new, downgraded DB instance as read replica of your old DB instance using mysql.rds_set_external_master and then start replication using mysql.rds_start_replication. Stop writes to your original DB, once the read replica catches up (you must monitor replication lag), run mysql.rds_reset_external_master which will promote your downgraded instance and turn off replication. Point your application to the downgraded RDS DB instance.

Method 2 will shorten your downtime to minimum, but is a bit more complex to execute. Here is a command reference to get familiar with to help you succeed: MySQL on Amazon RDS SQL Reference

You will find a great amount of examples in RDS documentation also - Importing and Exporting Data From a MySQL DB Instance:

Comments

[Michael - sqlbot]

"Replication from newer masters to older slaves may be possible, but is generally not supported." -- dev.mysql.com/doc/refman/5.7/en/replication-compatibility.ht‌​ml I suspect it's worth a try, though, for a short-term application like this.

[Michal Gasek]

@Michael-sqlbot I was not aware of it, good to know, thanks! I would probably give it a try anyway :)