MySQL 8.0 Upgrade Guide: 20 Critical Migration Pitfalls from 5.7

Pre-Upgrade: Turn Risks into Evidence

1. Pin the exact version. Patch versions affect the upgrade checker and defaults; test, staging, and production must use the same target version.

mysql --version
mysql -NBe "SELECT VERSION(), @@version_comment, @@basedir, @@datadir;"
mysqld --verbose --help 2>/dev/null | head -n 30

Save the output and record the software source. Do not let the package manager pick the "latest 8.0" during the maintenance window.

2. Verify replication switch prerequisites. Replication-based cutover isolates and rolls back more easily than in-place upgrade, but you must first confirm binlog, GTID, and server_id settings.

SHOW VARIABLES WHERE Variable_name IN ('gtid_mode','enforce_gtid_consistency','log_bin','binlog_format','server_id');
SHOW MASTER STATUS;

If binlog or GTID is not enabled, do not modify them temporarily and switch immediately; assess log space, existing replicas, and recovery paths.

3. Run the official upgrade checker. It only surfaces candidate compatibility issues; it cannot replace application regression testing.

mysqlsh --uri '<upgrade_user>@<source_host>:3306' \
  -- util checkForServerUpgrade --target-version=8.0.36 \
  --output-format=JSON > /var/tmp/mysql8-upgrade-report.json

Replace 8.0.36 with your actual target version and confirm parameters with mysqlsh -- util checkForServerUpgrade --help. All errors in the report must be resolved; warnings require a written disposition.

4. Inventory non-InnoDB tables. Logical backup consistency, replication semantics, and online schema changes all depend on the storage engine.

SELECT ENGINE, COUNT(*) AS tables
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
GROUP BY ENGINE;

MyISAM tables are not protected by --single-transaction consistency snapshots; they need separate locking or a conversion plan.

5. Find new reserved words. Tables, columns, views, routines, and dynamic SQL may use words that became reserved in 8.0.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
AND LOWER(TABLE_NAME) IN ('rank','system','window','groups','recursive');

Results are only candidates; renaming must synchronize ORM, reports, grants, and application releases. Do not rely on backticks alone for long-term governance.

6. Scan stored objects. Checking only table names misses views and routines.

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.routines
WHERE ROUTINE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
AND ROUTINE_DEFINITION REGEXP '(^|[^[:alnum:]_])(rank|window|groups)([^[:alnum:]_]|$)';

If privileges are insufficient, routine bodies may be invisible; run with a controlled audit account and cross-check with the code repository.

7. Inventory utf8mb3. In 5.7 utf8 is an alias for utf8mb3, which is deprecated in 8.0.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.columns
WHERE CHARACTER_SET_NAME IN ('utf8','utf8mb3')
AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Charset migration may rebuild tables; evaluate duration, lock impact, and backup needs on a rehearsal instance first.

8. Validate collation semantics. utf8mb4_0900_ai_ci may change case, accent, and language-specific comparison behavior.

SELECT COLLATION_NAME, CHARACTER_SET_NAME, IS_DEFAULT
FROM information_schema.collations
WHERE COLLATION_NAME IN ('utf8mb4_general_ci','utf8mb4_unicode_ci','utf8mb4_0900_ai_ci');

Pre-check unique-key conflicts on real data; the example below uses users.email as a placeholder — replace with your actual table. Run on a replica or during low traffic for large tables.

SELECT email, COUNT(*) AS n
FROM <business_db>.users
GROUP BY email COLLATE utf8mb4_0900_ai_ci
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 100;

9. Evaluate indexes and legacy row formats. The three-byte to four-byte change can increase index length, especially for historic COMPACT tables.

SELECT TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT, AVG_ROW_LENGTH
FROM information_schema.tables
WHERE ENGINE='InnoDB'
AND ROW_FORMAT IN ('Compact','Redundant')
AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema');

Before any ALTER TABLE, check table size, disk headroom, and rehearsal duration to avoid rebuilding large tables on the primary during peak hours.

10. List InnoDB tables without a primary key. Not a blocker, but affects row positioning and replication apply efficiency.

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
  AND c.CONSTRAINT_TYPE='PRIMARY KEY'
WHERE t.ENGINE='InnoDB'
AND t.TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
AND c.CONSTRAINT_NAME IS NULL;

Do not blindly add auto-increment columns just to clear the list; a new primary key may rewrite the table and change business semantics.

11. Compare strict SQL modes. Zero dates, truncation, and implicit conversions are more likely to fail explicitly in 8.0.

SELECT @@GLOBAL.sql_mode AS global_mode, @@SESSION.sql_mode AS session_mode;
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
FROM information_schema.columns
WHERE DATA_TYPE IN ('date','datetime','timestamp')
AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema');

