121

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
Improve this question
0

12 Answers 12

Reset to default
240

The below description is specifically for MySQL but many other official db docker images (postgres, mongodb....) work a similar way. Hence the symptom (i.e. access denied with configured credentials) and workaround (i.e. delete the data volume to start initialization from scratch) are the same.

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/*)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

17

I've tested with all of the possible solutions posted in this thread. However, after try and error, I identified for any reason complex passwords were not recognized.

I've changed

 mariadb: container_name: dev_db image: mariadb:10.5 restart: always environment: MARIADB_ROOT_PASSWORD: a8Gh@c8wi#gL^ MARIADB_DATABASE: wp_my_database MARIADB_USER: wp MARIADB_PASSWORD: a8Gh@c8wi#gL^

by

 mariadb: container_name: dev_db image: mariadb:10.5 restart: always environment: MARIADB_ROOT_PASSWORD: qwerty MARIADB_DATABASE: wp_my_database MARIADB_USER: wp MARIADB_PASSWORD: qwerty
  • docker compose version: '3.9'.
  • services:
    • nginx:1.20-alpine
    • php:7.2.34-fpm-alpine
    • mariadb:mariadb:10.5
    • phpmyadmin: phpmyadmin/phpmyadmin:latest.
Improve this answer

5 Comments

Unbelievable but true. Maybe it breaks somewhere in between .env, docker and mysql (5.7 & 8.0)
Similarly, I solved the same issue by replacing PASSWORD=$pecialcharacter$ with PASSWORD='$pecialcharacter$'. The presence of $ in the variable and the variable not being wrapped in single quotes was causing my issue. Hope this helps someone.
you just need to put your strings between quotes ". Actually # is the comment character in a compose file
Although the the content of your answer was true with some older version of mariadb (I remember testing it), 1) it is not true anymore (tested with version 10.5.22, using quotes or not around your password in the compose file) 2)in any case, this is not answering OP's question as this is absolutely not the problem he is/was facing as reported by the pasted logs.
Thank you, what an easy fix for what had cost me two hours already...
8

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
Improve this answer

1 Comment

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.
2

Changing host_port from 3306 to 3906 worked for me.

Example of conf with mysql and healthcheck:

mysql: image: 'mysql/mysql-server:8.0' ports: - '3906:3306' environment: MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}' MYSQL_ROOT_HOST: "%" MYSQL_DATABASE: '${DB_DATABASE}' MYSQL_USER: '${DB_USERNAME}' MYSQL_PASSWORD: '${DB_PASSWORD}' MYSQL_ALLOW_EMPTY_PASSWORD: 1 volumes: - db:/var/lib/mysql networks: - my_network healthcheck: test: [ "CMD", "mysqladmin", "ping", "-p${DB_PASSWORD}" ] retries: 3 timeout: 5s
Improve this answer

Comments

1

For everybody who runs that container in a Kubernetes environment and struggles with the same error, I'd like to provide one additional possible pitfall.

Preconditions:

  • Database volume is already deleted (in order to trigger initialization of the database on startup)
  • Your Pod runs a MySQL container
  • You stored the desired root user password in a Secret resource
  • You reference that password for the environment variable MYSQL_ROOT_PASSWORD:
 containers: - name: mysql-server image: mysql/mysql-server:8.0.32-1.2.11-server env: - name: MYSQL_ROOT_PASSWORD valueFrom: secretKeyRef: name: app-secret key: key.to.the.password

The values of the entries in Secret resources are Base64-encoded. Make sure that they don't have leading/trailing line breaks.

In my case, the password accidentally ended with a line break, which prevented MySQL from setting the root user password correctly. It terminated during initialization with:

Access denied for user 'root'@'localhost' (using password: YES)
Improve this answer

Comments

0

None of the suggested solutions were appropriate in my case, but I solved the same issue by replacing
PASSWORD=$pecialcharacter$
with
PASSWORD='$pecialcharacter$'
in my .env file. The presence of $ in the variable and the variable not being wrapped in single quotes was causing my issue. Hope this helps someone.

Improve this answer

1 Comment

special char was the issue, the reference to single quote was my fix. Initially i was using double quote but did not think it would be an issue. Thanks for the pointer
-1

I have the same problem, and after delete the volume the problem still there, then I find this solution:

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h password 'new-password'

Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation

Improve this answer

1 Comment

If 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)
-1

maybe a little too late. I had this same issue yesterday and today.
To solve this I had to remove the volume (in this example db_data)

$docker volume ls
$docker volume rm db_data

another way to fix it was running the container pointing to new volume (e.g. -v db_data2:/var/lib/mysql)

i thought that passwords were being stored on the volumes and hence its complaining. However, the volume was empty as shown by $ls -al db_data

don't understand why empty volume cannot be mounted to a new run

Improve this answer

Comments

-1

I had the same problem with mariadb. my solution was to upgrade the mariadb image from 10 to 10.8-jammy, of course after cleaning the docker environment images and running containers. I deleted everything and did a clean pull.

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
-1

If you are able to login to your Mariadb shell inside docker, then simply just do this

root/# /usr/bin/mariadb

Then you can change your password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
-2

I found a better way, no need to delete all volume etc.

1 - change your docker compose file to keep mysql container alive like this; under mysql service;

command: tail -F anything

after this operation, you can undo this, don't worry with the previous value.

2 - then, stop mysql container and run with

docker-compose up --build --force-recreate YOUR_SERVICE_NAME

this doesn't delete your volume.

3 - enter to mysql container;

docker exec -it YOUR_SERVICE_NAME bash

finally follow any mysql reset password guide like this;

https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password

Improve this answer

2 Comments

This 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.
my 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.
-3

For those that don't have the above issue, my issue was that I was setting the container port as 3307 but had forgotten to add the expose tag.

 ports: - "3307:3307" expose: - 3307

Once I did that my Spring Boot app worked whether the url had 127.0.0.1 or localhost for host as below

datasource: url: jdbc:mysql://localhost:3307/employeemanagementdb
Improve this answer

3 Comments

This does not answer the question as it has nothing to do with the symptoms op is reporting.
symptoms op is reporting: access denied on docker with mysql. My answer: one of the reasons you can get access denied on docker with mysql. @Zeitounator
The Compose expose: directive does almost nothing, and it's almost always safe to delete it if it's present.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.