• 2018/10/27

Upgrading MySQL 5.6 to 5.7

I migrated from MySQL 5.6 on a cloud VM to MySQL 5.7 on AWS RDS. Along the way I hit an issue where dumps failed because of row size limits, so here is what I did.

Background

• The plan was to move data from a MySQL 5.6 server in the cloud to an AWS RDS instance running 5.7.
• Using mysqldump straight away failed when tables with large rows were involved.

Steps

1. RDS makes it easy to restore from a snapshot, so I restored the instance (for verification I used a separate instance).
2. Once restored, I opened the console, clicked Change, and upgraded MySQL to 5.7.
3. Because it was a new instance I also set the security group and parameter group as needed.
4. Obtained a dump from the cloud MySQL server with mysqldump (see other articles for details).
5. Transferred the dump file to AWS via FTP/SFTP/rsync—any tool works.
6. In my test the dump still failed because the row format was incompatible, so I altered the table definitions before importing.
7. Logged into MySQL and checked the table definitions and file format:

SHOW TABLE STATUS LIKE 'DB_NAME'\G

SHOW GLOBAL VARIABLES LIKE 'innodb_file_format';

After verifying, I re-imported the dump. If mysqldump errors, you can often fix it by adjusting the table definitions and re-running the restore.

Table definition and file format notes

SHOW TABLE STATUS LIKE 'DB_NAME'\G shows ROW_FORMAT. Make sure it is DYNAMIC or COMPRESSED; COMPACT caused my dump to fail. For file format, SHOW GLOBAL VARIABLES LIKE 'innodb_file_format'; typically returns Antelope or Barracuda. My 5.6 server used Antelope and 5.7 used Barracuda, so switching to Barracuda resolved the issue. On RDS you can change it via the parameter group.

After these tweaks, I successfully imported the dump.