Upgrade MySQL 5.7 to MySQL 8.0

Early adopters of our customers are considering to upgrade to MySQL 8.0. To be prepared we have created a check-list:

RTFM: Upgrading MySQL. Really! This time is different than all the years before...
Upgrade Path:
- Only Upgrade from 5.7 to 8.0 is supported (skipping 5.7 is NOT supported!). So Upgrade to 5.7 first!
- Only Upgrade between GA releases (5.7.9+, 8.0.11+) is supported.
- Recommended: Upgrade to newest 5.7 first (5.7.25).
Check for Incompatibility Changes, Deprecated and Removed Features:
- Incompatibility Changes in MySQL 8.0
- Removals:
  - Query Cache was removed.
  - No support for non-native partitioning (MyISAM, Archive, etc.)
  - Reserved keywords (application changes)
  - No own tables in MySQL schema
  - No Foreign Key Constraint names longer than 64 characters
  - No obsolete sql_mode definitions (requires application changes)
  - Check ENUM and SET length (< 255 characters/1020 bytes)
  - No tables in System Tablespace (ibdata1) or General Tablespaces!
  - GROUP BY with ASC or DESC is deprecated (application change)
  - log_errors/log_warnings
  - PASSWORD()
- Reserved Keywords
- Server Variables
- Default Changes
  - Character Set (utf8mb4)
  - Default authentication plugin → Application impact.
  - log_bin = ON
- Some database (admin) tools and applications might not work any more after upgrade!
Upgrade MySQL language Interfaces as well!
PHP: mysqli/mysqlnd, Perl: DBD::mysql, Python: MySQLdb, Java: Connector/J,...
Test Upgrade AND Application first on a testing system created with a Physical Backup!
Read Release Notes: New policy: Changes WITHIN Major Release Series are possible (= New Features)!

Prepare Upgrade

Check for Upgrade

shell> mysqlcheck --user=root --all-databases --check-upgrade

MySQL Upgrade from MySQL 8.0 Shell is recommended!

Install MySQL Shell 8.0

shell> ./mysqlsh root:@localhost:3306
mysql-js> util.help("checkForServerUpgrade");
mysql-js> util.checkForServerUpgrade()

shell> ./mysqlsh root:@localhost:3306 -e "util.checkForServerUpgrade();"

Backup before Upgrade preparation!
Fix findings from MySQL Upgrade Check first!
Backup!
Downgrade is NOT supported!!!
Upgrade types:
- physical (inplace) Upgrade
- logical Upgrade
Physical (inplace) Upgrade
- ```
mysql> XA RECOVER;
```
- Rotate Keyring Master Key:
```
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
```
- ```
mysql> SET GLOBAL innodb_fast_shutdown = 0;
```
- Shutdown old MySQL 5.7 Server.
- Exchange binaries.
- Adjust MySQL Configuration (my.cnf).
- Start new MySQL 8.0 Server

Logical (dump/restore) Upgrade

mysql> mysqldump --user=root --all-databases --events > full_dump.sql

Shutdown old MySQL 5.7 Server.
Exchange binaries.
Remove old Instance (shell> rm -rf /var/lib/mysql/*)
Adjust MySQL Configuration (my.cnf).
Create a new, empty MySQL 8.0 Instance:

mysql> mysqld --initialize-insecure --datadir=/var/lib/mysql

Start new MySQL 8.0 Server

Restore dump: shell> mysql --user=root --force < full_dump.sql (Might take some very long time)

ERROR 3554 (HY000) at line 6400: Access to system table 'mysql.innodb_index_stats' is rejected.
ERROR 1062 (23000) at line 6402: Duplicate entry 'Journal-Events-PRIMARY-n_diff_pfx01' for key 'PRIMARY'
ERROR 3554 (HY000) at line 6410: Access to system table 'mysql.innodb_table_stats' is rejected.
ERROR 1062 (23000) at line 6430: Duplicate entry 'Journal-Events' for key 'PRIMARY'

Post Upgrade work
- shell> mysql_upgrade --user=root (might take some very long time).
- shell> mysql --user=root mysql < share/fill_help_tables.sql (seems to not exist any more in 8.0.15... Bug?)
- Restart Server
- Check Error Log
- Do a backup again.