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 "Sequel Pro" 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
In order to establish a database connection from your computer to the database server 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 Universal Port Forward Method
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").
As already mentioned in the SSH access guide, new 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 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 firstname.lastname@example.org -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-da125679-1234-4cdb-8800-a335023289b5:3306 beach@instance-12abc345d-abcd-1ad2-361c-ab1a032d0234
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