MySQL Database Service Revealed : Running MySQL As a Service in the Oracle Cloud Infrastructure

Access all the information you need to begin using the MySQL Database Service (MDS) in the Oracle Cloud Infrastructure (OCI). MDS is Oracle’s new platform as a service (PAAS) offering for open-source database users. This book covers getting started with an account in OCI, gives a brief overview of OCI services available, and provides a short tutorial on MDS. Reading this book helps you take advantage of the powerful OCI features by building your own MySQL database in the cloud. Examples in this book center around running MDS in OCI, and include several of the popular use cases as well as advice on how to implement them. In addition, you will learn more about the related MDS OCI features, such as the high availability features currently available. Finally, you will learn how to back up and restore your data as well as how to get your data into and out of the cloud. The skills you learn in this book will help you get started using MDS and letting Oracle do the heavy lifting of managing MDS operations and implementation. What You Will Learn Use Oracle Cloud Infrastructure (OCI) Deploy MySQL Database Service (MDS) systems in the cloud Connect your applications to MDS Back up and recover using the data recovery features of MDS Employ the newest high availability features of MDS Who This Book Is For Systems engineers, developers, and database professionals who want to learn about the powerful features of the MySQL Database Service (MDS) and how to incorporate cloud-based database storage into their infrastructure and applications. Readers who are new to MySQL will appreciate the tutorial chapter, and those familiar with MySQL will learn the latest features of MDS as well as how to build inexpensive, powerful MySQL database servers in the Oracle Cloud Infrastructure (OCI). Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: Getting Started with MySQL in the Cloud Overview What Is Cloud Computing? Why Move to the Cloud? Improved Agility Automation What Is the Oracle Cloud Infrastructure? What Is the MySQL Database Service? MDS in OCI Region Availability Required Identity and Access Management Policy Data Security MDS and Audit Service MDS Versions and Storage Engines Shape DB System MySQL HeatWave Use Cases Limitations Cloud Services Cost Expectations Summary Chapter 2: Oracle Cloud Infrastructure A Brief Tour of Services Terminology Virtualization Tenancy Compartments Security Zone OCI Resource Allocation Virtual Cloud Network Oracle Cloud Identifier (OCID) Compute Virtual Machine Bare Metal Host Shapes Images Provisioning OCI User Interfaces Core Services Networking Services Compute Services Storage Services Block Volume Local NVMe File Storage Object Storage Identity and Access Management Services Setting Up Your Account Always Free Resources Service Usage and Limits OCI Tutorial Create a New Compartment Create the Virtual Cloud Network Create the Compute Instance Add Block Storage to the Compute Instance Terminate the Resources Summary Chapter 3: A Brief Tutorial of MySQL Getting Started Connecting with MySQL Shell Features Modes Supported Making Connections Using a URI Using Individual Options Using SSL Connections Connecting with the MySQL Client Basic SQL Commands How to Get and Install MySQL Configuring and Managing Access to MySQL Configuration Files Creating Users and Granting Access Basic SQL Commands Creating Databases and Tables Searching for Data Creating Data Updating Data Deleting Data Using Indexes Views Triggers Simple Joins Stored Routines Connecting Applications MySQL Database Connectors Sample Database Example Connector: Connector/Python Installing Connector/Python Checking the Installation Example 1: Connecting to MySQL Example 2: Inserting Data Example Connector: Connector/J Installing Connector/J Checking the Installation Example 1: Connecting to MySQL Example 2: Inserting Data Summary Chapter 4: MySQL Database Service Getting Started Step 1: Create a VCN Step 2: Create a Group Create a Group Create and Add a User to the Group Step 3: Create Policies A Tour of MDS Creating Your First DB System Open the DB Systems Resource Page Create a DB System DB System Details Page Status and Buttons Resource Menu Metrics Endpoints HeatWave Backups Channels Work Requests MySQL DB System Information and Tags DB System Information Tab Tags Tab Stopping and Starting the DB System Connecting to Your DB System Connection Mechanisms Connecting via a Compute Instance Create a Compute Instance Modify the VCN – Create an Ingress Rule for the Private Subnet Connect to the Compute Instance Connect to the DB System Connecting Using a Bastion Session Bastion Terminology Session Types Prerequisites Create a Bastion Service Create a SSH Key Pair Create a Port-Forwarding Session Connect from Your PC Summary Chapter 5: Backup and Restore What Can Go Wrong? Backup Types of Backups Manual Backup Automatic Backup Final Backup Operator Backup Backup Details Enable or Disable Automatic Backups Create a Manual Backup Edit a Backup Manage Tags Delete a Backup Move a Backup Restore Summary Chapter 6: Point-in-Time Recovery Overview Setup Recovery Summary Chapter 7: Data Import and Export Overview Migrating Data to MDS Data Transfer Strategies Compatibility Concerns Security Older MySQL Versions Migrating Data to MDS Using an ObjectStore Bucket Configure Your PC for OCI CLI Access Install and Test the OCI CLI Create the ObjectStore Bucket Export to an ObjectStore Bucket Configure the Compute Instance Create a Pre-Authenticated Request (PAR) Import from the ObjectStore Bucket into a DB System Using a Compute Instance Export Data Using MySQL Shell Copy the Exported Data to the Compute Instance Import the Data on the DB System Using a Bastion Gateway Setup the Bastion Service Export Data Using MySQL Shell Start the Bastion Service SSH Sessions Import the Data on the DB System Migrating Data from MDS Using an ObjectStore Bucket Prepare Your PC Create the ObjectStore Bucket Export to ObjectStore Bucket Import from ObjectStore Bucket into Local MySQL Server Using a Compute Instance Export Data Using MySQL Shell Copy the Exported Data to Your Local MySQL Server Import the Data on the DB System Using a Bastion Gateway Export Data Using MySQL Shell Import the Data on Your Local MySQL Server Summary Chapter 8: High Availability Overview High Availability in MDS How Does Failover and Switchover Work? What Are the Conditions for Failover? Prerequisites How to Find Tables Without Primary Keys How to Add Surrogate Primary Keys How to Set Up HA Setup HA (Create DB System) Enable HA (Existing DB System) Disable HA (Existing HA DB System) How to Use MDS HA Limitations Advanced Topics Summary Chapter 9: OCI Command-Line and Application Programming Interfaces Getting Started Command-Line Interface (CLI) Install the CLI Capabilities Example Uses List Backups for a DB System Stop/Start a DB System Create a DB System For More Information OCI APIs MDS API Example Uses Install the Python SDK Getting Started Listing the MySQL Shapes Stop/Start DB System Creating a DB System Delete a DB System Summary Chapter 10: Migrating to MDS Getting Help While Using OCI and MDS Communication with Oracle Documentation Troubleshooting Examine Your DB System Needs Shapes Data Storage Networking Planning Your DB Systems Security Best Practices Summary Index