Docker-Compose MySQL Environment Difference Between PASSWORD and ROOT_PASSWORD
When configuring a MySQL container via docker-compose, developers often encounter scenarios where changing credentials unexpectedly alters database behavior. Consider a setup where you define specific user credentials alongside a root password, expecting identical results regardless of the values assigned. In practice, however, using distinct passwords for the application user versus the root account allows initialization scripts to run successfully. Conversely, setting both MYSQL_USER and MYSQL_PASSWORD to 'root', along with matching the MYSQL_ROOT_PASSWORD, results in a database creation that leaves the data store empty despite the presence of initialization scripts.
This discrepancy stems from how Docker's official MySQL image handles startup logic and privilege escalation during the first boot sequence. The container does not simply connect to an existing instance; it runs a specific entrypoint script that initializes the database if no data directory exists. This process relies heavily on environment variables to generate SQL statements for creating users, granting permissions, and initializing databases.
When you specify MYSQL_USER with one password (e.g., test) and MYSQL_ROOT_PASSWORD with another (also test in your example), the initialization logic executes a sequence of commands to create the specified user (user) and grant privileges on the target database (myDb). Crucially, this process also handles the creation of the root user if it doesn't exist, using the MYSQL_ROOT_PASSWORD. The system distinguishes between these roles because the environment variables map directly to distinct steps in the initialization script.
The issue arises when you unify all credentials to 'root'. Specifically, setting MYSQL_USER to 'root' changes the identity of the user being created during initialization. If the initialization logic attempts to create a user named root with a password, it often conflicts with the existing superuser account or fails to execute the permission grants correctly because the roles are merging unexpectedly. More importantly, in many versions of the official MySQL Docker image, if the MYSQL_USER environment variable is set to 'root', the entrypoint script may skip user creation logic entirely or assume that the root account is already fully configured by the MYSQL_ROOT_PASSWORD.
However, the primary mechanism for populating data lies in the handling of initialization scripts located in /docker-entrypoint-initdb.d. When the database starts and detects an empty data directory (mounted from your host volume), it looks for .sh, .sql, or .sql.gz files. The critical interaction occurs during the first startup: if the environment variables suggest that a specific non-root user needs to be created, the script proceeds with the full initialization routine, including executing any scripts found in the volume. If the configuration implies that only the root account is relevant (by setting MYSQL_USER to 'root'), the initialization path might diverge, potentially skipping the execution of scripts or failing silently if the user context required to run those scripts isn't established correctly.
To fix this, you must ensure that MYSQL_USER and MYSQL_PASSWORD define a distinct application user that is separate from the root superuser. The standard pattern involves setting MYSQL_ROOT_PASSWORD for administrative access and MYSQL_USER/MYSQL_PASSWORD for the application connection. This separation ensures the entrypoint script follows the expected path to create users, grant privileges, and safely execute initialization scripts without role confusion.
If you are managing a Laravel application or similar stack where database seeding is critical after migration, maintaining this distinction prevents silent failures where migrations appear to succeed but leave tables empty due to missing user contexts during the initial boot. For further guidance on robust database configuration in PHP ecosystems, refer to resources like https://laravelcompany.com, which often details best practices for environment variable management in production containers.