Connecting to your Read Replica

You will have received a set of credentials and a DNS endpoint as part of the Read Replica setup. These can be entered into the SQL client tool of your choice. Any tool which is compatible with MySQL will work. MySQL Workbench is one example, but there are many freely available.

Here is an example showing a successful connection with MySQL Workbench.

Configuring the SSL Certificate

Replicas can only be accessed via SSL, and specifically TLS 1.2. All modern SQL clients support this. Check your client program’s user documentation to see how this is done.

Amazon has a customer certificate which must be used in the client. You can download that certificate file here.

You can see above an example of how to reference the certificate in MySQL Workbench. Other SQL clients will have a similar configuration option.

Here is example of connecting to a replica using the standard mysql command line client:

mysql -u RO_USER -h XXXXXX-replica-cluster.cluster-c8z7nk1fiuah.eu-central-1.rds.amazonaws.com -p'123456789'  --ssl-ca=/Users/stu/Downloads/rds-ca-2019-root.pem --ssl-mode=VERIFY_CAs

Initial SQL to try

show databases;

use prod_main_clinspark;

select name from org;

Summary Statistics for collected data

select study.name as study_name, study.study_state, item_data.data_collection_status, count(item_data.id)
from item_data
inner join item_group_data igd on item_data.item_group_data_id = igd.id
inner join form_data fd on igd.form_data_id = fd.id
inner join study_event_data sed on fd.study_event_data_id = sed.id
inner join subject on sed.subject_id = subject.id
inner join study on subject.study_id = study.id
where study.study_state <> 'ARCHIVED'
group by study.name, study.study_state, item_data.data_collection_status 

Here is example output from the above

Note on “Whitelisted” access restrictions

As part of setup of the Read Replica, certain IP addresses will have been whitelisted. Only traffic originating from these IP addresses will be able to access the Replica. If you are unable to access the replica at all, please keep this in mind as the first thing to check.