MySQL - Upgrade 8+

 

Upgrading to 8+
Useful Links:
Upgrade Process Basics
Please note that these are purely informative and may vary depending on current server and situation, you are advised strongly to use the MySQL reference links above for more information.
  1. Backup
    Before starting the upgrade, ensure you have a complete backup of your database. This can be done using `mysqldump`, `mysqlpump`, or physical file backup methods
  2. Check Compatibility
    • Review Release Notes
      Thoroughly read the MySQL 8.0 release notes to understand new features, changes, and deprecated functionalities.
    • Use `mysql_upgrade`
      This tool checks and updates the system tables and performs any necessary repairs.
    • Compatibility Checker
      MySQL provides a `mysql-shell` utility with a `checkForServerUpgrade` function that checks for potential issues before upgrading.
  3. Upgrade Paths
    • Direct upgrades from MySQL 5.7 to 8.0 are supported.
      Ensure your 5.7 version is up-to-date (preferably the latest minor release).
    • In-Place Upgrade
      This involves stopping the MySQL server, replacing the binaries, and restarting the server. Run `mysql_upgrade` to update the system tables.
    • Logical Upgrade
      Dump all databases using `mysqldump` or `mysqlpump`, install MySQL 8.0, and then import the dump files.
  4. Configuration File Updates
    • Review the `my.cnf` or `my.ini` configuration files for deprecated or changed parameters.
    • Update the configuration files to reflect any new parameters or changes in defaults.
  5. Testing
    • Set up a test environment that mirrors your production environment.
    • Perform the upgrade on the test system first to identify and resolve issues.
    • Run your application against the test database to ensure compatibility.
Potential Pitfalls and Watchouts
  1. Deprecated Features
    • MySQL 8.0 removes certain features and options that were deprecated in 5.7. Review the deprecated features list to update your application code and configuration accordingly.
  2. SQL Mode Changes
    • MySQL 8.0 has stricter default SQL modes, which might cause queries that worked in 5.7 to fail. Review and test your queries under the new SQL modes.
  3. Character Set Changes
    • The default character set in MySQL 8.0 is `utf8mb4`, replacing `latin1` or `utf8` from older versions. Ensure your applications and data handling are compatible with `utf8mb4`.
  4. Authentication
    • MySQL 8.0 uses the `caching_sha2_password` plugin as the default authentication method, replacing `mysql_native_password`. Ensure your client libraries and connectors support this authentication plugin.
  5. Index and Table Changes
    • Some storage engines, indexes, and table features have been changed or optimized in 8.0. Review the changes to ensure your schema design and queries are optimized.
  6. Performance Considerations
    • MySQL 8.0 introduces several performance improvements and new features. Benchmark your application to identify performance gains or bottlenecks post-upgrade.
  7. Third-Party Tools
    • Ensure that any third-party tools or plugins you are using are compatible with MySQL 8.0.