MySQL 5.7 to 8.0: A Safe Upgrade Path and Pitfalls to Avoid – ITFROMZERO

Table of Contents

Checklist: Upgrading MySQL in 5 Minutes

If you need a “quick-and-dirty” process to apply immediately to your staging server, here are the 5 steps I’ve refined after dozens of real-world projects:

Compatibility Scan: Install MySQL Shell and run util.checkForServerUpgrade(). Don’t skip this step unless you want your database to crash with errors right after starting 8.0.
Practical Backup: Don’t rely on luck. Use mysqldump --all-databases --routines --events > full_backup.sql to ensure you have a safe rollback path.
Clean Shutdown: Stop the 5.7 server after setting innodb_fast_shutdown = 0 to ensure all data from the Buffer Pool is flushed to disk.
Replace Binaries: Install the MySQL 8.0 package via apt or yum.
Monitor Logs: MySQL 8.0 will automatically upgrade the data dictionary. Just sit back and monitor the process using tail -f /var/log/mysql/error.log.

Why You Must “Break Up” with MySQL 5.7 Right Now

MySQL 5.7 officially reached End of Life (EOL) in October 2023. Continuing to use it means exposing your system to security vulnerabilities without any further patches.

But the real reason is performance. MySQL 8.0 handles JSON incredibly fast and supports CTEs, making complex queries much cleaner. In an e-commerce project I worked on, moving to 8.0 boosted revenue reporting queries by 30% by leveraging Window Functions—something that required exhausting nested subqueries in 5.7.

Three “Critical” Changes That Could Break Your System

Upgrading isn’t just about clicking “Update.” There are three major architectural changes that could cause your application to freeze instantly.

1. New Password Encryption Standard

Problem: MySQL 8.0 changes the default plugin to caching_sha2_password. If you’re using an older version of PHP (below 7.4) or early Node.js versions, you’ll immediately hit an “Authentication method unknown” error.

Solution: If you haven’t updated your code yet, force MySQL 8.0 back to the old method in your my.cnf file:

[mysqld]
default-authentication-plugin=mysql_native_password

Or manually fix it for a specific user:

ALTER USER 'user_app'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

2. Reserved Keyword Conflicts

Problem: MySQL 8.0’s reserved words list now includes terms like GROUPS, RANK, and ROW_NUMBER. If your table has a column named rank that isn’t wrapped in backticks ( ` ), your queries will fail with a syntax error.

Hard-earned experience: I once saw a CMS crash completely just because a configuration column was named system. Upon moving to 8.0, the entire module failed due to the system keyword conflict. Use MySQL Shell to scan before it’s too late.

# Quick scan command in MySQL Shell
mysqlsh --user root --execute "util.checkForServerUpgrade()"

3. Removal of Query Cache

Fact: MySQL 8.0 has completely removed the Query Cache. If you’re relying on this cache to handle the load on a weak server, you might see CPU usage spike from 20% to 80% overnight after the upgrade.

Solution: Don’t try to turn it back on; it no longer exists. Focus on optimizing indexes or offloading cache to Redis or Memcached.

A Safe In-Place Upgrade Process

This is the method I typically use for Ubuntu or CentOS servers to minimize risk.

Step 1: Pre-upgrade Check

Ensure there are no corrupted tables and no legacy data types from the 5.5 era. Run a comprehensive check:

mysqlcheck -u root -p --all-databases --check-upgrade

Step 2: Configure Safe Shutdown

Before stopping the service, force MySQL to flush all pending data from memory to disk using this command:

SET GLOBAL innodb_fast_shutdown = 0;

Then, stop the service: systemctl stop mysql.

Step 3: Installation and Configuration

On Ubuntu, simply update the repository and run the installation. During setup, the system will ask about the SHA2 password mechanism. If your application is legacy, choose Retain MySQL 5.7 Compatibility to avoid connection errors.

Handling Collation Mismatches (Old vs. New Tables)

MySQL 8.0 defaults to utf8mb4_0900_ai_ci. If your old data uses utf8_general_ci, joining old and new tables will result in a Collation mismatch error.

When a users table reaches 10 million rows, changing the collation for the entire table is a nightmare because it locks the table for a long time. My advice: keep the old collation for existing tables and only apply the new standard to new features or tables created later.

Pro-tips to Avoid Headaches

Always Use a Clone: Clone your database to a test server and run the entire upgrade process there before touching production.
Monitor RAM: MySQL 8.0 consumes an additional 200MB-500MB of RAM just for the Performance Schema. If your server only has 1GB-2GB of RAM, consider disabling parts of this feature to avoid the OOM (Out of Memory) killer.
Check sql_mode: The ONLY_FULL_GROUP_BY mode is enabled by default in 8.0, which will cause legacy PHP code with non-standard GROUP BY queries to fail immediately.

Upgrading to 8.0 is inevitable. As long as you are careful during the compatibility check phase, you will eliminate 90% of potential issues.