Connect remote mysql servers to local phpmyadmin via ssh tunnel(s)

As a self-hosting aficionado I do prefer to use tools that are hosted on my home server instead of my development computer/laptop.

There may be couple reasons you would want to run phpmyadmin on separate box that isn’t your desktop/laptop :

you are like me – and your primary development OS is Linux (and we all really love linux). But let’s be honest- GNU/Linux is not known for it’s abundance of quality first-class desktop applications, as much as we’d all love that.
you don’t enjoy setting up a mysql workbench/sequel pro and importing connections every time you need to work on a different device.
you have multiple development machines – and you dont want to bother syncing configuration to each and every one of them.
you have coworkers that also need access to those databases – and each of you setting things up on personal laptops and keeping that up to date seems like a chore noone wants to do.
you dont want to spend time to reconnect to remote servers. you prefer data to always be there.

Im not advocating this to be a perfect fit for every team and environment. Im sure many people will thrown on both phpmyadmin and idea of exposing mysql via SSH tunnels. And obviously mysql being remote will not be super speedy either.

However for my personal side projects I do enjoy this setup – having my remote scattered network of mini-applications/websites/databases always connected to my homelab servers and awailable at the click distance.

More towards the topic of this article – it’s trivial to add phpmyadmin to a mysql server that is on the same box/network where phpmyadmin is. However for remote mysql server we do not want to expose mysql port for very valid security reasons. For those use-cases ssh-tunnels are there as a possible solution.

Create docker network

In order for our containerized phpmyadmin to be able to access a tunneled mysql – we want tunnel containers to be on the same exact network as phpmyadmin.

So let’s create it:

docker network create --attachable -d bridge infranet

Launch dockerized SSH tunnels to remote mysql servers

Here is an example docker-compose.yml and accompanying .env file you could use:

version: "3.5"

networks:
  default:
    external:
      name: infranet

services:
  pma-prod-somemysql1:
    image: kingsquare/tunnel
    container_name: ${CNTNAME1}
    restart: unless-stopped
    volumes:
      - /test/.ssh/keys-remote-dbs/:/root/.ssh/keys-remote-dbs/
      - /test/.ssh/config:/root/.ssh/config
    command: "*:3306:127.0.0.1:3306 ${SSHHOST1}"

  pma-prod-somemysql2:
    image: kingsquare/tunnel
    container_name: ${CNTNAME2}
    restart: unless-stopped
    volumes:
      - /test/.ssh/keys-remote-dbs/:/root/.ssh/keys-remote-dbs/
      - /test/.ssh/config:/root/.ssh/config
    command: "*:3306:127.0.0.1:3306 ${SSHHOST2}"

  pma-prod-somemysql3:
    image: kingsquare/tunnel
    container_name: ${CNTNAME3}
    restart: unless-stopped
    volumes:
      - /test/.ssh/keys-remote-dbs/:/root/.ssh/keys-remote-dbs/
      - /test/.ssh/config:/root/.ssh/config
    command: "*:3306:127.0.0.1:3306 ${SSHHOST3}"

  pma-prod-somemysql4:
    image: kingsquare/tunnel
    container_name: ${CNTNAME4}
    restart: unless-stopped
    volumes:
      - /test/.ssh/keys-remote-dbs/:/root/.ssh/keys-remote-dbs/
      - /test/.ssh/config:/root/.ssh/config
    command: "*:3306:127.0.0.1:3306 ${SSHHOST4}"

All I did above is specified that we want to launch 4 ssh tunnel containers (using great kingsquare/tunnel image) called:

pma-prod-somemysql1
pma-prod-somemysql2
pma-prod-somemysql3
pma-prod-somemysql4

The most important tidbits in docker-compose.yml above are command variables.

For example:

*:3306:127.0.0.1:3306 ${SSHHOST4}"

What the above command means is – connect to ${SSHHOST4} SSH config (which is remotedbhost4), take port 3306 from remote server and mount it on 127.0.0.1 (your docker tunnel container) and then expose it on port 3306 available to all (star *) incoming ips. Which means – any container on infranet network can just connect to remote mysql server by using pma-prod-somemysql4 as a hostname and 3306 as a port. 🙌

Once docker-compose up is invoked – those containers will be available on a network called infranet.

As you can see – we’re passing in container names from .env file:

${CNTNAME1}
${CNTNAME2}
${CNTNAME3}
${CNTNAME4}

We are also passing in remote host names from .env file:

${SSHHOST1}
${SSHHOST2}
${SSHHOST3}
${SSHHOST4}

The idea here is that whenever we have another remote mysql server -> we simply add 2 variables to .env file and additional container into docker-compose.yaml -> and a new mysql server will become automatically accessible to your local phpmyadmin container once you execute docker-compose restart. Fairly easy && simple in my opinion.

.env file might look like this:


#Mysql tunnel n1
CNTNAME1=pma-prod-somemysql1
SSHHOST1=remotedbhost1

