MariaDB 10.6 to MySQL Aurora 8.0 Migration Guide — Part 3: Schema and User Migration

Schema migration is the most labor-intensive phase of a MariaDB to Aurora MySQL migration. AWS DMS handles data movement efficiently, but it does not convert schema objects. Tables, indexes, stored procedures, triggers, views, and user grants must be migrated manually — and the compatibility gap between MariaDB 10.6 and MySQL 8.0 syntax means a raw mysqldump import will fail.

This part covers the complete schema migration pipeline: exporting from MariaDB with syntax transformations applied, reviewing and cleaning incompatible objects, importing to Aurora, optimizing indexes for DMS throughput, and migrating database users.

The MariaDB–Aurora MySQL Compatibility Gap

MariaDB 10.6 and MySQL 8.0 share the same historical root but have diverged significantly. The main incompatibilities you'll encounter in schema dumps:

Exporting the Schema

The export command applies a chain of sed transformations at export time to handle the most common incompatibilities automatically. Run this on the jump server with connectivity to the MariaDB source.

There is no single dump command that works for every schema — the right one depends on how your MariaDB instance was configured, what MariaDB version features your schema uses, and how Aurora strict mode handles certain defaults. Two variants are documented below. Start with Variant 1; if the import fails or produces unexpected output, try Variant 2.

Variant 1

cd /root/tools/aurora_migration/

mysqldump --defaults-file=/root/.<dbidentifier>.cnf \
  --no-data --routines --triggers --skip-comments \
  --compatible=mysql40 \
  --default-character-set=utf8mb4 \
  <schema_name> | \
  sed 's/TYPE=InnoDB/ENGINE=INNODB/g' | \
  sed -E "s/(COMMENT ')[^']*\(([^']*)\)'/\1\2'/g" | \
  sed 's/ROW_FORMAT=COMPRESSED/ROW_FORMAT=DYNAMIC/g' | \
  sed -E 's/\b(int|bigint|tinyint)\([0-9]+\)/\1/gI' | \
  sed -E "/\b(tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob)\b/ s/[[:space:]]+NOT[[:space:]]+NULL//Ig" | \
  sed -E "/\b(tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob)\b/ s/[[:space:]]+DEFAULT[[:space:]]*\(?('([^']*)'|NULL)\)?//Ig" | \
  sed -E "s/[[:space:]]+,/,/g; s/[[:space:]]+;/;/g" \
  > <schema_name>.schema.$(date +'%Y-%m-%d_%H-%M_%p').sql

Variant 1 — Pipeline Explained

Variant 2

If Variant 1 does not produce a clean import, this alternative has worked in cases where the schema uses stricter charset/collation declarations, version-conditional comments, or non-standard column defaults that Variant 1 does not fully cover.

mysqldump --defaults-file=/root/.<dbidentifier>.cnf \
  --skip-set-charset \
  --skip-tz-utc \
  --no-data --routines --triggers --skip-comments \
  --default-character-set=utf8mb4 \
  <schema_name> | \
  sed 's/TYPE=InnoDB/ENGINE=InnoDB/g' | \
  sed -E "s/(COMMENT ')[^']*\(([^']*)\)'/\1\2'/g" | \
  sed 's/ROW_FORMAT=COMPRESSED/ROW_FORMAT=DYNAMIC/g' | \
  sed -E 's/\b(int|bigint|tinyint)\([0-9]+\)/\1/gI' | \
  sed -E "/\b(tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob)\b/ s/[[:space:]]+NOT[[:space:]]+NULL\b//Ig" | \
  sed -E "/\b(tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob)\b/ s/[[:space:]]+DEFAULT[[:space:]]*\(?('([^']*)'|NULL)\)?//Ig" | \
  sed -E "s/DEFAULT '([^']*)'/DEFAULT ('\1')/g" | \
  sed -E 's/CHARSET=utf8mb[0-9]+/CHARSET=utf8mb4/gI' | \
  sed -E 's/COLLATE=utf8mb[0-9a-z_]+/COLLATE=utf8mb4_0900_ai_ci/gI' | \
  sed -E '/\/\*![0-9]{5}/d' \
  > <schema_name>.schema.$(date +'%Y-%m-%d_%H-%M_%p').sql

Variant 2 — Key Differences from Variant 1

If Both Variants Fail — Build Your Own Pipeline

Both variants are starting points, not universal solutions. Every schema carries its own quirks depending on the MariaDB version, the ORM or tooling that created the tables, and how strictly the application used MySQL-compatible syntax. If neither variant produces a clean import, don't treat them as a black box — treat them as a menu of individual steps.

The approach: run the dump with no sed transformations at all, attempt the import, and read the first error MySQL returns. Each error will point to a specific syntax issue. Match that issue to the sed step that handles it from the tables above, add that step to your pipeline, and repeat until the import is clean.

A few practical tips for this process:

• Import errors are specific — Aurora MySQL will tell you the exact line number and syntax it rejected. Use that line in the SQL file to identify which transformation is missing.
• Test on a copy — run sed transformations against the exported file and inspect the output before importing. A quick grep for the failing pattern in the output file is faster than re-running the full dump.
• Order matters — some sed steps depend on earlier ones having already cleaned up the line. If you're combining steps from both variants, follow the same ordering: flags first, then engine/format fixes, then type fixes, then LOB constraints, then charset/collation, then comment removal last.
• Some issues require manual fixes — not everything can be handled with a sed one-liner. Zero date defaults, unsupported storage engines, and MariaDB-specific function syntax may need to be edited directly in the SQL file before import. The review section below covers the most common cases.

Reviewing the Exported Schema

The automated pipeline handles the most common issues, but a manual review pass is necessary. Open the exported SQL file and check for:

Remove Header and Footer Comment Blocks

