How to Upgrade Amazon Aurora MySQL from 5.6 to 5.7

Upgrade AWS MySQL Aurora RDS version from 5.6 to 5.7 with minimal downtime

== Source : MySQL version 5.6 Aurora 1.17.3
== Target  : MySQL version 5.7 Aurora 2.05.0

Full backup: we will use latest backup snapshot of the source
Incremental changes:  DMS or native Mysql replication (recommended to use native Mysql Replication)


kindly look into the prerequisites before proceeding with the migration. Just to list a few,
== Enable binary logs on master (requires reboot).
    --  enable binary logs by editing into parameter group , parameter is: "log-bin"
    --  can ensure by executing this command on source "show binary logs"
 
== Increasing the binary log retention on master so that they are not purged before they are replicated on the slave cluster.
    -- call mysql.rds_show_configuration;
    -- call mysql.rds_set_configuration('binlog retention hours', 240);     #10 Days 

== Ensure proper connectivity between the clusters. Replication is a "pull" process, and also it happens over the public network.

    -- create replication user on source

mysql> CREATE USER 'repl_user'@'<domain_name>' IDENTIFIED BY '<password>';

mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'<domain_name>';

You may refer the below MySQL document so that you can identify the points to be considered before setting up replication between different versions of MySQL.

[+] MySQL replication compatibility: https://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html

Considering AWS DMS,  you have to review the limitations of using MySQL based engine as source and target for DMS, and see if it affects your use case.
[+] Limitations on Using a MySQL Database as a Source for AWS DMS : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html#CHAP_Source.MySQL.Limitations 

[+] Limitations on Using a MySQL-Compatible Database as a Target for AWS Database Migration Service : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.MySQL.html#CHAP_Target.MySQL.Limitations
 

Steps:-

Phase 1)

(i) Restore latest snapshot of the source and select required DB Engine version ( Aurora (MySQL 5.7) 2.05.0) , also enable log exports (error log, general log )

(ii) Once Restoration is completed, has to setup native replication between source and target

    -- collect mysql binlog file and position , this you can get from the target Logs & Events tab or 

you have to find into Error/General log file. 

(iii) Go to Target cluster 

CALL mysql.rds_set_external_master ('mydbinstance.123456789012.us-east-1.rds.amazonaws.com', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);

CALL mysql.rds_start_replication;

107 --> is position

0 --> SSL is disabled (1 == SSL enabled)

(iv) show slave status\G

    
 
Phase 2)

    - Application stops write
    - Stop replication (execute below command from target i.e 5.7 cluster)
        -- CALL mysql.rds_stop_replication;    
    - Update db end point
       -- rename db endpoint 5.6 to old and 5.7 to same db endpoint which is in application config.  (it can be modify directly from the AWS RDS console) system reboot needed.
    - Start application

    OR

    - Start application with new end point.
    - Stop Replication

Phase 3
    - Release old 5.6 MySQL aurora cluster