dcsimg
 

Using JDeveloper with MySQL Database and Oracle Database on AWS RDS, Part 1

Thursday Feb 1st 2018 by Deepak Vohra

Explore using JDeveloper with Oracle and MySQL databases on RDS.

AWS Relational Database Service (RDS) is a managed service for the most commonly used relational databases; these include the Oracle, MySQL, PostgreSQL, and SQL Server databases. A managed service includes automation of tasks, such as back-up and restoration, that typically a DBA would perform. RDS does not include a client platform from which a database may be connected to and tables and data accessed. Although database-specific client tools such as SQL*Plus for Oracle Database, MySQL Command Line Interface (CLI) for MySQL database, and psql for a PostgreSQL database could be used to connect to a database, these client tools lack the database features that an integrated development environment (IDE) with a GUI (graphical user interface) provides. JDeveloper provides database-specific features, including a connection wizard, a SQL Worksheet, and several other features to explore a result set. In this article, we shall discuss using JDeveloper with Oracle and MySQL databases on RDS. This article has the following sections:

Setting the Environment

Download and install Oracle JDeveloper 12c. Create an AWS account at https://aws.amazon.com/resources/create-account/.

Using JDeveloper with MySQL Database

Oracle JDeveloper provides in-built support for MySQL database. In the following sub-sections, we shall discuss creating a MySQL database instance on AWS RDS and connect to the database with JDeveloper. The new RDS Console, in which some usability issues have been fixed, is used.

Creating a MySQL Database Instance on RDS

To create a MySQL database instance on RDS, select the RDS service and click Get Started Now, as shown in Figure 1.

RDS>Get Started Now
Figure 1: RDS>Get Started Now

In Select Engine, select MySQL, as shown in Figure 2. The MySQL engine supports database sizes up to 16 TB, and offers up to 32 vCPUs and 244 GB memory. Automated backups to S3 and point-in-time recovery are supported. Cross-region read replicas also are supported.

Selecting MySQL Engine
Figure 2: Selecting MySQL Engine

Select the checkbox Only enable options eligible for RDS Free Usage Tier and click Next, as shown in Figure 3.

Select Engine>Next
Figure 3: Select Engine>Next

Next, choose the use case, as shown in Figure 4. Select Dev/Test - MySQL and click Next. The other options are Production - Amazon Aurora and Production - MySQL. Amazon Aurora is a database available only on AWS RDS and is MySQL and PostgreSQL database compatible.

Selecting Use Case as Dev/Test - MySQL
Figure 4: Selecting Use Case as Dev/Test - MySQL

Specify DB Details, as shown in Figure 5. First, specify the Instance specifications in which keep the default selection for License model as general-public-license. Select a DB engine version, the default setting being mysql 5.6.37.

Specifying DB Details>Instance specifications
Figure 5: Specifying DB Details>Instance specifications

With the checkbox Only enable options eligible for RDS Free Usage Tier selected, a DB instance class (db.t2.micro) (see Figure 6). Storage Type is General Purpose (SSD) by default for the free tier. Keep the default setting for Allocated Storage as 20 GB.

Selecting a DB Instance Class and Storage
Figure 6: Selecting a DB Instance Class and Storage

In the Specify DB Details section in the Settings sub-section, specify a DB instance identifier (mysqldb), as shown in Figure 7. Specify a Master username (dvohra) and a Master Password, which must be at least 8 characters. The Master Username may be reset, if required, as we shall discuss in the Modifying the MySQL Database Instance section.

Database Settings
Figure 7: Database Settings

In Configure Advanced settings, select a VPC and Subnet group, as shown in Figure 8. The default setting for Public accessibility is No, which does not assign a Public IP to the RDS DB instance and the DB instance is inaccessible outside the VPC in which created.

Selecting network options for VPC and Subnet Group
Figure 8: Selecting network options for VPC and Subnet Group

Availability zone is set to No preference by default, as shown in Figure 9. Select the option to Create a new VPC security group, as shown in Figure 9.

Selecting Availability Zone and VPC Security Group
Figure 9: Selecting Availability Zone and VPC Security Group

