Unable to Access MySQL Container in Docker-Compose Setup

1. Overview

Docker is a container platform for developing, distributing, and running containerized applications. The Docker container technology stack provides several technologies including standalone Docker containers and Docker Compose among others.

MySQL is an open-source, relational database.

If we can’t access a MySQL container in a Docker Compose setup, there could be several reasons; therefore, it’s important to understand how the MySQL client program chooses the connection method.

In this tutorial, we’ll learn to fix some common issues. The only prerequisite we need to perform is to install Docker and Docker Compose on a supported Linux platform.

2. How Does the MySQL Client Choose a Connection Method

We can use a client program such as mysql to connect to the MySQL server running in a Docker environment using a supported connection transport protocol. However, on Linux, which is the operating system Docker is mainly used on, we can use only one of the two connection transport protocols: TCP/IP and Unix socket file.

2.1. Local and Remote Connections

When we don’t specify a host, the client only tries to connect to the local host. Further, MySQL client uses the Unix socket file protocol by default when it connects locally on Linux. The Unix socket file it uses is /var/run/mysqld/mysqld.sock by default. We can also use the TCP/IP protocol to connect locally. When we want to remotely connect to a MySQL server running in a Docker container, we can only use the TCP/IP protocol.

2.2. Choosing the Connection Protocol

MySQL client program first determines the protocol to use. It establishes the protocol from two connection options: –host and –protocol. The mysql client uses the default values for these connection options when these aren’t specified explicitly. Furthermore, the operating system (Linux, Windows), and whether the connection is local or remote are also determining factors in choosing the protocol.

Let’s discuss how we can use a specific connection method/protocol for a local connection on Linux.

2.3. Using the TCP/IP Protocol

We can use the TCP/IP protocol for a local connection with either of the commands:

$ mysql -u root --protocol=TCP
$ mysql -u root --host=127.0.0.1
$ mysql -u root --host=127.0.0.1 --protocol=TCP
$ mysql -u root --host=localhost --protocol=TCP

When we specify the IP address of the local host (127.0.0.1 or other), instead of localhost, the client program uses the TCP/IP protocol by default. Further, when we specify –host as localhost along with –protocol as TCP/IP, the client program interprets localhost to be 127.0.0.1. The –host, and –protocol values must be compatible when we specify both of them.

2.4. Using the Unix Socket File Protocol

Accordingly, when we want a protocol to be Unix socket file, we can run any of the equivalent MySQL client commands:

$ mysql -u root
$ mysql -u root --host=localhost
$ mysql -u root --protocol=SOCKET
$ mysql -u root --host=localhost --protocol=SOCKET

This time, it uses the localhost as the host instead of interpreting it as the 127.0.0.1 host IP address.

3. Common Issues

Let’s discuss some common issues and misconceptions a user could encounter when accessing a container running MySQL server or the server itself. Some of these issues can happen regardless of the environment, whether it is a standalone Docker or Docker Compose.

Let’s remember that accessing a container isn’t the same as accessing the MySQL server running in it.

3.1. A Docker Container on a Local Machine Is Not Local

A common misconception is to take a Docker container on a local machine to be local. Although the Docker container is running on the local machine, it’s a virtual machine of its own and, therefore, remote. As a result, we can’t use a localhost connection to the MySQL server running in the Docker container. However, we can connect to the MySQL server remotely using the TCP/IP protocol. As an example, we can find the IP address of a container named mysql-db-1:

$ docker inspect 
-f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 
mysql-db-1

172.21.0.2

We can use the IP address in the output to make a remote connection to MySQL:

$ mysql 
-h 172.21.0.2 
-P 3306 
--protocol=tcp 
-u root 
-p

Thereafter, we get connected to the MySQL server.

3.2. A Docker Compose Setup Has Its Networking

When we run multiple containers separately from the same Docker image, the containers are not necessarily on the same network.

However, when we run multiple containers in a Docker Compose setup, a new network is created, and all the containers in the Compose setup join this network. As a result, one container in a Docker Compose setup can communicate with another by just using the IP address of the other container to make a remote connection.

3.3. Access Denied for User Root

We can get an error message when we don’t provide a password for the root user after we’ve set a root password:

$ docker exec -it mysql-db-1 bash
root@3012e3ae6cec:/# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

This issue can be fixed by including the -p option in the command:

root@3012e3ae6cec:/# mysql -u root -p
Enter password: 

We should supply the password when prompted.

3.4. Wrong or Unknown Protocol

Another issue concerns using the wrong or unsupported connection transport protocol. As an example, let’s try to connect to the MySQL server locally using the host as 127.0.0.1 and the protocol as SOCKET:

$ mysql -u root 
--protocol=SOCKET 
--host=127.0.0.1

Indeed, the error message indicates a wrong protocol:

ERROR 2047 (HY000): Wrong or unknown protocol

We can fix the issue by specifying compatible connection options. Remember that the SOCKET, or Unix socket file, protocol can only connect using the local hostname.

3.5. Can’t Connect To Local MySQL Server Through Socket

Another issue could occur when a local host Unix socket file connection can’t be established:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

This could occur due to one of a set of reasons relating to the MySQL server start not yet completed, socket file configuration in /etc/mysql/my.cnf, or the permissions to the directory containing the socket file. To demonstrate, let’s try to start a Docker container for MySQL and establish an interactive bash shell to the container in the same docker run command:

$ docker run 
-e MYSQL_ALLOW_EMPTY_PASSWORD=yes  
-it 
mysql:8.4.2 bash

Thereupon, let’s try to connect to MySQL at the interactive bash shell:

bash-5.1# mysql -u root

We’re likely to get the message: ERROR 2002 (HY000): Can’t connect to local MySQL server through socket because the MySQL server hasn’t yet started.

3.6. services.db.environment Must Be a Mapping

Yet another issue could occur due to incorrect configuration for the Docker Compose environment variables. To demonstrate, let’s configure Docker Compose environment variables in an environment file (.env by default). Subsequently, use the docker-compose.yml file configured as:

services:
  db:
    image: mysql:8.4.2
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE=${MYSQL_DATABASE}
      MYSQL_USER=${MYSQL_USER}
      MYSQL_PASSWORD=${MYSQL_PASSWORD}

When we run the docker compose up command, we’re likely to get an error message:

validating docker-compose.yml: services.db.environment must be a mapping

While the docker-compose.yml file uses the interpolation syntax correctly, it does not specify a mapping. This issue can be fixed by adding the hyphens (‘-‘) to create a mapping:

services:
  db:
    image: mysql:8.4.2
    restart: always
    environment:
     - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
     - MYSQL_DATABASE=${MYSQL_DATABASE}
     - MYSQL_USER=${MYSQL_USER}
     - MYSQL_PASSWORD=${MYSQL_PASSWORD}

Alternatively, we can specify the environment values in the docker-compose.yml file directly, with or without quotes.

4. Conclusion

In this article, we learned about finding the cause of and fixing the issue of being unable to access a MySQL container in a Docker Compose setup despite a successful Docker image run. We also learned how the MySQL client protocol chooses a connection protocol to connect to the MySQL database. We also discussed how to fix some specific issues.

As always, the Docker commands’ scripts used in this article are available over on GitHub.