By Martin Wendler —

MariaDB/MySQL: Migrate from MariaDB to MySQL (using MySQL Shell)

This blog post is about how to migrate from MariaDB to MySQL using MySQL Shell.

MariaDB/MySQL: Migrate from MariaDB to MySQL (using MySQL Shell)

Long time ago, Sun Microsystems bought MySQL, and Oracle bought Sun Microsystems. Nobody knew what would happen to MySQL, and the author of original MySQL, Michael Widenius, started a fork of MySQL, called MariaDB. So many companies and Linux distributions moved from MySQL to MariaDB.

MySQL as well as MariaDB have many similarities, though many things are very different.

If you now want to move from MariaDB to MySQL, no matter for what reason (maybe you want to use software support from Oracle, or you need the Enterprise Edition), there are a few ways to migrate your data:

  • Dump
  • MySQL Dump

MySQL Dump is quite easy (see the documentation):

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

It's quite the same on MariaDB site (see documentation):

mariadb-dump [options] db_name [tbl_name ...]
mariadb-dump [options] --databases db_name ...
mariadb-dump [options] --all-databases
mariadb-dump [options] --system=[option_list]

But when your database is quite large, or you just want to speed up your export/import, you can use MySQL Shell (either login using the --js switch, or interactively switch to JS mode in shell using \js:

mysqlsh root@<mariadb server>:<port> --js

MySQL  192.168.86.49:13306 ssl  nation  JS > util.dumpSchemas(["nation"],"/tmp/mysql",{threads: 2})

NOTE: Backup lock is not supported in MySQL 5.6 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 schemas will be dumped and within them 9 tables, 0 views.
Gathering information - done 
All transactions have been started
Global read lock has been released
Writing global DDL files
Running data dump using 2 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done       
Starting data dump
1 thds dumping \ 100% (11.26K rows / ~11.26K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:02s
Schemas dumped: 1
Tables dumped: 9
Uncompressed data size: 292.19 KB
Compressed data size: 108.79 KB
Compression ratio: 2.7
Rows written: 11261
Bytes written: 108.79 KB
Average uncompressed throughput: 292.19 KB/s
Average compressed throughput: 108.79 KB/s

MySQL Shell now thinks the source database is something like MySQL 5.6, so some special features, like locking, will not work.

In my directory, there are now many files:

Now we can connect to the MySQL database:

# mysqlsh root@<mysql db>:<port> --js

 MySQL  192.168.86.49:23306 ssl  JS > util.loadDump("/tmp/mysql",{threads: 2})

We get an error:

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
local_infile disabled in server (MYSQLSH 53025)

Ok. Let's set the parameter in the MySQL database:

 MySQL  <mysql db host>:<port> ssl  SQL > set global local_infile=ON;

Next try:

 MySQL  <mysql db host>:<port> ssl  JS > util.loadDump("/tmp/mysql",{threads: 2})
Loading DDL and Data from '/tmp/mysql' using 2 threads.
Opening dump - done 
Target is MySQL 8.4.4. Dump was produced from MySQL 5.6.0-11.6.2-MariaDB-ubu2404
ERROR: Destination MySQL version is newer than the one where the dump was created. Loading dumps from non-consecutive major MySQL versions is not fully supported and may not work. Enable the 'ignoreVersion' option to load anyway.
MySQL version mismatch (MYSQLSH 53011)

Ok, let's use that parameters:

 MySQL  <mysql db host>:<port> ssl  JS > util.loadDump("/tmp/mysql",{threads: 2, ignoreVersion: true})
Loading DDL and Data from '/tmp/mysql' using 2 threads.
Opening dump - done 
Target is MySQL 8.4.4. Dump was produced from MySQL 5.6.0-11.6.2-MariaDB-ubu2404
WARNING: Destination MySQL version is newer than the one where the dump was created. Source and destination have non-consecutive major MySQL versions. The 'ignoreVersion' option is enabled, so loading anyway.
Scanning metadata - done       
Checking for pre-existing objects - done 
Executing common preamble SQL - done 
ERROR: While processing schema `nation`: MySQL Error 1273 (HY000): Unknown collation: 'utf8mb3_uca1400_ai_ci': CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nation` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci */
ERROR: Error processing schema `nation`: Unknown collation: 'utf8mb3_uca1400_ai_ci'
ERROR: [Worker000]: While executing DDL for schema nation: Unknown collation: 'utf8mb3_uca1400_ai_ci'
ERROR: Aborting load...
Executing DDL - done       
No data loaded.
Total duration: 0 sec
1 errors and 0 warnings were reported during the load.
Error loading dump (MYSQLSH 53005)

MariaDB has collations which are unknown by MySQL.

Let's see which file has this utf8mb3_uca1400_ai_ci inside:

# grep -rli "utf8mb3_uca1400_ai_ci" *
@.json
nation.sql
nation@continents.sql
nation@countries.sql
nation@country_languages.sql
nation@country_stats.sql
nation@guests.sql
nation@languages.sql
nation@region_areas.sql
nation@regions.sql
nation@vips.sql

Hm, maybe just replace by something that could be known to MySQL?

# for file in $(grep -rli "utf8mb3_uca1400_ai_ci" *)
  do
  sed -i '' 's/utf8mb3_uca1400_ai_ci/utf8mb4_unicode_ci/g' $fil
done

Next try:

 MySQL  192.168.86.49:23306 ssl  JS > util.loadDump("/tmp/mysql",{threads: 2, ignoreVersion: true})
Loading DDL and Data from '/tmp/mysql' using 2 threads.
Opening dump - done 
Target is MySQL 8.4.4. Dump was produced from MySQL 5.6.0-11.6.2-MariaDB-ubu2404
WARNING: Destination MySQL version is newer than the one where the dump was created. Source and destination have non-consecutive major MySQL versions. The 'ignoreVersion' option is enabled, so loading anyway.
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done       
Executing common preamble SQL - done 
ERROR: While processing schema `nation`: MySQL Error 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3': CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nation` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb4_unicode_ci */
ERROR: Error processing schema `nation`: COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3'
ERROR: [Worker001]: While executing DDL for schema nation: COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3'
ERROR: Aborting load...
Executing DDL - done       
There was no remaining data left to be loaded.
Total duration: 0 sec
1 errors and 0 warnings were reported during the load.
Error loading dump (MYSQLSH 53005)

Ok, utf8mb4_unicode_unicode_ci does not fit utf8mb3. So let's that convert also:

# for file in $(grep -rli "utf8mb3" *)
  do
  sed -i '' 's/utf8mb3/utf8mb4/g' $file
done

Now it seems to run:

 MySQL  192.168.86.49:23306 ssl  JS > util.loadDump("/tmp/mysql",{threads: 2, ignoreVersion: true})
Loading DDL and Data from '/tmp/mysql' using 2 threads.
Opening dump - done 
Target is MySQL 8.4.4. Dump was produced from MySQL 5.6.0-11.6.2-MariaDB-ubu2404
WARNING: Destination MySQL version is newer than the one where the dump was created. Source and destination have non-consecutive major MySQL versions. The 'ignoreVersion' option is enabled, so loading anyway.
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done       
Executing common preamble SQL - done 
Executing DDL - done        
Executing view DDL - done       
Starting data load
100% (292.19 KB / 292.19 KB), 287.31 KB/s (20.96K rows/s), 9 / 9 tables done                
Building indexes - done               
Executing common postamble SQL - done 
9 chunks (11.26K rows, 292.19 KB) for 9 tables in 1 schemas were loaded in 1 sec (avg throughput 287.11 KB/s, 11.07K rows/s)
10 DDL files were executed in 1 sec.
Data load duration: 1 sec
Total duration: 3 sec
0 warnings were reported during the load.

As these are my test databases and the data is just a sample data, I would just accept that the import was successful.

In a production environment, I would:

  • involve support or developers of the application or software
  • involve specialist users of the application or software
  • Do many test to know and inform users about
    • the duration of export/import
    • the changes
  • Check if these parts work using MySQL instead of MariaDB
    • Monitoring
    • Backup
    • Client tools
  • Keep a fallback