Run a mysql command from a bat file without prompting for a password

by

Summary

The issue involves a failed automated authentication attempt where a Windows batch script fails to execute a MySQL command because the client is not picking up the credentials defined in the my.ini configuration file. Despite the user attempting to hardcode credentials under the [client] section, the MySQL client continues to prompt for a password (or fails with Access denied), leading to a breakdown in automated task execution.

Root Cause

The failure stems from a misunderstanding of configuration precedence and file location awareness in MySQL client operations:

  • Configuration File Scope: The MySQL client does not automatically search all directories for a my.ini. If the client is executed from a path where it cannot locate the specific configuration file, it reverts to default behavior (no password).
  • Incorrect Sectioning: While [client] is a valid section, some environments or specific builds may require credentials to be explicitly tied to the [mysql] block for the command-line tool specifically.
  • Permission/Locking Issues: In Windows environments, C:\ProgramData often has strict ACL (Access Control List) requirements. If the user executing the .bat file does not have read permissions to the specific my.ini in that directory, the client silently ignores it.
  • Syntax Errors: The user’s attempt to use HTML entities like " within a configuration file is invalid; configuration files require standard literal quotes or no quotes at all.

Why This Happens in Real Systems

In production environments, this is a classic case of Environment Drift:

  • Configuration Fragmentation: Systems often have multiple versions of a tool installed (e.g., a standalone MySQL client vs. a bundled driver). Each looks for configuration in different default paths (%APPDATA%, C:\Windows, etc.).
  • Implicit vs. Explicit Configuration: Relying on “magic” files like my.ini is dangerous because the application’s behavior changes based on the working directory from which the command is launched.
  • Security vs. Automation Tension: The desire to avoid hardcoded passwords often leads engineers to use configuration files, but if those files aren’t strictly managed and located, the automation fails.

Real-World Impact

  • Broken CI/CD Pipelines: Automated deployment scripts that rely on database migrations will fail, halting the entire release cycle.
  • Monitoring Gaps: Scheduled health checks or data scraping tasks will fail, leading to false negatives in system monitoring.
  • Operational Overhead: On-call engineers are woken up by “Access Denied” alerts that are actually configuration mismatches rather than actual security breaches or database downtime.

Example or Code

To solve this securely and reliably, use a login path. This avoids storing passwords in plain text in .bat files or unpredictable .ini files.

@echo off
:: Step 1: Create a secure login path (Run once manually)
:: mysql_config_editor set --login-path=local_dev --host=localhost --user=user --password

:: Step 2: Use the login path in the batch file
mysql --login-path=local_dev -e "SELECT 1;"

How Senior Engineers Fix It

A senior engineer moves away from “guessing” which config file is being read and moves toward explicit configuration:

  • Use mysql_config_editor: This is the industry standard for non-interactive authentication. It stores credentials in an obfuscated .mylogin.cnf file, which is much more secure than a plain-text my.ini.
  • Explicit Flagging: Instead of relying on the global my.ini, they use the --defaults-file= flag in the batch script to point directly to the known, valid configuration file.
  • Principle of Least Privilege: They ensure the service account running the .bat file has the absolute minimum permissions required to read the necessary config files.

Why Juniors Miss It

  • The “It Works on My Machine” Trap: Juniors often test by manually typing commands in a terminal where environment variables are already loaded, failing to realize the contextual difference of a non-interactive batch execution.
  • Over-reliance on Global State: They assume that because a file exists in C:\ProgramData, it is “globally” active. They fail to account for the search order of the application.
  • Ignoring Syntax Nuances: They may overlook how special characters or encoding (like the " error) impact the parsing logic of configuration engines.

Leave a Comment