You are here
MySQL - MariaDB migration
Table of Contents
- General findings
- Migration from MySQL 5.x to MariaDB 5.5
- Migration from MySQL 5.0 to MariaDB 10.3
- Migration from MariaDB 10.0 to Percona Server 5.6
- Migration from MySQL/Percona 5.6 to MariaDB 10.0
- Migration from MySQL 5.7 to MariaDB 10.2
- Migration from MySQL 5.7 to MariaDB 10.3
- Migration from MySQL 5.7 to MariaDB 10.4
- Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
- Migration from MySQL 5.7 to MariaDB 10.5
- Migration from MySQL 5.7 to MariaDB 10.6
- Migration from MySQL 5.7 to MariaDB 10.6 on Windows
More and more MySQL users want to switch from MySQL/Percona Server to MariaDB over time or, more rarely, the other way around. This is mostly caused by the change of the default in the Linux Distributions (RHEL/CentOS 7, SLES 12, Debian 8) to MariaDB.
Up to MySQL/MariaDB 5.5 everything was quite easy, both Branches claim to be a drop-in replacement of each other. But after the separation into different forks (MariaDB 10.0 ff. vs. MySQL 5.6 ff.) we expect more and more problems migrating from one branch to the other what we call sidegrade.
Percona Server code is genetically closer to MySQL than MariaDB. So we expect to see less problems on this sidegrade.
See also our article about Migration between MySQL/Percona Server and MariaDB
General findings
- Some customers experienced performance slow-down of some specific queries. This has to do with differences in the optimizers. Also faster response time has to be expected but you will not complain about it... This can happen in both directions.
- MySQL to MariaDB migration: handling privilege table differences when using
mysqldump
Migration from MySQL 5.x to MariaDB 5.5
- Significant slow-down experienced upgrading from MySQL 5.0 to MariaDB 5.5 on CentOS 7 due to wrong MariaDB optimizer decision for sub-query optimization.
Migration from MySQL 5.0 to MariaDB 10.3
- We tried to upgrade from MySQL 5.0 to MariaDB 10.3 via direct Replication. This did not work because of an error in the IO thread of the replication:
Last_IO_Errno: 1595, Relay log write failure: could not queue event from master
We will try with an MariaDB 5.5 intermediate Slave...
Migration from MariaDB 10.0 to Percona Server 5.6
- Sidegrade from MariaDB 10.0 to MySQL/Percona 5.6: MariaDB binary logs cause error messages in MySQL error logs. Purge of the old (MariaDB) binary logs helps to get rid of the messages. The utility
mysql_upgrade
does not solve all problems. Needs some manual fixes on the tables:mysql.event
,mysql.innodb_table_stats
andmysql.innodb_index_stats
.
Migration from MySQL/Percona 5.6 to MariaDB 10.0
- Sidegrade from MySQL/Percona 5.6 to MariaDB 10.0:
InnoDB: in InnoDB data dictionary has unknown flags 40/50/52.
The commandOPTIMZE TABLE
solves these issues. The utilitymysql_upgrade
does not solve all problems. Needs some manual fixes on the tables:mysql.innodb_table_stats
andmysql.innodb_index_stats
.
You get rid of these error messages. If it causes any technical harm we cannot say so yet. To be on the safe side use a dump/restore to migrate the data (careful with huge databases!!!)
Migration from MySQL 5.7 to MariaDB 10.2
MySQL 5.7 handles JSON
data types like LONGBLOB
s. MariaDB 10.2 stores JSON
data types in LONGTEXT
fields. This means that with JSON
data types we have an incompatibility issue. These data types need some kind of conversion. Since MariaDB 10.5 there is the MYSQL_JSON
plugin to cover this issue.
Migration from MySQL 5.7 to MariaDB 10.3
Some table creation statements seems to be not compatible any more. Nasty but easy to fix:
$ zcat zabbix.sql.gz | mysql -u root zabbix ERROR 1005 (HY000) at line 1028: Can't create table `zabbix`.`history_str` (errno: 140 "Wrong create options") CREATE TABLE `history_str` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) unsigned NOT NULL DEFAULT '0', `value` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '', `ns` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
Migration from MySQL 5.7 to MariaDB 10.4
More detail can be found in the following article: Migration from MySQL 5.7 to MariaDB 10.4
Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
The sst user additionally needs the PROCESS
privilege.
More detail can be found in the Migration Workshop - PXC 5.5 to MariaDB 10.4 Slides.
Migration from MySQL 5.7 to MariaDB 10.5
MySQL 5.7 handles JSON
data types like LONGBLOB
s. MariaDB 10.5 stores JSON
data types in LONGTEXT
fields. This means that with JSON
data types we have an incompatibility issue. These data types need some kind of conversion. Since MariaDB 10.5 there is the MYSQL_JSON
plugin to cover this issue.
See also: JSON Data Type and Making MariaDB understand MySQL JSON.
Migration from MySQL 5.7 to MariaDB 10.6
Partitioned tables containing JSON columns seems NOT to be supported by the MySQL JSON plug-in. We opened a bug report at MariaDB for this: MDEV-32277: Migration from MySQL 5.7 to MariaDB 10.6 with partitioned tables fails. A workaround could be to alter the JSON column to TEXT already on MySQL and after the upgrade convert it back to JSON on MariaDB. Has to be verified yet...
Migration from MySQL 5.7 to MariaDB 10.6 on Windows
- Attempt 1: Dumping with
mysqldump
and restoring withmariadb
CLI failed. Attempt 2: Dumping withmariadb-dump
and restoring withmariadb
CLI failed. Following errors on Windows:ERROR 1064 (42000) at line 1 in file: 'C:\backup_mysql.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 ERROR at line 1 in file: 'C:\backup_mysql.sql': No query specified
We did NOT investigate any further and did NOT find the reason. - Verfication tests on Linux did NOT show the same symptoms but the following error:
SQL> mariadb --user=root < /tmp/database_dump.sql ERROR 1118 (42000) at line 1240: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
Fixing the table columns solved the problem...
- Migration to MariaDB 10.6 on Windows miserably failed because of frequent nested Stored Procedure calls in combination with many
JSON_EXTRACT()
function calls and JSON documents causing very high CPU load. Rolling back...