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

Share on:

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.



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@

Where: 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.

Share on:

More from this Author

ExaCC: Patch Virtual Machine Cluster Grid Infrastructure

Overview: Oracle is responsible for performing patches and updates to ExaCC infrastructure every quarter (there is no patch in Dec). That includes: ... Read More

Peering OCI VCNs in Different Regions Using Dynamic Routing Gateway

In this blog, we will demonstrate the steps to peer two VCNs in different regions through a DRG in the same tenancy. This is called a remote VCN ... Read More

Back to Top