The Ultimate Guide to High Availability with MariaDB

Upgrading from MySQL to MariaDB

Contents

  1. Upgrading from MySQL 5.6 or MySQL 5.7
    1. Prerequisites
    2. MySQL SHA-256 Authentication
    3. JSON
    4. XA
    5. Encryption and Compression
    6. Config Files
      1. Audit plugin
    7. Common Steps
    8. Sample Steps for Single Instance MySQL Server
    9. MySQL replication setup
  2. Upgrading on Windows
  3. Upgrading my.cnf
  4. Other Things to Think About
  5. See Also

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 from MySQL 5.6 or MySQL 5.7

Prerequisites

Check the values of the following server variables:

select @@Innodb_file_per_table,@@Innodb_fast_shutdown\G

innodb_file_per_table should be 1. This is the default setting for MySQL and MariaDB. If not, one should use mysqldump for migration as some of the following recommendations will not work.

innodb_fast_shutdown should be 0 (at least on the migrated server during shutdown, to ensure that a full shutdown is done when taking server down). This is required when upgrading between major versions of both MySQL and MariaDB as the format of the undo or redo files can change between major versions. This variable can be set just before doing the shutdown.

If your distribution allows it, install the MariaDB packages or the MariaDB tar distribution on the database server. Do not start MariaDB yet! This will decrease the downtime while doing the migration.

MySQL SHA-256 Authentication

MariaDB does not support the MySQL SHA-256 authentication protocol as it's cumbersome and not secure for in-house attacks. (clear text password is available inside the server)

  • See [authentication-plugin-sha-256/|Authentication Plugin - SHA-256]]
  • MDEV-9804

You can check which MySQL users are using SHA-256 by executing:

SELECT user, plugin FROM mysql.user where plugin like "%sha%";

You can change the user/s to use a protocol compatible with both MySQL and MariaDB with:

ALTER USER user_name IDENTIFIED WITH mysql_native_password BY  'new_password';

JSON

MariaDB stores JSON differently than MySQL. Normally you do not have to do anything when migrating JSON data, except if you are using replication or Galera. If this is the case, then you should convert your JSON columns to TEXT to ensure that all data is stored identically in MySQL and MariaDB:

If you are using JSON columns and want to upgrade to MariaDB, use the mysql_json plugin to automatically convert MySQL JSON to TEXT.

Alternatively you need to either convert them to TEXT or use mysqldump to copy these tables to MariaDB.

You can check if you are have tables that uses the MySQL JSON type with:

select table_schema, table_name from information_schema.COLUMNS where
data_type="JSON";

You can convert the JSON column to text with:

ALTER TABLE table_name MODIFY json_column LONGTEXT;

XA

When doing a backup, ensure that there are no active XA transactions in the backup, as these transactions needs to be committed/rolled back before the migration.

Encryption and Compression

Encryption and compression are very different in MySQL and MariaDB.

Encrypted/compressed tables need to be de-encrypted/de-compressed before starting the conversion, and then encrypted/compressed again afterwards.

Detect compressed tables with the following query:

select table_name, create_options from information_schema.TABLES where
create_options like "%comp%";

Config Files

  • Create config files for MariaDB that match the MySQL cluster option files
  • There are some configuration options that differ. See System Variable Differences between MariaDB and MySQL.
  • You can use [mariadb] or [mariadb-x.y.z] (for the specific version) in the current config files for MariaDB-specific options.
  • You can also place MySQL-specific options inside a [mysqld-5.7] section. This includes all options that use mysql-specific directories for logging or replication (on other words, paths with mysql as part of the path).
  • With a combination of these, it's easy to create a config file that will work with both MariaDB and MySQL, no matter what options are present.
  • See also MDEV-32745 for an upcoming tool to automatically detect incompatible options.

Audit plugin

  • MariaDB does not have the audit_log_plugin. Rather, MariaDB uses the server_audit plugin, which takes different options.

Common Steps

  • Perform all the prerequisite steps (SHA-256) on the MySQL Server.
  • If replication is used, do these steps also on the primary.
  • Take a backup.
  • Install the MariaB packages or MariaDB tar distribution.
  • Fix the my.cnf file to work with both MariaDB and MySQL.
  • When MariaDB is installed, you can test your config files with
    mariadbd --help --verbose > /tmp/log 2>&1 which will display all unsupported config options. It's also possible to use the script at MDEV-32745 to find all unsupported options.
  • Check the log for ERROR and fix the config files if needed.

Sample Steps for Single Instance MySQL Server

In the shell:

shell> mysql --user=root ...##

In MySQL:

set @@global.innodb_fast_shutdown=0;
quit
  • Take down the node with mysqladmin shutdown or sudo service mysqld stop: 
shell> mysqladmin --user=root shutdown
  • Drop the MySQL packages: 
shell> yum -y remove Percona-Server*

or

shell> rpm -q -a | grep Percona | xargs rpm -e --nodeps

or

shell> rpm -q -a | grep -i mysql | xargs rpm -e --nodeps
  • This can also be done later if one has both MySQL and MariaDB installed at the same time)
  • Remove the unsupported auditlog (Only relevant with Percona server): 
shell> mv /etc/my.cnf.d/auditlog.cnf /etc/my.cnf.d/auditlog.cnf-backup
  • Install MariaDB if not already installed from the download site
  • Using the repo is recommended, or 
shell> yum install MariaDB-server MariaDB-client
  • Start mariadbd on the node data 
systemctl start mariadb.service
  • If it does not start, check the error file. Remove all unsupported options from the config files and try again.
  • run mariadb-upgrade: 
mariadb-upgrade
  • If there were problems with a plugin that does not work or is not supported, you can disable it with: 
mariadb mysql
  • Then, from the MariaDB client: 
select * from mysql.plugin
  • and then for each unsupported plugin: 
UNINSTALL PLUGIN IF EXISTS #plugin_name#;
  • Test your new server
  • As long as one does not create new tables or alter tables, it should be possible to go back to MySQL by:
  • Dropping all tables in the 'mysql' database 
set @@global.innodb_fast_shutdown=0;
SHUTDOWN
  • Start MySQL server with --skip-grants
  • Install the backup of the 'mysql' database with: 
shell> mysql mysql < /tmp/mysql-dump.txt
shell> mysqladmin flush-privileges

MySQL replication setup

In case of replication you can either convert one of the existing nodes directly to MariaDB or create a new node, convert that to MariaDB and then delete the old MySQL node after testing.

  • Do all the "Prerequisite steps" on the to-be-converted node.
  • Follow the Single instance MySQL server instructions for the node.
  • Start MariaDB as a replica to MySQL.
  • Test that the new node works as expected.
  • Note that a MariaDB replica will work with replication positions, not with MySQL GTID.
  • Repeat with all other nodes.
  • Switch one of the MariaDB nodes to be the primary.
  • Convert the old primary according to the above instructions.
  • Add the old primary as a replica.

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 are a few things that you should consider changing in your my.cnf file.

Other Things to Think About

See Also

Comments

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.