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.

Prerequisites

 

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

mysql27

mysql28

 

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

mysql29

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

mysql30

 

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

mysql31

 

 

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

mysql32

mysql33

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

mysql34

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

mysql35

mysql36

 

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

mysql37

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

mysql38

 

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

mysql39

 

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"})

mysql40

mysql41

 

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().

mysql42

 

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