docker compose mysql在远程服务器上的工作方式与在windows docker桌面上的不同-用户'root'@'172.18.0.2'的访问被拒绝

lsmepo6l  于 2023-02-18  发布在  Mysql
关注(0)|答案(1)|浏览(236)

bounty将在6天后过期。回答此问题可获得+50声望奖励。Lou K希望引起更多人关注此问题。

我有一个docker组合的图像集，其中一个基于mysql。堆栈是从https://github.com/docker/awesome-compose/tree/c2f8036fd353dae457eba7b9b436bf3a1c85d937/nginx-flask-mysql改编的
这在我的（“本地”）Windows 10 Docker桌面（docker v20.10.22）上运行良好，但当我尝试在我的（“远程”）centos7服务器（Docker版本23.0.0，构建e92 dd 87，托管在digitalocean的远程服务器）上运行时，我在尝试连接数据库时看到Access denied for user 'root'@'172.18.0.3' (using password: YES)。
我应该注意到，我已经看到了类似https://stackoverflow.com/a/59839180/799921的答案，并且我已经（反复）尝试删除远程服务器上的卷。
无论我使用root和MYSQL_ROOT_PASSWORD还是MYSQL_USER（用户）和MYSQL_PASSWORD，都会发生此故障。我已验证可以从mysql容器连接到数据库，但应用程序容器未安装mysql，因此无法从应用程序容器手动测试连接。
按照https://hub.docker.com/_/mysql，“从MySQL命令行客户端连接到MySQL”，我能够连接，所以这是应用程序的问题根据https://dev.mysql.com/doc/refman/8.0/en/problems-connecting.html，“如果你对Perl、PHP、Python或ODBC程序有访问问题，尝试使用mysql-u用户名数据库名称或mysql-u用户名-密码数据库名称连接到服务器。如果您能够使用mysql客户端进行连接，问题出在你的程序上”，但我不确定是什么。

$ docker run -it --network webmodules_backend-network --rm mysql mysql -hdb -uroot -p
Enter password:
Welcome to the MySQL monitor.
...

我查看了两个运行版本的mysql.user表，没有发现任何错误。
mysql.user在本地：

mysql> select * from mysql.user where user='root';
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher             | x509_issuer              | x509_subject               | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| %         | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | mysql_native_password | *52594A4243313A7447185F38CB9D3859DDC5FF77 | N                | 2023-02-14 21:20:23   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | mysql_native_password | *52594A4243313A7447185F38CB9D3859DDC5FF77 | N                | 2023-02-14 21:20:23   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
2 rows in set (0.00 sec)

mysql.user在远程：

mysql> select * from mysql.user where user='root';
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher             | x509_issuer              | x509_subject               | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| %         | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | mysql_native_password | *CFBC0A14FD2027A55F04E2A65FAF93B5D528800B | N                | 2023-02-14 21:22:12   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | mysql_native_password | *CFBC0A14FD2027A55F04E2A65FAF93B5D528800B | N                | 2023-02-14 21:22:12   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
2 rows in set (0.01 sec)

这是从本地的以下内容开始的：

docker compose -f docker-compose.yml -f docker-compose.dev.yml up -d

在远程（从本地计算机）：

docker --context webmodules compose -f docker-compose.yml -f docker-compose-prod.yml up -d

其中
docker-compose.yml:

version: "3.8"

services:
  db:
    # https://github.com/docker-library/mysql/issues/275#issuecomment-636831964
    image: mysql:8.0.32 # 32 gives access denied on centos7 server for both root and user
    # command: '--default-authentication-plugin=mysql_native_password'
    command: '--default-authentication-plugin=mysql_native_password --log_error_verbosity=3' # mysql
    # restart: always
    secrets:
      - db-password
      - user-password
    volumes:
      - db-data:/var/lib/mysql
    networks:
      - backend-network
    environment:
      - MYSQL_DATABASE=webmodules
      - MYSQL_ROOT_PASSWORD_FILE=/run/secrets/db-password
      - MYSQL_USER=user
      - MYSQL_PASSWORD_FILE=/run/secrets/user-password

  app:
    build: app
    restart: always
    secrets:
      - db-password
      - user-password
    networks:
      - backend-network
      - frontend-network

  web:
    build: web
    restart: always
    ports:
      - 8000:80
    networks:
      - frontend-network

