Migrating from MariaDB to MySQL

This webinar will cover the advantages and process for migrating from MariaDB/Galera cluster to InnoDB cluster. Over these last 2 years and especially with MySQL 8.0, MySQL Innodb Cluster has matured a lot. In this webinar I will go over the key difference between both solutions. I will use my experience to show how to migrate your application from MariaDB/Galera cluster over to MySQL InnoDB cluster with the least possible amount of downtime.

The demo recording is available at https://drive.google.com/file/d/12FqRUedReL1lMOE3j347KqQj0w349504/view?usp=sharing

Transcript

1. 2 © The Pythian Group Inc., 2018 19 December 2019 Online Webinar Migrating from MariaDB to MySQL Matthias Crauwels
2. 3 © The Pythian Group Inc., 2018 Who am I?
3. 4 © The Pythian Group Inc., 2018 4 Matthias Crauwels • Living in Ghent, Belgium • Bachelor Computer Science • ~20 years Linux user / admin • ~10 years PHP developer • ~8 years MySQL DBA • 3rd year at Pythian • Currently Lead Database Consultant • Father of Leander
4. 5 © The Pythian Group Inc., 2018 5 © Pythian 2019 Helping businesses use data to compete and win
5. 6 © The Pythian Group Inc., 2018 6 Founded in 1997, Pythian is a global IT services company that helps organizations transform how they compete and win by helping them turn data into valuable insights, predictions and products. From cloud automation to machine learning, Pythian designs, implements and supports customized solutions to the toughest data challenges.
6. 7 © The Pythian Group Inc., 2019 AGENDA • Why • What is MariaDB Cluster • What is InnoDB Cluster • Comparison • Caveats • Demo
7. 8 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 8 • Why do we need a webinar about migrating from MariaDB to MySQL? Isn't it a drop in replacement? • The answer to this is simple, no it isn't a drop in replacement anymore since version 5.5. • There are 2 different development teams with different goals and focuses. • Differences are getting bigger with each release. • Classic MySQL protocol is still compatible and regular replication between both is known to work. Why?
8. 9 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 9 • Galera Cluster • Extension to MySQL - WSREP (WriteSet Replication) API • Developed by Codership • Current version v4 • Bundled with MariaDB since version 10.1 • MaxScale • Advanced database proxy, firewall and query router • Developed by MariaDB Corporation • Current version v2.4 • Business Source License (not free for most production use cases) MariaDB Enterprise Cluster aka MariaDB TX (transactions)
9. 10 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 10 • Core component: Group Replication plugin • Developed by MySQL Team at Oracle • Bundled and tested with each release of MySQL since 5.7.17 • Using GTID, Binary logs, ... • MySQL Router • Follows MySQL Community release schedule • Basic routing application that was designed to work with Group Replication • Will automatically adapt configuration on changes in the the GR cluster • MySQL Shell • Follows MySQL Community release schedule • Orchestration tool for ease of use, contains AdminAPI to hide much complexity • All components are available as part of the MySQL Community Edition InnoDB Cluster
10. 11 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 11 • License made available by MariaDB corporation which is not an Open Source License. • Limits the "production" use of projects licensed under BSL without purchasing a license from MariaDB Corporation (https://mariadb.com/bsl11/) • For MaxScale there is an "additional use grant" that prevents you to use the software in a production environment if there are 3 or more servers in the production environment without purchasing a license from MariaDB Corporation (https://mariadb.com/projects-using-bsl-11/) • 4 years after release date the software becomes available under GPLv2 Business Source License (BSL)
11. 12 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2018 12 InnoDB Cluster • Binary logs • Clone plugin • Can be fully synchronous (Different Consistency levels) • By default configured in Single-Primary mode, can be configured to Multi-Primary mode • Possibility to combine NoSQL Document store with regular SQL MariaDB (Galera) Cluster • Galera Cache • SST • Virtually synchronous (Eventual consistency) • All nodes are equal on cluster level Best practice is to have a single writer node to avoid conflicts but it requires a proxy to achieve this Comparison
12. 13 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 13 • Both only support InnoDB • Large transactions are problematic • Max cluster nodes for InnoDB Cluster is 9 • All tables are required to have a primary key in Galera • Multi-column primary keys are not supported in Galera • No support for Foreign Keys with Cascading constraint in InnoDB Cluster Multi-primary mode. Caveats
13. 14 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 14 Initial topology Preparing for the demo
14. 15 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 15 Targeted topology Preparing for the demo
15. 16 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 16 • Software Versions • running in Vagrant boxes, basebox centos/7 • nodes galera01, galera02 and galera03 ▪ MariaDB Server 10.3.21 ▪ MariaDB Client 10.3.21 ▪ MariaDB Backup 10.3.21 • nodes innodbcluster01, innodbcluster02, innodbcluster03 ▪ MySQL Community Edition 8.0.18 ▪ MySQL Shell 8.0.18 • node app01 ▪ Sysbench 1.0.18 ▪ Maxscale 2.3.15 ▪ MySQL Community Router 8.0.18 • Vagrantfile available at https://github.com/mcrauwel/galera-innodb-cluster-webinar Preparing for the demo
16. 17 © The Pythian Group Inc., 2019 © The Pythian Group Inc., 2019 17 The plan Preparing for the demo
17. 18 © The Pythian Group Inc., 2019 Demo
18. 19 © The Pythian Group Inc., 2019 Questions?
19. 20 © The Pythian Group Inc., 2019 Thank you Matthias Crauwels crauwels@pythian.com Twitter @mcrauwel