Import Data to MySQL Database Service - From On-Prem, Cloud or an OCI Object Storage

We are going through the process of importing data from a MySQL on-prem workload to MySQL HeatWave Database Service.
We will look on how to export the data, store it on Oracle Cloud Infrastructure in an Object Storage bucket, and finally import the data to MySQL HeatWave.

Transcript

1. Import Data to MySQL Database Service From On-Prem, Cloud or an OCI Object Storage Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz
2. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 2 Me, Myself & I  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also: DBA, Consultant, Architect, Trainer, ...  MySQL Cloud Principal Solutions Architect EMEA at Oracle  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Twitter: @freshdaz Olivier DASINI
3. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 3 Goals 3 What we want to achieve
4. • Import data into MySQL Database Service and MySQL HeatWave. • Learn how to – Export the data from a MySQL instance – Store them on an OCI Object Storage bucket – Import data from a bucket to MySQL Database Service Goals Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 4
5. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 5 Prerequisites 5 Topics not covered
6. • MySQL Shell is installed (used as MySQL client) – https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html – MySQL Shell is also available on the OCI Cloud Shell • https://docs.oracle.com/en-us/iaas/Content/API/Concepts/cloudshellintro.htm • OCI CLI configuration file (used for MySQL Shell dump utility) – https://docs.oracle.com/en-us/iaas/Content/API/Concepts/sdkconfig.htm • OCI user must be part of a group with the suitable policies – https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/policygetstarted.htm • An Object Storage Bucket must have been created • (Optionally) A dedicated compartment (named DBA in this presentation) Prerequisites Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 6
7. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 7 Workflow 7 The big picture
8. MySQL Shell Dump MDS Data Import or MySQL Shell Dump Load OCI Object Storage OCI Network Your Network MySQL On-premise, public or private cloud... MySQL Database Service Migration to MySQL HeatWave Database Service Using MySQL Shell & Data Import Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 8
9. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 9 Plan 9
10. • 1/ Dump data from MySQL to a bucket • 2/ Create a MySQL HeatWave instance from the bucket dump • 3/ Add the HeatWave Cluster • 4/ Load data into the HeatWave cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 10 Plan
11. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 11 Dump data from MySQL to a bucket 11 Using MySQL Shell Dump utilities
12. • Connect to your source MySQL instance using the MySQL Shell – OCI CLI configuration file must be properly setup • You can either use: – util.dumpInstance() – util.dumpSchemas() – util.dumpTables() • Ex. util.dumpSchemas(["tpch", "employees"], "", {osBucketName: "mysqlData", osNamespace: "abcdefghijkl", ocimds: true, threads: 16, compatibility: ["force_innodb", "strip_restricted_grants", "strip_definers", "strip_tablespaces", "create_invisible_pks"]}) • https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html MySQL Shell Instance / Schema / Table Dump Utilities Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 12 Export of all schemas or a selected schema from a MySQL instance into an OCI Object Storage bucket or a set of local files
13. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 13
14. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 14 Create a MySQL HeatWave instance from the bucket dump 14
15. • Open the navigation menu, and select Databases. Under MySQL, click DB Systems • Click Create MySQL DB System • Provide DB System information • Setup your DB system • Create Administrator credentials • Configure Networking • Configure placement • Configure hardware • Configure Backup Plan • Show Advanced Options – Data Import – Click here to create a PAR URL for an existing bucket • Click Create Creating a DB System Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 15 https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-db-system1.html#GUID-AE89C67D-E1B1-4F11-B934-8B0564B4FC69
16. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 16 Creating a DB System (main options) 1/3
17. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 17 Creating a DB System (main options) 2/3
18. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 18 Creating a DB System (main options) 3/3
19. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 19 Add the HeatWave Cluster 19
20. MySQL HeatWave - MySQL in-Memory Query Accelerator Architecture Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 20
21. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 21 Add the HeatWave Cluster
22. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 22 Add the HeatWave Cluster – Node Count Estimation
23. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 23 Load data into the HeatWave cluster 23
24. Automate the most important & challenging aspects of achieving high query performance at scale MySQL Autopilot: Machine Learning Based Automation https://dev.mysql.com/doc/heatwave/en/heatwave-autopilot.html Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 24
25. • Auto Parallel Load facilitates the process of loading data into HeatWave by automating many of the steps involved, including: – Excluding schemas, tables, and columns that cannot be loaded – Verifying that there is sufficient memory available for the data – Optimizing load parallelism based on machine-learning models – Loading data into HeatWave. • Auto Parallel Load, which can be run from any MySQL client or connector, is implemented as a stored procedure named heatwave_load, which resides in the MySQL sys schema • Ex: CALL sys.heatwave_load(JSON_ARRAY('tpch'), NULL); Load data into HeatWave using Auto Parallel Load Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 25 https://dev.mysql.com/doc/heatwave/en/auto-parallel-load.html
26. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 26 Load data into HeatWave using Auto Parallel Load
27. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 27 MySQL Database Service 27 100% developed, managed, & supported by the MySQL team
28. MySQL Database Service – Highlights Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 28
29. MySQL Database Service costs less Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 29 MySQL Database Service: Standard E3 AMD 16GB/Core, all regions have the same price. Amazon RDS: Intel R5 16GB/Core, AWS US East. Azure: Memory Optimized Intel 20GB/Core, MS Azure US-East. Google: High Memory N1 Standard Intel 13GB/Core, GCP Northern Virginia. Configuration: 100 OCPUs, 1 TB Storage. MySQL Database Service Amazon RDS Microsoft Azure Google Cloud SQL $0 $50,000 $100,000 $150,000 $200,000 $250,000 $65,833 $215,652 $212,974 $170,244 Annual cost for a 100 OCPUs, 1TB Storage configuration
30. MySQL Database Service: High Availability, multi AD Region 30 Copyright © 2021, Oracle and/or its affiliates. All rights reserved. Deployed across fault and availability domains Native MySQL Group Replication No data loss in case of failure (RPO=0) Online and fast fail-over (RTO=minutes) Inbound Replication MySQL Database Service Applications (Secondary) (Secondary) (Primary) AD1 MySQL Group Replication AD2 AD3 Fault-tolerant system with automatic failover & zero data loss through Group Replication
31. MySQL HeatWave - MySQL in-Memory Query Accelerator Easily run high performance analytics against your MySQL database, no ETL required Single MySQL database for OLTP & analytics applications All existing applications work without any changes Extreme performance: 400x faster than MySQL, scales to thousands of cores OLAP Applications OLTP Applications OLTP Engine Analytics Engine MySQL HeatWave Query Accelerator InnoDB Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 31
32. *Benchmark queries are derived from the TPC-H benchmark, but results are not comparable to published TPC-H benchmark results since they do not comply with the TPC-H specification 400G, 64 cores MySQL HeatWave dramatically speeds up analytic queries Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 32
33. MySQL HeatWave performance and price comparison Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 33 30TB TPCH, MySQL HeatWave is faster, cheaper & easier to use than all the competitive database services See documented performance comparisons that show how HeatWave is 6.5X faster than Amazon Redshift at half the cost, 1400X faster than Amazon Aurora at half the cost, and 5400X faster than Amazon RDS for MySQL at two-thirds the cost www.oracle.com/mysql/heatwave/performance
34. MySQL HeatWave ML – Run Machine Learning on existing cluster Build, train, deploy, & explain machine learning models within MySQL HeatWave, at no additional cost Single MySQL database for all applications All existing applications work without any changes Train, inference & explain within the database No need to learn new language or ML packages Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 34
35. MySQL HeatWave - OLTP + OLAP + ML Example : Loan Approval Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 35
36. MySQL HeatWave ML is 25x faster than Redshift ML 25x faster on average without compromising accuracy www.oracle.com/mysql/heatwave/performance Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 36
37. MySQL HeatWave ML is 1% of cost of Redshift ML 1% of cost and no additional cost for MySQL HeatWave customers www.oracle.com/mysql/heatwave/performance Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 37
38. Single MySQL database for OLTP & Analytics & Machine Learning applications All existing applications work without any changes Machine-learning–based automation with MySQL Autopilot Extreme performance: Accelerates MySQL by orders of magnitude, scales to thousands of cores Enables running analytics & ML on data stored on-premises Dramatically faster and lower cost compared to other cloud services MySQL HeatWave The only MySQL service with a massively-scalable, native query accelerator Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 38
39. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 39 Miscellaneous 39
40. • The CLI is a small-footprint tool that you can use on its own or with the Console to complete Oracle Cloud Infrastructure tasks • The CLI provides the same core functionality as the Console, plus additional commands • Some of these, such as the ability to run scripts, extend Console functionality • The configuration file name and default location (on Linux) is /home/opc/.oci/config OCI CLI Configuration File Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 40 https://docs.oracle.com/en-us/iaas/Content/API/Concepts/sdkconfig.htm • It should have the following information: user: OCID of the user calling the API fingerprint: Fingerprint for the public key that was added to this user key_file: Full path and filename of the private key tenancy: OCID of your tenancy region: An Oracle Cloud Infrastructure region
41. • MySQL Shell is an advanced client and code editor for MySQL • In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL • X DevAPI enables you to work with both relational and document data (MySQL Document Store) • AdminAPI enables you to work with InnoDB Cluster, InnoDB ClusterSet, and InnoDB ReplicaSet • Tuto: Connect to MySQL Database Service Using MySQL Shell – http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/ MySQL Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 41 https://dev.mysql.com/doc/mysql-shell/8.0/en/ MySQL Server 5.7 MySQL 8.0 Upgrade Checker Prompt Themes Auto Completion & Command History Document StoAre X Dev API SQL CLI Output Formats (Table, JSON, Tabbed) Batch Execution JavaScript Python SQL importJSON Dump Utilities InnoDB Cluster/ ReplicaSet / ClusterSet MySQL Server 8.0
42. • Oracle Cloud Infrastructure Cloud Shell gives you access to an always available Linux shell directly in the Oracle Cloud Infrastructure Console • You can use the shell to interact with resources like MySQL Database Service, Oracle Container Engine for Kubernetes cluster, Oracle Autonomous Database, … • Cloud Shell provides: – An ephemeral machine to use as a host for a Linux shell, pre-configured with the latest version of the OCI Command Line Interface (CLI) and a number of useful tools – 5GB of storage for your home directory – A persistent frame of the Console which stays active as you navigate to different pages of the console • Tuto: Connect to MySQL Database Service Using Cloud Shell – http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/ OCI Cloud Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 42 https://docs.cloud.oracle.com/iaas/Content/API/Concepts/cloudshellintro.htm
43. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 44 Resources
44. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 45  Introducing the MySQL Database Service https://blogs.oracle.com/mysql/introducing-the-mysql-database-service  Migrate from on premise MySQL to MySQL Database Service https://blogs.oracle.com/mysql/migrate-from-on-premise-mysql-to-mysql-database-service  Setup Disaster Recovery for OCI MySQL Database Service https://lefred.be/content/setup-disaster-recovery-for-oci-mysql-database-service/  Cost Estimator https://www.oracle.com/cloud/cost-estimator.html  OCI Free Trial https://www.oracle.com/cloud/free/  Documentations https://docs.cloud.oracle.com/en-us/iaas/mysql-database/index.html https://www.oracle.com/mysql/  MySQL Database Service - Infographic https://www.oracle.com/a/ocom/docs/mysql/mysql-database-service-infographic.pdf  MySQL Database Service - Ebook https://www.oracle.com/a/ocom/docs/mysql/mysql-database-service-ebook.pdf MySQL Database Service Resources
45. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 46  Discovering MySQL Database Service – Episode 1 – Introduction http://dasini.net/blog/2021/08/03/discovering-mysql-database-service-episode-1-introduction/  Discovering MySQL Database Service – Episode 2 – Create a compartment http://dasini.net/blog/2021/08/10/discovering-mysql-database-service-episode-2-create-a-compartment/  Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network http://dasini.net/blog/2021/08/17/discovering-mysql-database-service-episode-3-create-a-virtual-cloud-network/  Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket http://dasini.net/blog/2021/08/24/discovering-mysql-database-service-episode-4-dump-your-mysql-data-into-an-object-storage-bucket/  Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump http://dasini.net/blog/2021/08/31/discovering-mysql-database-service-episode-5-create-a-mysql-db-system-from-a-mysql-shell-dump/  Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List http://dasini.net/blog/2021/09/07/discovering-mysql-database-service-episode-6-update-the-private-subnet-security-list/  Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session http://dasini.net/blog/2021/09/14/discovering-mysql-database-service-episode-7-use-a-bastion-ssh-port-forwarding-session/  Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/  Discovering MySQL Database Service – Episode 9 – Connect to MySQL Database Service Using MySQL Workbench http://dasini.net/blog/2021/09/28/discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench/  Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/ Discovering MySQL Database Service Tutorials where I show you, step by step, how to use MDS and some other OCI services
46. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 47 MySQL HeatWave — New Machine Learning Capabilities (2022) • What is MySQL HeatWave? – https://www.youtube.com/watch?v=cTUCzsYAi94 • MySQL HeatWave Database Service – https://www.oracle.com/mysql/ • In-Database Machine Learning with MySQL HeatWave – https://blogs.oracle.com/mysql/post/in-database-machine-learning-with-mysql-heatwave • MySQL HeatWave ML – https://www.oracle.com/a/ocom/docs/mysql/mysql-heatwave-ml-technical-brief.pdf • Estuda.com increases query responses by 300X with MySQL HeatWave – https://www.youtube.com/watch?v=9cedEkFEKLs • VRGlass increases database performance 5X with MySQL HeatWave – https://www.youtube.com/watch?v=D4z-Ewk9bh8 • Genius Sonority speeds game analytics by 90X with MySQL HeatWave – https://www.youtube.com/watch?v=zwmVYq0MsPs One MySQL Database for OLTP, OLAP, and machine learning (ML) https://www.oracle.com/fr/events/live/mysql-heatwave-ml/on-demand/
47. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 48 MySQL Database Service—New HeatWave Innovations (2021) • Highlights: Announcing MySQL Database Service—New HeatWave Innovations – https://youtu.be/FUbnOTlJ7mI • Demonstration of MySQL HeatWave Autopilot – https://youtu.be/CyuBxBryJVA • Tetris.co speeds real-time insights with MySQL HeatWave – https://www.oracle.com/customers/tetris-co/ • Red3i increases insights by 1,000X with MySQL HeatWave – https://www.oracle.com/customers/red3i/ • FANCOMI accelerates ad analytics by 10X with MySQL HeatWave – https://www.oracle.com/customers/fancomi/ • Customer feedack: Tetris.co – https://youtu.be/TrYAluHRXXs • Customer feedack: Fan Communications – https://youtu.be/SiBHyYPlSNU https://www.oracle.com/events/live/mysql-heatwave-innovations/
48. Upcoming Session How to Replicate MySQL HeatWave to a Different Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 49 13 May 2022 10:00 a.m. CEST Register now with the QR code!
49. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 50 Follow us on Social Media
50. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 51 Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz