how to connect to a MySQL database in a docker container?

Question

starting up the default mysql server:

root $ 
root $ docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql/mysql-server:latest
d2b066e9aa4192b45334e9dada923b6b3dca5703e811733e150333f52849c1a1
root $ 
root $ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                            PORTS                               NAMES
d2b066e9aa41        mysql/mysql-server:latest   "/entrypoint.sh mysq…"   9 seconds ago       Up 6 seconds (health: starting)   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql
root $ 

trying to login to the container, but getting the host MySQL instance:

nicholas $ 
nicholas $ mysql -h 172.17.0.2 -P 3306 --protocol=tcp -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | user             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql> 

I'm trying that explicit IP address because:

root $ 
root $ docker inspect mysql | grep IP
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.2",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
                    "IPAMConfig": null,
                    "IPAddress": "172.17.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
root $ 

although I'm also getting the same result for localhost in that I'm still logging into the host instance of MySQL rather than the container.

root $ 
root $ docker exec -ti --user root mysql bash
bash-4.2# 
bash-4.2# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | healthchecker    |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql> 

which is virgin, not having user user configured on the container.

Additionally:

root $ 
root $ netstat -peanut | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      0          25585352   488887/docker-proxy 
root $ 
root $ docker inspect mysql | grep password
                "MYSQL_ROOT_PASSWORD=password",
root $ 

and so I was expecting that by specifying tcp for localhost from the host I'd be able to connect to the docker container. Yet that's not happening, I'm getting the host's database:

nicholas $ 
nicholas $ mysql -h localhost -P 3306 --protocol=tcp -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | user             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql> 

I think if I uninstall mysql server from the host I'll be able to connect to the docker database, but surely there's a better approach.

Answer 1

One way to achieve this is by using the MySQL socket to connect instead of port. For this you would have bind a mount to your container. You would anyway need a host directory bound to container to persist Database data.

• First create a directory on host to mount container /var/lib/mysql
• Use -v option in docker run to attach host path to container
• Connect from client using -S and point to mysql.sock socket file

Example:

mkdir /home/user/mysql-1

Then start the container so that /home/user/mysql-1 will attach to container's /var/lib/mysql:

docker run --name mysql -d \
-v /home/user/mysql-1:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
mysql/mysql-server 

Once the container is started, you can connect to it with:

mysql -h localhost -u user -p -S /home/user/mysql-1/mysql.sock