MYSQL_ROOT_PASSWORD 已设置，但“访问被拒绝用户 'root'@'localhost'(使用密码:YES)"；在码头集装箱 [英] MYSQL_ROOT_PASSWORD is set but getting "Access denied for user 'root'@'localhost' (using password: YES)" in docker container

问题描述

我有一个 docker-compose 文件和一个 Dockerfile.MySQL 已正确安装.我已经设置了MYSQL_ROOT_PASSWORD.但是当尝试访问 mysql db 时，出现错误 - 访问被拒绝.我已经阅读了本网站的其他主题，但没有得到太多帮助.:(

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

这是我的 docker-compose 文件:

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"

和 Dockerfile:

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

这里是构建日志:

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

查看容器:

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

这是这个容器的日志:

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)

现在进入容器:

docker exec -it 49419cb9980a bash

root@49419cb9980a:/#

我已经检查过 MYSQL_ROOT_PASSWORD 是否设置正确(在容器中):

I have checked if MYSQL_ROOT_PASSWORD is set correctly(in the container):

root@49419cb9980a:/# echo $MYSQL_ROOT_PASSWORD
123

然后尝试登录mysql:

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)

我的问题是，如何解决这个问题?为什么我不能访问mysql?我也试过没有密码选项.这给了我这个错误:

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)

这是我的项目结构:

tree
.
├── db
│   ├── Dockerfile
│   └── database100.sql
└── docker-compose.yml

1 directory, 3 files

推荐答案

假设您已经显示了整个启动日志，看来您已经针对预先存在的 db_data 卷启动了 mysql 容器包含一个 mysql 数据库文件系统.

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.

如果你想初始化你的实例，你必须从头开始.在使用像您这样的命名卷时，使用 docker compose 很容易.警告:这将永久删除您的 db_data 卷中的内容，清除您在那里拥有的任何先前数据库.如果您需要保留内容，请先创建一个备份.

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

如果您转换为绑定挂载，则必须自己删除所有内容(即 rm -rf/path/to/bind/mount/*)

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

注意:许多其他官方 db docker 镜像(postgres、mongo ....)的工作方式类似.

Note: many other official db docker images (postgres, mongo....) work a similar way.

这篇关于MYSQL_ROOT_PASSWORD 已设置，但“访问被拒绝用户 'root'@'localhost'(使用密码:YES)"；在码头集装箱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！