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
- Using JDeveloper with MySQL Database
- Creating a MySQL Database Instance on RDS
- Modifying the MySQL Database Instance
- Conclusion
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Figure 10: Database options
Keep the default settings for Encryption, Backup, Monitoring, and Maintenance, and click Launch DB Instance (see Figure 11).
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.
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…”
Figure 13: MySQL DB Instance Summary
In the Dashboard>Instances, the DB instance mysqldb is listed as “creating,” as shown in Figure 14.
Figure 14: MySQL DB Instance mysqldb Creating
When the mysqldb DB instance has been created, the Status becomes available, as shown in Figure 15.
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).
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.
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.
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.
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.
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.
Figure 21: Modify DB Instance
The RDS instance Status becomes “modifying,” as shown in Figure 22.
Figure 22: RDS Instance Modifying
When the instance has modified, the Status becomes “available,” as shown in Figure 23.
Figure 23: RDS DB Instance mysqldb Available
The modified mysqldb instance should list Publicly accessible as Yes, as shown in Figure 24.
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.
