In this blog, we will cover the steps to create MySQL Heatwave in Oracle OCI and load MySQL shell dump data into MySQL Heatwave from OCI object storage.

Prerequisites

- An Oracle cloud fee trial or paid account.

- MySQL shell dump data stored in OCI object storage. Kindly complete previous blog "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 3/3".

Step #1: Create MySQL Heatwave System on OCI

1. Sign in to your Oracle Cloud account. Navigate to “Databases” > “MySQL”. On “DB System” page, ensure you have the correct compartment selected and click ‘Create DB System’.

2. Name MySQL database system ‘MySQL-HW’. Select ‘Development or testing’ and enable ‘MySQL HeatWave’ option under ‘Configure MySQL HeatWave’ section.

2. Under ‘Create administrator credentials’ section, enter MySQL admin username and password.

3. Under ‘Configure networking’ section, make sure to pick private subnet.

4. Under ‘Configure hardware’ section, keep the default shape and set Data storage size to appropriate size based on your MySQL shell dump data size.

5. Click on “Show advanced options”, click on “Networking” tab. Enter hostname ‘MySQL-HW’ and leave the default port.

6. To load data from MySQL shell dump data created in previous blog, click on the “Data Import” tab. Once you are on the ‘Data Import’ tab, click on the “Click here to create a PAR URL” link.

7. On ‘Create PAR for existing bucket’ screen, select ‘MySQL-Bucket’ created in previous blog, specify appropriate expiration time, then click ‘Create and set PAR URL’.

8. Click ‘Create’ to start creating MySQL Heatwave system.

9. Once your MySQL DB System is 'ACTIVE', a “Private IP Address” will be allocated to it.

10. You can now login to MySQL DB System using MySQL Shell from OCI compute instance (created in previous blog). Copy MySQL Heatwave system private IP from previous step.

11. To confirm data load, switch to SQL mode “\sql”.

Step #2: Load MySQL data into Heatwave in-memory

1. To Enable Heatwave for MySQL DB System, navigate to “Databases” > “MySQL”. Once on the MySQL page, click on the name of your MySQL HeatWave System. This will take you to "DB System Details" page for your MySQL HeatWave. Upon landing on the "DB System Details" page, click on "More actions", then click “Add HeatWave cluster” option.

2. On ‘Add HeatWave cluster’ screen, click “Estimate node” to estimate the size of the HeatWave cluster and load the data into memory and run queries.

3. On ‘Estimate node’ screen, click “Generate estimate”. It will show you a list of all the databases that you have in your MySQL HeatWave system. Afterwards, you can select what tables and databases you want to load in-memory, from the list of databases that will appear after clicking “Generate estimate”. Once you click on “Generate estimate”, it may take several minutes to display your schema information.

It pulled up all the databases that we currently have in MySQL. You can either select the whole database or select individual tables that you want to load in memory.

In this lab, we will select both databases ‘emplyees’ & ‘world’.

4. After you are done selecting the tables/databases you want to load in-memory, on that same screen, scroll down until you see a "Summary" and "Load command" section. The "Summary" section shows how many HeatWave nodes will be required depending on the data we have selected.

5. Under the "Load command" section, copy that line of code, afterwards, click "Apply estimated node".

6. After clicking “Apply estimated node”, the number of nodes required to load the data that you have selected, will change (depending on your data size). Finally, click "Add HeatWave cluster" to finish the HeatWave cluster creation process.