Skip to content
BSWEN
Blog

How to resolve Access denied for user 'root'@'localhost' or Host '127.0.0.1' is not allowed to connect to this MySQL server when trying to access mysql with docker

Cowrie
Cowrie
Dev @ Bswen

Purpose

In this post, I will demonstrate how to resolve the following error when trying to access MySQL in Docker.

Terminal window
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:

Terminal window
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

  • -p means to bind a host port and map it to container port, from host to container.

  • -d means detach

But when I go into the container and try to access mysql , I got this error:

Terminal window
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:

Terminal window
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:

Terminal window
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.

Final Words + More Resources

My intention with this article was to help others who might be considering solving such a problem. So I hope that’s been the case here. If you still have any questions, don’t hesitate to ask me by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!

Tags: