MySQL to MariaDB migration guide

IntoSQL reads a MySQL or MariaDB dump and writes one MariaDB script with the same tables, columns, keys, indexes, foreign keys, defaults, counters and rows. A MySQL 8 dump often fails on MariaDB as it is (the utf8mb4_0900_ai_ci collation, functional defaults, invisible columns); the converter rewrites what MariaDB doesn't accept and lists every change in the report. The script loads on MariaDB 10.11 and later.

Convert in the browser: MySQL to MariaDB opens the converter set up for this pair.

Dump for the best result#

Any dump from mysqldump, mariadb-dump, mysqlpump, phpMyAdmin or Adminer works, as .sql or .sql.gz. These options keep more of your data:

mysqldump --default-character-set=utf8mb4 --hex-blob \
  --routines --triggers --events --single-transaction \
  shop | gzip > shop.sql.gz

• --default-character-set=utf8mb4 keeps emoji and other 4-byte characters. A dump made with utf8 has already lost them, and the report says so (dump_lost_four_byte_chars).
• --hex-blob writes binary columns as hexadecimal, which survives copying between systems.
• --routines --triggers --events add those objects to the dump, so the report can list them by name.
• --single-transaction takes a consistent snapshot of InnoDB tables without locking them.

A dump made with --no-create-info still converts: the types are read from the values, and the report says the schema was missing (no_schema).

Convert#

In the browser, open MySQL to MariaDB or the converter on the home page, drop the dump and choose MariaDB. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to mariadb --output shop.mariadb.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a dump converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "mariadb"}.

Load into MariaDB#

mariadb -e "CREATE DATABASE shop"
mariadb --default-character-set=utf8mb4 shop < shop.mariadb.sql

The script sets the character set, the time zone and a SQL mode that accepts the dump's zero and impossible dates, then creates each table with its keys and copies the rows with foreign key and unique checks off, as mysqldump does. Each AUTO_INCREMENT counter continues where the dump left it.

Or load it straight in#

intosql load and the desktop app load the dump into MariaDB without a script, with the same tables, keys and indexes the script creates:

intosql load shop.sql.gz --into mariadb://ana@localhost/shop

It stops before writing anything when one of the dump's tables already exists. MariaDB can't roll back a table, so a failed load drops the tables it created. Loading into a database server is part of IntoSQL Pro, with a 14-day trial. See Loading.

What isn't converted#

Views, triggers, stored procedures, functions and events are listed in the report by kind and name, never dropped silently, so you can check each one against MariaDB before recreating it. ON UPDATE CURRENT_TIMESTAMP stays as it is.

Data types#

MySQL to MariaDB data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule#

Generated from the engine, where each rule is decided.