Delete the first and last blocks of lines starting with -- or /*. These are MySQL version-specific comments generated by mysqldump that can cause parse errors on import.

Partitioning

If any tables use partition-based maintenance cron jobs (e.g., dropping old partitions on a schedule), disable those jobs before starting the migration. Partition DDL changes on the source during migration will not replicate cleanly through DMS.

Character Set and Collation

Replace any remaining references to the legacy _utf8mb3 character set (which is an alias for utf8) with utf8mb4. Use this migration as an opportunity to standardize the default character set and collation across all tables:

sed -i 's/_utf8mb3/utf8mb4/g; s/ utf8mb3 / utf8mb4 /g' <schema_name>.schema.<date>.sql

Zero Date Defaults

Aurora MySQL runs with sql_mode that includes NO_ZERO_DATE. Any column with DEFAULT '0000-00-00 00:00:00' will fail on import. Replace these with NULL or an application-appropriate default.

Search for all occurrences in the dump:

grep -n "0000-00-00" <schema_name>.schema.<date>.sql

Other Default Value Cleanups

Aurora MySQL 8 is stricter than MariaDB about column defaults. Watch for:

• DEFAULT (_utf8mb3'en') — rewrite as DEFAULT 'en'
• Function-based defaults not supported in MySQL 8.0 syntax (MySQL 8.0.13+ supports expressions, but older 8.0 minor versions do not)

Unsupported Storage Engines

Check for non-InnoDB engines that are not available in Aurora MySQL:

grep -iE "ENGINE=(Aria|TokuDB|MyRocks|MEMORY|CSV|ARCHIVE)" <schema_name>.schema.<date>.sql

Aria (MariaDB's default for system tables) and TokuDB are not available in Aurora. Convert them to InnoDB. MEMORY tables can remain as MEMORY if their use case is appropriate, but note that Aurora does not persist MEMORY table data across instance restarts.

Stored Procedures and Triggers

Review all routines and triggers for MariaDB-specific functions that have no MySQL equivalent. Common issues:

• SLEEP() behavior differences
• GET_LOCK() semantics differ between MariaDB and MySQL
• MariaDB-specific JSON functions (check against MySQL 8 JSON function reference)
• REGEXP behavior may differ slightly

Importing the Schema to Aurora

Once the SQL file is reviewed and cleaned, import it to the Aurora MySQL instance:

mysql -h <aurora-instance-endpoint> -u <username> -p -P 3306 \
  <schema_name> < <schema_name>.schema.<date>.sql

Secondary Index Strategy

AWS recommends dropping secondary indexes on the target before the DMS full load, then recreating them afterward. The rationale is that every inserted row triggers index maintenance on all secondary indexes, which can slow a full load significantly on large tables.

That said, this is a recommendation, not a requirement — and it comes with a trade-off: recreating indexes after the full load on a large dataset can itself be time-consuming and resource-intensive, putting pressure on the Aurora instance at a sensitive point in the migration. Whether dropping indexes first is the right call depends on your dataset size, available maintenance window, and how comfortable you are with the index rebuild phase.

For smaller datasets or schemas with few secondary indexes, keeping them in place is often the simpler path. For very large tables (hundreds of millions of rows) with many indexes, dropping them first can make the full load meaningfully faster — but factor in the rebuild time before committing to that approach.

Whichever approach you choose, primary keys must always remain in place — DMS requires them for CDC replication.

If you decide to drop and recreate indexes, generate the scripts upfront so you have them ready:

SELECT
  CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP INDEX `', INDEX_NAME, '`;') AS drop_stmt,
  CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ADD ',
    IF(NON_UNIQUE = 0, 'UNIQUE ', ''), 'INDEX `', INDEX_NAME, '` (',
    GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX), ');') AS create_stmt
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '<schema_name>'
  AND INDEX_NAME != 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, NON_UNIQUE;

Split the output into drop_indexes.sql (run on Aurora before the task starts) and create_indexes.sql (run after the full load completes, covered in Part 5).

Verifying the Imported Schema

Use mysqlcheck to validate the imported schema on Aurora:

mysqlcheck --check -u admin -p -h <aurora-instance-endpoint> -P 3306 <schema_name>

All tables should report OK. Investigate any errors or warnings before proceeding to the DMS setup.

Migrating Database Users

MariaDB and Aurora MySQL handle user grants differently. MariaDB uses IDENTIFIED BY PASSWORD with a native password hash, while Aurora MySQL 8 uses IDENTIFIED WITH 'mysql_native_password' AS with the same hash value. The pt-show-grants tool from Percona Toolkit handles this translation cleanly.

Export Users from MariaDB

Run this from the jump server:

/usr/bin/pt-show-grants \
  --user=<username> \
  --ask-pass \
  --host=<mariadb-endpoint> \
  --port=3306 \
  | grep -v -E "(root|mariadb\.sys|rdsadmin|rdsrepladmin|rdsproxyadmin|dd_agent_stat|dms_user|proxysql_check)" \
  | sed -E "s/IDENTIFIED BY PASSWORD '(.*)'/IDENTIFIED WITH 'mysql_native_password' AS '\1'/" \
  > /root/tools/aurora_migration/<schema_name>.db.users.$(date +"%Y-%m-%d_%I-%M_%p").sql

What this does:

• grep -v -E "..." — excludes system accounts and the DMS user that was already created in the target
• sed — rewrites the password clause from MariaDB format (IDENTIFIED BY PASSWORD 'hash') to MySQL 8 format (IDENTIFIED WITH 'mysql_native_password' AS 'hash')

Import Users to Aurora

mysql -u <username> -p -h <aurora-endpoint> -P 3306 < <schema_name>.db.users.<date>.sql

Verify the import by comparing user counts between source and target:

-- Run on both MariaDB and Aurora, compare output
SELECT user, host FROM mysql.user WHERE user NOT IN ('root','rdsadmin','rdsrepladmin','mysql.sys','mysql.infoschema','mysql.session') ORDER BY user, host;

With the schema and users in place on Aurora, proceed to Part 4 to configure DMS endpoints and the migration task.