Connect to MySQL through Session Manager

How to open a local port to the Aurora MySQL cluster of an environment. The clusters sit in private subnets, so you cannot connect to them directly. AWS Systems Manager Session Manager forwards a port on your machine through a jumphost instance to the cluster, and your mysql client then talks to 127.0.0.1. No SSH key, no VPN and no open inbound port are involved.

Prerequisites

• A working SSO setup, per Configure AWS SSO for the CLI.
• The Session Manager plugin for the AWS CLI. Without it, aws ssm start-session fails with SessionManagerPlugin is not found.
• A mysql client on your machine.
• A database user and password for the environment. This page does not cover how you get one.

1. Log in

aws sso login --sso-session alma-sso

One login covers every environment below, so you do not repeat this per environment.

2. Start the port forward

Each tab names the AWS account the cluster sits in, because that is also the profile the command uses. Two of them are test clusters: the one in nettix predates the split-account estate and stayed in the shared account, alongside production. Environments explains the two naming schemes.

Pick the environment. Each command forwards local port 3306 to the reader endpoint, which is the right choice unless you are going to write. A typo against the writer in production is a typo against live data. When you must write, swap host for the writer endpoint given under the command.

Test (ajo-mark-dev)

The ajomark-main-test cluster in account ajo-mark-dev. This is the test environment of the split-account estate.

aws ssm start-session --profile devadmin@ajo-mark-dev --region eu-west-1 \
  --target i-001ef2cce4d90c664 \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters host="ajomark-main-test-main-db-cluster.cluster-ro-c0bwfp0lhmkn.eu-west-1.rds.amazonaws.com",portNumber="3306",localPortNumber="3306"

Writer endpoint:

ajomark-main-test-main-db-cluster.cluster-c0bwfp0lhmkn.eu-west-1.rds.amazonaws.com

Test (nettix)

The almaajo-new-test cluster. It lives in account nettix, the old shared account the legacy estate was built in — which is also the account production runs in.

aws ssm start-session --profile devadmin@nettix --region eu-west-1 \
  --target i-056a3f03e3df9ac45 \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters host="almaajo-new-test-aurora-mysql-cluster.cluster-ro-csuqkpopy0pb.eu-west-1.rds.amazonaws.com",portNumber="3306",localPortNumber="3306"

Writer endpoint:

almaajo-new-test-aurora-mysql-cluster.cluster-csuqkpopy0pb.eu-west-1.rds.amazonaws.com

Staging (ajo-mark-preprod)

Nothing to forward to yet

Account ajo-mark-preprod exists but is empty, so staging has no jumphost and no cluster. There is no command to give you until it does.

Production (nettix)

The almaajo-aurora3-production cluster in account nettix.

Prod DevAdmin is a separate group

readonly@nettix cannot start the session, and devadmin@nettix in prod belongs to a deliberately small group. Without it this tab has nothing you can run — ask via Slack at #mobility_mp_infra.

aws ssm start-session --profile devadmin@nettix --region eu-west-1 \
  --target i-0e60ee29bf7ec05a6 \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters host="almaajo-aurora3-production-db.cluster-ro-csuqkpopy0pb.eu-west-1.rds.amazonaws.com",portNumber="3306",localPortNumber="3306"

Writer endpoint:

almaajo-aurora3-production-db.cluster-csuqkpopy0pb.eu-west-1.rds.amazonaws.com

The command stays in the foreground and prints Waiting for connections... when the tunnel is up. Leave it running and open a second terminal for the client. Stopping it with Ctrl-C closes the tunnel and every connection through it.

If you already run MySQL locally, port 3306 is taken. Set localPortNumber to something else, such as 3307, and use that port in the next step.

3. Connect

mysql --host=127.0.0.1 --port=3306 --user=<username> --password

The host is always 127.0.0.1, because the tunnel is what you connect to. Anything that speaks MySQL over TCP, such as a GUI client or an application config, works the same way with the same host and port.

Troubleshooting

Symptom	Cause / fix
SessionManagerPlugin is not found	The plugin is not installed, or not on your PATH. See Prerequisites.
Error loading SSO Token, ExpiredToken	Session expired: aws sso login --sso-session alma-sso
TargetNotConnected	The jumphost is stopped, or its SSM agent is not reporting in.
AccessDeniedException on ssm:StartSession	ReadOnly cannot start sessions in any account — use the devadmin@ profile of the tab’s account. In prod, ask for that access via Slack at #mobility_mp_infra.
The document is rejected, or the session opens but no port does	The jumphost’s SSM Agent is older than 3.1.1374.0, which AWS-StartPortForwardingSessionToRemoteHost needs. Whoever runs the jumphost updates it.
An invalid or unknown target ... was specified	Wrong account for that jumphost. Check the profile and target against the same tab.
Can't connect to MySQL server on '127.0.0.1'	The tunnel is not up, or it listens on a different localPortNumber than the one you gave mysql.
mysql hangs, then times out	The tunnel is up but the host parameter is wrong. The jumphost can only reach its own environment’s endpoints.

Related

• Configure AWS SSO for the CLI — the profiles the commands use
• Environments — what test, staging and production mean, and why two accounts both hold a test cluster
• System map — where Aurora MySQL sits in the estate