Toad World Blog

Oracle MySQL Cloud Service on Oracle Cloud Platform

Mar 1, 2017 3:18:40 PM by Deepak Vohra

Oracle’s Database as a Service (DBaaS) is available both as a private and a public database service. Toward the end of Q3of calendar 2016 Oracle made new offerings in the Oracle Cloud Platform with its Oracle Platform-as-a-Service (PaaS). Also in Q3-Q4, Oracle Cloud Platform added MySQL Database as a cloud service. The cloud environment makes it feasible to provision a fast, scalable, and reliable MySQL database service. The DBaaS is designed for workloads of all sizes, from small scale applications to enterprise level applications. Oracle also made available more than a dozen other new cloud services including Application Container Cloud Service, Big Data Cloud Services, Data Integration Cloud Service, and Oracle Exadata Cloud Service. An Oracle Cloud Stack Manager is provided to manage applications developed from multiple PaaS services. In this article we shall discuss the Oracle MySQL Cloud Service.

 

Setting the Environment

To use the Oracle MySQL Cloud Service, subscribe to the Oracle Database Cloud Service. Subscription packages of different level are available at https://cloud.oracle.com/database. A 300 hour free trial is also available and is recommended for initial evaluation.

3531.deepakv_Oracle_MySQL_Cloud_Service_Article_01

Different editions of Oracle Database are available. For most purposes the Standard Edition should suffice as a starter edition. For enterprise-level applications one of the other editions may be selected.

4606.deepakv_Oracle_MySQL_Cloud_Service_Article_02

The number of environments, the number of OCPUs per instance, the database size in GB, the amount of data transfer in GB may be selected based on requirements. Database backup is provided as “Local + Cloud Storage”.

6663.deepakv_Oracle_MySQL_Cloud_Service_Article_03

Initially when the subscription is made the Oracle Cloud account dashboard does not list the services subscribed; these are listed when the subscription is activated by Oracle. Access the Oracle Cloud dashboard at https://myaccount.cloud.oracle.com/mycloud/faces/dashboard.jspx.

8738.deepakv_Oracle_MySQL_Cloud_Service_Article_04

The subscription gets activated right away but the services activation message might not be received for a day. After the services have been activated the services get listed.

5277.deepakv_Oracle_MySQL_Cloud_Service_Article_05

Login to the subscribed services at the URL provided in the services activation message from Oracle Cloud Services to notify the subscriber about the service activation. The URL is of the form https://myservices.us.oraclecloud.com/mycloud/<identity doman>/faces/dashboard.jspx. The <identity domain> is a variable and different for different users. Specify the Identity domain, plus the Username and password that are also included in the activation message, and click on Sign In.

7343.deepakv_Oracle_MySQL_Cloud_Service_Article_06

The MySQL Cloud Service is available in the Database Service package.

3124.deepakv_Oracle_MySQL_Cloud_Service_Article_07

Click on the MySQL Cloud Service link for service details.

 5280.deepakv_Oracle_MySQL_Cloud_Service_Article_08

 

Creating a MySQL Database Service

When the Oracle Cloud Database service is activated a URL is sent to the subscriber for each of the services. Access the Oracle MySQL Cloud Service at the link provided which is of the form. Click on Go to Console to list the MySQL Cloud Services and create a new service.

7356.deepakv_Oracle_MySQL_Cloud_Service_Article_09

Click on Create Service to create a new service.

8322.deepakv_Oracle_MySQL_Cloud_Service_Article_10

Specify a Service Name (mysqldb for example) and click on Next.

6761.deepakv_Oracle_MySQL_Cloud_Service_Article_11

The default settings for a new service are listed. The SSH Public Key needs to be provided in order to access the Compute instance on which the MySQL Database service is to run. Click on Edit for the SSH Public Key field to create and download a new SSH Public and Private Key pair. Extract the privateKey and publicKey from the sshkeybundle.zip file. We shall need the SSH Private Key privateKey to SSH login to the VM instance on which MySQL Cloud service instance is running. Specify Administration Password and select Backup Destination as None.

8836.deepakv_Oracle_MySQL_Cloud_Service_Article_12

Click on Next in the Service Details page.

0003.deepakv_Oracle_MySQL_Cloud_Service_Article_13

A Summary of the service to be created gets listed. Click on Create.

2068.deepakv_Oracle_MySQL_Cloud_Service_Article_14

The MySQL Cloud Service starts to get created. Initially the service is listed as “Creating service…”. The MySQL Database version is 5.7.16.

5773.deepakv_Oracle_MySQL_Cloud_Service_Article_15

When the service gets created the status message indicating that the service is being created gets removed the “Submitted On” timestamp gets replaced with a “Created On” timestamp, and Storage get allocated.

7840.deepakv_Oracle_MySQL_Cloud_Service_Article_16

Click on Activity tab to list the activity log, which includes the Create Service operation.

5287.deepakv_Oracle_MySQL_Cloud_Service_Article_17

