In this post I describe a lesser known reason you may run into Access Denied for User ‘root’@’localhost’ (using password: YES) error when trying to login to MySQL. The setup is as follows: you are running MySQL inside a Docker container and using volumes. The startup script may look like:

docker container create \
	--name benny \
	--network $NETWORK \
	--volume nina:/var/lib/mysql \
	--log-opt max-file=3 \
 	--log-opt max-size=3m \
	--workdir /home \
	--env MYSQL_ROOT_PASSWORD=abracadabra \
	--env MYSQL_DATABASE=wordpress \
	--env MYSQL_USER=wpuser \
	--env MYSQL_PASSWORD=XJJm8f \
	--env TZ=UTC \
  mysql:8.0.24 \
  mysqld --default-authentication-plugin=mysql_native_password

Before running the command above, we first create the Docker volume:

docker volume create nina

Then we run the docker container create command. Verify that container starts and that you are able to login using the passwords above.

$ docker exec -it benny /bin/bash
root@95544c832e2b:/home# mysql -u root -p
Enter password: <enter password from above>

Now do following:

• Stop and remove the container (but DO NOT remove the Docker volume)
• Provision a new container but giving it new passwords (why would you do that? maybe you have a script that autogenerates a new password every time its run). E.g.:

docker container create \
	--name benny \
	--network $NETWORK \
	--volume nina:/var/lib/mysql \
	--log-opt max-file=3 \
 	--log-opt max-size=3m \
	--workdir /home \
	--env MYSQL_ROOT_PASSWORD=alibaba \
	--env MYSQL_DATABASE=wordpress \
	--env MYSQL_USER=wpuser \
	--env MYSQL_PASSWORD=t42rSC \
	--env TZ=UTC \
  mysql:8.0.24 \
  mysqld --default-authentication-plugin=mysql_native_password

Now if you try to login with the new passwords, it won’t work and you get the Access Denied error. So the new passwords (environment variables) DO NOT OVERRIDE the original passwords stored in the Docker volume and MySQL expects you to use those passwords. The lesson here is to store those passwords safely somewhere. If you lose those passwords, then you will lose access to MySQL.

From the man page:

Do note that none of the variables below will have any effect if you start the container with a data directory that already contains a database: any pre-existing database will always be left untouched on container startup.

https://hub.docker.com/_/mysql