"Reading initial communication packet" error while trying to connect to a mysql docker container from the host

Question

I'd like to be able to connect to my docker container as if MySQL server were installed on my local machine. I test my connection with:

mysql -u root -proot -h 127.0.0.1 -P 3306 --protocol=tcp

If I create a container using docker, I can successfully do it. Like this:

docker run --name some-mysql-standalone -p 127.0.0.1:3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7.29

If I use a container as a service in docker-compose, I get an error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2

MySQL server is running inside the container and I can access it.

My docker compose snippet:

version: '2'

services:

    mysql:

        image: mysql:5.7.29
        container_name: some_mysql
        restart: unless-stopped

        volumes:
            - ./mysql/data:/var/lib/mysql
            - ./mysql/init:/docker-entrypoint-initdb.d

        ports:
            - 3306:3306

        environment:
            MYSQL_DATABASE: some_mysql
            MYSQL_USER: root
            MYSQL_PASSWORD: root
            MYSQL_ROOT_PASSWORD: root

The interesting part is that I've been using this docker-compose.yml for some time now without any issues. I'm not really sure what's changed about my environment that caused it to stop working.

How can I make my mysql container in docker-compose accessible from the host machine?

Answer 1

At first, try to set -e MYSQL_ROOT_HOST=% on creating the container.

If it doesn't work: some docker images ignores environment variable directives (like -e MYSQL_ROOT_PASSWORD=<MY_PASSWORD> -e MYSQL_ROOT_HOST=%), so you have to add the (root) user on other hosts (%) by yourself manually:

docker exec -it <YOUR_CONTAINER_NAME> bash

mysql -u root -p<MY_PASSWORD>

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<MY_PASSWORD>';

FLUSH PRIVILEGES;

Answer 2

As stated by 7_R3X in this response, it seems to be a protection of mysql server to be more secure.

In my case, I was running the service in a docker image built on top of mysql:5.7 and the mysql server was started by a call to /etc/init.d/mysql start and not by using the entrypoint of mysql official image. Thus it might not apply to the question context but it does to the specified error in mysql service.

The steps that solved the issue were:

• Replacing the localhost/loop address to all in /etc/mysql/mysql.conf.d/mysqld.cnf
  • sed -i "s/bind-address.*/bind-address = 0.0.0.0/g" /etc/mysql/mysql.conf.d/mysqld.cnf
• Restarting the mysql service: /etc/init.d/mysql restart

Then I was able to access from outside (checked with the host machine) to the docker exposed port

Answer 3

Sometimes I meet this error after I stop and resume my virtual machine. The way to solve the problem is restart the virtual machine.

Answer 4

Found a similar error on this thread. See if any of your firewall configuration has been changed and try to bind your SQL server to 0.0.0.0 instead of localhost or 127.0.0.1.