#Mysql tunnel n2
CNTNAME2=pma-prod-somemysql2
SSHHOST2=remotedbhost2

#Mysql tunnel n3
CNTNAME3=pma-prod-somemysql3
SSHHOST3=remotedbhost3


#Mysql tunnel n4
CNTNAME4=pma-prod-somemysql4
SSHHOST4=remotedbhost4

Note that I’m passing ssh config file and a directory with ssh keys into the container:

/test/.ssh/keys-remote-dbs/:/root/.ssh/keys-remote-dbs/
/test/.ssh/config:/root/.ssh/config

SSH keys are fairly explanatory, and ssh config might look like this:

#====================================================================================
# This file is to be used for connecting to remote mysql hosts
#====================================================================================

Host remotedbhost1
    User bytepursuitsuser
    HostName 1.2.3.4
    IdentityFile /root/.ssh/keys-remote-dbs/remotedbcert1

Host remotedbhost2
    User bytepursuitsuser
    HostName 1.2.3.4
    IdentityFile /root/.ssh/keys-remote-dbs/remotedbcert2

Host remotedbhost3
    User bytepursuitsuser
    HostName 1.2.3.4
    IdentityFile /root/.ssh/keys-remote-dbs/remotedbcert3

Host remotedbhost4
    User bytepursuitsuser
    HostName 1.2.3.4
    IdentityFile /root/.ssh/keys-remote-dbs/remotedbcert4

Prepare config file for phpmyadmin

Before launching phpmyadmin we should setup persistence for phpmyadmin container and configure servers to be used (using phpmyadmin config file).

Typically you would want to create 2 directories to store sessions && configs:

mkdir -p phpmyadmin/{sessions,configs}

You can pull a sample config file from here and change as desired, and place it in phpmyadmin/configs/config.user.inc.php.

The most important part in this config – we need to add mysql connection configs for 4 mysql servers we just exposed via tunnels, ex:

/**
 * Servers configuration
 */
$i = 0;

$i++; // server 1 :
$cfg['Servers'][$i]['auth_type']       = 'config';
$cfg['Servers'][$i]['verbose']           = 'pma-prod-somemysql1';
$cfg['Servers'][$i]['host']                  = 'pma-prod-somemysql1';
$cfg['Servers'][$i]['user']                  = 'root';
$cfg['Servers'][$i]['password']        = 'demopass';

$i++; // server 2 :
$cfg['Servers'][$i]['auth_type']       = 'config';
$cfg['Servers'][$i]['verbose']           = 'pma-prod-somemysql2';
$cfg['Servers'][$i]['host']                  = 'pma-prod-somemysql2;
$cfg['Servers'][$i]['user']                  = 'root';
$cfg['Servers'][$i]['password']       = 'demopass';

$i++; // server 3 :
$cfg['Servers'][$i]['auth_type']       = 'config';
$cfg['Servers'][$i]['verbose']           = 'pma-prod-somemysql3';
$cfg['Servers'][$i]['host']                  = 'pma-prod-somemysql3';
$cfg['Servers'][$i]['user']                  = 'root';
$cfg['Servers'][$i]['password']       = 'demopass';

$i++; // server 4 :
$cfg['Servers'][$i]['auth_type']     = 'config';
$cfg['Servers'][$i]['verbose']         = 'pma-prod-somemysql4';
$cfg['Servers'][$i]['host']                = 'pma-prod-somemysql4';
$cfg['Servers'][$i]['user']                = 'root';
$cfg['Servers'][$i]['password']     = 'demopass';

Launch PHPMYADMIN container

Now that we have remote mysql servers exposed to internal docker network, persistence is ready and config file ready – we are ready to launch our phpmyadmin container.

Similarly to above – let’s look at the docker-compose.yml and .env files.

docker-compose.yml file:

version: "3.5"

networks:
  default:
    external:
      name: infranet

services:

  pma-app:
    image: phpmyadmin:latest
    container_name: pma-app
    restart: unless-stopped
    ports:
    - ${HOST1}:80
    volumes:
    - ${FS_DIR}/sessions:/sessions
    - ${FS_DIR}/configs/config.user.inc.php:/etc/phpmyadmin/config.user.inc.php

.env file:

#General settings
COMPOSE_PROJECT_NAME=phpmyadmin
FS_DIR=phpmyadmin

#Phpmyadmin app settings
HOST1=31731

You should be able to launch this phpmyadmin docker like this:

docker-compose up -d
/code[

Phpmyadmin should be accessible on DOCKERHOST:31731, where DOCKERHOST is whatever ip address you run your homeserver on (192.168.10.103 in my case).

Obligatory screenshot

the end. Thank you for reading.

M	T	W	T	F	S	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Create docker network

Launch dockerized SSH tunnels to remote mysql servers

Prepare config file for phpmyadmin

Launch PHPMYADMIN container

Obligatory screenshot

Leave a Comment Cancel reply