Blogs

MySQL: Migrate MySQL to OCI MySQL HeatWave – Part 2/3

In the 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 free trial or paid account
• MySQL server installed on OCI compute instance. Kindly complete the 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 the “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”. Afterward, 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‘ afterward 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 the 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 the previous step into file “/home/opc/.oci/privapikey.pem” on the compute instance

Step #3: Create OCI Object Storage

1. Navigate back to the Oracle Cloud Console and create an 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 the Cloud Shell window, where you already ssh into the compute instance, and log in to the MySQL database instance using MySQL Shell

 

2. Make sure you are in ‘JavaScript’ mode of MySQL Shell by executing “\js” and performing the command “util.dumpInstance()” to export the dump data into the 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-separated .tsv files containing the actual data.

Note: “sample dump” 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 the “MySQL-Bucket” Object Storage bucket. Check to see if you see your files under “sample dump” 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.

More from this Author