Docker Compose生产环境部署后SQLSTATE[HY000] [1045]数据库访问拒绝问题求助

Let's break down why you're hitting this SQLSTATE[HY000] [1045] error and walk through fixes step by step. The fact that both your PHP app and manual connections inside the database container are failing points to a problem with how the MySQL user is being set up or persisted in your Docker environment.

Common Causes & Fixes #

1. Old Database Volume Persisting Initialization Data #

This is the most frequent culprit with Docker MySQL setups. When you mount a volume (./database/data:/var/lib/mysql), MySQL stores all its data there—including user accounts. The MYSQL_USER, MYSQL_PASSWORD, and MYSQL_DATABASE environment variables only take effect the first time the container is started (when the data directory is empty). If you've ever started this container before with different credentials, the old user/password is still stored in the volume.

Fix:

Stop your containers, delete the existing data volume, and rebuild from scratch:

# Stop and remove containers + volumes
sudo docker-compose down -v
# Delete the local data directory to ensure a fresh start
sudo rm -rf ./database/data
# Rebuild and restart
sudo docker-compose up -d --build

2. Conflicting User Setup in init.sql #

You're mounting ./database/init.sql to /docker-entrypoint-initdb.d/init.sql, which runs on first initialization. If this script includes manual CREATE USER or GRANT statements, they might conflict with the user created by the official MySQL/MariaDB image's environment variables.

For example, if your init.sql has something like:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'old_password';
GRANT ALL ON dbname.* TO 'user'@'localhost';

This creates a user@localhost account with a different password than what's in your .env, while the environment variable creates user@% (allowing connections from any host). When you try to connect locally in the container using mysql -u user -p, it defaults to the localhost socket, which uses the mismatched user@localhost account.

Fix:

• Remove any CREATE USER or GRANT statements from init.sql that duplicate the environment variable setup.
• If you need custom permissions, ensure the script uses the same password as DATABASE_PASSWORD and grants access to the correct host (e.g., user@% to allow connections from your app container's network).

3. Environment Variables Not Being Passed Correctly #

Double-check that your .env file is in the same directory as your docker-compose.yml, and that the variables are being correctly injected into the database container.

Verify:

Enter the database container and print the environment variables:

# Get the container name (look for the "database" service)
sudo docker ps
# Enter the container
sudo docker exec -it <your-database-container-name> bash
# Check variables
echo $MYSQL_USER
echo $MYSQL_PASSWORD
echo $MYSQL_DATABASE

If these don't match your .env values, ensure your docker-compose.yml is correctly referencing them (which it looks like it is), and that you haven't made a typo in the .env file.

4. Test Connection with Correct Host in the Database Container #

When you run mysql -u user -p inside the database container, it tries to connect via the Unix socket (using localhost), which may not map to the user@% account created by the environment variables. Try connecting via TCP instead to test the user@% account:

mysql -u user -p -h 127.0.0.1

Enter your DATABASE_PASSWORD—if this works, it confirms the user@% account is set up correctly, and the earlier error was due to using the localhost socket (which expects a user@localhost account that doesn't exist or has the wrong password).

Final Notes #

Once you've applied these fixes, your PHP app's DATABASE_URL (which connects to database:3306, using the container's network IP) should work, since it uses the user@% account that's now properly initialized with the correct password.

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