MySQL - MariaDB migration

Table of Contents

• General findings
• MySQL 5.x to MariaDB 5.5
• MySQL 5.0 to MariaDB 10.3
• 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
• Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
• 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

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.

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...

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 and mysql.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 command OPTIMZE TABLE solves these issues. The utility mysql_upgrade does not solve all problems. Needs some manual fixes on the tables: mysql.innodb_table_stats and mysql.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

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.

MySQL 5.7 to MariaDB 10.6 on Windows

• Attempt 1: Dumping with mysqldump and restoring with mariadb CLI failed. Attempt 2: Dumping with mariadb-dump and restoring with mariadb 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...

• Upgrade 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...