Depending on your operating system and individual environments, the upgrade process can vary. Release notes should be reviewed for differences between database versions, which may affect your environment. Though we offer professional advice on how to upgrade MySQL 5.5 to MariaDB 10.2, this is not a step-by-step guide and upgrades should be completed by an experienced MySQL DBA.
To clarify, this upgrade procedure is for testing a MariaDB 10.2 database. Either option will give users a MariaDB 10.2 database that is not production ready but is ready to make changes to any removed or deprecated variables or data types or to the application. The final product is a test environment to prepare for a production MariaDB 10.2 instance.
There are two options for upgrading a MySQL 5.5 instance to MariaDB 10.2: an in-place upgrade or a dump-and-load upgrade.
To begin, the in-place upgrade option, fortunately, does not require new hardware. Instead, MySQL/MariaDB is upgraded in-place during the downtime, and because the data already resides on the server this option usually results in significantly less downtime for large datasets (say, larger than 100 GB). In this case, however, there is no direct in-place upgrade path from MySQL 5.5 to MariaDB 10.2. Though it may seem tedious, this option requires upgrading from MySQL 5.5 to MySQL 5.6, then to MySQL 5.7, and then to MariaDB 10.2.
As stated above, we advise users review release notes to make the appropriate changes to the application, the database table structures and to the MySQL configuration files. And, any upgrade should first be carried out in a test environment.
Unlike the in-place upgrade, the dump-and-load option requires new hardware (or a new VM). The operating system is installed on the new server and then MariaDB is installed — both installations come prior to the downtime. During the downtime, the source server (running MySQL 5.5) is dumped using mysqldump, and then that dumpfile is then loaded into the destination server (running MariaDB 10.2). In addition to the downtime benefits, an SQL dump-and-load process can help to clear up fragmentation to reclaim disk space.
It is helpful to note that there are significant changes to variables, data types, reserved words, and other portions of the database, which must be addressed prior to upgrading. Because MariaDB has more features than MySQL, the additional features should be reviewed for optimization of the database before upgrading. Before making the appropriate changes to the application and to the MySQL/MariaDB configuration files, be sure to review the release notes. Just like with the in-place upgrade, any upgrade using the dump-and-load option should first be carried out in a test environment.
Deciding on which option to upgrade with is contingent on a couple different items. As far as efficiency when it comes to these two options for an upgrade, it really depends on the size of the data. Very large datasets are time-consuming to migrate using mysqldump. For example, a 1 TB database can take up to 24 hours to back up with mysqldump. In this scenario, they would need to run an in-place upgrade given time constraints for an export/import. Additionally, the restore can sometimes take over twice as long as the original backup required to complete.
Another reason for choosing an in-place upgrade would be if there is no access to an additional server. On the other hand, if the user had a smaller data set (let’s say, roughly 15 GB) and were able to create a separate VM, then they might find the dump-and-load option more desirable given the size of the data and the amount of downtime they are willing to take for their upgrade. These are just a few reasons for choosing one over the other. Again, it all depends on the size of data, server/VM access, available downtime, and what the user is looking to accomplish with upgrading.