How to connect mysql workbench to running mysql inside docker?

Pawan Kumar picture Pawan Kumar · Nov 20, 2015 · Viewed 71.3k times · Source

I am using mysql server inside docker container and able to access inside docker. How to create connection in mysql workbench running on my local(Host Machine).

Answer

Andrey Lebedenko picture Andrey Lebedenko · Nov 22, 2018

By default after deployment MySQL has following connection restrictions:

mysql> select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

Apparently, for the security purposes you will not be able to connect to it outside of the docker image. If you need to change that to allow root to connect from any host (say, for development purposes), do:

  1. Start your mysql image with all port mappings required:

    docker run -p 3306:3306 --name=mysql57 -d mysql/mysql-server:5.7

or, if the complete port mapping is required:

docker run -p 3306:3306 -p 33060:33060 --name=mysql57 -d mysql/mysql-server:5.7
  1. If this is the fresh installation - grab the default password:

    docker logs mysql57 2>&1 | grep GENERATED

  2. Connect using mysql client directly to the mysqld in docker:

    docker exec -it mysql57 mysql -uroot -p

  3. If this is the fresh installation you will be asked to change the password using ALTER USER command. Do it.

  4. Run SQL:

    update mysql.user set host = '%' where user='root';

  5. Quit the mysql client.

  6. Restart the container:

    docker restart mysql57

Now you will be able to connect from MySQL Workbench to

host: `0.0.0.0` 
port: `3306`

After all the changes the query will show:

select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+