Note that MariaDB 10.4 is not the latest version and has had new releases since 10.4. The client requirement was to move to MySQL 8.0 on the cloud for specific RDS features.
Caution: It is important to verify that you are not using any specific MariaDB features before migrating to MySQL 8.0.x.
Recently, we had a client wanting to move from MariaDB 10.4 to MySQL 8.0, and this transition presented several challenges. Incompatibilities between the versions and unsupported replication made a direct upgrade impossible. This blog will explore the hurdles encountered during the upgrade process and the innovative solution that saved the day.
The incompatibility roadblock
MariaDB 10.4 uses the Aria engine for system tables. Since Aria is a MariaDB-only engine, a direct MariaDB 10.4 > MySQL 8 ‘in-place’ upgrade is impossible for and after 10.4. I’m not listing all incompatibilities/differences, but you can find them here. I tried the in-place upgrade on my test VM for my own curiosity and got some redo log format errors/Data Dictionary errors.
1 2 3 | 2023-05-12T08:10:38.946611Z 1 [ERROR] [MY-013894] [InnoDB] Found redo log file ./ib_logfile0 which has format (v104) and is stored outside #innodb_redo. 2023-05-12T08:10:38.946683Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error. 2023-05-12T08:10:39.374389Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine. |
Importing tablespace — risky along with downtime
As the client dataset was 1TB+, we tried other quicker options before going for the logical backup/restore method (the safe/preferred option). We tried schema dump and importing tablespaces (refer to Importing InnoDB tables for more details), which worked fine on the test VM, but this option involved read lock/downtime, which wasn’t preferred for a production setting and was also considered a risky option.
The logical backup/restore approach — but how to keep data in sync?
At last, after consensus, the logical backup/restore option was selected as the safest option. But after that, we also had to test the ‘unsupported’ replication setup to ensure data can be synced from MariaDB 10.4 to MySQL 8.x until we are ready for migration to the MySQL 8.x server. This replication test worked OK on my test VMs having minimal data apart from some expected user creation statements failure which had an easy workaround.
However, while testing with client data, we were not so lucky; error logs revealed unexpected event sequences during the replication setup, leading to a crash on the MySQL 8 node.
1 2 3 4 | [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master 'source-bin.000046' binary log file, at position 368. [Warning] [MY-010444] [Repl] QUERY(BEGIN) is not expected in an event stream in the middle of a DDL. [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master 'source-bin.000046' binary log file, at position 394363. - mysqld got signal 11 ; |
We did our research for the error, but it was clear the binlogs were incompatible, and the replication setup was unreliable.
Exploring the next option — MySQL 5.7 intermediate replica
We needed a replication link to establish minimal interruption, giving us time to do the cutover with minimal downtime. As the replication to MySQL 8 didn’t work, we tried replication from MariaDB 10.4 to MySQL 5.7 to test the binlog compatibility. So we configured a MySQL 5.7 replica after logical backup restore.
And it worked! The replication setup from MariaDB 10.4 > MySQL 5.7 worked, and the replica got in sync without any issues. We monitored the replica for several days to ensure no errors were encountered. Later we configured another node with MySQL 8.0 replicating from the MySQL 5.7 node making a chain replication topology, and the migration to MySQL 8.x was done as usual. In short, the topology was configured as below:
Conclusion
Introducing a MySQL 5.7 intermediate replica proved to be a helpful solution in this case, overcoming the replication challenges between MariaDB 10.4 and MySQL 8.0. It also shows the importance of thorough testing in complex database upgrades. I hope it encourages others facing similar obstacles during database migrations to consider alternative strategies and embrace the power of testing for successful outcomes. Or you have Percona to help with that!
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
