11

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?

sr9yar
  • 4,850
  • 5
  • 53
  • 59

8 Answers8

4

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;

Hermann Schwarz
  • 1,495
  • 1
  • 15
  • 30
  • This worked, thank you! As you said, `-e MYSQL_ROOT_PASSWORD=` was being ignored so I had to login using default password "root" – Martin Meli Jul 18 '22 at 10:45
2

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.

7_R3X
  • 3,904
  • 4
  • 25
  • 43
  • Thanks for your suggestion, but I already tried any possible combinations of IPs, bind-address and other my.cnf settings . Completely removing all containers and images, verifying that mysql has actually started inside the container, updating grants, crating new mysql users, even checked my disk usage :) Also everything is installed on my local machine. The docker outputs `0.0.0.0:3306->3306/tcp,` as always, meaning it bound the port correctly to my host and this port is actually used. Also I tried restarting the docker service , didn't try reinstalling it though ;) – sr9yar Feb 19 '20 at 10:24
2

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

Jess Chen
  • 3,136
  • 1
  • 26
  • 35
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

MarcosBernal
  • 562
  • 5
  • 13
2

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
Atelst
  • 21
  • 1
  • I forgot to add that first use port 3306, then replace it with 8888, this will definitely help, checked by myself – Atelst Oct 17 '21 at 12:15
0

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.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
        networks: 
            - 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.

sr9yar
  • 4,850
  • 5
  • 53
  • 59
0

This configuration worked for me:

ports:
  # <Port exposed> : < MySQL Port running inside container>
  - '3306:3306'
expose:
  # Opens port 3306 on the container
  - '3306'
ACV
  • 9,964
  • 5
  • 76
  • 81
0

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

Carmageddon
  • 2,627
  • 4
  • 36
  • 56