Search code examples
mysqldockerphpstorm

Docker - Failed to Connect to MySQL instance. How to solve it?

Running in a MAC OS.

Following these Basic Steps for MySQL Server Deployment with Docker, I am trying to make connection with the container trough the php storm database.

And I am getting the error below in the picture:

enter image description here

I can get access to it trough the terminal:

docker exec -it 0ed bash
bash-4.2# mysql -uroot -pdockerLocal
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

mysql>

There is no process running in the port 3306.netstat -vanp tcp | grep 3306 shows nothing.

And my Laravel can't connect to the db server neither.

DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=testdb
DB_USERNAME=root
DB_PASSWORD=dockerLocal

This is the container info:

docker ps -a
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS                    PORTS                 NAMES
0edce223c684        mysql/mysql-server:5.7   "/entrypoint.sh mysq…"   34 minutes ago      Up 34 minutes (healthy)   3306/tcp, 33060/tcp   stupefied_johnson

How could I test this connection and how could I have the phpstorm db connection working?

UPDATE

After the port is exposed (with was the issue) we can't connect to the container using root@localhost.

SELECT host, user FROM mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

This is the error :

Connection to @0.0.0.0 failed.
            [HY000][1130] null,  message from server: "Host '172.17.0.1' is not allowed to connect to this MySQL server"

The solution is here in this post.

Check if the database user exists and can connect

In MySQL, each database user is defined with IP address in it, so you can have for example a root user allowed to connect from localhost (127.0.0.1) but not from other IP addresses. With a container, you never access to the database from 127.0.0.1, it could explain the problem.

In short it works if I do like this:

CREATE USER 'jerry'@'%' IDENTIFIED BY 'jerrypassword';
SELECT host, user FROM mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | jerry         |
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
Solution

  • You need to bind your container port to your host.

    Instead of doing 

    docker run --name=mysql1 -d mysql/mysql-server:tag

    Do

    docker run --name=mysql1 -p 3306:3306 -d mysql/mysql-server:tag

    This binds port 3306 of the container to port 3306 on 127.0.0.1 of the host machine.

    You will then be able to connect via localhost:3306

    Reference : Docker Run -- Publish or expose port (-p, --expose)