How to Seamlessly Upgrade MySQL 5.7 to 8.0: Step‑by‑Step Guide and Common Pitfalls

1. Introduction

The upgrade was triggered by a mandatory security assessment that required fixing high‑risk MySQL vulnerabilities. Taking advantage of MySQL 5.7 reaching end‑of‑life, the team decided to migrate to MySQL 8.0.

2. MySQL Lifecycle

MySQL 5.7 is at the end of its official support window, meaning no further updates, bug fixes, or security patches will be released.

3. New Features in MySQL 8.0

Default character set changes from latin1 to utf8mb4.

All MyISAM system tables are converted to InnoDB.

Enhanced JSON support.

Invisible indexes and histograms.

Changes to default sql_mode values.

New default password policy.

Role‑based access control.

Support for window functions and hash joins.

4. Upgrade Recommendations

Only GA‑to‑GA upgrades are supported (e.g., 5.7 → 8.0). Skipping major versions (5.6 → 8.0) is not allowed.

Upgrade to the latest minor version of the current major release before jumping to the next major version (e.g., 5.7.43 → 8.0).

Always take a full backup; data loss is unacceptable.

5. Pre‑Upgrade Preparation

5.1 Check Compatibility with MySQL Shell

Run util.checkForServerUpgrade() via MySQL Shell to identify incompatibilities.

# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz -C /root
# tar -xf mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz
# cd /root/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit/bin
# ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log

5.2 Logical Backup

# which mysqldump
/home/application/mysql/app/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF --databases db1 db2 > /root/all-database-20231026.sql

5.3 Graceful Shutdown

# mysql -uroot -p
mysql> SELECT VERSION();
mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';
mysql> SET GLOBAL innodb_fast_shutdown=0;
mysql> SHUTDOWN;
# Verify no MySQL processes remain
ps -ef | grep mysql

5.4 Backup Data, Installation, and Configuration Directories

# cp -r /home/application/mysql/data /home/application/mysql/data_bak_$(date +%F)
# cp -r /home/application/mysql/app /home/application/mysql/app_bak_$(date +%F)
# cp /etc/my.cnf /etc/my.cnf_$(date +%F)

5.5 Download and Extract MySQL 8.0

# cd /home/application/mysql
# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
# tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
# mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql8
# chown -R mysql:mysql /home/application/mysql/mysql8
# rm -f mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz

5.6 Adjust my.cnf for 8.0

Key differences include sql_mode, basedir, authentication plugin, and character set. Example snippets:

# MySQL 5.7 my.cnf (excerpt)
[mysqld]
user=mysql
basedir=/home/application/mysql/app
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# MySQL 8.0 my.cnf (excerpt)
[mysqld]
basedir=/home/application/mysql/mysql8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default_authentication_plugin=mysql_native_password
character_set_server=utf8
collation-server=utf8_general_ci

5.7 Run the Upgrade Program

# /home/application/mysql/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE

Monitor /home/application/mysql/mysql-error.log for errors, then verify the version:

# mysql -uroot -p
mysql> SELECT VERSION();
+-----------+
| version() |
+-----------+
| 8.0.34   |
+-----------+

5.8 Update Environment Variables

# vim /etc/profile
export PATH=$PATH:/home/application/mysql/mysql8/bin
source /etc/profile
# Verify
which mysql
mysql -V

5.9 Switch to systemd Management

# kill -9 $(ps -ef | grep mysql | awk '{print $2}')
# Edit /etc/systemd/system/mysqld.service to point ExecStart to the new binary
ExecStart=/home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf
# Reload and enable
systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
# Verify
systemctl status mysqld

6. Common Post‑Upgrade Issues

6.1 SSL Connection Errors

MySQL 8.0 enables SSL by default, causing JDBC errors if the client does not support it.

Disable SSL at the server level by adding skip_ssl to my.cnf.

Or disable SSL in the JDBC URL with &useSSL=false.

6.2 Host Blocked by Too Many Connection Errors

When many failed connections occur (often due to SSL failures), MySQL blocks the host. Resolve by running: # mysqladmin -u root -p flush-hosts Investigate the root cause of the failed connections to prevent recurrence.

7. References

https://www.modb.pro/db/1715541568826990592

https://www.modb.pro/db/530848

https://www.modb.pro/db/1716302208709517312