I have a docker compose set of images with one of them based on mysql. The stack was adapted from https://github.com/docker/awesome-compose/tree/c2f8036fd353dae457eba7b9b436bf3a1c85d937/nginx-flask-mysql
This runs fine on my ("local") Windows 10 docker desktop (docker v20.10.22), but when I try to run it on my ("remote") centos7 server (Docker version 23.0.0, build e92dd87, remote server hosted at digitalocean), I see Access denied for user 'root'@'172.18.0.3' (using password: YES) when trying to connect with the database.
I should note I've seen answers like https://stackoverflow.com/a/59839180/799921, and I have (repeatedly) tried removing the volume on the remote server.
This failure happens whether I use root and MYSQL_ROOT_PASSWORD or MYSQL_USER (user) and MYSQL_PASSWORD. I've verified I can connect to the database from the mysql container, but the app container does not have mysql installed so I haven't been able to manually test the connection from the app container.
Following https://hub.docker.com/_/mysql, "Connect to MySQL from the MySQL command line client", I'm able to connect, so this is a problem with the app according to https://dev.mysql.com/doc/refman/8.0/en/problems-connecting.html, "If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -ppassword db_name. If you are able to connect using the mysql client, the problem lies with your program", but I'm not sure what.
$ docker run -it --network webmodules_backend-network --rm mysql mysql -hdb -uroot -p
Enter password:
Welcome to the MySQL monitor.
...
I've looked at the mysql.user table on both running versions and nothing jumps out at me as being incorrect.
mysql.user on local:
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 on remote:
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)
This is started with the following on local:
docker compose -f docker-compose.yml -f docker-compose.dev.yml up -d
and on remote (from the local machine):
docker --context webmodules compose -f docker-compose.yml -f docker-compose-prod.yml up -d
where
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
app/Dockerfile:
# 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"]
The full log file from the mysql container on remote:
$ 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)
And not sure if this is relevant, but the python code is in 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 (local and remote identical):
# 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/
