Containers

Setting Up MySQL + phpMyAdmin Containers

PTS

Aug 31, 2021 • 6 min read

MySQL, MariaDB, Postgres... Databases are extremely useful. As containers, they can also be resource intensive, so you probably don't want too many. The below walkthrough shows you how to create a single server with the ability to create multiple databases for any MySQL need you have.

For those interested, I explain a bit more about the various types of database here, and you can check out this page's Postgres counterpart here.

Creating the Containers

SSH into your machine. The first thing we will do is create two custom networks in docker for our containers to connect to:

docker network create mysql && docker network create databases

the system will automatically choose the next available docker bridge subnet

for more detailed information about docker networks, click here

Now it's time to create our MySQL and phpMyAdmin containers. In your favourite docker compose folder, create your 'docker-compose.yml' and copy paste the following:

networks:
  mysql:
    external: true
  databases:
  	external: true

services:
    mysql:
      image: mysql:latest
      container_name: mysql
      volumes:
        - /path/to/mysql:/var/lib/mysql # change the local path to your NAS location where you want the DB data to live
      environment:
        - MYSQL_ROOT_PASSWORD=agoodpassword # enter a password for your main sql root user account 
      ports:
        - 3306:3306 # change before the ':' if necessary
      restart: unless-stopped
      networks:
        - mysql
        - databases
    
    phpmyadmin:
      image: phpmyadmin/phpmyadmin:latest
      container_name: mysql_admin
      ports:
        - 80:80 # change before the ':' if necessary
      environment:
        - PMA_PORT=3306 # enter the same port that you have your SQL running on
        - PMA_HOST=[NASIPaddress] # change the value here to your NAS IP LAN address
      restart: unless-stopped
      networks:
        - mysql

notice how we connect both containers to our mysql network, but only the mysql container to our databases network

Back in SSH, navigate to the folder you've stored the above docker-compose.yml file, and type docker-compose up -d. This will create your MySQL and Admin containers.

if you get a permissions error, add sudo to the front of your compose command. If you don't want to have to use sudo each time, follow the steps listed in this article, point 6

From now on, we only need to work with the phpMyAdmin container. Remember that if you have your NAS firewall set, you may need to modify the settings to allow the docker network IP and/or ports. To find this IP, type docker network inspect mysql into your SSH terminal.

There are 5 things for us to do:

Access phpMyAdmin
Create a database
Create a user account
Assign user/database privileges
Test the user

Accessing phpMyAdmin

In your browser, type in your NAS IP, : followed by the port number, for instance 192.168.1.2:80, which will bring you to the login screen:

the username is `root` and the password is the MYSQL_ROOT_PASSWORD you set in the docker-compose file

Once logged in you will be greeted with this screen:

Creating your first database

From the above screen, click either 'New' top left, or the 'Databases' tab. Type in a name for your database, and in the drop down box select 'Collation' for the default collation, 'utf8_bin', or a particular collation you know you need.

Creating a new user account

Click 'New' top left again, then the 'User accounts' tab, and 'Add user account'

Give a name to your User
Under 'Host name' select 'Local' from the drop down box which will auto-populate 'localhost' in the field
Choose a password, or generate one
DO NOT add privileges unless you know what you're doing. We'll give our new user privileges to our database in the next step

When you hit 'Go' at the bottom left corner of the screen, your user will be created, and you should see a confirmation screen similar to this:

Assigning database privileges to our user

Click the 'Home' button top right, then navigate to the 'User accounts' tab
Locate the user you created/want to assign database privileges to, and select 'Edit privileges' associate with that user
Below, we'll edit privileges for our newly created 'MyNewUser' account.

Once again ignore everything under 'Global'
Click the 'Database' tab (NOT the 'Databases' tab)
Where it says 'Add privileges on the following database(s)` select your database
Hit 'Go'

You'll notice it now says 'Database-specific privileges'
The below shows the minimum you should check which provide compatibility with most current apps as per the MySQL documentation. In my experience that's never been enough, and so I always 'Check all'
Hit 'Go'

You will then get another confirmation screen.

Testing the User

You can test everything has worked properly by logging out and logging back in with your new user.

if you're having trouble logging in with your new user, try changing the 'Host' from 'Local' to 'Any host' in the 'User accounts' tab.

If you can log in successfully, you should see only one database in the nav bar on the left underneath 'information_schema':

Making sure your new container can communicate with MySQL

Now that you've configured your new database inside your MySQL container, you need to make sure that your app can connect to it. To do this, you need to make sure that your app container and your MySQL container are on the same docker network.

if you need/want to create a new network, follow the steps here

Let's say we wanted to connect our MySQL and Nextcloud containers to a network called databases. We already created this network in the first step at the top of this article, but in case you didn't, here's the SSH command again:

docker network create databases

There are a couple of ways we can connect our containers to new networks:

Modify your docker-compose file to include the new network (you can use more than one network in your 'networks' block, and you can connect your container to more than one network at a time)
Use Portainer - go to your container inside portainer, scroll to the bottom, select the right network from the drop down list and click the 'Join network' button
SSH - you can connect a container to a network by typing

docker network connect [network name] [container name]

replace the [ ] information with your correct network and container names if you changed them

If you used the above docker-compose file, your mysql container should already be connected to the databases network. So to connect our nextcloud container to it, we type:

docker network connect databases nextcloud

you can read up a lot more on docker networking in the official docker documentation here

Happy collating!