In Database options, specify a Database name (mysqldb), as shown in Figure 10. Select the default Database Port setting of 3306. Select the default Option group and IAM DB Authentication setting.

Database options
Figure 10: Database options

Keep the default settings for Encryption, Backup, Monitoring, and Maintenance, and click Launch DB Instance (see Figure 11).

Launch DB Instance
Figure 11: Launch DB Instance

The message "Your DB Instance is being created" gets displayed, as shown in Figure 12. Click View DB Instance Details.

View DB Instance Details
Figure 12: View DB Instance Details

The mysqldb database Summary gets displayed as shown in Figure 13. Initially the DB instance status is listed as "creating..."

MySQL DB Instance Summary
Figure 13: MySQL DB Instance Summary

In the Dashboard>Instances, the DB instance mysqldb is listed as "creating," as shown in Figure 14.

MySQL DB Instance mysqldb Creating
Figure 14: MySQL DB Instance mysqldb Creating

When the mysqldb DB instance has been created, the Status becomes available, as shown in Figure 15.

MySQL DB Instance mysqldb Creating
Figure 15: MySQL DB Instance mysqldb Creating

Modifying the MySQL Database Instance

Even if all the required settings are applied when creating a new MySQL DB instance, an instance should not be required to be modified. But, if some setting needs to modified because it is not optimal or not accurate, RDS has the provision to modify an instance. As an example, the default setting of Public accessibility is No, as shown in Figure 8. For JDeveloper on a local machine, or any machine that is not in the same VPC as the RDS DB instance to be able to access the RDS, the DB instance Public accessibility must be set to Yes. The modification made in this section should be made only if JDeveloper is not in the same VPC as the RDS DB instance, which would be true if JDeveloper is installed on a local machine.

To modify the mysqldb instance, select the instance in the RDS>Instances and select Instance actions>Modify (see Figure 16).

mysqldb>Instance actions>Modify
Figure 16: mysqldb>Instance actions>Modify

The RDS>Instances>Modify wizard gets displayed as shown in Figure 17. As we don't need to modify any of the Instance Specifications scroll down for other settings.

RDS>Instances>Modify
Figure 17: RDS>Instances>Modify

In Settings, a New master password is required to be set when modifying an instance, as shown in Figure 18.

Setting New Master Password
Figure 18: Setting New Master Password

Scroll down farther to display the Network & Security section. Select Public accessibility as Yes, as shown in Figure 19.

Selecting Public accessibility as Yes
Figure 19: Selecting Public accessibility as Yes

Scroll down even farther and keep all the other settings the same. Click Continue, as shown in Figure 20.

Modify DB Instance: mysqldb> Continue
Figure 20: Modify DB Instance: mysqldb> Continue

In Summary of Modifications, the settings to be modified are displayed (see Figure 21). The only attribute listed is Publicly accessible and its Current Value is No and New Value is Yes. In Schedule of Modifications, two options are provided: Apply during the next scheduled maintenance window and Apply immediately. A database has to be available to be able to apply modifications. If the database is not available, such as the database is getting created or is already modifying, new modifications should not be applied. Click Modify DB Instance.

Modify DB Instance
Figure 21: Modify DB Instance

The RDS instance Status becomes "modifying," as shown in Figure 22.

RDS Instance Modifying
Figure 22: RDS Instance Modifying

When the instance has modified, the Status becomes "available," as shown in Figure 23.

RDS DB Instance mysqldb Available
Figure 23: RDS DB Instance mysqldb Available

The modified mysqldb instance should list Publicly accessible as Yes, as shown in Figure 24.

Modified Instance is publicly accessible
Figure 24: Modified Instance is publicly accessible

Conclusion

In this article, we discussed using Oracle JDeveloper to connect to MySQL and Oracle Database instances on AWS Relational Database Service (RDS). Some of the salient configuration features are:

  • If JDeveloper is in a different VPC than the RDS DB instance, the RDS DB instance must be made Publicly accessible.
  • A Security group Inbound rule for the IP Address of the machine on which JDeveloper is installed must be added.
  • Oracle Database on RDS does not allow access to the SYS user. If SYS user is required, or if SYSDBA privileges are required for any application, Oracle Database on RDS is not a suitable option.
Home
Mobile Site | Full Site