Databases
Remember this picture?
Several of our Docker Containers will be talking to a database. and this database also needs to run in a Docker Container.
Let's start with spinning up a Docker Container with a MySQL Database.
Create a new directory next to ProxyServer called 'mysql'. In there create a directory called 'database'.
cd deployProject
mkdir -p mysql/database
docker-compose.yml for mysql
MySQL gets its own docker-compose.yml file. We keep all those files as standard as possible without the need to create Docker images.
version: '3.9'
services:
db:
image: mysql:5.7.36
restart: always
container_name: mysql
environment:
MYSQL_DATABASE: nginxindocker_database
MYSQL_USER: exampleuser
MYSQL_PASSWORD: examplepass
MYSQL_RANDOM_ROOT_PASSWORD: '1'
volumes:
- ./database:/var/lib/mysql
ports:
- "8086:3306"
networks:
my-net:
ipv4_address: 172.30.0.90
networks:
my-net:
external: true
name: my-net
MYSQL Environment variables
The nice people creating the MySQL Docker Image gave us the tools to configure everything with Environment Variables. No need to create our own MySQL Images.
For an explanation on the MySQL environment variables, check out the MySQL Docker Image on Docker Hub
For a simple setup we only need 4 of them:
- MYSQL_DATABASE: nginxindocker_database
- MYSQL_USER: exampleuser
- MYSQL_PASSWORD: examplepass
- MYSQL_RANDOM_ROOT_PASSWORD: '1'
Security
Isn't is a problem to have the database password in the docker-compose.yml file, which will be on Github?
Well, no, it is not a problem. The reason is that the Host should not have username/password access. All ssh access should be done by Public/Private KeyPairs.
Paranoia security experts might be disagreeing on this, and I would love to hear their comments. If I'm wrong, I will rectify and change the configuration.
Ports
Interesting here is that the Container port is the default 3306, and the port on the Host for this Database is 8086.
This 8086 is NOT used by other services. (See below why we have this port then) The other services connect to the IP address and the internal Port.
Starting the MySQL Container
Go in the mysql directory and start the Container with docker-compose
$ docker-compose up
[+] Running 12/12
⠿ db Pulled 15.7s
⠿ a10c77af2613 Pull complete 4.1s
(Many more Pulls) 13.3s
⠿ 1c5fd9c3683d Pull complete 13.4s
⠿ 2e35f83a12e9 Pull complete 13.4s
[+] Running 1/1
⠿ Container mysql Started 0.7s
Attaching to mysql
Other interesting lines from the log
mysql | 2021-11-21 16:46:36+00:00 [Note] [Entrypoint]: Initializing database files
...
mysql | 2021-11-21 16:46:44+00:00 [Note] [Entrypoint]: Database files initialized
...
mysql | 2021-11-21 16:46:47+00:00 [Note] [Entrypoint]: Creating database nginxindocker_database
mysql | 2021-11-21 16:46:47+00:00 [Note] [Entrypoint]: Creating user exampleuser
mysql | 2021-11-21 16:46:47+00:00 [Note] [Entrypoint]: Giving user exampleuser access to schema nginxindocker_database
...
mysql | 2021-11-21T16:46:50.500496Z 0 [Note] mysqld: ready for connections.
mysql | Version: '5.7.36' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
The first line shows that a database is created. The reason is that the MySQL Image starting scripts do not see a database in /var/lib/mysql (the database location in the Container). Therefore, one is created.
Conveniently this location is mapped (by Volumes setting) in the docker-compose file to a directory on the Host (./database). So, the database remains if the container is restarted.
If you want a new database, then just delete directory ./database in the mysql directory.
Deleting a Database?
Well, when you try to delete those database files, you will notice that they have another permission/owner than your account on the host. This is because you probably have a non-root account on the host, and the MySQL Container does everything under root. So, use sudo to remove those files.
Network
Again, the external network is my-net and this docker-compose needs to attach to it.
On the my-net this MySQL Database Container has an IP address of 172.30.0.90.
There is no need to add the MySQL Container to the Proxyserver. The reason is that we only access the database from other Containers, not from the Proxyserver.
Showing results
$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cd1b870b2039 mysql:5.7.36 "docker-entrypoint.s…" 4 seconds ago Up 2 seconds 33060/tcp, 0.0.0.0:8086->3306/tcp mysql
099e16c935af nginx:1.21.4 "/docker-entrypoint.…" 2 days ago Up 2 days 80/tcp staticsite
1f1a282fd9cc nginx:1.21.4 "/docker-entrypoint.…" 2 days ago Up 2 days 0.0.0.0:80->80/tcp proxyserver
Different methods to access the database
You can access the database in 4 possible ways:
Accessing the database on the Host
When trying to access the database from the Host, the following connection string is used:
mysql://exampleuser:examplepass@localhost:8086/nginxindocker_database
Notice the port number being 8086 instead of 3306.
This means also that if you want to access the database from another system like your laptop to the database running on the Digital Ocean Droplet (or AWS EC2 Instance) in the Container, your SysAdmin needs to open port 8086 on the Host-Firewall.
Accessing the database via an SSH Tunnel
Suppose you want to use the method described above, but your SysAdmin refuses to open port 8086 on the Host. (which would make him a very wise SysAdmin)
If you have ssh access to that system via SSH Keys, you could also open an ssh tunnel and access the database locally on your MacBook:
Setting up the tunnel:
ssh -L localhost:9999:hostsystem:8086 username@hostsystem -N
- "username" is the username you are doing all this work under on the Host system.
- "hostsystem" here is the machine the Containers are running on, our 'host' in this story.
The command basically says:
Create a connection using my Public/Private Key Pair, and after that send all requests on my MacBook to localhost:9999 to hostsystem:8086 over this connection
And your laptop opens a database on localhost:9999.
This is, in my view, the best and most secure option. Use JetBrains-DataGrip or any other database management tool on your laptop and use the ssh tunnel to connect.
Accessing the database via the Proxyserver
In case you did not map a Port in the docker-compose file, you could set up the database in the Proxyserver and forward the requests to the IP number and internal Port.
Which is quite an insecure method in my view.
Use phpmyadmin
You could also set up a new Docker Container running PHPmyadmin connecting to the database.
This is also not an ideal (insecure) situation in my view.