We have a sharded MySQL infrastructure at work where we sometimes create new shards from a .sql file. Each shard has all the same tables/triggers/functions, etc but the data is unique to the customer for which that shard is assigned to. This file is created from an alpha environment which has different users than production. This started to result in a situation where we are getting definers for triggers/functions on production but for users that do not exist. I wrote a script to send alerts for these but manually fixing them was getting annoying. The permanent fix so that doesn't happen anymore is in the works but the bureaucracy at work is taking too long so I wrote a bash script to fix them automatically on production. A portion of my bash script was inspired from this site:
http://codersresource.com/news/dzone-snippets/change-ownership-of-definer-and-triggers
Here is the script from the above site:
#!/bin/sh
host='localhost'
user='root'
port='3306'
# following should be the root@localhost password
password='root@123'
# triggers backup
mysqldump -h$host -u$user -p$password -P$port --all-databases -d --no-create-info > triggers.sql
if [[ $? -ne 0 ]]; then exit 81; fi
# stored procedure backup
mysqldump -h$host -u$user -p$password -P$port --all-databases --no-create-info --no-data -R --skip-triggers > procedures.sql
if [[ $? -ne 0 ]]; then exit 91; fi
# triggers backup
mysqldump -h$host -u$user -p$password -P$port --all-databases -d --no-create-info | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql
if [[ $? -ne 0 ]]; then exit 101; fi
# drop current triggers
mysql -h$host -u$user -p$password -P$port -Bse"select CONCAT('drop trigger ', TRIGGER_SCHEMA, '.', TRIGGER_NAME, ';') from information_schema.triggers" | mysql -h$host -u$user -p$password -P$port
if [[ $? -ne 0 ]]; then exit 111; fi
# Restore from file, use root@localhost credentials
mysql -h$host -u$user -p$password -P$port < triggers_backup.sql
if [[ $? -ne 0 ]]; then exit 121; fi
# change all the definers of stored procedures to root@localhost
mysqldump -h$host -u$user -p$password -P$port --all-databases --no-create-info --no-data -R --skip-triggers | sed -e 's/DEFINER=[^*]*\*/\*/' | mysql -h$host -u$user -p$password -P$port
if [[ $? -ne 0 ]]; then exit 131; fi
My script was different but the idea was the same. However, it blew up and this is a bad idea. I tested it several times on a non-prod and it was working pretty good. My testing environment only had about a dozen databases. The particular MySQL server that has over 1500 databases. For whatever reason that I haven't been able to pinpoint, running a query on the triggers table takes 20 minutes. That little portion in the above script that creates a DROP triggers query with the SELECT concat and then sends the results back into another mysql session is not a good idea! It will cause queries to get locked up and hold up replication. It created slave lag in our clustered environment which got really far behind. There are plenty of other problem with our MySQL infrastructure which are out of my control which also contributed to this situation.
Long story short is that I'll be re-writing my version of the script to use SHOW TRIGGERS/SHOW FUNCTION STATUS/SHOW PROCEDURE STATUS because those commands run much faster. However, I'll have to loop over every single database and limit my query to only that database.
No comments:
Post a Comment