A Comprehensive Guide to Executing SQL Scripts in Bash: Automating MySQL Database Configuration

Technical Background and Core Concepts

In Linux system administration, automating database configuration via Bash scripts is a common operational task. MySQL, as a widely used relational database management system, provides the command-line tool mysql to execute SQL statements and scripts. Understanding how to properly invoke this tool is essential for building reliable automation workflows.

Basic Command Execution Method

The fundamental command format for executing an SQL script is: mysql -u username -p < script.sql. Here, the -u parameter specifies the username, -p prompts for a password (interactively), and the < operator redirects the content of the file script.sql to the standard input of the mysql command. For example, to run a script named db.sql, use:

mysql -u admin -p < db.sql

Upon execution, the system will prompt for a password, ensuring authentication security. This method is suitable for one-time execution of entire scripts without manual SQL input.

Specifying the Target Database

If the script needs to run within a specific database, the database name can be directly specified in the command. For example: mysql -u user -p database_name < db.sql. Here, database_name is passed as a positional argument to mysql, and the command automatically selects that database after connection before executing the script content. This avoids the need for a USE database_name; statement within the script, streamlining the process. A code example is:

mysql -u developer -p myapp_db < db.sql

This command connects as the developer user and runs the db.sql script against the myapp_db database.

Advanced Configuration and Error Handling

To enhance the robustness of automation scripts, it is advisable to incorporate additional parameters. For instance, use --defaults-file to specify a configuration file for managing connection parameters, or add --verbose for detailed execution output. Error handling can be implemented by checking the command exit status:

if mysql -u user -p db < script.sql; then
    echo "Script executed successfully"
else
    echo "Execution failed with exit code: $?"
fi

Furthermore, setting environment variables like MYSQL_PWD allows non-interactive password passing, but security risks should be noted, recommending use only in controlled environments.

Practical Case and Best Practices

In real-world deployments, database initialization is often integrated into Bash scripts. For example, create a setup_db.sh file:

#!/bin/bash
# Define variables
USER="app_user"
DB="app_database"
SCRIPT="init.sql"

# Execute SQL script
echo "Configuring database..."
mysql -u $USER -p$PASSWORD $DB < $SCRIPT

# Verify execution result
if [ $? -eq 0 ]; then
    echo "Database configuration completed"
else
    echo "Error occurred during configuration" >&2
    exit 1
fi

Best practices include using strong passwords, restricting script permissions, logging execution details, and testing script compatibility across different environments. Avoid hardcoding sensitive information in scripts; instead, leverage configuration management tools or key vaults.

Supplementary References and Extensions

Alternative methods, such as piping SQL content (cat db.sql | mysql -u user -p), are also viable, but redirection is generally more efficient. For complex scenarios, combine with tools like mysqladmin for database state management or mysqldump for data backups. Referring to community discussions, common issues include character encoding handling and timeout settings, which can be resolved by adding parameters like --default-character-set=utf8.

In summary, mastering the integration of the mysql command with Bash scripts significantly enhances the automation level of database operations, ensuring consistency and reliability in deployment processes.