Upgrade MySQL 5.7 to MySQL 8.0

Tue, 2019-02-05 14:30 — Shinguz

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()

      or

      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.

Literature

tags: 
mysql upgrade 8.0 5.7