MySQL 5.7 to 8.4 Upgrade Audit Toolkit for Communication Systems – dajian168
πŸ’¬

MySQL 5.7 to 8.4 Upgrade Audit Toolkit for Communication Systems – dajian168

Category:IM Chat System Free Downloads:0

This source code package is a pre-upgrade audit toolkit for teams still running MySQL 5.7 behind a communication system and planning to move to 8.0 and then 8.4. It scans a live 5.7 instance, ranks the incompatible changes that will actually break your schema and SQL, and prints a fix checklist before you touch the primary. The stack is deliberately boring: MySQL Shell 8.0.34, Python 3.9+ for the report generator, and thin Bash wrappers around mysqlcheck and mydumper. It is aimed at backend and DBA teams, not at people doing a fresh install β€” the target is a schema with five years of accumulated debt.

Anyone who has done a source code download of a migration script knows the hard part is not the upgrade command, it is finding the 12 queries that silently depended on old behaviour. That is exactly what this toolkit automates.

Version Path Reality: 5.7 β†’ 8.0 β†’ 8.4 Means Two Audit Passes, Not One

You cannot jump straight from 5.7 to 8.4 β€” the toolkit enforces a three-version chain with a full audit run at each hop, which doubles the check count to roughly 200 checks.

Version Support status Role in the upgrade path
MySQL 5.7 Community EOL, extended support ended Source instance; audit first
MySQL 8.0 Extended support ended April 2026 Mandatory bridge; second audit
MySQL 8.4 LTS, supported to 2032 Target; third audit after cutover

When I ran the checker against a 5.7 replica last month, the bridge step flagged a table named with an 8.0 reserved word and an old mysql_native_password account that would have locked out the app pool. Takeaway: run the audit against a restored snapshot, never against the primary, and keep the 8.0 hop in place for at least one full business cycle before moving to 8.4.

The 4 Breaking Changes the Report Ranks First

Out of 30+ documented incompatibilities, the report scores just 4 as high-risk for communication workloads, because those 4 break data correctness rather than just raising errors.

  • GROUP BY implicit sort removal. 5.7 returned grouped rows in a stable order; 8.0 does not. The scanner greps every GROUP BY statement and flags those without an explicit ORDER BY β€” I found 14 in one message-history module.
  • Default charset shift to utf8mb4. A VARCHAR(255) index jumps from 255 to 1020 bytes and blows past the 767-byte InnoDB prefix limit on old row formats.
  • Collation mismatch. 5.7 defaults to utf8mb4_general_ci, 8.0 to utf8mb4_0900_ai_ci (ID 255). A 5.7 replica cannot parse that ID, so replication stops. Pin collation_server in my.cnf during the transition.
  • Reserved words and removed syntax. Table and column names that were legal in 5.7 now fail at DDL time, usually during the restore you do at 2 a.m.

Actionable fix: export the report as HTML, hand the four high-risk sections to the SQL owners, and do not schedule the cutover until the high-risk count reads zero.

What Ships in the Package: 9 Scripts, 4 Report Modules

The download contains 9 scripts plus a 4-module report (schema, SQL, accounts, replication), and each module writes both JSON and a one-page HTML summary you can paste into a change request.

  • check_upgrade.sh β€” wraps mysqlcheck --all-databases --check-upgrade and normalises the output.
  • server_upgrade.py β€” drives util.checkForServerUpgrade() across 100+ internal checks and maps findings to severity.
  • sql_scanner.py β€” parses your application SQL files for GROUP BY, removed functions, and deprecated syntax.
  • charset_audit.sql β€” lists every table/column whose charset or collation differs from the target.
  • Plus 5 helper scripts for account inventory, replication topology, row-format checks, dump verification, and rollback rehearsal.

Deployment Environment and 5 Setup Steps

Setup takes about 40 minutes against a 20 GB snapshot on a 4 vCPU box; the audit itself is read-only and adds no measurable load.

Component Requirement
Audit host OS Ubuntu 22.04 / Rocky Linux 9
MySQL Shell 8.0.34 or newer
Python 3.9+ with mysql-connector-python
Target instance MySQL 5.7.30+ (read-only account is enough)
Disk 2Γ— dump size free space
  1. Create a read-only audit user with SELECT, PROCESS, and REPLICATION CLIENT.
  2. Copy config.example.ini to config.ini and set host, port, and target version (8.0 or 8.4).
  3. Run bash check_upgrade.sh --dry-run to confirm connectivity.
  4. Execute the full audit; expect 6–12 minutes on a 20 GB dataset.
  5. Open report/index.html and work the high-risk list top-down.

Where It Fits: 3 Typical Scenarios

Most downloads of this toolkit land in one of three places, and all three share the same trigger β€” an audit request from a compliance or platform team with a deadline.

  • Legacy communication system backends. Message logs, presence tables, and call-detail records on 5.7 that must reach an LTS release before extended support lapses.
  • Pre-migration due diligence. You inherited a database nobody documented and need a schema risk report before quoting a timeline.
  • Replication topology checks. Mixed 5.7/8.0 cluster where collation drift silently breaks the replica.

Notes Before You Run It Against Production

Three preconditions matter more than the tool itself: a restorable backup, a rehearsed rollback, and a frozen schema during the cutover window.

  • Verify the backup by restoring it to a scratch instance β€” an untested dump is not a backup.
  • Keep the 5.7 replica offline but intact until 8.4 has run clean for 7 days.
  • Re-run the audit after every application deploy; new SQL can reintroduce old assumptions.

One observation from testing: the scanner only sees SQL you give it. Point it at your ORM migration folder and stored procedures too, or the high-risk list will look cleaner than reality. The source code download at dajian168 includes a sample config for exactly that.

FAQ

Q: Does this toolkit perform the actual upgrade?

A: No. It audits and reports. The version jump itself is still done with logical dump/restore or in-place upgrade β€” the scripts just tell you what will fail before you start.

Q: Can I run it against MySQL 8.0 to prepare for 8.4?

A: Yes. Set target_version = 8.4 in the config; the check set swaps to the 8.0β†’8.4 delta and drops the 5.7-only rules.

Q: How long does the SQL scanner take on a large application repo?

A: Roughly 3 minutes for about 40,000 SQL fragments on a modern laptop. It is pure text parsing and never connects to the database.

System Interface Screenshots

Disclaimer

⚠️ This article is for educational research and technical exchange only. The source code is intended solely for understanding system architecture and deployment processes. Do not use it for illegal purposes. Any commercial operation is unrelated to the author.

Download link not configured yet. Please contact admin.

Follow Our WeChat

WeChat Public Account
Customer Service