CODEWITHSUNDEEP
mysqljenkinsdockerliquibasedocker-container
Oksana Vikul
Oksana Vikul

Reputation: 61

Can’t connect to mysql docker image

I have issue using mysql/mysql-server docker image. I want to connect to mysql server, create db, user with all PRIVILEGES to have ability to create new schema and run liquibase in future.

I run container like this:

sudo docker run --name db -e MYSQL_ROOT_PASSWORD=qwe123 -e MYSQL_DATABASE=test \
  -d -p 3306:3306 mysql/mysql-server:5.6 --lower-case-table-names=1

when I do docker ps I see that it's running but when I'm trying to run liquibase (using root/qwe123) it failed with:

[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project db: null, message from server: "Host '172.17.0.1' is not allowed to connect to this MySQL server" -> [Help 1]

tried with user:

sudo docker run --name db -e MYSQL_ROOT_PASSWORD=qwe123 -e MYSQL_DATABASE=test \
  -e MYSQL_USER=admin -e MYSQL_PASSWORD=qwe123 -d -p 3306:3306 \
  mysql/mysql-server:5.6 --lower_case_table_names=1

with admin user have error creating new schema, it looks like admin has only access to test database:

[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project db: Access denied for user 'admin'@'%' to database 'test2' -> [Help 1]

with root the following error:

[ERROR] The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

Also tried to set privileges for admin in init command:

docker run --name db -e MYSQL_ROOT_PASSWORD=qwe123 -e MYSQL_DATABASE=test -d \
  -p 3306:3306 mysql/mysql-server:5.6 --lower_case_table_names=1 \
 '--init-connect=CREATE USER `admin`@`localhost` IDENTIFIED BY "qwe123"; GRANT ALL PRIVILEGES ON *.* TO `admin`@`localhost` WITH GRANT OPTION; CREATE USER `admin`@`%` IDENTIFIED BY "qwe123";GRANT ALL PRIVILEGES ON *.* TO `admin`@`%` WITH GRANT OPTION;FLUSH PRIVILEGES;'

In this case for root user have error:

[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project db: null, message from server: "Host '172.17.0.1' is not allowed to connect to this MySQL server" -> [Help 1]

for admin - the following:

12:13:57 [ERROR] The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

Also tried to use localhost and 127.0.0.1 as host to DB, always have error with connection. Any ideas?

Upvotes: 6

Views: 19357

Answers (2)

Mohsin Ejaz
Mohsin Ejaz

Reputation: 414

Follow the following Steps to play with the docker container with yours external client ie dbeaver , mysql workbench etc

1- Run the following docker mysql command

open your terminal i.e bash | zsh | cmd

Note :: mysql/mysql-server is my image of mysql ... yours may be different otherwise it will pull new image if not present

docker run -d -p 3306:3306 --name mysql-test -e MYSQL_ROOT_PASSWORD=1234 mysql/mysql-server

2-open mysql CLI

open your terminal run below one after another to run cli

mysql-test is container name;

docker exec -it $(docker ps -q -f name='mysql-test') bash -l;

mysql -uroot -1234;

cli will look like this " mysql > .... " in terminal

3- Create New user and grant privileges

Note :: change username and password with your own user in below commands run on your terminal

SE mysql;
CREATE USER 'new_usernmame'@'localhost' IDENTIFIED BY 'new_password';
GRANT ALL ON *.* TO 'new_usernmame'@'localhost';
FLUSH PRIVILEGES;

RESULT

🌈 Congrats you can now make jdbc connection with newly created user

FOR MORE DYNAMIC SOLUTION

SEE https://mohsin-ejaz.gitbook.io/docker-notes/docker-mysql/dockerize-02

Upvotes: 1

podarok
podarok

Reputation: 535

You should allow the user within MySQL to connect from the IP of your host machine. Do not forget to flush privileges after changing user in MySQL

GRANT CREATE USER ON *.* TO 'root'@'%';

And restart the mysql instance

Also there is a chance to have mysql bind to 127.0.0.1 via my.cnf. If it is so - change the bind to 0.0.0.0 to get it accessible via any IP

Upvotes: 3

Related Questions