volumes:
  db-data:

secrets:
  db-password:
    file: db/password.txt
  user-password:
    file: db/userpassword.txt

networks:
  backend-network:
  frontend-network:

docker-compose.dev.yml:

version: '3.8'

services:
  app:
    ports:
      - 5678:5678
    volumes:
      - ./app/src:/app
    environment:
      - FLASK_DEBUG=True

docker-compose-prod.yml:

version: '3.8'

secrets:
  db-password:
    file: /home/appuser/.docker/webmodules-db-password.txt
  user-password:
    file: /home/appuser/.docker/webmodules-user-password.txt

应用程序/停靠文件：

# For more information, please refer to https://aka.ms/vscode-docker-python
FROM python:3.9-slim

EXPOSE 5000

# Keeps Python from generating .pyc files in the container
ENV PYTHONDONTWRITEBYTECODE=1

# Turns off buffering for easier container logging
ENV PYTHONUNBUFFERED=1

# set the working directory in the container
WORKDIR /app

# Install pip requirements
COPY requirements.txt .
RUN python -m pip install -r requirements.txt

# copy the content of the local src directory to the working directory
# this isn't needed when developing as there's a bind under volumes: in the docker-compose.dev.yml file
COPY src .

# Creates a non-root user with an explicit UID and adds permission to access the /app folder
# For more info, please refer to https://aka.ms/vscode-docker-python-configure-containers
RUN adduser -u 5678 --disabled-password --gecos "" appuser && chown -R appuser /app
USER appuser

# During debugging, this entry point will be overridden. For more information, please refer to https://aka.ms/vscode-docker-python-debug
CMD ["gunicorn", "--reload", "--bind", "0.0.0.0:5000", "app:app"]

远程上mysql容器中的完整日志文件：

$ docker logs webmodules-db-1
2023-02-15 12:35:06+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.32-1.el8 started.
2023-02-15 12:35:07+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-02-15 12:35:07+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.32-1.el8 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2023-02-15T12:35:08.185441Z 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run.
2023-02-15T12:35:08.174478Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-02-15T12:35:08.182644Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-02-15T12:35:08.182660Z 0 [Note] [MY-013932] [Server] BuildID[sha1]=6b049f17400f850658b2eb3ff165ec9a085d9655
2023-02-15T12:35:08.182673Z 0 [Note] [MY-010949] [Server] Basedir set to /usr/.
2023-02-15T12:35:08.182694Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
2023-02-15T12:35:08.195754Z 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO
2023-02-15T12:35:08.195970Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2023-02-15T12:35:08.196046Z 0 [Note] [MY-010747] [Server] Plugin 'ndbcluster' is disabled.
2023-02-15T12:35:08.196062Z 0 [Note] [MY-010747] [Server] Plugin 'ndbinfo' is disabled.
2023-02-15T12:35:08.196069Z 0 [Note] [MY-010747] [Server] Plugin 'ndb_transid_mysql_connection_map' is disabled.
2023-02-15T12:35:08.197842Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-15T12:35:08.197887Z 1 [Note] [MY-013546] [InnoDB] Atomic write enabled
2023-02-15T12:35:08.197923Z 1 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2023-02-15T12:35:08.197942Z 1 [Note] [MY-012944] [InnoDB] Uses event mutexes
2023-02-15T12:35:08.197948Z 1 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2023-02-15T12:35:08.197956Z 1 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2023-02-15T12:35:08.206838Z 1 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2023-02-15T12:35:08.207494Z 1 [Note] [MY-012203] [InnoDB] Directories to scan './'
2023-02-15T12:35:08.207577Z 1 [Note] [MY-012204] [InnoDB] Scanning './'
2023-02-15T12:35:08.212659Z 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 4 files.
2023-02-15T12:35:08.213682Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
2023-02-15T12:35:08.228723Z 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2023-02-15T12:35:08.385456Z 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-02-15T12:35:08.386138Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2023-02-15T12:35:08.386668Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2023-02-15T12:35:08.432730Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2023-02-15T12:35:08.432776Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 4
2023-02-15T12:35:08.432817Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2023-02-15T12:35:08.432849Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2023-02-15T12:35:08.531818Z 1 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 31919058 in redo log file ./#innodb_redo/#ib_redo9.
2023-02-15T12:35:08.532211Z 1 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 31918620, whereas checkpoint_lsn = 31919058 and start_lsn = 31918592
2023-02-15T12:35:08.585369Z 1 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2023-02-15T12:35:08.760113Z 1 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2023-02-15T12:35:08.760147Z 1 [Note] [MY-012535] [InnoDB] Apply batch completed!
2023-02-15T12:35:08.760387Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-02-15T12:35:08.763735Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-02-15T12:35:08.768232Z 1 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-02-15T12:35:08.768318Z 1 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 2832
2023-02-15T12:35:08.788140Z 1 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 1 thread: 19811 ms.
2023-02-15T12:35:08.788273Z 1 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2023-02-15T12:35:08.788341Z 1 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-02-15T12:35:08.835413Z 1 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2023-02-15T12:35:08.835602Z 1 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2023-02-15T12:35:09.003141Z 1 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2023-02-15T12:35:09.037574Z 1 [Note] [MY-012976] [InnoDB] 8.0.32 started; log sequence number 31919068
2023-02-15T12:35:09.038169Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-15T12:35:09.046561Z 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80023'.
2023-02-15T12:35:09.184823Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2023-02-15T12:35:09.185738Z 1 [Note] [MY-012356] [InnoDB] Scanned 6 tablespaces. Validated 6.
2023-02-15T12:35:09.246031Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80023'.
2023-02-15T12:35:09.252593Z 0 [Note] [MY-011332] [Server] Plugin mysqlx reported: 'IPv6 is available'
2023-02-15T12:35:09.254590Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '::' port: 33060'
2023-02-15T12:35:09.254626Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. socket: '/var/run/mysqld/mysqlx.sock''
2023-02-15T12:35:09.278358Z 0 [Note] [MY-010902] [Server] Thread priority attribute setting in Resource Group SQL shall be ignored due to unsupported platform or insufficient privilege.
2023-02-15T12:35:09.310954Z 0 [Note] [MY-013911] [Server] Crash recovery finished in binlog engine. No attempts to commit, rollback or prepare any transactions.
2023-02-15T12:35:09.311015Z 0 [Note] [MY-013911] [Server] Crash recovery finished in InnoDB engine. No attempts to commit, rollback or prepare any transactions.
2023-02-15T12:35:09.316319Z 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin
2023-02-15T12:35:09.316414Z 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end
2023-02-15T12:35:09.322395Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-02-15T12:35:09.322785Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 230215 12:35:09
2023-02-15T12:35:09.433353Z 0 [Note] [MY-010913] [Server] You have not provided a mandatory server-id. Servers in a replication topology must have unique server-ids. Please refer to the proper server start-up parameters documentation.
2023-02-15T12:35:09.435029Z 0 [Note] [MY-010182] [Server] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2023-02-15T12:35:09.435065Z 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory.
2023-02-15T12:35:09.438539Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-02-15T12:35:09.438584Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-02-15T12:35:09.438630Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2023-02-15T12:35:09.438643Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2023-02-15T12:35:09.438802Z 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3306
2023-02-15T12:35:09.438849Z 0 [Note] [MY-010253] [Server] IPv6 is available.
2023-02-15T12:35:09.438857Z 0 [Note] [MY-010264] [Server]   - '::' resolves to '::';
2023-02-15T12:35:09.438885Z 0 [Note] [MY-010251] [Server] Server socket created on IP: '::'.
2023-02-15T12:35:09.440185Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2023-02-15T12:35:09.461816Z 0 [Note] [MY-011025] [Repl] Failed to start slave threads for channel ''.
2023-02-15T12:35:09.463840Z 5 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 5
2023-02-15T12:35:09.464101Z 0 [Note] [MY-011240] [Server] Plugin mysqlx reported: 'Using SSL configuration from MySQL Server'
2023-02-15T12:35:09.464743Z 0 [Note] [MY-011243] [Server] Plugin mysqlx reported: 'Using OpenSSL for TLS connections'
2023-02-15T12:35:09.464917Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2023-02-15T12:35:09.464973Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-02-15T12:36:06.574103Z 8 [Note] [MY-010926] [Server] Access denied for user 'root'@'172.18.0.2' (using password: YES)

