How to connect MySQL with Docker using Navicat or MySQL Workbench?

Question

I am using the MySQL Docker images, and I can connect to this Docker with mysqli_connect('mysql', 'docker', 'docker'); in php

Here is docker ps:

CONTAINER ID        IMAGE                   COMMAND                  CREATED             STATUS              PORTS                                          NAMES
c271f8e68378        afraidjpg/nginx:nginx   "nginx -g 'daemon of…"   2 hours ago         Up 2 hours          0.0.0.0:80->80/tcp, 0.0.0.0:10086->10086/tcp   nginx
92d7c923f777        afraidjpg/php:php56     "docker-php-entrypoi…"   2 hours ago         Up 2 hours          0.0.0.0:9000->9000/tcp                         php
68b818bd68c4        afraidjpg/mysql:mysql   "docker-entrypoint.s…"   2 hours ago         Up 2 hours          0.0.0.0:3306->3306/tcp, 33060/tcp              mysql

I use --link mysql option when starting PHP Docker.

IP of MySQL Docker is 172.17.0.2.

My mac's IP is 192.168.1.50

here is user in mysql:

mysql> select User, Host from mysql.user;
+--------+-----------+
| User   | Host      |
+--------+-----------+
| docker | %         |
| root   | localhost |
+--------+-----------+
2 rows in set (0.00 sec)

then I tried to connect mysql with navicat and mysql-workbench

host:192.168.1.50, user:docker, pass:docker Access denied for user 'docker'@'%%' (using password: YES)

host:127.0.0.1, user:docker, pass:docker Access denied for user 'docker'@'%%' (using password: YES)

host:172.17.0.2, user:docker, pass:docker Can't connect to MySQL server on '172.17.0.2

all get the error with Access denied for user 'docker'@'%%' (using password: YES)

if l tried to change user from docker to root, error become Access denied for user 'docker'@'172.17.0.1' (using password: YES)

I found some article before, and followed their step, but always failed. I don't where I was wrong

here are some screenshot

update I cannot ping from local to docker, and there is no ping command in docker so I cannot do the test to ping from docker to local

zk ~/Documents/PHP/www/docker $ ping 172.17.0.2
PING 172.17.0.2 (172.17.0.2): 56 data bytes
Request timeout for icmp_seq 0
Request timeout for icmp_seq 1
Request timeout for icmp_seq 2
Request timeout for icmp_seq 3

more update

I can connect to mysql with command in terminal with mysql -udocker -p --portocol=tcp

more details

error message is Access denied for user 'docker'@'%%' (using password: YES), It said @'%%', I know what the '%' is, but what's mean of '%%'?

Answer 1

Do try host as mysql instead of localhost from the connected container. If you are on windows try 192.168.99.100 as host.

If still having issue try retest with mysql-compose.yml

version: "3.1"
services:

  mysql:
    image: mysql:5.7.22
    command: "--innodb_use_native_aio=0"
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    env_file:
      - .env
    ports:
      - "3306:3306"
    volumes:
      - /var/database/docker/common:/var/lib/mysql
    networks:
      - mysqlnet
      - mysqlnetfpm

networks:
  mysqlnet:
    driver: bridge
  mysqlnetfpm:
    driver: bridge

Note: Before running new mysql container don't forgot to delete the older mysqldata files.

Now you can use the username as root, password as root and for the internal containers(attached containers) use the host as mysql.

If you are try to connect from outside the docker do try with host as localhost or 127.0.0.1 or your public docker ip.