Docker Compose环境下MySQL容器连接错误解析与端口配置指南

services:
  mysql:
    image: mysql:latest
    ports:
      - "3307:3306" # 宿主机端口3307映射到容器内部端口3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: db
      MYSQL_USER: user
      MYSQL_PASSWORD: password
    networks:
      - sql_network
    volumes:
      - /mysql_data:/var/lib/mysql

config = {
    'host': 'mysql',  # Docker Compose服务名称，正确
    'port': '3307',   # **错误：应为容器内部端口3306**
    'user': 'user',
    'password': 'password',
    'database': 'db',
}

# dbService.py
import mysql.connector
import logging
import time

logger = logging.getLogger(__name__)

config = {
    'host': 'mysql',  # Docker Compose服务名称，在Docker网络中作为主机名使用
    'port': 3306,     # MySQL容器内部监听的端口
    'user': 'user',
    'password': 'password',
    'database': 'db',
}

def create_tables():
    logger.info("Entering create tables method from dbservice")
    max_retries = 5
    retry_delay = 5 # seconds
    for i in range(max_retries):
        try:
            connection = mysql.connector.connect(**config)
            cursor = connection.cursor()
            # Check if 'emails' table already exists
            cursor.execute("SHOW TABLES LIKE 'emails'")
            table_exists = cursor.fetchone()

            if not table_exists:
                cursor.execute(
                    '''
                    CREATE TABLE emails (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    email VARCHAR(255) NOT NULL,
                    verified BOOLEAN NOT NULL,
                    flatType VARCHAR(255) NOT NULL,
                    streetName VARCHAR(255) NOT NULL,
                    blkFrom INT NOT NULL,
                    blkTo INT NOT NULL,
                    lastSent TIMESTAMP,
                    token VARCHAR(255))
                    ''')
                connection.commit()
                logger.info("Table 'emails' created successfully.")
            else:
                logger.info("Table 'emails' already exists.")
            connection.close()
            return # Success, exit function
        except mysql.connector.Error as err:
            logger.error(f"Attempt {i+1}/{max_retries}: Unable to connect to MySQL or create tables: {err}")
            time.sleep(retry_delay)
        except Exception as e:
            logger.error(f"An unexpected error occurred: {e}")
            break # For unexpected errors, no retry
    logger.error("Failed to connect to MySQL and create tables after multiple retries.")