The Service Create and Delete History lists the service details.

 6354.deepakv_Oracle_MySQL_Cloud_Service_Article_18

Click on the mysqldb service to display the service Overview.

5706.deepakv_Oracle_MySQL_Cloud_Service_Article_19

The MySQL Cloud Service may be restarted with the Restart button.

2134.deepakv_Oracle_MySQL_Cloud_Service_Article_20

The MySQL Cloud Service may be stopped with the Stop button.

1586.deepakv_Oracle_MySQL_Cloud_Service_Article_21

The stopped MySQL Cloud Service may be restarted with the Start button.

4201.deepakv_Oracle_MySQL_Cloud_Service_Article_22

 

Scaling MySQL Database Service

As the CPU and memory requirements may vary, the MySQL Cloud service may be scaled up or down as required. Click on “Scale Up/Down” in “Manage this service” popup menu to scale a service as required.

 6366.deepakv_Oracle_MySQL_Cloud_Service_Article_23

 

In the Scale Up/Down dialog select the MySQL Servers Compute Shape based on requirements. As an example, scale up to OC5 compute shape, which provides 4.0 OCPU and 30.0GB RAM.

4048.deepakv_Oracle_MySQL_Cloud_Service_Article_24

Click on Yes, Scale Up/Down VM.

7652.deepakv_Oracle_MySQL_Cloud_Service_Article_25

The service Status becomes "Service Maintenance..." and the service becomes unavailable.

0728.deepakv_Oracle_MySQL_Cloud_Service_Article_26

The Resources section displays a Scale Application In Progress message.

3443.deepakv_Oracle_MySQL_Cloud_Service_Article_27

The activity log lists the “Scale Application” entry.

5518.deepakv_Oracle_MySQL_Cloud_Service_Article_28

When the scaling completes the Scale Application In Progress message becomes “Done scaling host”.

8233.deepakv_Oracle_MySQL_Cloud_Service_Article_29

The service Summary lists 4 OCPUs, and 30GB memory, scaled up from 1 OCPUs and 7.5GB memory.

1300.deepakv_Oracle_MySQL_Cloud_Service_Article_30

 

Connecting with the MySQL Database Service

Next, we shall connect with the MySQL Cloud service using SSH. Select the SSH Access tab to get and add a new SSH public key to the VM instance on which MySQL Database is running. Click on Add New Key to view the SSH key or to add a new SSH Key.

2465.deepakv_Oracle_MySQL_Cloud_Service_Article_31

In the Add New Key dialog the option is provided to select the Upload a new SSH Public Key value from file and click on Choose File. We do not need to upload a new SSH Public Key as we already created and downloaded an SSH Key when we created the MySQL Cloud service. The latest SSH Public Key is also listed in the Add New Key dialog.

6170.deepakv_Oracle_MySQL_Cloud_Service_Article_32

The Access Rules and SSH Access information may also be obtained by selecting the corresponding option from the Manage this service options.

 7245.deepakv_Oracle_MySQL_Cloud_Service_Article_33

Copy the privateKey to a local Linux machine and set file permissions to 600, which allows only the file owner to read and write the file.

chmod 600 privateKey

Obtain the Public IP Address of the VM instance on which the MySQL database is running.

 0312.deepakv_Oracle_MySQL_Cloud_Service_Article_34

SSH login to the VM instance with user ocp and the Public IP Address.

ssh -i privateKey opc@129.150.64.150
Set user to “oracle”.
sudo su oracle

The VM instance command prompt gets displayed.

3036.deepakv_Oracle_MySQL_Cloud_Service_Article_35

 

Creating a MySQL Database Table

Next, we shall create a database in the MySQL database service on the Oracle Cloud platform. To be able to start the MySQL CLI run the mysqlcommand. The MySQL CLI command prompt gets displayed.

5102.deepakv_Oracle_MySQL_Cloud_Service_Article_36

List the databases with the show databases command. Some of the databases are system databases while others are user databases. Set the database to “mysql” with the set mysql command.

3531.deepakv_Oracle_MySQL_Cloud_Service_Article_37

Run the following SQL script to create a database table called wlslog.

CREATE TABLE wlslog(time_stamp VARCHAR(255) PRIMARY KEY,category VARCHAR(255),type VARCHAR(255),
servername VARCHAR(255), code VARCHAR(255),msg VARCHAR(255));

The “mysql” database table gets created.

1882.deepakv_Oracle_MySQL_Cloud_Service_Article_38

Add data to the wlslog table with the following SQL script.

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:16-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STANDBY');

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:17-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STARTING');

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:18-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to ADMIN');

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:19-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RESUMING');

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:20-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000361','Started WebLogic AdminServer');

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:21-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RUNNING');

INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:22-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000360','Server started in RUNNING mode');

Database table wlslog data gets added.

The data added may be listed with a SQL SELECT statement.

3058.deepakv_Oracle_MySQL_Cloud_Service_Article_40

In this article we used the new MySQL Cloud Service made available on the Oracle Cloud Platform in Q3 of 2016.

Tags: MySQL Oracle

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.