Batch execution of SQL scripts using Shell scripts

Script Example

#!/bin/bash

# Configure database and user informationDATABASES=("Database1" "Database2" "Database3" "Database4" "Database5")
USERS=("user1" "user2" "user3" "user4" "user5" "user6")
PASSWORD="your_password" # Assume that all users use the same passwordSQL_SCRIPT="upgrade_script.sql" # SQL script path
# Database execution functionexecute_sql() {
    local db=$1
    local user=$2
    local sql=$3
    echo "Executing script on database: $db, user: $user..."
    mysql -h localhost -u "$user" -p"$PASSWORD" "$db" &lt; "$sql"
    if [[ $? -ne 0 ]]; then
        echo "Error: Execution failed on $db for user $user" &gt;&amp;2
        return 1
    fi
    echo "Success: Executed script on $db for user $user"
}

# Main loop: execute SQL for each database and userfor db in "${DATABASES[@]}"; do
    for user in "${USERS[@]}"; do
        execute_sql "$db" "$user" "$SQL_SCRIPT"
    done
done

echo "All scripts executed successfully!"

Workflow

1. Configuration part:

DATABASES Lists all target databases.
USERS lists all users who need to execute SQL scripts.
PASSWORD is the user's unified password, and the script uses the -p parameter to pass the password.
SQL_SCRIPT is the file path to the SQL script.

2. Function definition:

The execute_sql function connects to the database and executes scripts through the mysql command.
Check the command return value $?, if there is any error, the failure message will be output to the standard error.

3. Main loop:

The outer layer loops through each database.
The inner layer loops through each user.
Execute the execute_sql function for each database-user.

4. Logging:

Print execution progress when executing scripts.
Success and failure information are output to standard output and standard error respectively.

Things to note

Idepotency of SQL scripts:

Make sure that the SQL script is idempotent (multiple executions have no repetitive effects).
If necessary, you can add IF NOT EXISTS and other judgment conditions to the SQL script.

Database and user permissions:

Make sure all users have execution permissions to the target database, otherwise a permission error will occur.

MySQL Password Management:

There may be security risks in the clear text storage of passwords in scripts, and you can use . files to manage credentials instead:

[client]
user=user1
password=your_password

Then when called, it is simplified to:

mysql Database1 < upgrade_script.sql

Script execution path:

Make sure that the SQL_SCRIPT file path is correct when the script is executed. If the script is running in a different directory, it is recommended to use an absolute path.

Perform error handling:

If a database or user fails to execute, it is recommended that the script continue to run and record the failed database and user for subsequent retry.

Multithreaded optimization (optional):

If database and server performance allows, concurrent execution can be used to improve efficiency:

for db in "${DATABASES[@]}"; do
    for user in "${USERS[@]}"; do
        execute_sql "$db" "$user" "$SQL_SCRIPT" &
    done
done
wait

Probable problems

1. Script execution failed:

Cause: The script content is incorrect, the database user has no permissions, network problems, etc.

Solution: View the failure log, correct SQL scripts or user permissions.

Execution timeout:

If the script is very large or the query takes a long time, a timeout problem may occur.

Solution: Adjust the max_allowed_packet and wait_timeout parameters in MySQL.

3. Password leakage risk:

There are security risks when passwords are stored in scripts.

It is recommended to use . or environment variables to store passwords instead.

4. Conflicts of concurrent execution:

Concurrent runs may lead to locked tables or resource competition.

Solution: Control the number of concurrency, or execute one by one in order.

Guide to grant database execution permissions to all users

Step 1: Clarify permission requirements

Confirm the type of permissions you need to grant. For the requirement to execute SQL scripts, EXECUTE or other relevant permissions (such as SELECT, UPDATE, INSERT, DELETE) are usually required.

Confirm which users need permissions.

Step 2: SQL Syntax Example

Assuming the target database is named target_db, you need to grant permissions to 6 users of each of the 5 databases. The following is the common GRANT syntax:

USE target_db;

-- Example：For users user1 Grant EXECUTE Permissions
GRANT EXECUTE ON DATABASE target_db TO user1;

-- Example：If still needed SELECT、INSERT Permissions：
GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE target_db TO user1;

Step 3: Grant permissions in batches for all users

Assuming there are multiple users and multiple databases that can be processed with script loops, the following is a manual SQL example:

-- Grant permissions for each user cycle under the target database
USE target_db;

GRANT EXECUTE ON DATABASE target_db TO user1;
GRANT EXECUTE ON DATABASE target_db TO user2;
GRANT EXECUTE ON DATABASE target_db TO user3;
GRANT EXECUTE ON DATABASE target_db TO user4;
GRANT EXECUTE ON DATABASE target_db TO user5;
GRANT EXECUTE ON DATABASE target_db TO user6;

Step 4: Batch execution of GRANT using Shell scripts

Script content

Suppose we use the mysql client login to perform these SQL authorization operations in batches:

#!/bin/bash

# Database configurationHOST="localhost"
USER="root"
PASSWORD="your_password"

# Database and user listDATABASES=("db1" "db2" "db3" "db4" "db5")
USERS=("user1" "user2" "user3" "user4" "user5" "user6")

# Authorization scriptfor DB in "${DATABASES[@]}"; do
    for USER in "${USERS[@]}"; do
        echo "Granting EXECUTE privilege on $DB to $USER..."
        mysql -h "$HOST" -u "$USER" -p"$PASSWORD" -e "GRANT EXECUTE ON $DB.* TO '$USER';"
    done
done

echo "All privileges granted!"

Things to note

1. Permission verification:

Make sure the target user already exists in the database. If the user does not exist, you need to create the user through CREATE USER first.

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';

PRIVILEGES：

Some databases need to run FLUSH PRIVILEGES after permissions are updated to refresh the permission table.

FLUSH PRIVILEGES;

3. Permission scope:

If you only need to grant permissions to a specific table, you can use GRANT EXECUTE ON db_name.table_name.

4. Error handling:

If an Access Denied error occurs in the script, it may be that the user permissions currently executing the script are insufficient. Make sure that the script runner has sufficient permissions (such as GRANT OPTION permission).

5. Repeated authorization:

MySQL and other databases usually do not make errors due to repeated execution of GRANT, so you can safely execute scripts in batches.

Frequently Asked Questions

There is no error in the user:

It is necessary to ensure that all users have been created. If you need to create users automatically, you can extend the Shell script and add CREATE USER.

Insufficient permissions:

Ensure that the user running the script (such as root) has GRANT OPTION permission.

Multi-database environment:

If multiple databases exist, make sure that the user is granted all required database permissions.

The above is the detailed content of using Shell scripts to batch execute SQL scripts. For more information about Shell batch execution of SQL, please pay attention to my other related articles!