To connect to a MySQL database hosted on Amazon RDS using ssl.


1. Download the public key for Amazon RDS.
http://s3.amazonaws.com/rds-downloads/mysql-ssl-ca-cert.pem

2. Add the key to the java keystore for use with Omniscope.

You will need to install the latest Java SE Development Kit 7 to use the keytool. We will also be using the root certificates file provided with the java runtime.

Java SE Development Kit 7 Downloads

http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

Create a directory for working with the certificates.
Copy the Amazon RDS public key file that you downloaded into it.
Copy the cacerts authority file from the jdk jre security library directory.

mkdir C:\Users\USERNAME\omniscope-server\security
cd C:\Users\USERNAME\omniscope-server\security
copy "C:\Users\USERNAME\Downloads\mysql-ssl-ca-cert.pem” "C:\Users\USERNAME\omniscope-server\security\”
copy "C:\Program Files\Java\jdk1.7.0_65\jre\lib\security\cacerts” "C:\Users\USERNAME\omniscope-server\security\”

Import the Amazon RDS public key into the authority file.

"C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe” -import -v -alias awsrdsmysql -file mysql-ssl-ca-cert.pem -keystore cacerts

You will be prompted for the password which is "changeit”

You will be prompted to Trust this certificate”, type yes

Certificate was added to keystore
[Storing cacerts]

You can view the certificates to check using the following command
"C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe” -list -v -keystore cacerts

You now have a file with the default root certificates and the Amazon RDS mysql public key.

3. Set the Java keystore file for Omniscope to this file.
Edit the installconfig.properties file for your Omniscope installation.

http://www.visokio.com/kb/installconfig

Uncomment the ADDITIONAL_JVM_ARGS parameter if commented out and add the following , the value for -Djavax.net.ssl.trustStore must be the full path and filename of the keystore file.

-Djavax.net.ssl.trustStore=”C:\Users\USERNAME\omniscope-server\security\cacerts"

ADDITIONAL_JVM_ARGS=-Djavax.net.ssl.trustStore="
C:\Users\USERNAME\omniscope-server\security\cacerts"

4. Download and install the latest official JDBC driver for MySQL - MySQL Connector/J.
http://dev.mysql.com/downloads/connector/j/

5. Start Omniscope and connect to the database using the Database table source or Database table output.

Connection details
Select database: JDBC (Advanced)
Driver jar path: path to the MySQL Connector/J JDBC driver you installed earlier
e.g.
C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.31-bin.jar

Driver Class: com.mysql.jdbc.Driver
JDBC URL: jdbc:mysql://HOSTNAME:PORT/DATABASENAME?useSSL=true

The HOSTNAME:PORT is the authorized Endpoint listed in the RDS Dashboard for the database instance. You need to append the useSSL=true parameter to the url.
See http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html for the JDBC URL Format.

e.g.
jdbc:mysql://visokiodbinstance.cnyz7m0qcuin.eu-west-1.rds.amazonaws.com:3306/visokiomysqldb?useSSL=true