CODEWITHSUNDEEP
mysqldockerdocker-composedockerfile
Mukit09
Mukit09

Reputation: 3409

MYSQL_ROOT_PASSWORD is set but getting "Access denied for user 'root'@'localhost' (using password: YES)" in docker container

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

Upvotes: 121

Views: 198508

Answers (12)

veritas
veritas

Reputation: 432

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

Upvotes: -2

PAX
PAX

Reputation: 1156

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)

Upvotes: 0

Coder_Craft
Coder_Craft

Reputation: 37

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';

Upvotes: -1

illummina
illummina

Reputation: 21

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

Upvotes: 1

TüT
TüT

Reputation: 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.

Upvotes: -1

Zeitounator
Zeitounator

Reputation: 44828

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

Upvotes: 235

Rob Dirnens
Rob Dirnens

Reputation: 109

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.

Upvotes: 0

Ani
Ani

Reputation: 351

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

Upvotes: -1

user3931921
user3931921

Reputation: 179

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.

Upvotes: 17

swaggerjacker
swaggerjacker

Reputation: 199

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

Upvotes: 8

Rafie Tarabay
Rafie Tarabay

Reputation: 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

Upvotes: -1

Nurullah Macun
Nurullah Macun

Reputation: 552

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

Upvotes: -2

Related Questions