Using Toad Edge with Oracle MySQL Cloud Service

Introduction

MySQL is the #1 open source database. Oracle Cloud is a complete, integrated cloud and provides a wide range of services across Software as a Service (SaaS), Platform as a Service (PaaS), and Infrastructure as a Service (IaaS). Oracle PaaS provides Oracle Database and MySQL Database services in the Oracle Cloud. Oracle MySQL Cloud Service is a simple, automated, managed service that is enterprise ready. Oracle MySQL Cloud Service is a scalable, highly available, and secure service that provides all the features and management tools that a DBA would need for database administration. Common DBA tasks such provisioning, patching, backup and recovery, performance monitoring, scaling, replication, and authentication are managed by the MySQL Cloud service. Oracle MySQL Cloud service is seamlessly integrated with other Oracle Cloud services such as Java Cloud Service, Mobile Cloud, Messaging Cloud, Application Container Cloud, and Developer Cloud.

Problem

Oracle MySQL Cloud Service relies on standard MySQL server tools to load data into the cloud service. A user interface to access the Oracle MySQL Cloud Service and perform data management tasks would be useful management tool.

Solution

Toad Edge is an all-in-one data management toolset for MySQL database (support for other open source databases to be added). Toad Edge Preview software is available for download here.

From the Toad Edge graphical user interface (GUI) connect to a MySQL database service in the Oracle MySQL Cloud and create database objects including tables and load data into the tables, or run SQL scripts in a SQL Worksheet to add table data. Table data may be exported to SQL scripts or spreadsheets. Other typical DBA tasks, such as creating users and setting user privileges, may also be performed from Toad Edge.

In this article we shall discuss using Oracle MySQL Cloud Service with Toad Edge Preview. This article has the following sections.

Setting the Environment
Creating a MySQL Service on Oracle Cloud Platform
Enabling Network Access in Toad Edge
Connecting to Oracle MySQL Cloud Service
Creating a Database Table

Setting the Environment

To use Oracle MySQL Cloud Service create an Oracle Cloud account at https://cloud.oracle.com/ . If exploring and evaluating Oracle Cloud, use the free trial at https://cloud.oracle.com/tryit to get started. After registering a user account with Oracle Cloud a user is sent the following information which is unique to the user:

Username
Temporary Password
Identity domain

A My Services URL is also included in the email message sent on the activation of an Oracle Cloud user account. To log in to the Oracle Cloud services open the My Services URL in a browser and provide the username, password and identity domain.

The Oracle Cloud provides several services; the service that includes MySQL database is the Oracle Database Cloud service. Oracle Database service is available in various subscription formats: Metered Services, Virtual Image, Non-Metered Services, and Non-Metered Virtual Image. Detailed information about the various Oracle Cloud services is available on the free trial page at https://cloud.oracle.com/tryit.

Download and install Toad Edge Preview from http://www.toadworld.com/products/toad-edge/. Supported OS are Windows and Mac OS X.

Creating a MySQL Service on Oracle Cloud Platform

Select the Oracle MySQL Cloud Service in the Oracle Cloud My Services. Click on Create Service as shown in Figure 1.

Figure 1. Create Service

Specify a Service Name (mysqldb), select a Datacenter Region, and select a Metering Frequency as shown in Figure 2. Region may be selected as “No Preference” if a specific region is not to be provided. Click on Next.

Figure 2. Configuring Service Name, Region and Frequency

In Configuration select a Compute Shape, which is the resource description for the CPU and RAM to use, as shown in Figure 3. Click on Edit to create & download a SSH Public Key for VM Access to the service instance. Select Assign Automatically for Reserved IPs and select None for Backup Destination. Select default settings for MySQL Configuration header fields usable Database Storage (25 GB), Administration Username (root), Server Character Set, and Configure MySQL Enterprise Monitor (No). Specify a Password, which must consist of at least 8 characters and include one lowercase, one uppercase, one number, and one special character. Specify a Database Schema Name (mysqldb).

Figure 3. MySQL Configuration

Click on Next on the Service Details page as shown in Figure 4.

Figure 4. Service Details

On Confirmation page click on Create as shown in Figure 5.

Figure 5. Confirmation

Initially the Service Status is “Creating Service..” and the MySQL service after being created is shown in Figure 6.

Figure 6. MySQL Cloud Service “mysql”

Click on the service link to list the service detail, which includes Service Overview and Resources as shown in Figure 7.

