Change Ownership Of Definer And Triggers

• Tweet
• 

        // change definer and triggers to 'root'@'localhost'

#!/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

Tags:
• MySQL

Comments

Snippets Manager replied on Wed, 2012/03/14 - 5:07am

It was an excellent way opted by you to describe your ideas with us.I would love to read more about your newly updated posts. ===================================================================================================== ibm certifications|| ibm certification|| ibm certification exams|| ibm certification exam||

• Login or register to post comments