This guide walks you through the steps to connect to your instance's MariaDB database from a remote computer via SSH. We assume that you have the following ready:
- a Beach project with at least one instance
- an SSH client (such as OpenSSH or Putty) and a personal SSH key pair
- a MySQL or MariaDB compatible client, such as the "mysql" client binary or a tool like "TablePlus" for Mac, "Valentina Studio", MySQL Workbench or built-in database support in PhpStorm
If you have never accessed your instance via SSH, you may want to read our guide about SSH access first.
Behind the Scenes
Your instance is using a database which is either located on a shared database server or a dedicated server or even cluster. No matter which server you are using, the connection method is always the same.
For security reasons, none of the database servers is accessible directly from the internet. Therefore, there a basically two ways to connect to your database:
- directly from the instance by logging in to the instance container via SSH
- from a remote computer (such as your notebook) by SSH port-forwarding
The one-fits-all way to connect to your database is establishing a kind of SSH tunnel which forwards the database server's port to your computer. Once the port forward is established, you can access the database server as if it was running on your computer (via "localhost").
In order to establish such a tunnel you will create a connection via SSH from your computer to the Beach SSH Gateway ("jump host"), from there a connection via SSH to your Beach instance and from your Beach instance a connection to port 3306 of your database server. While that may sound a bit complex, it can usually be done with a single command. And it's more secure than exposing the database server to the internet.
The Easy Way
As already mentioned in the SSH access guide, modern versions of SSH support an option for connecting through a so-called "jump host" (the -J parameter) while older versions don't have that option. The easiest (and recommended) way is to copy the tunnel command from the instance detail page, just look for SSH tunnel (via instance) in the Connection Info box. It looks like this:
ssh -nNT -J firstname.lastname@example.org -L 3399:database-…-cc609b4211b9.beach:3306 beach@instance-45bdb1cc-…-db7a012c0599.beach
After opening the tunnel you can connect to 127.0.0.1:3399 using any MySQL client with the credentials from the Connection Info box to access the database.
The Universal Port Forward Method
In case your OpenSSH version is too old, you need to create an entry in your ~/.ssh/config file which allows you to connect to any Beach instance you have access to by just using the instance identifier:
For this to work you need to add the following to ~/.ssh/config:
ProxyCommand ssh email@example.com -W %h:%p
Once you have that, you at least don't need to take care of the jump-host part and your computer knows how to connect to any host named "instance-…".
Now find the database host name in the connection details of your instance and you are ready to start a port forwarding session:
ssh -nNT -L 3399:database-…-a335023289b5.beach:3306 beach@instance-12abc345d-…-ab1a032d0234.beach
You should now be able to talk to the database server through your computer's local port 3399. Just make sure to use "127.0.0.1" as the host name and port "3399" for the configuration of your MySQL / MariaDB client.
If you have the "mysql" command-line tool installed on your computer, you can connect to your database like so:
mysql --user=<your-database-username> --port=3399 --password=<your-database-password> --database=<your-database-name> --host=127.0.0.1