Skip to main content

How to Connect to a MySQL Database from a Batch Script

Many web applications (like WordPress or Laravel) heavily utilize MySQL databases. As a system administrator, you frequently need to automate database maintenance, such as creating nightly .sql schema dumps, dropping obsolete tables, or executing specific stored procedures. All of these administrative tasks can be accomplished directly via a Batch script by utilizing the official mysql and mysqldump command-line clients.

In this guide, we will demonstrate how to securely interact with a MySQL or MariaDB instance from a Batch script.

The Strategy: The mysql.exe Client​

  1. Identify the MySQL bin directory location (C:\Program Files\MySQL\MySQL Server X.Y\bin).
  2. Gather your credentials (Host, Username, Password, Database Name).
  3. Write the desired SQL queries to a temporary .sql file, or pass them inline using the -e (execute) parameter.

Method 1: Running an Inline SQL Statement​

If you only need to trigger a quick status check or truncate a specific log table, passing the command inline is ideal.

@echo off
setlocal

:: Define the MySQL executable
set "mysqlExe=C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe"

:: Define MySQL connection details
set "mySqlHost=127.0.0.1"
set "mySqlUser=root"
set "mySqlDb=webapp_dev"

:: Check if mysql.exe exists
if not exist "%mysqlExe%" (
    echo [ERROR] mysql.exe not found at "%mysqlExe%".
    pause
    exit /b 1
)

:: Prompt for the password so it is not stored in plain text
set /p "mySqlPass=Enter MySQL password: "
if "%mySqlPass%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

echo.
echo Connecting to MySQL Server at %mySqlHost%...
echo Executing COUNT query...
echo.

:: Define the SQL query
set "query=SELECT COUNT(*) AS UserCount FROM users;"

:: Execute the query using the -e flag
:: Note: there is no space between -p and the password value
"%mysqlExe%" -h "%mySqlHost%" -u "%mySqlUser%" -p"%mySqlPass%" -D "%mySqlDb%" -e "%query%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo EXECUTION SUCCESSFUL
    echo ==========================================
) else (
    echo.
    echo [ERROR] MySQL returned error code: %errorlevel%.
    pause
    exit /b %errorlevel%
)

pause
endlocal

Method 2: Exporting Query Results to a File​

When running analytics queries (e.g., extracting yesterday's total sales), returning the output directly to the Command Prompt is not always helpful. Instead, redirect the command output to a flat file.

@echo off
setlocal

set "mysqlExe=C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe"
set "mySqlHost=192.168.1.100"
set "mySqlUser=report_user"
set "mySqlDb=production_db"

:: Output file for query results
set "outputFile=C:\Reports\ActiveUsers.tsv"

:: Check if mysql.exe exists
if not exist "%mysqlExe%" (
    echo [ERROR] mysql.exe not found at "%mysqlExe%".
    pause
    exit /b 1
)

:: Prompt for the password
set /p "mySqlPass=Enter MySQL password: "
if "%mySqlPass%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

:: Ensure the output directory exists
for %%A in ("%outputFile%") do (
    if not exist "%%~dpA" mkdir "%%~dpA"
)

set "query=SELECT id, email, created_at FROM users WHERE status='active';"

echo Extracting active user data to "%outputFile%"...
echo.

:: Use standard output redirection (>) to push the SQL results to a file
:: The --batch flag forces tab-separated output with no ASCII-art table borders
"%mysqlExe%" -h "%mySqlHost%" -u "%mySqlUser%" -p"%mySqlPass%" -D "%mySqlDb%" --batch -e "%query%" > "%outputFile%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo EXTRACTION SUCCESSFUL
    echo Output: %outputFile%
    echo ==========================================
) else (
    echo.
    echo [ERROR] MySQL returned error code: %errorlevel%.
    pause
    exit /b %errorlevel%
)

pause
endlocal
tip

The --batch flag produces tab-separated output (TSV), not comma-separated (CSV). If you need true CSV output, add --raw alongside --batch and use a post-processing step to convert the delimiter, or use a SELECT ... INTO OUTFILE statement with a FIELDS TERMINATED BY ',' clause directly in your SQL query.

Method 3: Executing a .sql Script File (Dumps and Restores)​

For extensive schema updates, relying on inline variables is impractical. Instead, provide a .sql file via input redirection (<).

Similarly, to back up the entire database, use the mysqldump.exe utility.

Database Backup (Dump)​

@echo off
setlocal

