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.
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:
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:
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
sudoto the front of your compose command. If you don't want to have to use
sudoeach 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:
In your browser, type in your
: followed by the
port number, for instance
192.168.1.2:80, which will bring you to the login screen:
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
- If you used the above docker-compose file, your mysql container should already be connected to the
databasesnetwork. So to connect our
nextcloudcontainer 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