Migrate from MySQL to MariaDB: Step-by-Step Guide

MySQL vs MariaDB: key differences

Step 1: Back up the MySQL data

mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers > /backup/mysql_full_$(date +%Y%m%d).sql

# Check the size
ls -lh /backup/mysql_full_*.sql

Step 2: Remove MySQL

sudo systemctl stop mysql
sudo apt remove --purge mysql-server mysql-client mysql-common
sudo rm -rf /var/lib/mysql
sudo apt autoremove

Step 3: Install MariaDB

sudo apt update
sudo apt install mariadb-server mariadb-client

# For the latest version from the official repository:
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
sudo apt install mariadb-server

Step 4: Initial configuration

sudo mariadb-secure-installation
# or
sudo mysql_secure_installation

Step 5: Import the data

# Check the connection
mysql -u root -p -e "SELECT VERSION();"

# Import the backup
mysql -u root -p < /backup/mysql_full_*.sql

Step 6: Post-migration checks

# List the databases
mysql -u root -p -e "SHOW DATABASES;"

# Check the tables
mysql -u root -p -e "USE mydb; SHOW TABLES; CHECK TABLE orders;"

# Test the application
curl -I https://yoursite.com

Common Errors During Migration

Most problems appear not during export, but on the first connection from the application to the new server.

Compatibility: What Can Break

MariaDB is not a bit-for-bit copy of MySQL, and some differences only show up under load.

• Authentication plugin: MySQL 8.0 defaults to caching_sha2_password, MariaDB uses mysql_native_password. Older PHP/Python drivers may not understand caching_sha2_password, so migrating toward MariaDB usually goes smoothly, while the reverse direction often does not.
• JSON functions partly differ: some MySQL 8.0 functions (for example certain JSON aggregate functions) are implemented differently in MariaDB or missing entirely.
• The query optimizer builds different plans for the same SQL — after migration, always run heavy queries through EXPLAIN and compare against the pre-migration log.
• Replicas on old MySQL cannot connect to a MariaDB master and vice versa — the replication protocols diverged starting with MariaDB 10.x.
• The default collation differs: MySQL 8.0 uses utf8mb4_0900_ai_ci, MariaDB uses utf8mb4_general_ci or utf8mb4_unicode_ci. String sorting and case-insensitive comparison can give different results for the same data.

When Migrating to MariaDB Is Not a Good Fit

Stay on MySQL if the application hard-depends on features MariaDB lacks: window function extensions specific to MySQL 8.0, the MySQL Document Store (X Protocol), or if you use a cloud provider's managed service that supports only one of the two engines without an external data migration path. Also avoid migrating "just in case" right before peak load — always test the change on a staging server with a copy of the production database first.

Checking Compatibility Before Switching Traffic

-- Compare versions and SQL modes
SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';

-- Run a typical heavy query and compare the plan
EXPLAIN SELECT o.id, u.email FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND o.created_at > NOW() - INTERVAL 7 DAY;

-- Check InnoDB state after a load test
SHOW ENGINE INNODB STATUS\G

If sql_mode differs from the source server, some INSERT statements with implicit type conversion may start failing with an error instead of silently rounding the value — verify this setting before cutting over production traffic. For safely moving data without touching the source database, see the article on mysqldump and restore, and for tuning the new server, see the article on my.cnf optimization.