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:

image-20211030210011891

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.