"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.29container_name: some_mysqlrestart: unless-stoppedvolumes:- ./mysql/data:/var/lib/mysql- ./mysql/init:/docker-entrypoint-initdb.dports:- 3306:3306environment:MYSQL_DATABASE: some_mysqlMYSQL_USER: rootMYSQL_PASSWORD: rootMYSQL_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?

Best Answer

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

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

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.

This helped me solve my problem:Create the container with the command

docker run --detach --rm --name mysql-server -p 8888:3306 --env MYSQL_ROOT_PASSWORD=admin mysql:latest

And log in to mysql

mysql -uroot -padmin -h127.0.0.1 -P8888 --protocol=tcp

From the practical point of view I made it work by explicitly creating a network. Here's an example:

version: '2'services:mysql:image: mysql:5.7.29container_name: some_mysqlrestart: unless-stoppedvolumes:- ./mysql/data:/var/lib/mysql- ./mysql/init:/docker-entrypoint-initdb.dports:- 3306:3306environment:MYSQL_DATABASE: some_mysqlMYSQL_USER: rootMYSQL_PASSWORD: rootMYSQL_ROOT_PASSWORD: rootnetworks: - default...networks:default:external:name: somename_default

I thought that since I'm unable to connect I should try to delete and create a new network, because docker-compose creates a separate network for containers behind the scenes. I'm still don't know the reasons why I faced this issue though.

This configuration worked for me:

ports:# <Port exposed> : < MySQL Port running inside container>- '3306:3306'expose:# Opens port 3306 on the container- '3306'

A workaround that actually works (I don't recommend doing it on prod without understanding the full implications, just for local docker env):alter user 'root'@'%' identified with mysql_native_password by 'password123'

Credits go to https://laracasts.com/discuss/channels/laravel/caching-sha2-password-error-when-running-php-artisan-migrate?page=1&replyId=578038

Accepted Answer

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;

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

Best Answer

Random Posts