Migrating from MariaDB to MySQL HeatWave - Motivation, process and best practice

Migrating from MariaDB to MySQL HeatWave

Many of the world's largest and fastest-growing organizations including Facebook, Twitter, Booking.com, and Verizon rely on MySQL to save time and money powering their high-volume Web sites, business-critical systems, and packaged software.

Continuing with our webinar series about migrating to MySQL HeatWave, given the recent news that MariaDB has discontinued SkySQL and Xpand, as well as the recent financial difficulties, the MySQL Team is ready to help with migrations from MariaDB.

Join this webinar to learn why you need to migrate from MariaDB to MySQL HeatWave – the only MySQL database service in the cloud for OLTP, OLAP, ML, and Lakehouse – and the operations that are involved in the migration process.

Transcript

1. Migrating from MariaDB to MySQL HeatWave Motivation, process and best practice Olivier Dasini Cloud Solutions Architect @ Oracle MySQL olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz 25/04/2024
2. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 2 Me, Myself & I 2 Olivier DASINI  MySQL Geek  Enjoying databases for 20+ years  Addicted to MySQL for 15+ years  MySQL Writer, Blogger and Speaker  Also former : DBA, Consultant, Architect, Trainer, ...  Cloud Solutions Architect at Oracle MySQL  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Slides: https://speakerdeck.com/freshdaz
3. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 3 Agenda 1. Why migrate from MariaDB to MySQL HeatWave? 2. Migration process 3. Potential challenges and solutions/workarounds 4. Summary 3
4. Why migrate from MariaDB to MySQL HeatWave? 4 Copyright © 2024, Oracle and/or its affiliates
5. 5 Copyright © 2024, Oracle and/or its affiliates https://finance.yahoo.com/quote/MRDB/
6. Why migrate from MariaDB? MySQL is the #1 open source RDBMS and MariaDB is a fork of MySQL • FORK - take a copy of source code from one software package and start independent and separated development on it • MariaDB has diverged significantly and none of the modern advances in MySQL 8.0 are available in any version of MariaDB MySQL, part of Oracle, is financially secure • Given the negative market reaction to MariaDB’s IPO, their ability to fund their ongoing operations is in doubt MySQL core technology is 100% developed by MySQL • MariaDB depends on third party companies: upstream MySQL for core technology, Percona (XtraBackup), Codership (Galera), Spider Engine, etc. MySQL Engineering Team is 300+ people Oracle has the largest MySQL dedicated support organization • MariaDB Support has a very small team and cannot cover the breadth of issues that arise, not just about MariaDB but also about the various third-party products that are included MySQL HeatWave is a single database service for OLTP, OLAP, ML, and Lakehouse … and soon GenAI/Vector store features 6 Copyright © 2024, Oracle and/or its affiliates
7. The MySQL universe - The view from the moon… Community, Enterprise, Cloud Services (HeatWave) 7 Copyright © 2024, Oracle and/or its affiliates MySQL Commercial/Enterprise MySQL Community + MySQL Enterprise Backup MySQL Enterprise Monitor MySQL Enterprise Authentication MySQL Enterprise Audit MySQL Enterprise TDE MySQL Enterprise Masking MySQL Enterprise Firewall MySQL Technical Support … MySQL Cluster CGE MySQL Cluster NDB + MySQL Enterprise + MySQL Cluster Manager MySQL Community MySQL Server MySQL Client, Workbench MySQL Shell MySQL GR plugin & InnoDB Cluster & Router MySQL Operator for Kubernetes MySQL Connector (C API, Java, Node.js, others) MySQL Support for MS VS Code (Preview) … MySQL Cluster NDB MySQL NDB Storage Engine MySQL NDB Operator for Kubernetes MySQL HeatWave (Cloud Services) MySQL HeatWave Databases Services (for OLTP) MySQL HeatWave (for Analytics) MySQL HeatWave Lakehouse MySQL HeatWave AutoML (for Machine Learning) MySQL HeatWave on AWS https://www.mysql.com/products
8. Why Oracle Cloud Infrastructure (OCI)? OCI Platform has a full IaaS, SaaS, PaaS support • Network, Compute, Storage • Kubernetes • Analytics Dashboard • Etc. OCI is a gen2 public cloud • Improved security • Improved performances OCI is cheaper than competitors OCI offers MySQL and DBaaS (MySQL HeatWave) • The only MySQL DBaaS supported • Always updated • With HeatWave Cluster 8 Copyright © 2024, Oracle and/or its affiliates
9. Why Oracle Cloud Infrastructure (OCI)? 9 Copyright © 2024, Oracle and/or its affiliates BUSINESS ANALYTICS Analytics Cloud, Fusion Analytics Analytics SERVERLESS Events, Functions, API Gateway BUSINESS & INDUSTRY SaaS ERP, HCM, SCM, Sales, Marketing, Service, Vertical Industry APP INTEGRATION Integration Cloud, Workflow, Notifications, Email Delivery Applications Open Source DBs & Others MySQL MySQL, NoSQL, Postgres, Search Indexing, Distributed Cache ORACLE DBs ATP, ADW, DBCS VM/BM, JSON, Dedicated, Exadata, Exadata C@C Databases MESSAGING Streaming, Queueing, Service Connector BIG DATA Big Data, Data Flow, Data Integration, Data Catalog, Golden Gate AI SERVICES Data Science, Text Analytics, Vision Anomaly Detection Data & AI OS, VMWARE Autonomous Linux, OS Mgmt Service, Marketplace COMPUTE Bare metal, VM, CPUs, GPUs, HPC CONTAINERS Containers, Kubernetes, Service Mesh, Registry NETWORKING VCN, LB, Service Gateway, FC, VPN, Cluster Networking STORAGE NVMe, Block, File, Object, Archive, Data Transfer Core Infrastructure OBSERVABILITY Monitoring, Logging, Logging Analytics, Notifications, Events, Operations Insights, APM, Management Cloud CLOUD OPS IAM, Compartments, Tagging, Console, Cost Advisor SECURITY Cloud Guard, Security Zones, Vault, KMS, Data Safe, DDoS, WAF Governance & Administration INFRASTRUCTURE as CODE Resource Manager, Terraform, Ansible LOW CODE APEX, Digital Assistant APPDEV Visual Builder Studio, GraalVM, Helidon, SQL Developer, Shell, APIs/CLI/SDKs/Docs Developer services 40 + COMMERCIAL REGIONS / GOV REGIONS / CLOUD@CUSTOMER
10. Why MySQL as DBaaS 10 Copyright © 2024, Oracle and/or its affiliates Automation MySQL HeatWave Database High Availability Read Replicas Backup Query Acceleration AutoPilot AutoML Security Patch & Upgrade Provision & Configure OS OS Security Patch & Upgrade OS Installation Server Hardware Provisioning & Maintenance Storage Storage Provisioning & Maintenance Data Center Rack & Space Power, HVAC, Networking 100% developed, managed and supported by MySQL
11. MySQL HeatWave Cluster One database for OLTP, Online Analytics (OLAP), Warehousing and Machine Learning Copyright © 2024, Oracle and/or its affiliates 11 Queries Results Social, eCommerce, gaming, healthcare, fintech apps. Analytics & ML tools MySQL HeatWave Analytics & Warehousing Autopilot OLTP AutoML Real-time analytics, machine learning, and OLTP in one cloud database service
12. MySQL HeatWave for OLTP Like for like on-premise replacement managed for you Standalone HA Read Scale (Standalone or HA) 12 Copyright © 2024, Oracle and/or its affiliates MySQL Database System App Server Virtual Machine Public or Private Subnet Private Subnet MySQL Database System MySQL Database System MySQL Database System Read/Write Endpoint (VIP) App Server Virtual Machine App Server Virtual Machine App Server Virtual Machine Group replication Failover paths Public or Private Subnet Private Subnet Standalone or HA MySQL Database System Read Replica Read Replica Load Balancer Async replication to 1-18 read replicas Client App Client App Client App Client App Client App Public or Private Subnet Private Subnet Read/Write Read Only Use cases: • development, non-critical apps • typical app – LAMP stack Shapes & cost • 2 ECPU / 16GB – 256 ECPU / 1TB • from $56 per month, $665 per year (2 ECPU, 16GB RAM, 50GB storage; price correct as of 5/4/2024) Automatic failover • RPO = 0 (no data loss); RTO = seconds • 99.99% availability SLA Cost is 3x standalone For read intensive applications Replicas do not have to be the same size as the source database Expand/reduce replicas with demand
13. MySQL HeatWave Query Accelerator 13 Copyright © 2024, Oracle and/or its affiliates TRANSACTIONS, REAL-TIME ANALYTICS ACROSS DATA WAREHOUSE AND DATA LAKE, AND MACHINE LEARNING IN ONE DATABASE SERVICE MySQL HeatWave Analytics In-database ML Autopilot OLTP Queries Results Social, eCommerce, IoT, gaming, fintech apps. Analytics & ML tools Object Store Database exports Process ALL workloads with MySQL HeatWave Database exports Streaming data Data Sources Enterprise Apps Web/Social Log files IoT MySQL storage Scales from 16 GB to 512 TB Available for $70/month
14. • Training, inference and explanations inside database • Training is fully automated • Explainable • Fast • Secure • Scales with size of cluster • No additional cost IN DATABASE MACHINE LEARNING HeatWave AutoML Oracle CloudWorld Copyright © 2023, Oracle and/or its affiliates Dataset Data preprocessing Algorithm selection Adaptive sampling Feature selection Hyper-parameter tuning Tuned model Model explainer Prediction explainer
15. HeatWave AutoML use cases Oracle CloudWorld Copyright © 2023, Oracle and/or its affiliates 15 Classification Player churn prediction Classify warranty claims Anomaly Detection Detect anomalies in supplies Predict assembly line jam Defective part identification Identify game hackers Predict when failure will occur IoT digital twin failure prediction Predict air pollution Return on advertising spend prediction Utilization demand forecasting Timeseries Forecasting Identify similar users Recommend movies to viewers Suggest substitute products Recommend new products Recommender System Loan default prediction Demand forecasting Predict flight delay Loan amount prediction Rain fall amount prediction Regression
16. Coming soon: Generative AI in HeatWave for new use cases Content generation & summarization • Generate insights from enterprise documents • Generate blogs from pdf instruction manuals • Summarize logs Retrieval Augmented Generation (RAG) • Search on public and private enterprise data • Search on unstructured data in vector store Natural language interaction • Natural language interaction with unstructured data • Content retrieval and response in natural language Copyright © 2024, Oracle and/or its affiliates 16 +
17. End to End Support for MySQL HeatWave 17 Copyright © 2024, Oracle and/or its affiliates From data sources to Heatwave; tooling integration and visualization Social ECommerce FinTech SaaS InnoDB HeatWave OLTP OLAP ML Tools Machine Learning Autopilot Lakehouse Database Exports MySQL HeatWave Analytics tools Database
18. Migration process From MariaDB to MySQL HeatWave on OCI 18 Copyright © 2024, Oracle and/or its affiliates
19. Migration Topics We already cover many migration topics • Go to Webinaires MySQL en français - (https://go.oracle.com/MySQLFrenchWebinars): • Migration AWS vers MySQL HeatWave • Migration de MySQL 5.7 vers MySQL 8 • Migrer vos bases MySQL vers MySQL HeatWave • Migrer d’Amazon RDS à MySQL Database Service 19 Copyright © 2024, Oracle and/or its affiliates
20. Migration Overview Planning and preparation will pay dividends Scoping and planning Determine: • Which components will be migrated • Database instance or schema? • Database users to be included? • Application(s) as well as database? • MySQL Version • LTS or Innovation • Update connectors • The impact to intersecting projects • Permitted downtime / unavailability • Cutover strategy • Big-bang or staged Baseline your database/application • Regression test suite Infrastructure build out MariaDB Side: • Host required for automation tooling (MySQL Shell) • Host must have visibility of the database instance and the internet OCI Side: • Instantiate Virtual Cloud Network • Instantiate a MySQL HeatWave instance • Instantiate a bastion and install automation tooling (MySQL Shell) • Setup a bucket in Object Storage • Get API keys for secure transfer of data Migrate Dump the database to OCI Object Storage • Use MySQL Shell’s dump instance or dump schema utilities – security & speed Load the dumped database from Object Storage into MySQL HeatWave • Use MySQL Shell’s load dump utility Test using your regression test suit Perform app related tasks then cutover 21 Copyright © 2024, Oracle and/or its affiliates
21. Create OCI services Networking • Remember to open port 3306 to MySQL HeatWave If required creates compute server • Compute or OKE machines MySQL HeatWave • Choose the right shape • Shape can be scaled up or down • HeatWave cluster can be added later on 22 Copyright © 2024, Oracle and/or its affiliates
22. MySQL Long Term Support (LTS) and Innovation Releases Choose the right MySQL version Copyright © 2024, Oracle and/or its affiliates 23 MySQL Innovation Releases • Leading-edge innovations • Easy migration between LTS & Innovation • Will likely be released every quarter • Support lifecycle: short term MySQL Long-Term Support (LTS) Releases • Stable: bugfix & security patches only • Backward compatibility • Will likely be released every 2 years • Support lifecycle: 5y premier + 3y extended Innovation releases (grey) Long Term Support (blue) Note: Numbering in the slide is an example and may change
23. Check and fix for differences Overview of MariaDB compatibility with MySQL Typically, majority of the applications uses a common subset of features between MariaDB and MySQL • No or few changes expected at application level MariaDB SQL syntax is somewhere different • Mostly for management, like users creation, roles, etc. • Check application MariaDB may use different storage engines than InnoDB • InnoDB is transactional, full ACID, MVCC,… • It's required to convert all tables to InnoDB • HeatWave (with Rapid Storage Engine) makes columnar engine useless, and OLAP workloads easier to address To simplify the checks, you can separately dump Data Model from Data, and test the import on a partial load Other differences and how to address them will be discussed later • Less common 24 Copyright © 2024, Oracle and/or its affiliates
24. Connect MariaDB Environment to OCI Data size, bandwidth and downtime are driving the choice • How big is the instance and how long is it required to transfer the dump? • How long can be the downtime at switch time? To transfer data efficiently there are various options • Create a VPN between the two environments • Requires additional software and dedicated configuration • Migration process may be easier to handle • Use intermediate hosts to temporarily store data • Use OCI Object storage with a Bastion host To use replication, a stable and secure connection is required between MariaDB and MySQL HeatWave • VPN or OCI FastConnect • Public IP assigned to MySQL HeatWave with an OCI Load Balancer • It can be used temporarily, only for the migration 25 Copyright © 2024, Oracle and/or its affiliates
25. MySQL Shell Overview MySQL Server 5.7 MySQL 8.0 Upgrade Checker Prompt Themes Auto Completion & Command History MySQL Server 8.0 Document Store X Dev API SQL CLI Output Formats (Table, JSON, Tabbed) Batch Execution JavaScript Python SQL importJSON Dump Utilities InnoDB Cluster / ReplicaSet / ClusterSet etc... Interface for Development and Administration of MySQL 26 © 2024 Oracle MySQL
26. MySQL Shell Copyright © 2024, Oracle and/or its affiliates 27 MySQL Shell is the new MySQL client that has been built with dev-ops in mind: • Scriptable client – SQL, JavaScript & Python built-in • Rapid prototyping for SQL/NoSQL applications • Embedded utilities – dump and load, upgrade checker, etc. • Easy to extend – dev-ops can create their own modules and bespoke reports Example usage: To connect to a MySQL database server use either \connect or \c followed by the connection url (e.g. root@localhost) To toggle to a particular shell type use \js or \py or \sql To ask for help use either \help or \h To ask for help on a specific module, util.help() To drill down on a module method/function util.help('copyInstance')
27. MySQL Shell Migration tool MySQL Shell is used to efficiently create the dump from MariaDB and load it in MySQL HeatWave • Multi thread dump and load and great performances • Compatibility options to migrate to MySQL HeatWave The version of MySQL Shell must be 8.3 or newer (Innovation) Install MySQL Shell on either the machine that hosts the database server to be upgraded, or on a machine that can connect to the database server • You may need to open host and network firewalls to allow TCP traffic on port 3306 MySQL Shell is available from • Commercial version is available on the Oracle support portal, go to https://support.oracle.com • The eDelivery website only provides the latest commercial versions, go to https://edelivery.oracle.com • Community version is available on mysql.com or repositories, go to https://dev.mysql.com/downloads/shell 28 Copyright © 2024, Oracle and/or its affiliates
28. How to use OCI Object storage with MySQL Shell 29 Copyright © 2024, Oracle and/or its affiliates
29. What’s an API key and config file? 30 Copyright © 2024, Oracle and/or its affiliates -----BEGIN PRIVATE KEY----- MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQC2IFMhfNuxJZDA xnT41MujZNqZztxuOEK4tpBNyF7FC0J0KwcX9d4M3uPf6YgB+DDz4sQ4RNVRjRof /la5a8Iiq3z1VL3vyUeJ9jPmqehoeHOga1zG2xbS2e7nOx2sin/3FYiedAzjqEOl 8bJAHp3GfZ9FNLERnNXgJgtIjQ9AR2El/4gaoZpedO/X98X+VJh65nuw6ejPouQV CKHzPYs127oVlV0/MOoX6lDzBalAvhraCw6hnsCDFb1emEdFcy4Ngm2LR/Gc7rV1 ... 1QdweUTW1nMdaB6mgXEhBd3IjXpXbYHBcvqbeag0Dq6wdYUDPOmhax6f0r5mqgah 2iW1wEFEYt0lir8OxwymKtaZ -----END PRIVATE KEY----- Log into your OCI account (1) Go to your profile, (2) click on API Keys, then (3) Add API Key Download the private key and (4) copy the config file 1. Click icon and then select either My Profile or User Settings from the drop down menu 2. Click API Keys 3. Click Add API Key 4. Copy the content and use it to create config files on the bastions
30. Installing the config file and private API key on the bastions The downloaded API key (in this case called, oci_api_key.pem, and OCI config file called, config, should be installed under .oci in the user’s home folder The user on the OCI compute instance is usually called opc (or ubuntu on... Ubuntu distributions) All files should be made read only for the user, i.e. in the .oci directory run chmod 0400 * 31 Copyright © 2024, Oracle and/or its affiliates
31. MySQL Shell’s dump and load utilities https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.3/group__util.html MySQL Shell creates a directory where multiple files are stored Many options to dump a full instance, schemas or tables, to migrate MariaDB is better to use dumpInstance(String outputUrl, Dictionary options) • Dumps all schemas, use includeSchemas:[list] or excludeSchemas:[list] to limit only to application • Exclude user swith users:false dumpSchema(List schemas, String outputUrl, Dictionary options) • For a specific schema or group of schemas (exclude MariaDB specific schemas) To load the dump, the command is the same for all loadDump(String url, Dictionary options) • Loads a dump created by dumpInstance(), dumpSchema() or dumpTables() into the target database If there is a direct network connection between MariaDB and MySQL HeatWave, it is possible to copy util.copySchemas(schemas, connectionData[, options]) Users can't be natively dumped with MySQL Shell, see later how to migrate them 32 Copyright © 2024, Oracle and/or its affiliates
32. Additional options When dumping from MariaDB and loading into MySQL HeatWave on OCI Options for Object Storage osNameSpace • A string which uniquely identifies your area of object storage. Object storage is divided into buckets osBucketName • Where to dump to and load from. Note the outputUrl and url parameters give the folder name ociConfigFile • Where to find the authentication and encryption details to secure comms between endpoints ocimds • For dump commands only, set this to true, to ensure the dump will be compatible with MySQL HeatWave Recommended settings for compatiblity force_innodb • To automatically convert tables strip_definers • Remove the DEFINER clause from views, routines, events, and triggers, so these objects are created with the default definer create_invisible_pks • Add missing PKs strip_tablespaces • Remove the TABLESPACE clause from CREATE TABLE statements Usability options dryRun • Set to true for testing, remove or set to false when performing the actual run threads • Default is 4 – tune according to your number of (v)cpu For a full list of options and how they relate to individual dump and load commands see https://dev.mysql.com/doc/mysql-shell /8.3/en/mysql-shell-utilities-dump-inst ance-schema.html 33 Copyright © 2024, Oracle and/or its affiliates
33. Identify Database schemas to migrate MariaDB [(none)]> show databases; +---------------------+ | Database | +---------------------+ | calpontsys | | columnstore_info | | employees | | infinidb_querystats | | information_schema | | mysql | | nation | | performance_schema | | proddb | | sys | | world | +---------------------+ 34 Copyright © 2024, Oracle and/or its affiliates Migrate only application schemas We don’t want to migrate system or MariaDB only schemas
34. Example Dump: connect to MariaDB • Check errors mysqlsh> \c admin@mariadb util.dumpInstance('./full',{includeSchemas:['employees','nation','proddb','world'], users:false, ocimds:true, threads: 4, compatibility:['force_innodb', 'strip_definers', 'strip_tablespaces', 'create_invisible_pks'],dryRun:true}); • Dump util.dumpInstance('./full',{includeSchemas: ['employees','nation','proddb','world'],users:false,ocimds:true,threads: 4,compatibility: ['force_innodb', 'strip_definers', 'strip_tablespaces', 'create_invisible_pks'],dryRun:false}); Load: connect to MySQL HeatWave • Check Load mysqlsh> \c admin@mysql-heatwave util.loadDump('./full',{ignoreVersion:true,DryRun:true}) • Load util.loadDump('./full',{ignoreVersion:true,DryRun:false}) 35 Copyright © 2024, Oracle and/or its affiliates
35. Migrate Users MariaDB and MySQL are using different syntax for users management, migrate users may requires some manual activity MySQL Shell can't copy natively MariaDB users, but a community plugin can be used user.getUsersGrants([find][, exclude][, ocimds][, session]) user.copy([dryrun][, ocimds][, force][, session]) • see here: https://github.com/lefred/mysqlshell-plugins/wiki/user MariaDB mysqldump can export only users, but you need to edit the sql before execute it on MySQL HeatWave mysqldump -uroot -p --system=users > users.sql • MariaDB user creation syntax: CREATE USER `username`@`%` IDENTIFIED BY PASSWORD '*81F5E…'; • MySQL user creation syntax: CREATE USER `username`@`%` IDENTIFIED WITH mysql_native_password AS '*81F5E…'; 36 Copyright © 2024, Oracle and/or its affiliates
36. How to reduce downtime Migration process as described has downtime from dump starting to load ending. To reduce the dump time • MySQL Shell execute dump and load activities with multi-thread (default:4, configurable) • Adjust CPU of the machine where MySQL Shell is running to increase parallelism • In case the dump is taken directly to object storage, test internet connectivity performances • Evaluate the util.copyInstance or util.copySchemas option that in a single step dump from MariaDB and load to MySQL HeatWave To reduce load time • MySQL Shell execute dump and load activities with multi-thread (default:4, configurable) • Adjust CPU of the machine where MySQL Shell is running to increase parallelism • Every 2 ECPU increase network bandwidth by 1Gb (scaling up and down may reduce timing) • This is valid for MySQL HeatWave and for OCI compute machines • Disable crash consistency on MySQL HeatWave increase load performances, at cost of Durability • In MySQL HeatWave bigger disk have bigger disk IOPS and bandwidth 37 Copyright © 2024, Oracle and/or its affiliates
37. Replication to reduce downtime To reduce migration downtime, you can setup replication between MariaDB and MySQL, but it's not supported • In case of problems, you can try to use a MySQL 5.7 in the middle MariaDB -> MySQL 5.7 -> MySQL HeatWave To configure MySQL HeatWave channel (=replication) • MySQL GTID is not compatible with MariaDB, so, use binlog position based replication • The position to use to setup replication is located in the dump directory of MySQL Shell, inside the @.json file … "binlogFile": "master1-bin.000002", "binlogPosition": 344, … • Disable SSL if not configured in MariaDB • Channel filters on specific schemas or tables may help to exclude replication issues 38 Copyright © 2024, Oracle and/or its affiliates Application MySQL HeatWave Asynchronous replication
38. Check and fix for differences 39 Copyright © 2024, Oracle and/or its affiliates
39. Recap: Check and fix for differences Overview of MariaDB compatibility with MySQL Typically, majority of the applications uses a common subset of features between MariaDB and MySQL • No or few changes at application level MariaDB SQL syntax is sometimes different • Mostly for management, like users creation, roles, etc. • HA, backups, infrastructure is managed via OCI • Check application To simplify the checks, you can separately dump Data Model from Data, and test the import on a partial load MariaDB documentation is not always updated with what's available or not in MySQL • Check https://dev.mysql.com/doc/ website 40 Copyright © 2024, Oracle and/or its affiliates
40. Storage Engines MariaDB may use different storage engines than InnoDB, but MySQL HeatWave supports only InnoDB • InnoDB is transactional, full ACID, MVCC, … • It's required to convert all tables to InnoDB • HeatWave (with Rapid Storage Engine) makes columnar engine useless, and OLAP workloads easier to address Different storage Engines need to be converted to InnoDB • Check application compatibility 41 Copyright © 2024, Oracle and/or its affiliates
41. Functions MariaDB differs from MySQL by some different functions • For example, in MariaDB you have JSON_DETAILED which is called JSON_PRETTY in MySQL This is not a blocking factor unless those functions are present in the default value of a column • several functions are acting like aliases. If we check the output of SHOW CREATE TABLE statement • For example, ADD_MONTHS() can be translated as current_timestamp() + interval XX month If the application uses some of these functions, it may be necessary to make some modifications to use the appropriate one in MySQL 42 Copyright © 2024, Oracle and/or its affiliates
42. Data types MySQL and MariaDB have some different data types • For example, MariaDB supports INET6 as a data type and in MySQL, IPv6 values are stored into VARBINARY(16) • Change them before the dump But on the other hand, MySQL supports JSON data type that in MariaDB are stored as LONGTEXT To list all data types used in your database, you can execute the following query: MySQL> SELECT DATA_TYPE, count(*) TOT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema') GROUP BY 1 ORDER BY 2 DESC; 43 Copyright © 2024, Oracle and/or its affiliates
43. Sequences MariaDB supports sequences which are not available in MySQL 8.0 • Change them before the dump Check if there are sequences: SELECT COUNT(*), TABLE_TYPE FROM information_schema.TABLES GROUP BY table_type; Usually sequences are used as default values in columns • You can use auto_increment • Create specific table/stored procedure 44 Copyright © 2024, Oracle and/or its affiliates
44. Features specific to MariaDB PL/SQL • We have not seen many MariaDB customers using it • MySQL does not have PL/SQL • MySQL offers preview support for javascript (not yet GA) • Rewrite stored procedures or move application logic to client side System-Versioned Tables • There is no equivalent in MySQL for such tables, so drop versioning • example: ALTER TABLE mydatabase.t DROP SYSTEM VERSIONING; • Those tables will be ignored from the dump as their table type is SYSTEM VERSIONED, MySQL Shell Dump & Load is only using BASE TABLE and VIEW 45 Copyright © 2024, Oracle and/or its affiliates
45. Summary 46 Copyright © 2024, Oracle and/or its affiliates
46. Some Reminders 1.MySQL is leading MySQL 2.MySQL is continuously innovating 3.MySQL HeatWave in all its forms is the only 100% MySQL Database Service • Developed, maintained and supported by MySQL Engineers who are the source for MySQL 4.MySQL for analytics is transparent (HeatWave cluster) 5.MySQL HeatWave is a single database for OLTP, OLAP, Warehousing and Machine Learning • Real-time analytics, runs SQL unchanged, no ETL • Soon with GenAI and Vector Store capabilities 6.OCI is a security-first public cloud infrastructure 7.MySQL Shell simplifies and automates upgrade and migration 47 Copyright © 2024, Oracle and/or its affiliates
47. References Migrate from MariaDB to MySQL and MySQL HeatWave web page • https://www.mysql.com/why-mysql/migration/mariadb/ Top 10 reasons to migrate from MariaDB to MySQL HeatWave • https://www.mysql.com/why-mysql/migration/mariadb/MySQL_vs_MariaDB_Top10.pdf How to migrate from MariaDB to MySQL HeatWave guide • https://www.mysql.com/why-mysql/white-papers/migration-guide-mariadb-to-mysql-heatwave-on-oracle-cloud-infrastructure-oci/ MySQL Dump&Load manual • https://dev.mysql.com/doc/mysql-shell/8.3/en/mysql-shell-utilities-dump-instance-schema.html LeFred's blog posts, how to migrate from MariaDB to MySQL • https://lefred.be/content/migrating-from-mariadb-to-mysql-using-mysql-shell/ • https://blogs.oracle.com/mysql/post/how-to-migrate-from-mariadb-to-mysql-80 Data Migration from MariaDB to MySQL • https://dasini.net/blog/2022/03/09/data-migration-from-mariadb-to-mysql/ LeFred’s modules for MySQL Shell • https://github.com/lefred/mysqlshell-plugins/wiki/ 48 Copyright © 2024, Oracle and/or its affiliates
48. Follow us on Social Media “Data is the Oxygen of Business” 50 Copyright © 2024, Oracle and/or its affiliates
49. Get $300 in credits and try free for 30 days Get started with MySQL HeatWave oracle.com/mysql/free Learn more about MySQL HeatWave oracle.com/mysql Request a guided workshop Ask your account manager 51 Copyright © 2024, Oracle and/or its affiliates
50. Merci! Q&R Olivier Dasini Cloud Solutions Architect @ Oracle MySQL olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Twitter : @freshdaz