Comments on: How to Upgrade Amazon Aurora MySQL from 5.6 to 5.7

By: islue

islue — Wed, 20 May 2020 10:17:27 +0000

Excuse me, but what is the meaning of restoring the snapshot to a 5.6 cluster, to mysqldump and restore to another 5.7 cluster?
I tried to restore the 5.6 snapshot directly to a 5.7 cluster and set up the replication. It seems working.

By: Dave

Dave — Wed, 01 Apr 2020 10:57:43 +0000

“The solution was to take a snapshot and use it to provision a temporary Amazon Aurora MySQL 5.6 cluster of one node. As part of the creation process, the events tab of the AWS console will show the binary log file and position consistent with the snapshot, it looks like this:”

I have followed these instructions twice, and in each case Recent Events only displayed “DB instance created” and nothing else. A colleague of mine agreed that Recent Events does not reliably display info related to binlog position.

The page below says, “You can also get the binlog file name and position by checking the MySQL error log for the last MySQL binlog file position or for the DB_CRASH_RECOVERY_BINLOG_POSITION entry.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html#AuroraMySQL.Replication.MySQL.CreateSnapshot

This was helpful for me. I found three entries similar to this in the first two log files created after the restore. (for example, in the file named error/mysql-error-running.log.2020-03-31.13)

2020-03-31 12:13:50 12937 [Note] Crash recovery finished.
InnoDB: Last MySQL binlog file position 0 1020, file name mysql-bin-changelog.023827

I hope this info might help others.

By: Yves Trudeau

Yves Trudeau — Thu, 05 Mar 2020 14:28:09 +0000

In reply to khoanguyen. Sorry for the late answer, I missed your question. You don't necessarily need the exact same configuration for an upgrade. Of course, in RDS you can't do "set global..." since SUPER is not granted, you have to change the parameter group.

By: Yves Trudeau

Yves Trudeau — Thu, 05 Mar 2020 14:23:31 +0000

In reply to Dave. Yes, it is a copy/paste from different runs. The way it is currently, it is difficult to edit the post once published.

By: Dave

Dave — Thu, 05 Mar 2020 12:45:03 +0000

In reply to Vhincent San Joaquin. I have the same question. Your screenshot says mysql.bin.changelog.000027 whereas your CALL mysql.rds_set_external_master uses mysql-bin-changelog.000018 Should those be the same? Thanks!

By: khoanguyen

khoanguyen — Thu, 10 Oct 2019 07:02:23 +0000

Please for me ask question.
When I create Amazon Aurora(MySQL5.7) I must create new [Cluter Parameter Group] and [DB Parameter Group] because Aurora(MySQL5.7) not support parameter of Aurora(MySQL5.6).
But I compared value paramater of Aurora(MySQL5.6) and Amazon Aurora(MySQL5.7) it is not same.
Example :
Num Parameter Name auroracluster5.7 auroracluster5.6
1 binlog_format OFF MIXED
2 character_set_client 0
3 character-set-client-handshake utf8
4 character_set_connection utf8
5 character_set_database utf8
6 character_set_filesystem binary
7 character_set_results utf8
8 character_set_server utf8
9 collation_connection utf8_general_ci
10 collation_server utf8_general_ci

I run command [SHOW VARIABLES;] and compare value Aurora(MySQL5.6) and Amazon Aurora(MySQL5.7)
have a lot of item not same. But variable is GLOBAL can not change value.
For exam I run [SET GLOBAL max_connections = 1000] throw error follow
# Time Action Message Duration / Fetch
0 1 15:27:00 SET GLOBAL max_connections = 1000 Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation 0.000 sec

If you have best solution upgrade Aurora(MySQL5.6) to Amazon Aurora(MySQL5.7) with same setting value in [Cluter Parameter Group] and [DB Parameter Group] please show me. Thanks you so much.

By: Vhincent San Joaquin

Vhincent San Joaquin — Thu, 16 May 2019 16:18:31 +0000

In reply to englishcentraladmin. Sorry, this is my question. continuing it, did you use the binlog file and position in the "Logs & events" tab of the 5.6 db which you got when you created the snapshot? I got confused with the different value in the screenshot and the one you used in CALL mysql.rds_set_external_master thanks!

By: englishcentraladmin

englishcentraladmin — Thu, 16 May 2019 16:12:50 +0000

Hi, I dont know if I just missed it out but,

By: Yves Trudeau

Yves Trudeau — Mon, 11 Mar 2019 12:32:46 +0000

In reply to Mike. That's only available with the Percona version of the client tools, the jumpbox was using the regular Community client.

By: Mike

Mike — Fri, 08 Mar 2019 01:46:19 +0000

Did you consider using mysqldump --innodb-optimize-keys instead of restore-indexes.sql?

By: Matthew Lang

Matthew Lang — Tue, 05 Mar 2019 19:49:47 +0000

Thank you for the post. It’s a big effort to upgrade from 5.6 to 5.7 on Aurora. Would like to consider using Continuent Tungsten Clustering in AWS instead of Aurora. It’s quite easy to upgrade MySQL versions (5.6 to 5.7 or other versions) without taking down the Tungsten MySQL cluster. This is because the clustering software is designed to support mixing MySQL versions and allows various maintenance tasks to be performed without downtime.

The steps are quite simple:
1. Put cluster in maintenance mode
2. Shun (remove as a failover candidate) a slave
3. Upgrade MySQL on that slave (mysql binaries, mysql_upgrade script)
4. Recover the slave. Now replication happens from the MySQL 5.6 master to this MySQL 5.7 slave
5. Repeat steps 2-4 for remaining slaves.
6. Switch master to one of the upgraded slaves. Now master is MySQL 5.7, and old master is still 5.6, and replication is handled without issue
7. Perform steps 2-4 on old master.
8. Put cluster into automatic mode
9. Upgrade to MySQL 5.7 done.

No downtime!