4

I am trying to configure the official MySQL docker container that it has different users for all my micro-services and only those micro-service can connect to using their user.

It seems logical to use the Docker service name as written in the Docker-compose.yml in combination with the build-in MySQL host limitation functionality.

So I added a user in MySQL:

CREATE USER 'user1'@`docker_service_name` IDENTIFIED BY 'my_password';
GRANT SELECT ON `my_database`.'*' to 'user1'@`docker_service_name`;

When I try to connect from docker_service_name I get the error:

SQLSTATE[HY000] [1045] Access denied for user 'user1'@'172.19.0.6' (using password: YES) (Connection: mysql, SQL: select * from `table1` where exists (.....)

This is of course true, the user that is allowed to connect from everywhere doesn't exist (and I don't want it to exist).

After some research I found this question: How to set up mysql host limitation working with docker container

the answer says:

right here the official dockerfile for mysql:5.7, and at line 70 we can find:

#don't reverse lookup hostnames, they are usually another container && echo '[mysqld]\nskip-host-cache\nskip-name-resolve' > /etc/mysql/

I don't understand why reverse name lookup is disabled because "they are usually another container". Why is this? Will it do any harm if I enable this?

St. Jan
  • 284
  • 3
  • 17
  • Can you use a separate container for each service, rather than trying to share a single database? Then each database container only needs one user, matching its service. – David Maze Mar 28 '23 at 19:46
  • @DavidMaze you are right. Yet that makes that only one container can be started and the whole idea of having a backup and being scalable goes out of the window. – St. Jan Mar 28 '23 at 19:55
  • Nothing should stop you from running multiple database containers, even in the same Compose file. You will wind up with a separate backup per service, but I'd expect that to be manageable. – David Maze Mar 28 '23 at 20:36
  • @DavidMaze the problem is not multiple DBMS, but the micro-services that need to be looked up by MySQL. I want them to spawn and shutdown on demand and at least two of each running. – St. Jan Mar 29 '23 at 06:27
  • @DavidMaze and my question is not how to make it work, with some hack. I want to know why this is in place in the official MySQL Docker container. And what will happen if I disable it (which is possible, and then everything would work). And the Solution in the Question I link to writes his own Dockerfile for MySQL, is there not a better way to start this without this option enabled? – St. Jan Mar 29 '23 at 06:31
  • 1
    Someone's created an issue about this here: https://github.com/docker-library/mysql/issues/154. There's one line that jumped out at me that I think might go some of the way to explaining this - "containers uses the DNS configured in /etc/resolv.conf at creation time". If you have an elastic service other containers would be pretty ephemeral and resolv.conf would wind up out of date. There are also a couple of suggestions there for how to fix it, running the container with `-v /path/to/my.cnf:/etc/mysql/my.cnf` looks like a pretty clean alternative to changing the Dockerfile to me. – James Schultz Apr 03 '23 at 11:36
  • @JamesSchultz OO look this I didn't find yet, I'll give it a good look over thx! maybe type it up as an anwser, so I can give you credit ;-) – St. Jan Apr 03 '23 at 11:40
  • 1
    What about limiting the source host to a meaningful subnet, where all dockers reside? I had a similar problem with my myql server being hosted elsewhere and my app engine development trying to connect from IPS, so getting new name/ip each day at least. I indeed implemented an own bash-based noob DDNS based on /etc/host to get rid of that IPS dial-on name without inflicting my reverse dns and doing a flush_hosts afterwards as mysqld even caches that kind of stuff. – Synopsis Apr 06 '23 at 16:55
  • That is a super good idea! and I will remember about it it this comes up during deployment. But if host lookup is disabled that would not work ;-) But @JamesSchultz his comment gave me the solution I needed – St. Jan Apr 06 '23 at 17:53
  • @St.Jan a [MCVE] in GitHub would really help – Bohemian Apr 09 '23 at 22:12

1 Answers1

1

As pointed out by James Schultz in the comment there is an open bug report with MySQL.

The reason why this is enabled by default seems to be:

But why is this option by default?

  1. Docker relies on the host DNS
  2. Containers uses the DNS configured in /etc/resolv.conf at creation time
  3. If that DNS becomes unreachable (eg. disconnection, connection to a new wifi, ...) that DNS won't work anymore mysql authentication lags, waiting for client hostname resolution.

So if you don't care lag or are sure you DNS is exceptionally well connected, editing the my.cnf of your MySQL Docker container seems to be your best bet.

you should remove (or comment out) the line:

skip-name-resolve

To do this in a Docker file add the following

#for  image: mysql:8.0.33-debian
RUN sed -i -e "/^skip-name-resolve/d" /etc/my.cnf

If your working with docker-compose you can add this to your .yml, the "mysqld" starts mysql so that the container will keep running.

#for image: mysql:8.0.33-debian
entrypoint: ['/bin/sh', '-c', 'sed -i -e "/^skip-name-resolve/d" /etc/mysql/conf.d/docker.cnf && /usr/local/bin/docker-entrypoint.sh mysqld']
St. Jan
  • 284
  • 3
  • 17