Run MySQL Script from Command Line Automatically

Executing external SQL scripts automatically through the MySQL command-line client is a fundamental skill for database administrators and developers. This guide explains how to run .sql files using the command-line interface without manual intervention, covering the basic syntax, password handling for automation, and execution within shell scripts or cron jobs.

The Standard Command Syntax

The most common way to execute an external SQL script is by using the command-line shell’s input redirection operator (<). This feeds the contents of your SQL file directly into the MySQL client.

The basic syntax is as follows:

mysql -u username -p database_name < /path/to/script.sql

When you run this command, the system will prompt you for your password. Once entered, MySQL will execute all queries inside script.sql against the specified database_name and then exit.

Automating Execution Without Prompts

To run scripts automatically (such as in a cron job or background task), you must bypass the interactive password prompt. There are three primary ways to achieve this safely.

1. Using a Configuration File (Recommended)

Storing credentials in a configuration file is the most secure method. Create a file named my.cnf (or a custom options file) with the following content:

[client]
user=your_username
password=your_password

Secure the file permissions so only your user can read it:

chmod 600 my.cnf

Then, run your script automatically by referencing this file:

mysql --defaults-extra-file=/path/to/my.cnf database_name < /path/to/script.sql

2. Using the Password Flag (Less Secure)

You can provide the password directly in the command. Note that there is no space between the -p flag and the password.

mysql -u username -pyour_password database_name < /path/to/script.sql

Warning: This method exposes your password to the system’s process list and shell history.

3. Using Environment Variables

You can temporarily set the MYSQL_PWD environment variable before running the command:

export MYSQL_PWD="your_password"
mysql -u username database_name < /path/to/script.sql

Running Scripts from Inside the MySQL Prompt

If you are already logged into the interactive MySQL monitor and want to execute a script, use the source command (or the \. shortcut):

mysql> source /path/to/script.sql;

or

mysql> \. /path/to/script.sql

Automating with Bash Scripts

To run multiple scripts or include error handling, you can wrap the MySQL command in a bash script:

#!/bin/bash

DB_USER="backup_user"
DB_PASS="secure_password"
DB_NAME="production_db"
SCRIPT_PATH="/opt/scripts/daily_update.sql"
LOG_FILE="/var/log/mysql_cron.log"

# Execute the script and log the output
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$SCRIPT_PATH" > "$LOG_FILE" 2>&1

if [ $? -eq 0 ]; then
    echo "SQL script executed successfully."
else
    echo "SQL script execution failed. Check log file."
fi