Hi everyone,
I am facing a challenge in my MySQL Galera Cluster setup while trying to generate and execute GRANT statements for a user with a bash script.
Here’s a brief overview of my setup and the script I’m using:
#!/bin/bash
# Define variables
DB_USER="testuser" # Existing user
DB_HOST="%" # Allow connections from any host
GRANTS_FILE="/tmp/grants.sql"
# Start a new grants SQL file
echo "-- GRANT statements for all non-system databases" > $GRANTS_FILE
# Get the list of non-system databases
databases=$(mysql -Bse "SHOW DATABASES;")
for db in $databases; do
# Exclude system databases
if [[ "$db" != "information_schema" && "$db" != "performance_schema" && "$db" != "mysql" && "$db" != "sys" ]]; then
# Get the tables in the database
tables=$(mysql -Bse "SHOW TABLES IN \`${db}\`;")
for table in $tables; do
# Generate GRANT statement
echo "GRANT SELECT ON \`${db}\`.\`${table}\` TO '${DB_USER}'@'${DB_HOST}';" >> $GRANTS_FILE
done
fi
done
# Execute the generated GRANT statements
mysql < $GRANTS_FILE
echo "GRANT operations completed successfully for user '$DB_USER' with access from '$DB_HOST'!"
The script generates a total of approximately 105,000 GRANT statements, such as:
GRANT SELECT ON example_database_1.database_log TO 'testuser'@'%';
GRANT SELECT ON example_database_1.database_lock TO 'testuser'@'%';
GRANT SELECT ON example_database_1.sandbox_5208 TO 'testuser'@'%';
...
However, when executing the commands with mysql < $GRANTS_FILE, I encounter the following error when trying to verify the grants from another session:
mysql> SHOW GRANTS FOR 'testuser'@'%';
ERROR 2027 (HY000): Malformed packet
No connection. Trying to reconnect...
Connection id: 109926
Current database: *** NONE ***
ERROR 2027 (HY000): Malformed packet
Interestingly, checking grants for other users works fine, but I only receive the “ERROR 2027 (HY000): Malformed packet” message for ‘testuser’ where grant script applyting the grant to this ‘testuser’. This issue does not persists after the GRANT script finishes running or aborted.
Could anyone please help me understand the potential root cause of this issue and how I might resolve it?
Thank you for your assistance!