others-How to solve Access denied for user 'root'@'localhost' (using password: YES) or Host '127.0.0.1' is not allowed to connect to this MySQL server when trying to access mysql with docker
1. Purpose
In this post, I would demo how to solve the following error when trying to access mysql in docker.
root@launch-advisor-20191120:~/mysql# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
fcbe5c899b25 mysql/mysql-server:latest "/entrypoint.sh mysq…" 18 seconds ago Up 14 seconds (health: starting) 127.0.0.1:3306->3306/tcp, 33060-33061/tcp mysql
root@launch-advisor-20191120:~/mysql# docker exec -it mysql bash
bash-4.4# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
bash-4.4# mysql -uroot -p -h127.0.0.1
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
bash-4.4# exit
2. What happened?
I am trying to deploy a mysql instance on my remote server, just as the following diagram shows:
I started a docker on my server , the command is as follows:
docker run --name mysql \
--restart=always \
-v mysql-vol:/var/lib/mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=qweasd \
-e MYSQL_DATABASE=testdb \
-e MYSQL_USER=testuser \
-e MYSQL_PASSWORD=123456 \
-d mysql/mysql-server:latest
I can explain the above options :
-
-v: –volume ,bind a volume, from host to container
-v, --volume=[host-src:]container-dest[:<options>]: Bind mount a volume. The comma-delimited `options` are [rw|ro], [z|Z], [[r]shared|[r]slave|[r]private], and [nocopy]. The 'host-src' is an absolute path or a name value. If neither 'rw' or 'ro' is specified then the volume is mounted in read-write mode. The `nocopy` mode is used to disable automatically copying the requested volume path in the container to the volume storage location. For named volumes, `copy` is the default mode. Copy modes are not supported for bind-mounted volumes. --volumes-from="": Mount all volumes from the given container(s) -
-p means to bind a host port and map it to container port, from host to container.
-P : Publish all exposed ports to the host interfaces -p=[] : Publish a container's port or a range of ports to the host format: ip:hostPort:containerPort | ip::containerPort | hostPort:containerPort | containerPort Both hostPort and containerPort can be specified as a range of ports. When specifying ranges for both, the number of container ports in the range must match the number of host ports in the range, for example: -p 1234-1236:1234-1236/tcp When specifying a range for hostPort only, the containerPort must not be a range. In this case the container port is published somewhere within the specified hostPort range. (e.g., `-p 1234-1236:1234/tcp`) (use 'docker port' to see the actual mapping) -
-d means detach
To start a container in detached mode, you use -d=true or just -d option.
By design, containers started in detached mode exit when the root process
used to run the container exits, unless you also specify the --rm option.
If you use -d with --rm, the container is removed when it exits or when
the daemon exits, whichever happens first.
But when I go into the container and try to access mysql , I got this error:
root@launch-advisor-20191120:~/mysql# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
fcbe5c899b25 mysql/mysql-server:latest "/entrypoint.sh mysq…" 18 seconds ago Up 14 seconds (health: starting) 127.0.0.1:3306->3306/tcp, 33060-33061/tcp mysql
root@launch-advisor-20191120:~/mysql# docker exec -it mysql bash
bash-4.4# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
bash-4.4# mysql -uroot -p -h127.0.0.1
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
bash-4.4# exit
3. How to solve it?
I tried to simplify the docker run command as follows:
docker run --name mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=qweabc \
-e MYSQL_DATABASE=testdb \
-e MYSQL_USER=testuser \
-e MYSQL_PASSWORD=123456 \
-d mysql:5.7
I removed the -v and --restart options from the command line. Because there is no such directory mysql-vol in the directory where I run the command. And in the same time, I changed the docker image to mysql:5.7, which is a stable version.
Now test again, I got this:
root@launch-advisor-20191120:~/mysql# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
75855f9cdbb4 mysql:5.7 "docker-entrypoint.s…" 5 seconds ago Up 3 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
root@launch-advisor-20191120:~/mysql# docker exec -it 75 bash
root@75855f9cdbb4:/#
root@75855f9cdbb4:/#
root@75855f9cdbb4:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
You can see that I succeeded to log on mysql in docker.
4. The reason
I think the key reason for the error are as follows:
- The ‘-v’ option bind a local directory to docker volume, but it does not exist in local path.
- The docker image file for mysql is very important, we should choose a version that is stable.
5. Summary
In this post, I demonstrated how to solve the ‘Access denied for user ‘root’@’localhost’’ or ‘Host ‘127.0.0.1’ is not allowed to connect to this MySQL server’ when using docker to install mysql. The key point is to find the right options to start your mysql container.