blog
Migrating from MySQL Enterprise to MariaDB 10.3
While it shares the same heritage with MySQL, MariaDB is a different database. Over the years as new versions of MySQL and MariaDB were released, both projects have differed into two different RDBMS platforms.
MariaDB becomes the main database distribution on many Linux platforms and it’s getting high popularity these days. At the same time, it becomes a very attractive database system for many corporations. It’s getting features that are close to the enterprise needs like encryption, hot backups or compatibility with proprietary databases.
But how do new features affect MariaDB compatibility with MySQL? Is it still drop replacement for MySQL? How do the latest changes amplify the migration process? We will try to answer that in this article.
What You Need to Know Before Upgrade
MariaDB and MySQL differ from each other significantly in the last two years, especially with the arrival of their most recent versions: MySQL 8.0, MariaDB 10.3 and MariaDB 10.4 RC (we discussed new features of MariaDB 10.4 RC quite recently so If you would like to read more about what’s upcoming in 10.4 please check two blogs of my colleague Krzysztof, What’s New in MariaDB 10.4 and second about What’s New in MariaDB Cluster 10.4).
With the release MariaDB 10.3, MariaDB surprised many since it is no longer a drop-in replacement for MySQL. MariaDB is no longer merging new MySQL features with MariaDB noir solving MySQL bugs. Nevertheless version 10.3 is now becoming a real alternative to Oracle MySQL Enterprise as well as other enterprise proprietary databases such as Oracle 12c (MSSQL in version 10.4).
Preliminary Check and limitations
Migration is a complex process no matter which version you are upgrading to. There are a few things you need to keep in mind when planning this, such as essential changes between RDBMS versions as well as detailed testing that needs to lead any upgrade process. This is especially critical if you would like to maintain availability for the duration of the upgrade.
Upgrading to a new major version involves risk, and it is important to plan the whole process thoughtfully. In this document, we’ll look at the important new changes in the 10.3 (and upcoming 10.4) version and show you how to plan the test process.
To minimize the risk, let’s take a look on platform differences and limitations.
Starting with the configuration there are some parameters that have different default values. MariaDB provides a matrix of parameter differences. It can be found here.
In MySQL 8.0, caching_sha2_password is the default authentication plugin. This enhancement should improve security by using the SHA-256 algorithm. MySQL has this plugin enabled by default, while MariaDB doesn’t. Although there is already a feature request opened with MariaDB MDEV-9804. MariaDB offers ed25519 plugin instead which seems to be a good alternative to the old authentication method.
MariaDB’s support for encryption on tables and tablespaces was added in version 10.1.3. With your tables being encrypted, your data is almost impossible for someone to steal. This type of encryption also allows your organization to be compliant with government regulations like GDPR.
MariaDB supports connection thread pools, which are most effective in situations where queries are relatively short and the load is CPU bound. On MySQL’s community edition, the number of threads is static, which limits the flexibility in these situations. The enterprise plan of MySQL includes threadpool capabilities.
MySQL 8.0 includes the sys schema, a set of objects that helps database administrators and software engineers interpret data collected by the Performance Schema. Sys schema objects can be used for optimization and diagnosis use cases. MariaDB doesn’t have this enhancement included.
Another one is invisible columns. Invisible columns give the flexibility of adding columns to existing tables without the fear of breaking an application. This feature is not available in MySQL. It allows creating columns which aren’t listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement when their name isn’t mentioned in the statement.
MariaDB decided not to implement native JSON support (one of the major features of MySQL 5.7 and 8.0) as they claim it’s not part of the SQL standard. Instead, to support replication from MySQL, they only defined an alias for JSON, which is actually a LONGTEXT column. In order to ensure that a valid JSON document is inserted, the JSON_VALID function can be used as a CHECK constraint (default for MariaDB 10.4.3). MariaDB can’t directly access MySQL JSON format.
Oracle automates a lot of tasks with MySQL Shell. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python.
Migration Process Using mysqldump
Once we know our limitations the installation process is fairly simple. It’s pretty much related to standard installation and import using mysqldump. MySQL Enterprise backup tool is not compatible with MariaDB so the recommended way is to use mysqldump. Here is the example process is done on Centos 7 and MariaDB 10.3.
Create dump on MySQL Enterprise server
$ mysqldump --routines --events --triggers --single-transaction db1 > export_db1.sqlClean yum cache index
sudo yum makecache fastInstall MariaDB 10.3
sudo yum -y install MariaDB-server MariaDB-clientStart MariaDB service.
sudo systemctl start mariadb
sudo systemctl enable mariadbSecure MariaDB by running mysql_secure_installation.
# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!Import dump
Mysql -uroot -p
> tee import.log
> source export_db1.sql
Review the import log.
$vi import.logTo deploy an environment you can also use ClusterControl which has an option to deploy from scratch.
ClusterControl can be also used to set up replication or to import a backup from MySQL Enterprise Edition.
Migration Process Using Replication
The other approach for migration between MySQL Enterprise and MariaDB is to use replication process. MariaDB versions allow replicating to them, from MySQL databases – which means you can easily migrate MySQL databases to MariaDB. MySQL Enterprise versions won’t allow replication from MariaDB servers so this is one-way route.
Here are some general rules pointed by the MariaDB.
- Replicating from MySQL 5.5 to MariaDB 5.5+ should just work. You’ll want MariaDB to be the same or higher version than your MySQL server.
- When using a MariaDB 10.2+ as a slave, it may be necessary to set binlog_checksum to NONE.
- Replicating from MySQL 5.6 without GTID to MariaDB 10+ should work.
- Replication from MySQL 5.6 with GTID, binlog_rows_query_log_events and ignorable events works starting from MariaDB 10.0.22 and MariaDB 10.1.8. In this case, MariaDB will remove the MySQL GTIDs and other unneeded events and instead adds its own GTIDs.
Even if you don’t plan to use replication in the migration/cutover process having one is a good confidence-builder is to replicate your production server on a testing sandbox, and then practice on it.
We hope this introductory blog post helped you to understand the assessment and implementation process of MySQL Enterprise Migration to MariaDB.