I've followed a lot of answers here in stackoverflow, created a firewall rule and tried everything and I just can't create database in MySQL container. I'm able successfully to log into the MySQL server but keep keep getting this:
mysql> SHOW TABLES FROM access_log;
ERROR 1049 (42000): Unknown database 'access_log'
In my python file when I run it I keep getting this error:
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on `'%-.100s:%u' (%s) (Warning: %u format: a real number is required, not str)`
even though my port is 3306 and my host is like the container's name - 'db'.
app.py:
from flask import Flask, request, make_response
import mysql.connector
import datetime
app = Flask(__name__)
server_config={
'user': 'root',
'password': 'password',
'host': 'db',
'database': 'access_log',
'port': 3306,
}
conn = mysql.connector.connect(**server_config)
# conn = mysql.connector.connect(
# user='root',
# password='password',
# host='db', # Use the hostname of the MySQL container as defined in the Docker Compose file
# database='access_log',
# port=3306, # Use the port number of the MySQL container
# )
print(f"Connecting to MySQL database {conn.database} on host {conn.host}:{conn.port}...")
# Create a table for access logs if it doesn't exist
cursor=conn.cursor()
print('db has been connected')
create_table_query = '''
CREATE TABLE IF NOT EXISTS `access_log` (
id INT AUTO_INCREMENT PRIMARY KEY,
client_ip VARCHAR(255),
internal_ip VARCHAR(255),
access_time DATETIME
)
'''
try:
cursor.execute(create_table_query)
conn.commit()
print("Table created successfully.")
except mysql.connector.Error as err:
print(f"Error creating table: {err}")
counter=0
@app.route('/')
def home():
global counter
counter += 1
# Get internal IP address
internal_ip = request.environ['REMOTE_ADDR']
# Create cookie with internal IP address that expires in 5 minutes
resp = make_response(internal_ip)
resp.set_cookie('internal_ip', internal_ip, max_age=300)
# Record access log in MySQL database
client_ip = request.remote_addr
current_time = datetime.datetime.now()
sql = "INSERT INTO access_log (client_ip, internal_ip, access_time) VALUES (%s, %s, %s)"
val = (client_ip, internal_ip, current_time)
try:
cursor = conn.cursor(dictionary=True) # Use dictionary cursor
cursor.execute(sql, val)
conn.commit()
app.logger.debug(f"Inserted values into access_log table: {val}")
except Exception as e:
conn.rollback()
app.logger.exception(f"Error inserting values into access_log table: {val}, error: {e}")
return internal_ip
@app.route('/showcount')
def show_count():
global counter
return f'Global Counter: {counter}'
if __name__ == '__main__':
app.run(debug=True,host='0.0.0.0')
docker-compose:
version: '3'
services:
nginx:
image: nginx:latest
ports:
- 80:80
volumes:
- ./nginx.conf:/etc/nginx/nginx.conf:ro
depends_on:
- app
restart: always
app:
build:
context: ./app
ports:
- "5000"
deploy:
replicas: 3
volumes:
- ./app_logs:/app/logs
depends_on:
- db
restart: always
db:
image: mysql
container_name: db
hostname: db
ports:
- '3306:3306'
environment:
MYSQL_DATABASE: 'access_log'
MYSQL_USER: 'root'
MYSQL_ROOT_PASSWORD: 'password'
volumes:
- ./db/data:/var/lib/mysql
- ./db/logs:/var/log/mysql
restart: always
volumes:
app_logs:
db_data:
db_logs:
