--- title: "What a careful MySQL to MariaDB migration still misses" publish_date: 2026-08-18 author: "Michael Aglietti" tags: - name: "Authentication" url: "/resources/blog/tag/authentication.md" - name: "Compatibility" url: "/resources/blog/tag/compatibility.md" - name: "GTIDs" url: "/resources/blog/tag/gtids.md" - name: "migration" url: "/resources/blog/tag/migration.md" - name: "MySQL Migration" url: "/resources/blog/tag/mysql-migration.md" - name: "MySQL replication" url: "/resources/blog/tag/mysql-replication.md" - name: "Replication" url: "/resources/blog/tag/replication.md" --- # What a careful MySQL to MariaDB migration still misses ## Key takeaways - **Hidden Engine Differences:** Standard migration tools often miss subtle gaps in authentication plugins and schema functions, leading to locked application accounts or unexpected cutover failures. - **Automated Migration Safeguards:** Specialized workflows in tools like the MariaDB Migrator safely preserve user access and maintain live data sync without altering or risking your source database. - **Proactive Risk Mitigation:** Running a free, read-only assessment upfront lets you catch and fix database compatibility issues early instead of troubleshooting live outages during high-stress maintenance windows. The MariaDB Migrator [announcement post](https://mariadb.com/resources/blog/an-easy-path-from-mysql-to-mariadb-introducing-mariadb-migrator/) introduced the MySQL-to-MariaDB migration tool and walked through its four modes. This post is about what those modes are up against. The three stories below are hypothetical, but the failures are not. I found the same patterns recurring across migration write-ups and forum threads, then reproduced each one on a MySQL 8.0 server migrating to MariaDB 11.4. MariaDB speaks MySQL’s protocol and runs MySQL’s SQL, so most schemas move over without trouble. That’s exactly why the places the two engines diverge are easy to miss. Only a few things are genuinely different, but those differences tend to surface on cutover day or later in production, when it’s hardest to find and fix them. Each team below followed best practices and internal policies; one used the most thorough dump command available, on purpose, and a problem still reached cutover. Each case is a place the MariaDB Migrator changes the outcome: it automates the fix in case one, gets case two right by default, and catches case three before the migration window opens. Curious? You can find these same differences in your own MySQL database today, whether or not a migration is on your calendar this quarter. ## Case one: users could not log in after migration **Scenario:** The team was retiring their last MySQL servers and standardizing on MariaDB 11.4, starting with the simplest database in the fleet to build confidence before touching anything riskier. Four gigabytes of data, a single application, no replication, no stored procedures. The server ran a stock MySQL 8.0 installation. The migration got the treatment a low-risk database gets. The team dumped the database with `mysqldump`, loaded the dump into MariaDB before lunch, and pointed the application at the new server. While all the data arrived, the application could not log in. **Challenge:** The application user accounts use the `caching_sha2_password` plugin, the default since MySQL 8.0 shipped, and had worked for years without anyone giving the auth plugin a second thought. MariaDB supports `caching_sha2_password` authentication, so these accounts continue to work after migration. However, they may not be passed as a text literal directly to preserve the original password. In this case, every account was found to have no working passwords, locking all logins. The grants compounded the problem. Several grants referenced MySQL 8 privileges, such as `APPLICATION_PASSWORD_ADMIN`, that MariaDB does not define, so replaying those grants by hand broke partway through. Rebuilding the accounts and their privileges took the rest of the day. **Solution:** The MariaDB Migrator handles users and privileges as a built-in step. The Migrator checks how each account signs in on the source and takes the matching path. An account on `mysql_native_password` keeps its password, carried over with MariaDB’s `IDENTIFIED VIA mysql_native_password USING` syntax. An account whose password cannot cross gets a temporary password instead, and by default the user has to reset their password at first login. Roles get found and rebuilt separately. Then the Migrator reads the grants off the source and replays them one at a time. Any grant MariaDB rejects goes into a report instead of stopping the run. Here is the report the run produced: ``` Application user migration summaryRoles created on target : 1Users migrated with original password: 1Users migrated with default password : 1 <- PASSWORD EXPIRE setUsers skipped (non-password plugin) : 0 <- manual handlingUsers that failed to migrate : 0Grants attempted : 5Grants successfully replayed : 4Grants dropped (incompatible) : 1...--- Users with password reset to default (PASSWORD EXPIRE set) ---'shop_app'@'%' (source plugin: caching_sha2_password) Note: Users will be prompted to set a new password on first login.--- Grants dropped (incompatible with MariaDB) ---'shop_app'@'%' -- GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO `shop_app`@`%` ``` The line in that report is a to-do, not a status. Every SHA-based account lands on the temporary-password path, so someone has to rotate those credentials before traffic resumes. The report names the accounts involved. The dropped grant is the MySQL 8 privilege that MariaDB does not define. The alternative is to find the same gap in production when an application call gets denied. A day of manual rebuilding turns into a setup prompt and a report to read. ## Case two: the low-downtime cutover that would not start **Scenario:** This migration had a hard constraint. The database backed a production service that could not tolerate a long maintenance window. The server ran MySQL Enterprise, the paid edition licensed on a subscription basis. The team wanted out of that license and its dependency. MariaDB had caught up with the paid MySQL features the team actually used, including encryption at rest and hot backups, so the move was a switch to open source rather than a version upgrade. The window was the constraint, so the team picked the method built for that problem. Stand up a MariaDB replica of the MySQL primary, let the replica catch up, then switch traffic over during a short pause. They configured replication with global transaction ID (GTID) positioning, the same way they had run every MySQL-to-MySQL cutover before. **Challenge:** Replication would not start. Each engine writes that GTID label differently. MySQL writes it as `source_uuid:transaction_id`. MariaDB has no notion of that format, tracking a domain-server-sequence triple instead. Neither format converts to the other, because neither engine recognizes the other’s setting: ``` -- on MariaDB 11.4SELECT @@gtid_mode;ERROR 1193 (HY000): Unknown system variable 'gtid_mode'-- on MySQL 8.0SELECT @@gtid_current_pos;ERROR 1193 (HY000): Unknown system variable 'gtid_current_pos' ``` The replica had nowhere to start from, and the cutover stalled before the window ever opened. **Solution:** Replication mode bridges that gap using a single set of coordinates both engines understand. It seeds the target from a snapshot taken with `--master-data=2`, records the binlog file and position from the source, and starts replication from those coordinates: ``` Applying replication coordinates: mysql-bin.000003:6922Replication started....IO running: YesSQL running: YesSeconds behind master/source: 0Replication verify passed. ``` The replica’s status output confirms it isn’t using GTID at all: ``` Seconds_Behind_Master: 0 Using_Gtid: No ``` `Using_Gtid: No` is the whole point. I ran an `INSERT` into the source after replication started, and the new row appeared on the target with no lag. That is exactly the state a low-downtime cutover needs, with the new server keeping pace with the old one until you flip traffic. Replication mode has two requirements worth knowing up front. The source needs `binlog_format=ROW`, and the schema cannot contain JSON columns. That second requirement rules out many MySQL 8 applications. The Migrator checks both conditions during preflight, so a schema that cannot use Replication mode gets caught before the seed starts rather than halfway through, and the error names the exact columns: ``` ==> Preflight checks (binlog)Checking source schemas for JSON columns...ERROR: Replication mode is not compatible with JSON columns in the source schema.Detected JSON columns: shopdb.customers.profile shopdb.orders.attrsJSON column types are not supported for online replication-based migration.Please use one of the offline migration modes: - Serial Streaming Copy - Parallel Streaming Copy - Offline Copy ``` The Migrator refuses the job, which is the right call. That refusal also means the low-downtime path is not open to everyone. If your schema carries JSON columns, plan on a maintenance window and one of the offline modes, and find that out now rather than on cutover day. ## Case three: the restore that failed on the schema **Scenario:** This was the last remaining MySQL server, a large production database being migrated during a planned maintenance window. MariaDB Community Server is GPL-licensed and stewarded by the MariaDB Foundation rather than a single commercial vendor. The team wanted a single clean cutover instead of a staged migration, so they dumped the entire database with `mysqldump --all-databases` and restored it into a fresh MariaDB instance. **Challenge:** `--all-databases` pulls MySQL’s own system tables along with everything else. Restoring those system tables left the target’s privilege tables in a state where `CREATE USER` no longer worked. Two more problems turned up in the same restore: a generated column that called `UUID_TO_BIN`, a MySQL 8 function MariaDB doesn’t have, and a schema-wide `utf8mb4_0900_ai_ci` collation that would have blocked the load entirely on an older MariaDB target. **Solution:** The MariaDB Migrator eliminates the system-table failure outright. The Migrator never touches the MySQL system schema. The run dumps only the application databases you name and rebuilds accounts through the step from case one, so the broken `CREATE USER` state has nowhere to come from. The other two failures live in the schema itself, and the Migrator’s Assess and Plan phase catches both before the window opens. Its 28 compatibility checks run across the whole server, including databases you aren’t migrating, and report back before any data moves: ``` [HIGH ] generated_columns_present 2 legacydb audit_events event_bin STORED GENERATED uuid_to_bin(`event_uuid`) shopdb orders line_total STORED GENERATED (`qty` * `unit_price`)[HIGH ] server_collation_0900 utf8mb4_0900_ai_ci[MEDIUM] mysql8_table_collations_present 3 legacydb audit_events utf8mb4_0900_ai_ci shopdb customers utf8mb4_0900_ai_ci shopdb orders utf8mb4_0900_ai_ci ``` Each flagged object becomes something you fix on a quiet afternoon instead of a stack trace mid-window. One flag deserves a caveat. The assessment ranks `server_collation_0900` HIGH, but the check is stale. MariaDB 11.4 already supports the collation, even though the warning claims it doesn’t, so verify against your target version before planning a rewrite you may not need. ## The pattern under all three The three failures have the same root cause. In each case, a specific difference between the two engines was sitting in the source all along, knowable in advance, and nobody went looking until cutover, when discovery costs the most. - **Authentication.** MySQL’s `caching_sha2_password` hashes don’t survive the move to MariaDB, and MySQL 8 dynamic privileges like `APPLICATION_PASSWORD_ADMIN` have no equivalent in MariaDB, so those grants can’t be replayed either. - **Replication.** MySQL’s transaction label, `source_uuid:transaction_id`, has no equivalent in MariaDB; instead, it tracks a domain-server-sequence triple. Neither engine recognizes the other’s GTID variables, so a MariaDB replica can never follow a MySQL primary by GTID. - **Schema.** MySQL 8 ships functions that MariaDB doesn’t implement, `UUID_TO_BIN` among them, so a generated column that calls one fails to create. MySQL’s default `utf8mb4_0900_ai_ci` collation loads cleanly on MariaDB 11.4 but stops the restore on 10.5 and 10.6-era targets, so this one depends on the version you land on. Each of these teams made a defensible call in the moment, and being more careful would not have caught any of these three failures. The missing ingredient isn’t process discipline. It’s engine-specific compatibility knowledge: which auth plugins carry a password across, which coordinate system replication can use, which functions and collations a schema is safe to carry. No dump-and-restore process, however careful, encodes that on its own. That is the knowledge the MariaDB Migrator brings. Users and grants come across with their plugins accounted for, plus a report of what was migrated and what wasn’t. It’s a logical migration into a fresh target, so your source is never modified and rolling back means nothing more than continuing to run it. `ANALYZE TABLE` runs right after the load, so your first queries meet an optimizer that already understands the data. And the assessment runs before anything moves, so you see what’s coming before you commit to a window. ## Assess and plan before you migrate You do not need a migration on the calendar to run an evaluation. The MariaDB Migrator’s Assess and Plan phase connects to a copy of your source, runs its compatibility checks, writes a report and a migration plan, and never touches a target. I tested that claim the blunt way. I pointed the assessment at a target hostname that does not exist, with a password that does not work, and the run still came back `ASSESSMENT: PASS`. The source is all the assessment needs. That assessment is the part worth running today. The phase is free and read-only, and the report tells you which differences in this post are already present in your own database. All three of these cases would have been shorter, calmer projects with that report in hand, and none of the three teams had one. Whether your migration is years off or already on the calendar, you’ll know what you’re carrying. The MariaDB Migrator is still in beta. Get the tool from the MariaDB community downloads: [mariadb.com/downloads/community/migration-tools](https://mariadb.com/downloads/community/migration-tools/).