How to connect mysql docker container to node.js server or workbench

Question

Iam beginner to docker and iam working on mysql and node.js I run mysql docker container as

docker run --name docker-mysql -e MYSQL_ROOT_PASSWORD=abc123 -d mysql:latest

and result of docker ps is showing mysql container is running and docker logs says

MySQL init process done. Ready for start up.

how to connect with this container in workbench or in my application

localhost:3306 or the port u mapped with host port. another option docker inspect db and find the container IP and contact with that IP — Adiii, Feb 23 '19 at 07:58
i find the container IP by "docker inspect container-name" it returns "IPAddress": "172.17.0.2" ... and many other properties.. i try to connect work bench with this ip and port 3306 but it didn't connect and stack for a minute and says failed to connected — Basit, Feb 23 '19 at 08:05
if i write localhost as host name it connect to my default instance the old one that i use with xampp — Basit, Feb 23 '19 at 08:07
make sure your port mapping is correct docker run -d --name consenter name -p 3306:3306 , in this case other application out side docker can see mysql — Pedram Ezzati, Feb 23 '19 at 08:19
now i make a separate dir for this container file as mentioned at https://docs.docker.com/samples/library/mysql/ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag and use docker exec -it mysql bash and create a new user and grant all previllages now i connect this with workbench and it is now connected and showing a test database , but... it now its now creating new schema as permission denied for use ''@localhost — Basit, Feb 23 '19 at 08:44

Mr.Turtle · Accepted Answer · 2019-02-23T10:17:49.587

5

Try this:

docker run -p 3306:3306 --name docker-mysql -e MYSQL_ROOT_PASSWORD=abc123 -d mysql:latest

This will bind the port 3306 on your local machine to the docker image. You should be able to connect to the database with localhost & port 3306 with username root and password abc123.

I just tested it and it works like a charm.

If you are struggling with the error:

failed to connect to localhost at 33016" details = Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

Update your MySQL-workbench.

If that doesn't work you will need to add a native password to the root user. Here is how:

Connect to your docker image via bash:

docker exec -it docker-mysql bash
Log into mysql as root

mysql --user=root --password
Enter the password for root (Default is 'root', but 'abc123' in this example)
Finally Run:

ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY 'abc123';

edited Feb 23 '19 at 10:17

answered Feb 23 '19 at 09:42

Mr.Turtle

2,950
6
28
46

when i use 3306:3306 docker: Error response from daemon: driver failed programming external connectivity on endpoint mysql (764873ef2b1633e3e20ae2ab91a4c45fbba5913ba3821c5d7795db3cc72ea44c): Error starting userland proxy: Bind for 0.0.0.0:3306 failed: port is already allocated. – Basit Feb 23 '19 at 09:46
if u try other ports like 33512:3306 no docker process start docker ps show nothing – Basit Feb 23 '19 at 09:47
it try this by localhost and 3306 it connects successfully as you share screenshot but its showing my old connection databases which i used with xampp – Basit Feb 23 '19 at 09:51
Ok. That means something else is running on that port. Do you have any other MySQL servers running on that port, maybe an local instance? Anyhow, you can try to run the docker on a different port like this: `docker run -p 3308:3306 --name docker-mysql -e MYSQL_ROOT_PASSWORD=abc123 -d mysql:latest` – Mr.Turtle Feb 23 '19 at 09:51
now i make a separate dir for this container file as mentioned at docs.docker.com/samples/library/mysql docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag – Basit Feb 23 '19 at 09:51
If you get your old stuff it means you are connecting to another mysql database. I would suggest: 1. Uninstall or at least stop the other mysql databse OR 1. Use a different port for the docker mysql – Mr.Turtle Feb 23 '19 at 09:55
now i type "docker run -p 33016:3306 --name mysql -e MYSQL_ROOT_PASSWORD=abc123 -d mysql:latest" and process started and showing in "docker ps" with containerID as well now i open workbench and try to connect with "localhost" , port:33016 user :root , password:abc123 fine.. now it says.. "failed to connect to localhost at 33016" details = Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found – Basit Feb 23 '19 at 09:59
That is a classic error :-) It means that your mysql workbench can't process the password. **Try updating your mysql workbench as it seems to have been a while.** Otherwise I would suggest: https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded – Mr.Turtle Feb 23 '19 at 10:03
should i use -v /my/own/datadir:/var/lib/mysql or not – Basit Feb 23 '19 at 10:14
You don't need that. Your only issue now is the mysql version is different than your mysql workbench version :) – Mr.Turtle Feb 23 '19 at 10:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188921/discussion-between-basit-raza-and-mr-turtle). – Basit Feb 23 '19 at 10:17
You were right @Mr Turtle i change mysql version to 5.7 which my workbench version support and now it connect to container port. thanks for your time – Basit Feb 23 '19 at 12:27

score 1 · Answer 2 · answered Feb 23 '19 at 09:17

1

You need to expose the port

Use -p, or -P

-p is bound to a custom port, -P will randomly assign a port to you.

:latest does not need to add, docker will help you add.

The final command should look like this:

docker run -dit -P --name docker-mysql -e MYSQL_ROOT_PASSWORD=abc123 mysql:latest

Then use xx to see the exposed ports:

docker port docker-mysql

Check which port of the machine is mapped to port 3306 of the container, My result is:

33060/tcp -> 0.0.0.0:32818
3306/tcp -> 0.0.0.0:32819

Now you can connect to this port via software or code.

answered Feb 23 '19 at 09:17

Black-Hole

893
8
20

for what -dit flag is used for – Basit Feb 23 '19 at 09:31
docker run -dit -P --name docker-mysql -e MYSQL_ROOT_PASSWORD=abc123 mysql:latest . doesn't start the docker process and nothing shows in docker ps – Basit Feb 23 '19 at 09:34
You try to use `docker ps -a` and `docker logs docker-mysql` – Black-Hole Feb 23 '19 at 11:20
I think this article can help you: https://hackernoon.com/nodejs-mysql-install-setup-tutorial-example-connection-insert-query-fd5c5032d3b0 – Black-Hole Feb 23 '19 at 11:27

score 0 · Answer 3 · answered Feb 23 '19 at 08:43

For development best way is to connect the container to "host" network

docker run --name docker-mysql --network host -e MYSQL_ROOT_PASSWORD=abc123 -d mysql:latest

When you will move to staging/prod environment consider to use some orchestration solution.

score 0 · Answer 4 · answered Oct 01 '20 at 05:22

Run docker inspect container_id its will show container port but will not show host port bind. By Default running without -p flag it will assign port to container but will not expose host port , so to achieve this you shout try -p flag, -p 3306:3306 in docker run command.

How to connect mysql docker container to node.js server or workbench

4 Answers4