Fix the data and SQL; do not relax sql_mode globally as a go-live shortcut.

12. Audit GROUP BY. ONLY_FULL_GROUP_BY rejects non-deterministic select lists.

EXPLAIN FORMAT=JSON
SELECT <group_col>, <non_agg_col>, COUNT(*)
FROM <business_db>.<table>
GROUP BY <group_col>;

This template is not directly executable. The <non_agg_col> must be defined by the business as an aggregate or functional dependency; arbitrary values are not allowed.

Authentication, Privileges, and Configuration

13. Inventory authentication plugins and clients. 8.0 defaults to caching_sha2_password; older drivers may fail to connect.

SELECT user, host, plugin FROM mysql.user ORDER BY user, host;
SELECT VERSION();

Upgrade Connector/J, middleware, and language drivers first. Only fall back to per-account compatibility if a short-term driver upgrade is impossible.

ALTER USER '<app_user>'@'<source_cidr>'
  IDENTIFIED WITH mysql_native_password BY '<new_password>';

This affects all connections matching that account. Canary one application node first, generate the password securely, and record when the temporary plugin will be removed; do not change the global default authentication plugin.

14. Export privileges for audit. Applications and inspection scripts must not rely on the old mysql.user column structure.

mysql -NBe "SELECT user,host FROM mysql.user" > /var/tmp/mysql-users-before-upgrade.tsv
mysql -e "SHOW GRANTS FOR '<app_user>'@'<source_cidr>';"
test -s /var/tmp/mysql-users-before-upgrade.tsv

Use SHOW GRANTS for each critical account. Roles, dynamic privileges, and TLS conditions must be verified on the target; do not replay exported statements blindly.

15. Check TLS. Connection failures may stem from certificate or TLS negotiation issues, not just passwords.

SHOW VARIABLES LIKE 'tls_version';
SHOW VARIABLES LIKE 'require_secure_transport';
SHOW STATUS LIKE 'Ssl_cipher';

Validate CA, hostname, and connection string with the real application driver on a canary instance. Do not disable TLS for troubleshooting.

16. Validate configuration with the 8.0 binary. Never copy the entire 5.7 my.cnf directly.

mysqld --defaults-file=/etc/mysql/my.cnf --validate-config
mysqld --defaults-file=/etc/mysql/my.cnf --verbose --help 2>&1 \
  | grep -Ei 'unknown|deprecated|query_cache|innodb_file_format' || true

Availability of --validate-config is confirmed via mysqld --help. Unknown settings must be removed or replaced with supported equivalents.

17. Clean deprecated configuration and monitoring items. The query cache is removed; old alerts must be cleaned up as well.

rg -n --glob='*.cnf' --glob='*.ini' \
  'query_cache|innodb_file_format|tx_isolation' /etc/mysql /etc/my.cnf 2>/dev/null || true

Follow backup → modify → validate → restart → verify → rollback for config changes; do not replace uncertain files directly.

18. Verify DEFINER, events, and time zones. Successful import does not guarantee views, routines, and scheduled tasks are executable.

SELECT TABLE_SCHEMA, TABLE_NAME, DEFINER
FROM information_schema.views
WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema');
SELECT EVENT_SCHEMA, EVENT_NAME, STATUS, TIME_ZONE FROM information_schema.events;

The target must have appropriate definers or definitions rewritten in advance; do not grant excessive privileges for convenience.

SHOW VARIABLES WHERE Variable_name IN ('event_scheduler','time_zone','system_time_zone');
SELECT COUNT(*) AS tz_names FROM mysql.time_zone_name;

Named time zones depend on the time zone tables. Loading methods vary by distribution; confirm data source and update responsibility during rehearsal.

Backup, Replication, and Cutover

19. Create verifiable, recoverable backups. File existence is not proof of recoverability. The script below applies to transactional tables for logical backup.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="<backup_dir>/mysql57-$(date +%F-%H%M%S)"
mkdir -p "${BACKUP_DIR}"
mysqldump --single-transaction --routines --events --triggers \
  --set-gtid-purged=OFF --databases <business_db> \
  | gzip -1 > "${BACKUP_DIR}/<business_db>.sql.gz"
gzip -t "${BACKUP_DIR}/<business_db>.sql.gz"
sha256sum "${BACKUP_DIR}/<business_db>.sql.gz" > "${BACKUP_DIR}/SHA256SUMS"

Pass passwords via a restricted defaults file or secrets manager to avoid exposure in process arguments. After backup, restore to an isolated instance and record restore time.

set -euo pipefail
gzip -dc "<backup_dir>/<business_db>.sql.gz" | mysql -h <target_host> -u <restore_user> -p
mysql -h <target_host> -u <check_user> -p -NBe \
"SELECT TABLE_SCHEMA,COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA='<business_db>' GROUP BY TABLE_SCHEMA;"

