How to connect to mysql docker container on a remote host from mysql workbench?

user_5855336 0
Viewed 2659

I've set up a docker container running a mysql instance on a remote computer I have. In the past this hasn't been an issue but for some reason I can't get it to work now. I am unsure what the issue might be. I am using docker compose and I can't seem to connect through mysql work bench on a different computer even those the container is running. Here are my details:

docker-compose.yaml

version: '3.7'

services:

  api:
    image: api
    restart: unless-stopped
    container_name: api
    build: ./node/
    ports:
      - 3008:3008
      
  mysql:
    image: mysql
    restart: unless-stopped
    container_name: mysql
    environment:
      MYSQL_DATABASE: pitapaldb
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: password
    build: ./database/
    ports:
      - 3306:3306
      
networks:
  default:
    external:
      name: my-net

database/Dockerfile

FROM mysql
COPY init.sql /docker-entrypoint-initdb.d

database/init.sql

CREATE DATABASE mydb;
USE mydb;
SET SQL_SAFE_UPDATES = 0;

ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY 'password';
flush privileges;


CREATE TABLE carts (
  id int PRIMARY KEY AUTO_INCREMENT,
  lat float,
  lon float,
  address varchar(255),
  status boolean,
  city_id int
);

container is definitely running:

CONTAINER ID        IMAGE                                    COMMAND                  CREATED             STATUS                          PORTS                                                              NAMES
784cf75183f4        mysql                                    "docker-entrypoint.s…"   2 minutes ago       Up About a minute               0.0.0.0:3306->3306/tcp, 33060/tcp                                  mysql

But when I try to connect via workbench I get 'unable to connect'. I've tried both username user and root with password password. The IP address I use definitely should work because I have other services operating from it with no issue:

enter image description here

docker-compose docker mysql-workbench containers mysql
1 Answers

@LoF10 Here is a quick list of things to check:

  1. Can you connect within the docker network on the machine running the MySQL docker? An easy way of testing this is by running a command such as this on your remote machine: docker run --rm -it --network my-net mysql:5.7 mysql -h mysql -uroot -ppassword If not, there may be a problem with your MySQL config, MySQL data, or the initialization of the container. These are what @JorgeCampos is suggesting you verify. Since you are pulling directly from MySQL's Docker Hub entry, the config should be set properly to allow remote connections. If good, proceed. FYI, you will know you've connected successfully if you see mysql> on the terminal. To exit: \q.
  2. Can you connect on exposed port on the localhost of the machine running the MySQL docker? An easy way of testing this is by running a command such as this on your remote machine: docker run --rm -it --network host mysql:5.7 mysql -h 127.0.0.1 -uroot -ppassword Make sure to use the IP used above and NOT localhost. This is b/c the MySQL client has special handling of the 'localhost' keyword by looking for mysqld locally. Using the 127.0.0.1 forces MySQL to connect via a proper socket connection. If you are not able to connect, then there is a problem with mapping the container's port to your host. If good, proceed.
  3. Assuming both machines are on the same network and the machine that has MySQL Workbench also has docker, can you connect using the IP of the machine running MySQL container e.g. 10.0.0.4? An easy way of testing this is by running a command such as this on your remote machine: docker run --rm -it mysql:5.7 mysql -h 10.0.0.4 -uroot -ppassword If not, you may want to verify if you can:
  • Ping the 10.0.0.4 machine
  • If there are any firewall rules that prevent its proper exposure to the network. This happens commonly with Windows' default Firewall...
  • If on AWS, there are a number of reasons why you might not be able to reach if it has been properly assigned a Public Port e.g. Security Groups, Route Tables, Internet Gateway, etc.

Once you are able to proceed from 3 above, then you should be able to connect using MySQL Workbench as you've described.

Hope those help. Any more detailed recommendation will require you sharing more about your local networking setup (OS, Physical/Virtual, how you are determining IP's, etc).

user_144575730
Related