Oracle MySQL Cloud Service on Oracle Cloud Platform

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.

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.

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”.

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.

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.

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

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

Click on the MySQL Cloud Service link for service details.

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.

Click on Create Service to create a new service.

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

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.

Click on Next in the Service Details page.

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

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

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.

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

The Service Create and Delete History lists the service details.

Click on the mysqldb service to display the service Overview.

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

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

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

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.

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.

Click on Yes, Scale Up/Down VM.

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

The Resources section displays a Scale Application In Progress message.

The activity log lists the “Scale Application” entry.

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

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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