How to access mysql running in docker container from host machine?

Question

I have read around and none of the solution seem to work for me so I would like to see if I am doing anything wrong here. I am trying to run mysql docker container and trying to access mysql from host machine. I tried as a root or new user or latest version of mysql with no luck. What am I doing wrong here? Any help would be appreciated. Thanks in advance.

I first ran following commend to start up a container:

docker run --name mysql57 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 -d mysql/mysql-server:5.7
docker exec -it mysql57 bash
mysql -h localhost -u root -p

Then

CREATE USER 'demo_java' IDENTIFIED BY 'java';
grant all on *.* to 'demo_java'@'%' identified by '1234';
FLUSH PRIVILEGES;
CREATE DATABASE hello_java CHARACTER SET utf8 COLLATE utf8_general_ci;

When I attempt to connect (note I have a main method with code below in host OSX):

Class.forName("com.mysql.cj.jdbc.Driver")
DriverManager.getConnection("jdbc:mysql://localhost:3306/hello_java", "demo_java", "java")

I get error below (note I tried as a root with root password as well):

Exception in thread "main" java.sql.SQLException: Access denied for user 'demo_java'@'172.17.0.1' (using password: YES)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

Eulodos · Accepted Answer · 2020-11-20T11:30:06.827

It's because you've changed your username password. First you set it during the creation, as:

CREATE USER 'demo_java' IDENTIFIED BY 'java';

But then you override it:

grant all on *.* to 'demo_java'@'%' identified by '1234';

Please, see this SO entry for more details.

Simply remove password change from your scripts. You would have something like this:

CREATE USER 'demo_java' IDENTIFIED BY 'java';
grant all on *.* to 'demo_java'@'%';
FLUSH PRIVILEGES;
CREATE DATABASE hello_java CHARACTER SET utf8 COLLATE utf8_general_ci;

Then

DriverManager.getConnection("jdbc:mysql://localhost:3306/hello_java", "demo_java", "java");

should work fine.

Regarding connecting as the root, please see another SO entry, after starting up new container, there are some connection restrictions by default in MySQL. If you want to be able to connect as the root, you'll have to change its priviliges like this:

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

then you'll have to restart your MySQL container and you should be able to connect as the root as well.

How to access mysql running in docker container from host machine?

1 Answers1