How to query MySQL database on EKS - GOV.UK Developer Documentation

Last updated: 23 Oct 2024

Pre-requisites: Logins and Credentials

In order to run the SQL queries and extract files from EKS pods, you would need to be able to access:

the bash shell from the desired EKS pod in the corresponding environment
MySQL database login credentials from AWS Secrets Manager for the corresponding environment

Log into an EKS pod bash session

Setup Kubernetes and environments according to the EKS setup guide
Switch to desired environment, e.g.

eval $(gds aws govuk-integration-poweruser -e --art 8h)
kubectl config use-context integration

Log into shell for desired app, e.g.

kubectl exec -it deploy/whitehall-admin -- bash

Validate environment

echo $GOVUK_ENVIRONMENT

Log into the AWS console with the desired role

gds aws govuk-integration-poweruser -l

Choose Secrets Manager from the Services menu
Search for mysql and click on the desired mysql database, e.g. govuk/whitehall-admin/mysql
Under Secret value, choose the Retrieve secret value button on the right-hand side.

How to: Query database through MySQL console

Log into the desired EKS pod
Log into MySQL console using the MySQL credentials fetched from AWS. You will be prompted for the password.

mysql -h <db-host> -u <db-username> -p -D <database name>

Run any SQL statements to query the database

How to: Export query into a CSV file for your local machine

Log into the desired EKS pod
Run direct query within command line and pipe the output to file. Note we are piping to tr first to get CSV format.

mysql -h <db-host> \
-u <db-username> -p \
-D <db name> -B \
-e "<SQL Statement>" \
| tr '\t' ',' \
> <output file e.g. output.csv>

Log out of the EKS box exit
Copy the file from the EKS pod to local machine

kubectl cp -n apps \
<pod-name>:<source file to copy> \
<target location>