Migrating from MySQL 5.5 to MariaDB 10.4.

Posted by in Databases, MySQL

After the return of the summer, we continue working with MySQL and MariaDB. After the entry about how to have MySQL 8 in portable format (that is, how to have MySQL 8 without installation), idem for MariaDB 10.4 both using Xampp and from files, comes the peak moment: migrating from a MySQL 5.5 database to the latest available version of MariaDB, which is version 10.4.

Moving from MySQL 5.5.x to MariaDB, without moving first to MySQL 8 neither trying other versions such as MySQL 5.6 or 5.7 or 10.0, 10.1, 10.2 of MariaDB is like stepping from the base to a 5th floor jumping from 10 to 10 steps. One false step and … error or warning!

Why migrating to MariaDB

To start with, I always have worked with MySQL, but this time, MariaDB is chosen for the issue of licenses: there is open source software ready for going into production. MariaDB started as a MySQL fork and although it has had a very parallel evolution, time is making both paths more and more divergent, but still with a lot of compatibility between them.

In fact, in many cases, if you were working with MySQL from ODBC you won’t need to update the connectors. In fact, MySQL’s own are perfectly valid with MariaDB, even with version 10.4 (the last one available as of May-June 2020).

Preparing the ground for the upgrade

There are two ways to perform the migration, but in both cases, the first is to make a backup of the database. I would also recommend you to make a copy from the data directory explorer (datadir) …. better prevent with double option!

Therefore, it’s important to make backups, but it is even more important to validate that they can be used. Without problems, something that we do not often run and then, problems come, so … check that the copies are useful.

Checking that the backups work is an interesting way to verify if you have any problems in your database, such as a view that has not been updated after a change in the data structure of a table, etc, … As you can imagine, it is a process that takes (or can take) a lot of time, but believe me it will be well invested!

Moving to MariaDB 10.4.x

In this new series about databases, dedicated to MariaDB 10.4, I am going to put some of the problems that you can find, and solutions that I have been taking, and that can be, as much of updates in MySQL 5.5, as in MySQL 8 or MariaDB 10.x.

MariaDB 10.0 is not recommended because it has finished its support, and it can be similar to MySQL 5.1. The latest version is 10.5, although there have been several versions in between. It is important to have the list of incompatiblities between MySQL and MariaDB at hand, especially with a view to the server configuration.

The features and news of the MariaDB 10.4 are:

  • Improvements in authentication, in InnoDB and in the optimizer
  • Sintaxis updates
  • New variables
  • News and improvements in replication

Also, you need to add the previous news and updates, that makes MariaDB very attractive to work with. In future entrances I will be writting about these features that I am using in my developments.

If you follow my blog, in previous entrances I use xampp with MySQL, but I`m not the only one that changed: XAMPP changed some time ago. Also, some famous Linux also changed, as you can read here.

And so, I wait for you in the next entry, where I tell you how to proceed with MySQL backups (of course, it is also applicable to MariaDB).

Happy coding!