Upgrading from MySQL to MariaDB

For all practical purposes, you can view MariaDB as an upgrade of MySQL:

  • Before upgrading, please check if there are any known incompatibilities between your MySQL release and the MariaDB release you want to move to.
  • In particular, note that the JSON type in MariaDB is a LONGTEXT, while in MySQL it's a binary type. See Making MariaDB understand MySQL JSON.
  • If you are using MySQL 8.0 or above, you have to use mysqldump to move your database to MariaDB.
  • For upgrading from very old MySQL versions, see Upgrading to MariaDB from MySQL 5.0 (or older version).
  • Within the same base version (for example MySQL 5.5 -> MariaDB 5.5, MySQL 5.6 -> MariaDB 10.0 and MySQL 5.7 -> MariaDB 10.2) you can in most cases just uninstall MySQL and install MariaDB and you are good to go. There is no need to dump and restore databases. As with any upgrade, we recommend making a backup of your data beforehand.
  • You should run mariadb-upgrade (as you would with mysql_upgrade in MySQL) to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses. Note that if you use a MariaDB package, mariadb-upgrade is usually run automatically.
  • All your old clients and connectors (PHP, Perl, Python, Java, etc.) will work unchanged (no need to recompile). This works because MariaDB and MySQL use the same client protocol and the client libraries are binary compatible. You can also use your old MySQL connector packages with MariaDB if you want.

Upgrading on Windows

On Windows, you should not uninstall MySQL and install MariaDB, this would not work, the existing database will not be found.

Thus On Windows, just install MariaDB and use the upgrade wizard which is part of installer package and is launched by MSI installer. Or, in case you prefer command line, use mysql_upgrade_service <service_name> on the command line.

Upgrading my.cnf

All the options in your original MySQL my.cnf file should work fine for MariaDB.

However as MariaDB has more features than MySQL, there is a few things that you should consider changing in your my.cnf file.

  • MariaDB uses by default the Aria storage engine for internal temporary files instead of MyISAM. If you have a lot of temporary files, you should add and set aria-pagecache-buffer-size to the same value as you have for key-buffer-size.
  • If you don't use MyISAM tables, you can set key-buffer-size to a very low value, like 64K.
  • If using MariaDB 10.1 or earlier, and your applications often connect and disconnect to MariaDB, you should set up thread-cache-size to the number of concurrent queries threads you are typically running. This is important in MariaDB as we are using the jemalloc memory allocator. jemalloc usually has better performance when running many threads compared to other memory allocators, except if you create and destroy a lot of threads, in which case it will spend a lot of resources trying to manage thread specific storage. Having a thread cache will fix this problem.
  • If you have a LOT of connections (> 100) that mostly run short running queries, you should consider using the thread pool. For example using : thread_handling=pool-of-threads and thread_pool_size=128 could give a notable performance boost in this case. Where the thread_pool_size should be about 2 * number of cores on your machine.

Other Things to Think About

  • Views with definition ALGORITHM=MERGE or ALGORITHM=TEMPTABLE got accidentally swapped between MariaDB and MySQL. You have to re-create views created with either of these definitions (see MDEV-6916).
  • MariaDB has LGPL versions of the C connector and Java Client. If you are shipping an application that supports MariaDB or MySQL, you should consider using these!
  • You should consider trying out the MyRocks storage engine or some of the other new storage engines that MariaDB provides.

See Also


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.