You are here
MySQL - MariaDB sidegrade
Table of Contents
- General findings
- MySQL 5.x to MariaDB 5.5
- MariaDB 10.0 to Percona Server 5.6
- MySQL/Percona 5.6 to MariaDB 10.0
- MySQL 5.7 to MariaDB 10.3
- MySQL 5.7 to MariaDB 10.4
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
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.
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
.
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!!!)
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;
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