Today I needed to reset a user’s password on a WordPress blog which uses MySQL as its backing database.
The WordPress blog, the MySQL database, and the NGINX server are all running on the remote server via Docker. More specifically they use a docker-compose.yaml file.
How this docker-compose.yaml file looks is not super important, but here is a stripped down example as one section in particular will need to be modified:
version: "3"
services:
nginx:
image: registry.example.com/sitename/nginx:1.2.3
php:
image: registry.example.com/sitename/www:1.4.5
mysql:
image: mysql:5.7.40
environment:
MYSQL_DATABASE: site_db
MYSQL_PASSWORD: a_very_strong_password_goes_here
MYSQL_ROOT_PASSWORD: and_a_very_strong_password_goes_here
MYSQL_USER: dbuserCode language: Dockerfile (dockerfile)All good, works fine.
Note here that the MySQL instance does not expose any ports. In this configuration it is not yet possible to connect to the MySQL docker container from the outside world.
Two Changes
There are two changes to be made in order to allow outside connections to your MySQL docker container.
These are:
- Provide a port mapping in your
docker-compose.yamlfile - Open up the corresponding firewall port on your host system
This does assume you are using a firewall. For these purposes I shall be using UFW.
Provide A Host To Container Port Mapping
The first change takes two steps:
version: "3"
services:
nginx:
image: registry.example.com/sitename/nginx:1.2.3
php:
image: registry.example.com/sitename/www:1.4.5
mysql:
image: mysql:5.7.40
environment:
MYSQL_DATABASE: site_db
MYSQL_PASSWORD: a_very_strong_password_goes_here
MYSQL_ROOT_PASSWORD: and_a_very_strong_password_goes_here
MYSQL_USER: dbuser
ports:
- 3336:3306
Code language: Dockerfile (dockerfile)I have deliberately used the external port of 3336 as it is not the MySQL default port of 3306. This is a small security precaution.
This change won’t apply immediately. You will need to stop and restart your MySQL container for the change to take effect.
docker-compose down
docker-compose up -dCode language: Shell Session (shell)Note – your method of stopping and starting docker containers may differ to this. This is a simplified example to highlight that you do need to re-create the MySQL database container for this change to take effect.
Once, done, you should be able to see that the port is now exposed:
docker ps -a
c0ab49dd1374 mysql:5.7.40 "docker-entrypoint.s…" 20 minutes ago Up 4 seconds 33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp your_container_dbCode language: Shell Session (shell)Your output will likely look slightly different. The important bit is:
0.0.0.0:3336->3306/tcpThe host to database container port mapping step is now active.
Open The Firewall Port
The container should now accept connections on external port 3336 and map them to the MySQL database port of 3306.
However, your host machine likely / hopefully will not allow remote connections from anyone to any port on your system.
In my case this system is using UFW. I can see the current UFW setup as follows:
sudo ufw status
Status: active
To Action From
-- ------ ----
80/tcp ALLOW Anywhere
443/tcp ALLOW Anywhere
80/tcp (v6) ALLOW Anywhere (v6)
443/tcp (v6) ALLOW Anywhere (v6) Code language: Shell Session (shell)Your output may be different to this, but as a basic example this will illustrate the point.
The current config is to allow outside connections from anywhere to ports 80 (http) and 443 (https) on our host system. In other words, allow in regular web traffic.
In order to allow external traffic to port 3336 we need to add in a new UFW firewall rule.
I am going to restrict this down as much as possible. The ways I will restrict this are:
- Allow traffic only from my current IP v4 address
- Restrict the available port to
3336 - Restrict the protocol to
tcp
You can find your current IP address by using a site such as WhatsMyIP. You then need to customise the command below:
sudo ufw allow from xxx.xxx.xxx.xxx to any port 3336 proto tcpCode language: CSS (css)Where xxx.xxx.xxx.xxx is your IP address.
This will create a firewall rule that allows incoming packets from the IP address xxx.xxx.xxx.xxx to destination port 3336, over the TCP protocol. The “from” and “to” keywords specify the source and destination addresses, respectively, and the “proto” keyword specifies the protocol (in this case, TCP). The “any” keyword means that the rule applies to all IP addresses on the host system.
sudo ufw status
Status: active
To Action From
-- ------ ----
80/tcp ALLOW Anywhere
443/tcp ALLOW Anywhere
3336/tcp ALLOW xxx.xxx.xxx.xxx
80/tcp (v6) ALLOW Anywhere (v6)
443/tcp (v6) ALLOW Anywhere (v6)
Code language: Shell Session (shell)You should now be able to connect to your MySQL database container via the host machine from the outside world.
Undo Your Changes
The changes made above do give some level of security. The database port is open, but restricted down to your IP address.
However, I would strongly urge you to undo your changes once you have done whatever you needed to do with your MySQL database. In my case, once the user’s password was changed, it was time to revert the changes.
Delete The Firewall Rule
To delete a UFW firewall rule, you can use the delete or delete allow command followed by the rule specification. For example, to delete the rule we created above that allows traffic from xxx.xxx.xxx.xxx to TCP port 3336, you can use the following command:
sudo ufw delete allow from xxx.xxx.xxx.xxx to any port 3336 proto tcpCode language: Shell Session (shell)Sometimes however, you do not remember the exact command you used. Deleting rules from UFW is also possible using the built in rule numbering system:
sudo ufw status numbered
Status: active
To Action From
-- ------ ----
[ 1] 111 DENY IN Anywhere
[ 2] 222/tcp ALLOW IN Anywhere
[ 3] 80/tcp ALLOW IN Anywhere
[ 4] 443/tcp ALLOW IN Anywhere Code language: Shell Session (shell)To delete a rule, just provide the number:
sudo ufw delete 2Code language: JavaScript (javascript)Double check you have deleted the rule with sudo ufw status. If all went well then you should no longer see the rule for the IP address xxx.xxx.xxx.xxx.
Remove The Port Mapping
The Docker change is probably the easier of the two.
Remove the two lines you added earlier:
version: "3"
services:
nginx:
image: registry.example.com/sitename/nginx:1.2.3
php:
image: registry.example.com/sitename/www:1.4.5
mysql:
image: mysql:5.7.40
environment:
MYSQL_DATABASE: site_db
MYSQL_PASSWORD: a_very_strong_password_goes_here
MYSQL_ROOT_PASSWORD: and_a_very_strong_password_goes_here
MYSQL_USER: dbuser
ports:
- 3336:3306
Code language: Dockerfile (dockerfile)You could use the # key to comment them out. This is a little time saver if you need to do this frequently.
This change won’t take effect immediately.
docker-compose down
docker-compose up -dAgain, your method of stopping and starting Docker containers may differ. This is a simplified example.
Check the output of docker ps -a to ensure the change is in play:
docker ps -a
c4b8d4c10de6 mysql:5.7.40 "docker-entrypoint.s…" 2 minutes ago Up 9 seconds your_container_dbCode language: CSS (css)You should now no longer be able to access the MySQL database container from the outside world.