Managing multiple databases with Docker and phpMyAdmin

When you are working locally with multiple projects you sometimes need to access different databases in order to view, change or manipulate data.

There are multiple ways to achieve this...

The old fashioned way

Using the good-old MySQL CLI in order to jump in and do what you want. This has the benefit of making you more conformable in using the terminal which might be required for staging or production environments, but this can also slow down your process and can be quite daunting at times.

We have an app for that!

There are multiple applications out there that can help you manage databases such as MySQL Workbench, TablePlus or Beekeeper Studio. While all these applications are awesome and are doing a great job at what they are designed for, they also have their drawbacks. MySQL Workbench can be hard to work with sometimes, TablePlus is not free and while Beekeeper has a free Community version it cannot be used in an enterprise environment or the free features might seem quite limiting at times.

phpMyAdmin to the rescue!

A web-based GUI that allows you to easily view and manage the databases that you want. Don't get me wrong, phpMyAdmin has its drawbacks as well, such as - prone to crashing, many vulnerabilities are often found, etc. but for local development I believe it has the right ratio of pros vs cons in order to be my daily DB management tool.

---

Setting up phpMyAdmin with Docker

If you're using Docker, the installation process of phpMyAdmin is quite easy and straight-forward. You'll only need a couple of things:

Install Docker

No matter which operating system you use, you will have to install Docker (duh!)

Create the docker-compose file

Select a folder in your computer where you want to run this project from and create a docker-compose.yml file.
This file will contain all the necessary information that Docker needs in order to build the appropriate containers. A default database will also be created in order to be used as a workbench and to avoid slow loading times if your other databases are not currently online or spun up.

version: '3.8'

services:

  workbench-mysql-database:
    image: 'mysql:8.0.0'
    container_name: 'workbench-mysql-database'
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
      MYSQL_DATABASE: 'workbench'
      MYSQL_USER: 'workbench'
      MYSQL_PASSWORD: 'workbench-pass'
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
    ports:
      - '${FORWARD_DB_PORT:-3310}:3306'
    networks:
      internal-net:
    volumes:
      - mysql-data:/var/lib/mysql
    restart: unless-stopped

  workbench-phpmyadmin:
    image: 'phpmyadmin:latest'
    container_name: 'workbench-phpmyadmin'
    environment:
      UPLOAD_LIMIT: '600M'
    ports:
      - '7090:80'
    volumes:
      - ./config.user.inc.php:/etc/phpmyadmin/config.user.inc.php
    networks:
      internal-net:
    restart: unless-stopped

volumes:
  mysql-data:
networks:
  internal-net:

As you can see in the code above a file named config.user.inc.php will also be mounted to the phpMyAdmin container. This file holds all the configuration needed in order to connect to our databases.

Create the phpMyAdmin configuration file

In the same folder that you created docker-compose.yml you also need to create the config.user.inc.php file.
In the configuration below we are specifying the attributes needed to connect to our other databases.

$cfg['Servers'] = [
    1 => [
        'auth_type' => 'config',
        'host' => 'host.docker.internal',
        'port' => 3306,
        'user' => 'db1user',
        'password' => 'db1pass',
        'verbose' => 'BD1'
    ],
    2 => [
        'auth_type' => 'config',
        'host' => 'host.docker.internal',
        'port' => 3307,
        'user' => 'db2user',
        'password' => 'db2pass',
        'verbose' => 'DB2'
    ],
    3 => [
        'auth_type' => 'config',
        'host' => 'workbench-mysql-database',
        'port' => 3306,
        'user' => 'root',
        'password' => 'root',
        'verbose' => 'Workbench'
    ]
];

For the host attribute there are three options that you can use:

1. If the database is in the same container you can use the instance name (e.g. workbench-mysql-database)
2. If the database is in the same machine but on a different Docker container, you can use the Docker host domain (host.docker.internal)
3. You can also use an internal or external IP (e.g. 192.168.1.205 or 10.15.203.5)

In this file you can also add extra phpMyAdmin configuration attributes such the default server to be loaded on boot and many other. The configuration can also be changed on the fly without having to restart the containers.

This guide assumes that on each project that you are working on you have a different external port assigned to your MySQL instances otherwise when you try to spin up two containers at once you'll get an IP conflict.