How to set up connection to MySQL database through SSH Tunnel in Laravel Sail
I recently ran into a situation, where I had to connect to MySQL database from within a Laravel app in Laravel Sail via SSH tunnel. Specifically, in the app I'm working on (legaldocsforge.com), I am caching prompts to Chat GPT API in the database shared between production and local development. There are no "update" or "delete" operations in that database. I'm simply adding new rows, and reading them.
The app is a creator of legal documents. Having cached prompts allows me to locally test & seed the database without paying extra for API usage. Also, if I create some legal document, or a bunch of them locally, I can use the same document creation prompt in production without hitting OpenAI API. Until this week, I've been using a hobby plan at PlanetScale to cache those prompts, but since they're retiring that plan on April 8, 2024, I moved to a self-hosted MySQL instance spun up via Laravel Forge, which steers you into "SHH tunnel" connection by default (I didn't want to play around with "allowed IPs" addresses). And so, I had to figure out to connect to that DB from Laravel Sail container, that I use for local development.
I don't know if this is the optimal way to do it, but it works for me. I guess this is quite generic solution you could use in any Docker app, but at this time the only way I'm interacting with Docker is via Laravel Sail, so can't say that for sure.
Setting up MySQL connection through SSH tunnel
The setup is pretty simple. I assume that you know how to connect to MySQL via SSH tunnel in general, and know what Laravel Sail is. I also assume you already have SSH key, and that you can access the database from your machine using that key. The only thing I'm focusing on here is how to replicate in the container what already works on the host system.
To get the connection going, you need to add another service to docker-compose.yaml, that is on the same network as the web service. The name of the web service is laravel.test
`, but I renamed it `legaldocsforge.test` in my app. Connection parameters can also be defined in the .env
file to make them easier to swap.
I need to add that besides "cached prompts" MySQL database, I'm using another ("main") MySQL database for the rest of the app. The "main" database has different state on local and production. The "cached prompts" database shares the state between environments.
Changes in docker-compose.yml
Add the following new service in docker-compose.yaml
:
prompts-db-ssh-tunnel:
image: alpine
volumes:
- '~/.ssh:/root/.ssh'
ports:
- '${FORWARD_PROMPTS_DB_PORT:-3306}:3306'
command: >
sh -c "
# install openssh-client inside the container
apk add --no-cache openssh-client;
# Start the SSH tunnel
ssh -4 -L 0.0.0.0:3306:${PROMPTS_DB_HOST}:${PROMPTS_DB_PORT:-3306} forge@${PROMPTS_DB_HOST} -N -o ExitOnForwardFailure=yes -o ServerAliveInterval=60 -o ServerAliveCountMax=3;
# Keep the container running after the SSH command is initiated
while true; do sleep 30; done;"
networks:
- sail
Let's walk step by step to understand what this service does:
prompts-db-ssh-tunnel
: is just the name of our service. It can be anything you want.image: alpine
: we're using a lightweight Linux containervolumes
: we're mapping here local directory~/.ssh
to/root/.ssh
inside the container. I assume, you have your SSH keys in~/.ssh
on your local machine (it's the default location)ports
:FORWARD_PROMPTS_DB_PORT
specifies the port via which you'll be able to connect toprompts-db-ssh-tunnel
service. The default value is 3306, and you can override it by setting the value ofFORWARD_PROMPTS_DB_PORT
in Laravel's.env
file. We're hardcoding3306
as the tunneling port within the service, as we don't really need to customize it - the container's only purpose is to provide the SSH tunnel for MySQL connection.command
: please see the comments in thedocker-compose.yaml
excerpt above for general info.ssh -4 -L 0.0.0.0:3306:${PROMPTS_DB_HOST}:${PROMPTS_DB_PORT:-3306} forge@${PROMPTS_DB_HOST} -N
: we're using-4
flag is to force connection via IPv4 (it didn't work for me without that flag).-L
flag specifies that we're setting up forwarding.0.0.0.0
is special value of "bind address" that allows you to connect to the container from other containers (it didn't work for me without explicitely specifying this value). 3306 is a hardcoded value for local port via which we're tunneling the connection to remote DB port (see "ports" section above").PROMPTS_DB_HOST
PROMPTS_DB_PORT
are a hostname and port of your remote DB.-N
flag tellsssh
that we only want to set up the forwarding, and we don't need interactive session with remote host. If you need more details, runman ssh
to checkssh
manual.networks
: we're making sure that our service is on the same network as our web service.
We also need to make a small adjustment to our web service, by adding prompts-db-ssh-tunnel
as a dependency. You need to add it to the depends_on
key, as in:
services:
legaldocsforge.test:
build:
context: ./vendor/laravel/sail/runtimes/8.3
dockerfile: Dockerfile
args:
WWWGROUP: '${WWWGROUP}'
image: sail-8.3/app
extra_hosts:
- 'host.docker.internal:host-gateway'
ports:
- '${APP_PORT:-80}:80'
- '${VITE_PORT:-5173}:${VITE_PORT:-5173}'
environment:
WWWUSER: '${WWWUSER}'
LARAVEL_SAIL: 1
XDEBUG_MODE: '${SAIL_XDEBUG_MODE:-off}'
XDEBUG_CONFIG: '${SAIL_XDEBUG_CONFIG:-client_host=host.docker.internal}'
IGNITION_LOCAL_SITES_PATH: '${PWD}'
volumes:
- '.:/var/www/html'
networks:
- sail
depends_on:
## We're adding the below line
- prompts-db-ssh-tunnel
- mysql
- redis
- mailpit
After you make these changes, you need to rebuild your container with sail build
(in my case, adding -- no-cache
flag was not necessary, which I'm happy about, since it saves a few minutes of waiting).
Changing values in your .env file:
Since I have 2 MySQL connections, I added PROMPTS_
prefix to the keys used by default in .env
file.
PROMPTS_DB_CONNECTION=mysql
PROMPTS_DB_HOST=XXX.XXX.XXX.XXX # host of your remote database on Forge
PROMPTS_DB_PORT=3306 # port of remote database on Forge
PROMPTS_DB_SSH_TUNNEL_SERVICE=prompts-db-ssh-tunnel # service name of our forwarding container
FORWARD_PROMPTS_DB_PORT=3306 # port on our forwarding container
PROMPTS_DB_DATABASE=cached_prompts # DB name on forge
PROMPTS_DB_USERNAME=my_username # DB username on forge
PROMPTS_DB_PASSWORD=my_password # password on forge
I then refer to these values in my config/database.php
as follows:
// ...
'connections' => [
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
// ...
],
'prompts' => [
'driver' => 'mysql',
'url' => env('PROMPTS_DATABASE_URL'),
'host' => env('PROMPTS_DB_SSH_TUNNEL_SERVICE'),
'port' => env('FORWARD_PROMPTS_DB_PORT'),
'database' => env('PROMPTS_DB_DATABASE'),
'username' => env('PROMPTS_DB_USERNAME'),
'password' => env('PROMPTS_DB_PASSWORD'),
// ...
],
// ...
Final thoughts
To sum up, the steps outlined above offer a pretty straightforward method for connecting to a MySQL database via SSH tunnel within a Laravel Sail environment. This method, while applied here in the context of caching Chat GPT API prompts for legaldocsforge.com, is broadly applicable and could benefit various Docker-based applications. Adjustments in docker-compose.yml
and .env
files are key to this setup, ensuring a seamless connection through an SSH tunnel.
With this setup, everything should be working properly. If you're running into any issues, please let me know in the comments below. Your thoughts, alternatives, or questions on this setup are welcome for further refinement and learning :)