Figure 7. Service Overview and Resources

Configuring Network Access in Toad Edge

Before being able to use the MySQL Cloud Service external access to the service must be configured or enabled. Click on the Manage service icon for the service and select Access Rules as shown in Figure 8.

Figure 8. Selecting Access Rules

The Access Rules indicate that public access to the MySQL database port 3306 from PUBLIC-INTERNET is not enabled. Click on Actions as shown in Figure 9.

Figure 9. Selecting Actions for Public Access to MySQL Database Port

Select Enable as shown in Figure 10.

Figure 10. Selecting Enable

In Enable Access Rule click on Enable as shown in Figure 11.

Figure 11. Enabling Access Rule

Public access to MySQL database port 3306 gets enabled, as shown in Figure 12.

Figure 12. Public Access to MySQL Port 3306 Enabled

Similarly, enable public access the Enterprise Manager as shown in Figure 13.

Figure 13. Public Access to Enterprise Manager Enabled

Connecting to Oracle MySQL Cloud Service

In this section we shall connect to the MySQL Database instance running in Oracle MySQL Cloud from Toad Edge. But first, we need to obtain the connection detail for the MySQL Cloud service. In the Service Overview, which may be displayed from the mysql service link, copy the Connect Descriptor as shown in Figure 14.

Figure 14. Obtaining the Connect Descriptor

In the Toad Edge click on Connect>New Connection as shown in Figure 15.

Figure 15. Connect>New Connection

In the New MySQL Connection specify the Public IP from the Connect Descriptor in the Host field. Specify the port 3306 in the Port field and specify the database instance (schema) name mysqldb in the Database field as shown in Figure 16. In Authentication specify User as root and Password as the password configured when creating the MySQL Cloud service. In Options, Enable AutoCommit and Restore previous work after login are enabled by default and should be kept as such. The Connection Name, which is generated automatically, must be unique. The Connection String Preview lists the connection URL for the MySQL Cloud service database. Click on Test Connection to test the connection. If the connection gets established, a message “Connection is OK” gets displayed, as shown in Figure 16.

Figure 16. Configuring and Testing Connection

Click on OK as shown in Figure 17.

Figure 17. Applying the Connection Configuration

A connection to the Oracle MySQL Cloud database gets created in Toad Edge and gets added in Connections, as shown in Figure 18.

Figure 18. Connection to MySQL Cloud Service Database Created in Toad Edge

The active database is the mysqldb database, which is configured when creating the MySQL Cloud service, as shown in Object Explorer in Figure 19.

Figure 19. Active Database mysqldb

The Users are also listed in the Object Explorer, as shown in Figure 20.

Figure 20. Users

When configuring the connection, the error “Connection has failed. Communications link failure” could get generated, as shown in Figure 21, even though all the connection parameters are configured as required. The error message is due to the network public access not being enabled to the MySQL Cloud service. Enabling public access is discussed in the previous section.

Figure 21. Connection has failed. Communications link failure

Creating a Database Table

After creating a connection to Oracle MYSQL Cloud service in Toad Edge, a database table may be created using a SQL Worksheet. One Worksheet is added by default when a new connection is created. Copy the following CREATE TABLE statement to the SQL Worksheet.

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

Click on Execute SQL Statement as shown in Figure 22.

Figure 22. Execute SQL Statement

A database table gets created, as indicated by the message in Figure 23.

Figure 23. Table Created

Next, open another SQL Worksheet from the toolbar as shown in Figure 24 to add data to the table.

Figure 24. Open SQL Worksheet

Copy the following SQL script the worksheet.

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');

Click on Execute All Statements as shown in Figure 25.

Figure 25. Execute All Statements

Database table wlslog gets created and gets added to the mysqldb schema, as shown in Figure 26.

Figure 26. Database Table wlslog Created

Right-click on the wlslog table and select Open as shown in Figure 27 to open the table.

Figure 27. wlslog>Open

Tabs for the wlslog table Columns and other attributes get displayed, as shown in Figure 28.

Figure 28. Table wlslog Columns

To display the table data, select the Data tab as shown in Figure 29.

Figure 29. Displaying Table Data

Conclusion

In this article we discussed using Oracle MySQL Cloud service from Toad Edge 1.0. First, we created a MySQL Cloud service and enabled public access to the MYSQL port. Subsequently we connected to the MySQL Cloud database from Toad Edge and created a database table.