MYSQL_ROOT_PASSWORD is set but getting "Access denied for user 'root'@'localhost' (using password: YES)" in docker container
Solution 1
Taking for granted you have shown your entire start log, it appears you started your mysql container against a pre-existing db_data
volume already containing a mysql database filesystem.
In this case, absolutely nothing will be initialized on container start and environment variables are useless. Quoting the official image documentation in the "Environment Variables" section:
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.
If you want your instance to be initialized, you have to start from scratch. It is quite easy to do with docker compose when using a named volume like in your case. Warning: this will permanently delete the contents in your db_data
volume, wiping out any previous database you had there. Create a backup first if you need to keep the contents.
docker-compose down -v
docker-compose up -d
If you ever convert to a bind mount, you will have to delete all it's content yourself (i.e. rm -rf /path/to/bind/mount/*
)
Note: many other official db docker images (postgres, mongo....) work a similar way.
Solution 2
If you are on a development server you could simply remove all unused local volumes. Unused local volumes are those which are not referenced by any containers:
docker volume prune
Mukit09
I love coding. Love the Java language very much. Have a good skill in C language as well. Currently using Spring Boot as a backend framework. Have a good knowledge of Docker. Love to share my knowledge and to help others. Completed BSc engineering from Chittagong University of Engineering & Technology.
Updated on July 22, 2022Comments
-
Mukit09 almost 2 years
I have a docker-compose file and a Dockerfile. MySQL is installed properly. I have set MYSQL_ROOT_PASSWORD. But when trying to access mysql db, getting the error - Access denied. I have read the other threads of this site, but couldn't get that much help. :(
Here is my docker-compose file:
version: '3' volumes: db_data: {} services: db: build: context: . dockerfile: ./db/Dockerfile args: - database=iTel - password=123 image: db_image volumes: - db_data:/var/lib/mysql ports: - "3306:3306"
and Dockerfile:
FROM mysql:5.7.15 ARG database ARG password RUN echo ${database} RUN echo ${password} MAINTAINER me ENV MYSQL_DATABASE=${database} \ MYSQL_ROOT_PASSWORD=${password} ADD ./db/database100.sql /docker-entrypoint-initdb.d EXPOSE 3306
Here are logs for build:
docker-compose up -d Building db Step 1/9 : FROM mysql:5.7.15 5.7.15: Pulling from library/mysql 6a5a5368e0c2: Pull complete 0689904e86f0: Pull complete 486087a8071d: Pull complete 3eff318f6785: Pull complete 3df41d8a4cfb: Pull complete 1b4a00485931: Pull complete 0bab0b2c2630: Pull complete 264fc9ce512d: Pull complete e0181dcdbbe8: Pull complete 53b082fa47c7: Pull complete e5cf4fe00c4c: Pull complete Digest: sha256:966490bda4576655dc940923c4883db68cca0b3607920be5efff7514e0379aa7 Status: Downloaded newer image for mysql:5.7.15 ---> 18f13d72f7f0 Step 2/9 : ARG database ---> Running in 62819f9fc38b Removing intermediate container 62819f9fc38b ---> 863fd3212046 Step 3/9 : ARG password ---> Running in ea9d36c1a954 Removing intermediate container ea9d36c1a954 ---> 056100b1d5eb Step 4/9 : RUN echo ${database} ---> Running in 941bd2f4fc58 iTel Removing intermediate container 941bd2f4fc58 ---> 7b2b48e7bd8c Step 5/9 : RUN echo ${password} ---> Running in 9cb80396bb62 123 Removing intermediate container 9cb80396bb62 ---> 155d184c78ba Step 6/9 : MAINTAINER me ---> Running in 8e3b3b53ce7b Removing intermediate container 8e3b3b53ce7b ---> 9a7617a24800 Step 7/9 : ENV MYSQL_DATABASE=${database} MYSQL_ROOT_PASSWORD=${password} ---> Running in e483e65caf55 Removing intermediate container e483e65caf55 ---> acf8ac829607 Step 8/9 : ADD ./db/database100.sql /docker-entrypoint-initdb.d ---> 42d992439f98 Step 9/9 : EXPOSE 3306 ---> Running in 4e138502c6f9 Removing intermediate container 4e138502c6f9 ---> a0818deda593 Successfully built a0818deda593 Successfully tagged db_image:latest WARNING: Image for service db was built because it did not already exist. To rebuild this image you must use `docker-compose build` or `docker-compose up --build`. Creating reve_db_1 ... done
to see the containers:
docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 49419cb9980a db_image "docker-entrypoint.s…" 10 seconds ago Up 8 seconds 0.0.0.0:3306->3306/tcp reve_db_1
That is log for this container:
docker logs 49419cb9980a 2020-01-21T07:53:13.050129Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-01-21T07:53:13.051767Z 0 [Note] mysqld (mysqld 5.7.15) starting as process 1 ... 2020-01-21T07:53:13.054945Z 0 [Note] InnoDB: PUNCH HOLE support available 2020-01-21T07:53:13.055053Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-01-21T07:53:13.055103Z 0 [Note] InnoDB: Uses event mutexes 2020-01-21T07:53:13.055179Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2020-01-21T07:53:13.055226Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2020-01-21T07:53:13.055268Z 0 [Note] InnoDB: Using Linux native AIO 2020-01-21T07:53:13.055608Z 0 [Note] InnoDB: Number of pools: 1 2020-01-21T07:53:13.055791Z 0 [Note] InnoDB: Using CPU crc32 instructions 2020-01-21T07:53:13.061164Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2020-01-21T07:53:13.072998Z 0 [Note] InnoDB: Completed initialization of buffer pool 2020-01-21T07:53:13.075325Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-01-21T07:53:13.101337Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2020-01-21T07:53:13.142134Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-01-21T07:53:13.142356Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-01-21T07:53:13.184613Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-01-21T07:53:13.185628Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2020-01-21T07:53:13.185733Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2020-01-21T07:53:13.186108Z 0 [Note] InnoDB: Waiting for purge to start 2020-01-21T07:53:13.236391Z 0 [Note] InnoDB: 5.7.15 started; log sequence number 12146163 2020-01-21T07:53:13.236828Z 0 [Note] Plugin 'FEDERATED' is disabled. 2020-01-21T07:53:13.237186Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-01-21T07:53:13.252074Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key 2020-01-21T07:53:13.252900Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2020-01-21T07:53:13.253023Z 0 [Note] IPv6 is available. 2020-01-21T07:53:13.253076Z 0 [Note] - '::' resolves to '::'; 2020-01-21T07:53:13.253184Z 0 [Note] Server socket created on IP: '::'. 2020-01-21T07:53:13.269950Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode. 2020-01-21T07:53:13.270581Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode. 2020-01-21T07:53:13.277379Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200121 7:53:13 2020-01-21T07:53:13.295467Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode. 2020-01-21T07:53:13.367019Z 0 [Note] Event Scheduler: Loaded 0 events 2020-01-21T07:53:13.368851Z 0 [Note] mysqld: ready for connections. Version: '5.7.15' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
Now entered in the container:
docker exec -it 49419cb9980a bash root@49419cb9980a:/#
I have checked if MYSQL_ROOT_PASSWORD is set correctly(in the container):
root@49419cb9980a:/# echo $MYSQL_ROOT_PASSWORD 123
Then tried to log into mysql:
root@49419cb9980a:/# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
My question is, how to solve this problem? Why can't I access mysql? I tried with no password option too. That gave me this error:
mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
This is my project structure:
tree . ├── db │ ├── Dockerfile │ └── database100.sql └── docker-compose.yml 1 directory, 3 files
-
Mukit09 over 4 yearsseems you are right. That was the case, I guess. Won't that down command delete my entire database? I run the command, then after running up command, it is giving error that 'iTel database can't be created'.
-
Mukit09 over 4 yearsafter rm command running, now everything is okay. Thanks man.
-
Maciej Paprocki over 3 yearsThis was one of those issues that are stupid but can take you hours. I even knew that I had to completely recreate it including destroying data volume but seemed like everything I did still kept the initialised originally version. Thank you very much. Finally learned how to actually rebuild MySQL container!
-
Zeitounator about 3 yearsThis will not play the image initialization routines, e.g. if you changed the password AND added a default db AND pushed some init sql/sh scripts, none of that will be taken into account. The whole point here was to understand that all of this takes place only on a "fresh" install. Once you got that, your mysql running inside docker can be managed as any other mysql install. You don't even need to change the command in your compose file: simply connect to the running container. Or even better: launch a separate container manually and mount the db volume for your maintenance.
-
Nurullah Macun about 3 yearsmy purpose was simply set a root password to get root access. and it was not possible within docker since when I stopped the mysqld docker container was exiting also. I don't know the other concerns here but, I wanted to note this to show an another example to people who only needs to set a root password, nothing else.
-
David Arenburg about 3 yearsYou deserve a medal.
-
HonorableTones about 3 yearsGive this man an Amazon Gift Card, Thank you for the help. Even in 20201- this is the best answer for anyone having these issues.
-
Zeitounator almost 3 yearsIf the problem is still there when you start from a fresh container with a fresh volume, you don't have the same problem (and I even doubt you are using the official mysql docker image)
-
Zeitounator almost 3 years
docker-compose down -v
already removes the needed local volumes with the advantage of removing only the ones concerned by the services declared in the compose file. -
Macdonald over 2 yearsThanks for this. After several hours of trying other options, this works just fine .
-
jfunk over 2 yearsThank you - this was it.