Step-by-Step Legacy Linux/MySQL Server Migration and Multi-Hop Upgrade Strategy

1-Line Summary

Directly dumping and restoring from extreme legacy MySQL 5.1 into MariaDB 11.x risks catastrophic dictionary corruption; engineers must follow a 5-step sequential binary upgrade path (5.1 ➔ 5.5 ➔ 5.7 ➔ 8.0 ➔ MariaDB) paired with a staging IP swap cutover SOP.

1. Technical Roadblocks in Legacy Migrations

1. PHP Runtime Deprecations (PHP 5.2/5.3 ➔ PHP 8.x): Elimination of legacy mysql_* functions and strict typing require application-level refactoring.
2. Data Dictionary Incompatibility: System tables (mysql.*), storage formats, and charset defaults cannot safely jump major versions directly.

[MySQL Multi-Hop Upgrade Roadmap]
MySQL 5.1 ──> MySQL 5.5 ──> MySQL 5.7 ──> MariaDB 10.11(LTS) ──> MariaDB 11.4
   │             │             │                  │
 (MyISAM)     (InnoDB)    (JSON/utf8mb4)     (Modern Optimizer)

---

2. 4-Stage Minimal Downtime Migration SOP

Step 1. Provision Target Staging Instance

Deploy a clean Rocky Linux 9.x instance configured with modern PHP-FPM, MariaDB 11.4, and Nginx.

Step 2. Baseline Sync & Local Hosts Validation

• Perform a baseline data transfer of /var/www/html and database dumps.
• Bind the staging IP in local hosts files to validate admin dashboards, payment integrations, and sql_mode query compatibility.

Step 3. Sequential Schema Upgrade Execution

Run mysql_upgrade sequentially across each version hop:

mysql_upgrade -u root -p --force

Step 4. Final Cutover & Atomic IP Swap

1. Enter Maintenance: Stop Apache and MySQL on source server to freeze writes.
2. Delta Dump & Re-import: Import delta records in 5 minutes.
3. Network IP Swap:
   • Swap public IP allocations or router NAT bindings to bypass DNS TTL propagation delays, routing live traffic instantly to the upgraded server.

---

3. Gotchas & Engineering Checkpoints

1. Legacy Character Set Encoding (latin1 / EUC-KR / Shift-JIS): Legacy MySQL 5.1 setups frequently shoved multibyte characters into latin1 columns; verify dump options with --default-character-set=latin1 before converting to utf8mb4.
2. Legacy 16-Byte Password Hashes: MySQL 5.1 old_passwords hashes are rejected by modern databases; reset database user credentials using native SHA256 before go-live.

---

Published: 2026-05-17 23:17:13Updated: 2026-08-15 13:57:00