Docker-Compose中MySQL的SQL Dump文件未执行问题求助

Hey there! Let's work through this MySQL initialization issue you're facing with Docker. I've run into similar snags when I was new to Docker too, so here's a step-by-step breakdown to get your dump file running and your database accessible:

First: Understand MySQL's Docker Initialization Logic #

The official MySQL image has a key behavior you might not know about: it only runs scripts in /docker-entrypoint-initdb.d/ the FIRST time the container starts, and only if the MySQL data directory (default: /var/lib/mysql) is empty. If you've started the db container before, old database files are already stored, so it skips running your dump file entirely. That's probably the biggest culprit here.

Step 1: Clean Up Old Containers and Data Volumes #

Let's start fresh by wiping existing containers and their associated data volumes:

# Stop and remove all containers + volumes from your docker-compose setup
docker-compose down -v

# If that doesn't work, manually target the db container/volume:
docker rm -f $(docker ps -q --filter "name=db")
docker volume rm $(docker volume ls -q --filter "name=*mysql*")

Step 2: Check Your SQL Dump File #

Your dump file might be missing critical pieces, or have permission issues that prevent MySQL from reading it:

1. Ensure the dump creates/uses the right database
   If your Dump20180430.sql doesn't include a CREATE DATABASE statement or a USE your_database_name; line at the top, all your tables will end up in the default mysql database (which you won't see when connecting as dbuser). Add these lines to the start of your dump:

   CREATE DATABASE IF NOT EXISTS your_target_db;
   USE your_target_db;
   -- Rest of your table/data statements...
   

2. Add User Creation & Permissions to the Dump
   Instead of manually creating dbuser in Workbench, include this in your dump file to automate it during initialization:

   CREATE USER 'dbuser'@'%' IDENTIFIED BY 'your_dbuser_password';
   GRANT ALL PRIVILEGES ON your_target_db.* TO 'dbuser'@'%';
   FLUSH PRIVILEGES;
   

   This ensures dbuser has full access to your target database right from the start.

3. Fix File Permissions
   Even if the file is in the container, MySQL might not have read access. Update your Dockerfile to set proper permissions:

   FROM mysql:5.7
   ENV MYSQL_ROOT_PASSWORD=root
   ENV MYSQL_ALLOW_SECRET_PASSWORD=yes
   COPY Dump20180430.sql /docker-entrypoint-initdb.d/
   RUN chmod 644 /docker-entrypoint-initdb.d/Dump20180430.sql
   

Step 3: Optimize Your Docker Compose Config #

Tweak your docker-compose.yml to make initialization smoother and avoid manual setup:

version: '3.4'
services:
  productservicejava:
    image: productservicejava
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      - db
    ports:
      - "8080:8080"
    # Add environment variables for your Java app to connect to the db
    environment:
      - DB_HOST=db
      - DB_PORT=3306
      - DB_NAME=your_target_db
      - DB_USER=dbuser
      - DB_PASSWORD=your_dbuser_password
  db:
    image: productservicedb
    build:
      context: .
      dockerfile: DockerfileDb
    ports:
      - "3307:3306"
    # Add these env vars to auto-create the db and user
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_ALLOW_SECRET_PASSWORD=yes
      - MYSQL_DATABASE=your_target_db  # Auto-creates this db on init
      - MYSQL_USER=dbuser              # Auto-creates this user
      - MYSQL_PASSWORD=your_dbuser_password  # Sets the user's password
    # Optional: Add a volume to persist MySQL data (so you don't lose data when containers are deleted)
    volumes:
      - mysql_data:/var/lib/mysql
# Define the volume for MySQL persistence
volumes:
  mysql_data:

With MYSQL_DATABASE, MYSQL_USER, and MYSQL_PASSWORD set, MySQL will automatically create the database and user with full permissions—no manual Workbench steps needed!

Step 4: Verify Initialization Worked #

After restarting with docker-compose up --build, check the db container logs to confirm your dump ran:

docker-compose logs db

Look for lines like these, which mean the initialization succeeded:

db_1 | 2018-05-24T12:13:39.XXXZ 1 [Note] Executing 'CREATE DATABASE IF NOT EXISTS `your_target_db`;'
db_1 | 2018-05-24T12:13:39.XXXZ 1 [Note] Executing 'GRANT ALL PRIVILEGES ON `your_target_db`.* TO 'dbuser'@'%''

About Those Log Warnings #

The --skip-name-resolve warnings in your logs are just minor configuration quirks—they don't affect your database's functionality. If you want to get rid of them, add this line to your MySQL Dockerfile to disable the skip-name-resolve setting:

CMD ["mysqld", "--skip-name-resolve=0"]

内容的提问来源于stack exchange，提问作者fofoky