MySQL access denied while connecting to the docker container-CodePudding

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

CodePudding user response：

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