set "mysqldumpExe=C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"
set "mySqlHost=localhost"
set "mySqlUser=root"
set "mySqlDb=webapp_dev"
set "dumpFile=C:\Backups\Daily_WebApp_Backup.sql"

:: Check if mysqldump.exe exists
if not exist "%mysqldumpExe%" (
    echo [ERROR] mysqldump.exe not found at "%mysqldumpExe%".
    pause
    exit /b 1
)

:: Prompt for the password
set /p "mySqlPass=Enter MySQL password: "
if "%mySqlPass%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

:: Ensure the backup directory exists
for %%A in ("%dumpFile%") do (
    if not exist "%%~dpA" mkdir "%%~dpA"
)

echo Creating MySQL dump: "%dumpFile%"...
echo.

:: mysqldump writes the SQL dump to stdout; redirect it to a file
"%mysqldumpExe%" -h "%mySqlHost%" -u "%mySqlUser%" -p"%mySqlPass%" "%mySqlDb%" > "%dumpFile%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo BACKUP SUCCESSFUL
    echo Output: %dumpFile%
    echo ==========================================
) else (
    echo.
    echo [ERROR] mysqldump returned error code: %errorlevel%.
    :: Remove a potentially incomplete dump file
    del "%dumpFile%" 2>nul
    pause
    exit /b %errorlevel%
)

pause
endlocal

Database Restore​

@echo off
setlocal

set "mysqlExe=C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe"
set "mySqlHost=localhost"
set "mySqlUser=root"
set "mySqlDb=webapp_dev"
set "restoreFile=C:\Backups\Daily_WebApp_Backup.sql"

:: Check if mysql.exe exists
if not exist "%mysqlExe%" (
    echo [ERROR] mysql.exe not found at "%mysqlExe%".
    pause
    exit /b 1
)

:: Check if the restore file exists
if not exist "%restoreFile%" (
    echo [ERROR] SQL file not found at "%restoreFile%".
    pause
    exit /b 1
)

:: Prompt for the password
set /p "mySqlPass=Enter MySQL password: "
if "%mySqlPass%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

echo Restoring MySQL database from: "%restoreFile%"...
echo.

:: Input redirection (<) feeds the .sql file into the mysql client
"%mysqlExe%" -h "%mySqlHost%" -u "%mySqlUser%" -p"%mySqlPass%" "%mySqlDb%" < "%restoreFile%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo RESTORE SUCCESSFUL
    echo ==========================================
) else (
    echo.
    echo [ERROR] MySQL returned error code: %errorlevel%.
    pause
    exit /b %errorlevel%
)

pause
endlocal
warning

Passing passwords on the command line (via -p) exposes them in the process list and triggers a mysql: [Warning] Using a password on the command line interface can be insecure. message. For production automation, use a MySQL option file with --defaults-extra-file="C:\secure\my.cnf" containing the credentials, and restrict that file with NTFS permissions.

Why Connect to MySQL from Batch?​

  1. Scheduled Maintenance: Executing an OPTIMIZE TABLE operation every Sunday night at 2:00 AM using the built-in Windows Task Scheduler.
  2. Dev Environment Resets: A script that drops the entire local dev_database and restores last night's production snapshot using < file redirection.
  3. Migration Pipelines: Packaging MySQL dumps alongside static IIS assets into a .zip archive before transferring them automatically via WinSCP to cold storage.

Important Considerations​

  1. Password Security: The -p flag requires you to attach the password directly to the letter p (e.g., -pMySecurePassword). Do not use -p MySecurePassword or MySQL will prompt you interactively and treat "MySecurePassword" as the database name. For production scripts, use a --defaults-extra-file option file instead of passing credentials on the command line.
  2. PATH Variables: If your script throws 'mysql' is not recognized as an internal or external command, it means the MySQL bin directory is not in your System PATH. Always define the explicit path to mysql.exe to guarantee reliability across different servers.
  3. Authentication Errors: If you receive "Access denied for user 'root'@'localhost'" errors, verify that the user possesses sufficient privileges (GRANT ALL PRIVILEGES) on the target database, and that the MySQL service is actively running (services.msc).

Conclusion​

Automating database queries, maintenance operations, and full schema dumps is essential for preserving the web application lifecycle. Integrating the mysql.exe and mysqldump.exe binaries into Batch scripts allows you to schedule and standardize your database workflows alongside the rest of your Windows infrastructure routines.

Table of Contents