You are here

Migration from MySQL 5.7 to MariaDB 10.4

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root
MariaDB upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql.columns_priv                                 OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views from mysql
Error    : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist
status   : Operation failed
Error    : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error
error    : Corrupt
Error    : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist
Error    : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
error    : Corrupt
sys.x$waits_global_by_latency                      OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
staging.sales                                      OK
Warning  : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
status   : OK
Phase 7/7: Running 'FLUSH PRIVILEGES'

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force

Some hints for upgrading

  • Make a backup first before you start!
  • Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
    The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
  • It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.