不确定这是否相关，但python代码在www.example.com中app.py:

import mysql.connector
from flask import Flask, jsonify
from ptvsd import enable_attach

# enable python visual studio debugger
enable_attach(address=('0.0.0.0', 5678))

app = Flask(__name__)
conn = None

# adapted from https://github.com/aiordache/demos/blob/c7aa37cc3e2f8800296f668138b4cf208b27380a/dockercon2020-demo/app/src/server.py
# similar to https://github.com/docker/awesome-compose/blob/e6b1d2755f2f72a363fc346e52dce10cace846c8/nginx-flask-mysql/backend/hello.py
class DBManager:
    def __init__(self, database='example', host="db", user="root", password_file=None):
        pf = open(password_file, 'r')
        self.connection = mysql.connector.connect(
            user=user, 
            password=pf.read(),
            host=host,  # name of the mysql service as set in the docker compose file
            database=database,
            auth_plugin='mysql_native_password'
        )
        pf.close()
        self.cursor = self.connection.cursor()
    
    def populate_db(self):
        self.cursor.execute('DROP TABLE IF EXISTS blog')
        self.cursor.execute('CREATE TABLE blog (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255))')
        self.cursor.executemany('INSERT INTO blog (id, title) VALUES (%s, %s);', [(i, 'Blog post #%d'% i) for i in range (1,5)])
        self.connection.commit()
    
    def query_titles(self):
        self.cursor.execute('SELECT title FROM blog')
        rec = []
        for c in self.cursor:
            rec.append(c[0])
        return rec

@app.route('/')
def hello_world():
    return 'Hello, Docker!'

@app.route('/blogs')
def listBlog():
    global conn
    if not conn:
        conn = DBManager(host='db', database='webmodules', user='root', password_file='/run/secrets/db-password')
        conn.populate_db()
        
    rec = conn.query_titles()

    result = []
    for c in rec:
        result.append(c)

    return jsonify({"response": result})

if __name__ == "__main__":
    app.run(host ='0.0.0.0', port=5000)

my.cnf（本地和远程相同）：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
mysql

1条答案

按热度按时间
rkkpypqq

rkkpypqq1#

应替换www.example.com中的以下行app.py

password=pf.read(),

通过以下行：

password=pf.read().strip(),

原因是python也会读取文件末尾的换行符作为密码字符串的一部分，mysql会把它切掉。
在Windows上，你可能使用了一个编辑器，它不会在文件的末尾放置一个换行符，或者它可能添加了一个回车符，而在Linux上，行尾只是一个换行符。
或者你可以确保你的密码文件在末尾不包含换行符，但是大多数编辑器不允许这样做。使用'echo -n'命令可以做到这一点：

> echo -n "bla" >password_no_lf.txt
> od -c < password_no_lf.txt
0000000    b   l   a
0000003

> echo "bla" >password_with_lf.txt
> od -c < password_with_lf.txt
0000000    b   l   a  \n
0000004
赞(0）分享  回复(0）举报  2023-02-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com