Analysis and Solution for MySQL Command Execution Issues in Bash Scripts

Problem Background and Phenomenon Analysis

In Unix/Linux environments, MySQL database operations are typically performed via the command-line interface. However, when migrating these commands to Bash scripts, developers often encounter unexpected execution failures. This article is based on a typical case: a MySQL command that runs successfully on the command line fails in a Bash script due to incorrect variable expansion, resulting in access denied errors.

Core Issue: Variable Expansion Mechanism

Variable expansion in Bash scripts is directly influenced by quote types. Single quotes (') prevent all variable expansion, treating content as literal strings, while double quotes (") allow variable expansion while preserving the literal meaning of most special characters. In the original script:

mysql --user='$user' --password='$password' --database='$database' --execute='DROP DATABASE $user; CREATE DATABASE $database;'

Due to the use of single quotes, variables $user, $password, and $database are not expanded, causing the MySQL server to receive the literal string $user as the username, thus triggering access errors.

Solution: Correct Use of Double Quotes

According to ShellCheck rule SC2016, double quotes should be used to ensure proper variable expansion. The modified script example is as follows:

mysql \
  --user="$user" \
  --password="$password" \
  --database="$database" \
  --execute="DROP DATABASE $user; CREATE DATABASE $database;"

This modification ensures that variables are correctly parsed into actual values before being passed to the MySQL command. The backslashes (\) are used to improve code readability by splitting long commands into multiple lines, without affecting execution logic.

In-Depth Analysis: Quotes and Special Character Handling

In Shell scripts, proper handling of special characters is crucial. Inside double quotes, variable expansion, command substitution, and arithmetic expansion are performed, while backslash escaping only applies to $, `, ", and \. For example, if a password contains special characters like ! or *, double quotes ensure safe transmission, preventing misinterpretation by the Shell as wildcards or history expansion.

Code Examples and Best Practices

Below is a complete Bash script example demonstrating how to safely execute MySQL commands:

#!/bin/bash
# Define database connection parameters
user="myusername"
password="mypassword"
database="mydatabase"

# Use double quotes to ensure variable expansion
mysql \
  --user="$user" \
  --password="$password" \
  --database="$database" \
  --execute="DROP DATABASE $user; CREATE DATABASE $database;"

# Check command execution status
if [ $? -eq 0 ]; then
    echo "Database operations completed successfully."
else
    echo "Error: MySQL command failed." >&2
    exit 1
fi

This script not only resolves variable expansion issues but also enhances error handling by checking the exit status code ($?).

Debugging Tips and Considerations

When debugging similar issues, it is recommended to use set -x to enable debug mode and view the actual commands after variable expansion. Additionally, avoid hardcoding sensitive information such as passwords in scripts; consider using environment variables or configuration files instead. For complex SQL statements, store them in external files and execute them via the --execute parameter or input redirection to improve maintainability.

Conclusion

MySQL command execution failures in Bash scripts often stem from variable expansion issues. By correctly using double quotes, developers can ensure variables are properly parsed, thereby avoiding access errors. The solutions and best practices provided in this article help enhance script reliability and security, applicable to various database automation tasks.