Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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.

Olivier DASINI

June 05, 2024
Tweet

More Decks by Olivier DASINI

Other Decks in Programming

Transcript

  1. Migrating from MariaDB to MySQL HeatWave Motivation, process and best

    practice Olivier Dasini Cloud Solutions Architect @ Oracle MySQL [email protected] 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? 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. • 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
  13. 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
  14. 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 +
  15. 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
  16. Migration process From MariaDB to MySQL HeatWave on OCI 18

    Copyright © 2024, Oracle and/or its affiliates
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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')
  25. 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
  26. How to use OCI Object storage with MySQL Shell 29

    Copyright © 2024, Oracle and/or its affiliates
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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
  36. 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
  37. 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
  38. 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
  39. 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
  40. 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
  41. 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
  42. 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
  43. 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
  44. Follow us on Social Media “Data is the Oxygen of

    Business” 50 Copyright © 2024, Oracle and/or its affiliates
  45. 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
  46. Merci! Q&R Olivier Dasini Cloud Solutions Architect @ Oracle MySQL

    [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Twitter : @freshdaz