MySQL: Migrate MySQL to OCI MySQL Heatwave - Part 2/3

In previous blog post "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 1/3", we came over the steps to install MySQL on OCI compute instance. 

In this blog post, we will cover the steps to dump MySQL data into OCI object storage.

Prerequisites

- An Oracle cloud fee trial or paid account.

- MySQL server installed on OCI compute instance. Kindly complete previous blog "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 1/3".

Step #1: Add OCI user profile API Key

1. Sign in to your Oracle Cloud account. Navigate to the “Profile” icon on the top-right. From there click on “User settings”.

2. Under “Resources” section, click on “API Keys” then click “Add API Key”.

3. When you click on 'Add API Key' a popup will appear saying "Add API Key". On that popup, select ‘Generate API Key Pair’ and download both the “Private Key” and “Public Key”. Afterwards, click “Add”.

4. Once you ‘Add’ the API Key, a new popup will appear saying “Configuration File Preview”. Copy the contents to a file and click 'Close' afterwards to exit out of the Configuration File Preview.

Step #2: Setup the “config” file in the OCI compute instance

1. SSH to OCI compute instance using Cloud Shell tool. Paste the 'Configuration File Preview' contents from last step in a ".oci" directory.

ssh -i .ssh/id_rsa opc@192.18.157.156

Where: 192.18.157.156 is compute instance public IP

2. Copy the contents of the private key downloaded from previous step into file “/home/opc/.oci/privapikey.pem” on the compute instance.

Step #3: Create OCI Object Storage

1. Navigate back to Oracle cloud console and create object storage bucket. On the Buckets page, make sure you have the right Compartment selected, then click “Create Bucket”.

2. Name the bucket “MySQL-Bucket”, keep the ‘Default Storage Tier’ to “Standard” and click "Create".

3. Click on the Bucket Name and note down the “Bucket Name” as well as “Namespace”. 

Step #4: Perform the MySQL Shell Dump

1. Navigate back to Cloud Shell window, where you are already ssh into the compute instance, and login to MySQL database instance using MySQL Shell.

2. Make sure you are in ‘JavaScript’ mode of MySQL Shell by executing “\js” and perform the command “util.dumpInstance()” to export the dump data into Oracle Cloud Object Storage bucket. The util.dumpInstance() command will take a dump of all the databases except “mysql, sys, performance schema, and information schema”. The dump comprises of DDL files for the schema structure and tab-seperated .tsv files containing the actual data.

Note: “sampledump” is the prefix under which all our dump files will be stored in Object Storage. Change the ‘osBucketName’ and ‘osNamespace’ to match with what you have. “ocimds”: “true” option ensures compatibility of the dump with MySQL Database Service/HeatWave.

util.dumpInstance("sampledump", {"osBucketName": "MySQL-Bucket", "osNamespace": "yza00k7tuks0", "ocimds": "true", "compatibility": ["strip_restricted_grants", "strip_definers"], users: "true", dryRun:"false"})

3. Once the dump is complete, navigate back to Oracle Cloud and to “MySQL-Bucket” Object Storage bucket. Check to see if you see your files under “sampledump” from the util.dumpInstance().

Next blog "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 3/3", we will cover Create MySQL Heatwave System.