MySQL access denied while connecting to the docker container

Question

I set up a docker container with the following command

sudo docker run --name qa_mysql -e MYSQL_ROOT_PASSWORD=0000 -p3306 -d mysql:latest

while doing sudo docker ps -a i can see, that the container exists:

CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS                  PORTS                                                    NAMES
bb79e1f63b0c   mysql:latest   "docker-entrypoint.s…"   53 seconds ago   Up 48 seconds           33060/tcp, 0.0.0.0:49153->3306/tcp, :::49153->3306/tcp   qa_mysql

But i cant connect to the container via mysql using this command

mysql -h 127.0.0.1 -P3306 -u root -p

I keep getting this error

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I used all variations of passwords (like password, that i wrote when set up container, real sudo password etc.)

Also, I tried to change password for mysql root@locathost with these commands:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables
sudo service mysql start
sudo mysql -u root
use mysql;
show tables;
describe user;
update user set authentication_string=password('0000') where user='root';
FLUSH PRIVILEGES;

But nothing helped.

ALSO: when i tried

update user set authentication_string=password('0000') where user='root';

the second time i got:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('0000') where user='root'' at line 1

My OS: Ubuntu 20.04.4 LTS.
MySQL: mysql Ver 8.0.29-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
Docker: Docker version 20.10.14, build a224086

Tolis Gerodimos · Accepted Answer · 2022-05-04 20:30:25Z

1

The issue is with the published ports, specifically -p3306

Use instead:

sudo docker run --name qa_mysql -e MYSQL_ROOT_PASSWORD=0000 -p 3306:3306 -d mysql:latest

This way you will publish the container port 3306 to host's port 3306. With your previous way your container port was bounded to a random host port.

if you run docker ps, after the status section you can see the port assigned

Example with random port assignment

In this scenario you would run

mysql -h 127.0.0.1 -P49156 -u root -p

edited May 4, 2022 at 20:30

answered May 4, 2022 at 19:58

Tolis Gerodimos

4,5832 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

neon_genesis_Malevich Over a year ago

now i get this error: docker: Error response from daemon: driver failed programming external connectivity on endpoint qa_mysql (37c26ac258ce4d80ca5d7a162b144b9063424800046c79d3395fbf6465fa31b9): Error starting userland proxy: listen tcp4 0.0.0.0:3306: bind: address already in use.

Tolis Gerodimos Over a year ago

This error indicates that another service is listening to this port. Run sudo netstat -tupln | grep 3306. To find the service and stop it

Tolis Gerodimos Over a year ago

@neon_genesis_Malevich updated answer with an example to tackle your issue with the original docker run command

neon_genesis_Malevich Over a year ago

Okay, i got it. All successed, but i get this error while entering a password (ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 ). I checked the port, i connect on the right one, no mistake

neon_genesis_Malevich Over a year ago

and i just added mysqld: ALL line into file /etc/hosts.deny to check if host is listened. Now everything works

Collectives™ on Stack Overflow

MySQL access denied while connecting to the docker container

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related