On import failure, preserve error logs and target state; do not repeatedly replay on partially imported data.

20. Build a read-only target replica. Target server_id must be unique; the configuration below is a minimal replication example, not a capacity-tuning template.

[mysqld]
server_id=<target_server_id>
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
relay_log=mysql-relay-bin
read_only=ON
super_read_only=ON
character-set-server=utf8mb4

Backup config before changes; validate on isolated target host before joining replication.

#!/usr/bin/env bash
set -euo pipefail

CONF=/etc/mysql/my.cnf
STAMP=$(date +%F-%H%M%S)
sudo cp -a "${CONF}" "${CONF}.${STAMP}.bak"
sudo mysqld --defaults-file="${CONF}" --validate-config
sudo systemctl restart mysql
sudo systemctl is-active --quiet mysql
mysqladmin ping

Restart interrupts connections to the target. On failure, restore the .bak and re-validate; do not keep trial-and-error on production.

CREATE USER '<repl_user>'@'<target_cidr>' IDENTIFIED BY '<strong_password>';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '<repl_user>'@'<target_cidr>';
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='<source_host>', SOURCE_PORT=3306,
  SOURCE_USER='<repl_user>', SOURCE_PASSWORD='<strong_password>', SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

Granting replication privileges expands the source attack surface; restrict by CIDR and record password rotation. I/O and SQL threads showing Yes is only the start — also check GTID sets, lag, and error fields.

SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;
SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_coordinator;

Replication errors cannot be resolved by skipping transactions to "catch up"; fixes must be based on error numbers, binlog events, and data impact.

Before cutover, stop writes from the application side, record the source GTID, and wait for the target's executed set to include it. Database read-only mode does not block high-privilege accounts and cannot replace application-level write stop.

#!/usr/bin/env bash
set -euo pipefail

mysql -h <source_host> -u <ops_user> -p -e "SET GLOBAL super_read_only=ON;"
SOURCE_GTID=$(mysql -h <source_host> -u <ops_user> -p -NBe "SELECT @@GLOBAL.gtid_executed")
mysql -h <target_host> -u <ops_user> -p -e "SELECT WAIT_FOR_EXECUTED_GTID_SET('${SOURCE_GTID}', 60);"
mysql -h <target_host> -u <ops_user> -p -e "SHOW REPLICA STATUS\G"

The 60-second timeout is just an upper bound. If the target has not caught up, abort the cutover and re-enable writes on the source; never change connection strings before data is synchronized.

SELECT COUNT(*) AS orders_total, MAX(updated_at) AS newest_order
FROM <business_db>.orders;
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

Row count and max update time are only basic spot checks. Amounts, inventory, and state machines must be reconciled by business primary keys; InnoDB TABLE_ROWS is an estimate and cannot serve as final consistency evidence.

#!/usr/bin/env bash
set -euo pipefail

MYSQL_HOST="<target_host>"
MYSQL_USER="<healthcheck_user>"
mysql --connect-timeout=5 -h "${MYSQL_HOST}" -u "${MYSQL_USER}" -p \
  -NBe "SELECT VERSION(), @@read_only, @@super_read_only, NOW();"

After traffic cutover, canary a small subset of applications first; verify real driver connections, key reads/writes, background jobs, and error rates. Only roll back to 5.7 if you have confirmed that 8.0 has received no independent writes.

-- ONLY on the original 5.7 instance AND after confirming the new instance has no independent writes
SET GLOBAL super_read_only=OFF;
SET GLOBAL read_only=OFF;
SELECT @@hostname, @@version, @@read_only, @@super_read_only;

This restores write capability for all connections. Confirm traffic has returned to 5.7 before executing; immediately verify business writes, error rates, and replication status afterward.

After the confirmation window, stop replication, drop temporary accounts, and decommission the old instance only when the new primary has independent backup and recovery chains. Retain check reports, backup validations, config diffs, GTID sets, and anomaly logs so the next upgrade becomes truly repeatable.

For the first week post-go-live, daily review error logs, authentication failures, slow queries, replication/backup results, and capacity trends. Pay special attention to low-frequency paths that only run after upgrade: month-end settlement, scheduled reports, historical data queries, async retries, and disaster recovery drills. Classify findings into "compatibility, performance, ops scripts, privileges" rather than attributing every anomaly to MySQL 8.0 itself.

For every fix, retain the corresponding SQL, config change, execution time, and verification evidence. This ensures the team does not have to re-guess historical decisions during subsequent patch upgrades, incident retrospectives, or new instance deliveries.

This is also part of the upgrade acceptance criteria.