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

Oracle 23ai Bigfile Tablespace Shrink

Oracle 23ai: Bigfile Tablespace Shrink

Overview: If you try to resize/shrink a datafile before Oracle Database 23ai, you’ll get “ORA-03297: file contains used data beyond ... Read More

Oracle 23ai Managing Flashback Database Logs Outside the Fast Recovery Area

Oracle 23ai: Managing Flashback Database Logs Outside the Fast Recovery Area

Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23ai Database introduces the ... Read More

Back to Top