Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/local/bin/bash
- #
- # Description: Dump all triggers of a database, each in a seperate file. See below which parameters to use. At execution it will ask for the MySQL password
- #
- #
- usageMessage=$(cat << EOF
- usage: mysqldump_trigger.sh [-h hostname] [-p port] [-u user] [-d database] [-o outfolder] [\?]
- Dump all triggers of MySQL database schema to files. Each trigger will be put in a seperate file. At execution there will be asked for the password of the SQL user.
- -h : Hostname of de MySQL server
- -p : Port number of the MySQL server
- -u : MySQL user
- -d : Database name of which the triggers will be dumped
- -o : Output folder of the trigger.sql files. Each file will have the name of the trigger.
- EOF
- )
- # INITIALIZING variables
- MYSQL=`which mysql`
- newline=$(echo -e '\r')
- if (( $OPTIND == 1 )); then
- echo "${usageMessage}"
- exit 1
- fi
- # Parsing commandline arguments
- while getopts ":u:h:p:d:o:H" opt; do
- case ${opt} in
- h ) # host
- host=$OPTARG
- ;;
- p ) # port
- port=$OPTARG
- ;;
- u ) # user
- user=$OPTARG
- ;;
- d ) # database
- database=$OPTARG
- ;;
- o )
- outputFolder=$OPTARG
- mkdir -p "${outputFolder}"
- ;;
- \? ) #Help
- echo "Invalid option ${OPTARG}"
- exit 1
- ;;
- : ) # parameter is missing a argument
- echo "Invalid option: ${OPTARG} requires an argument" 1>&2
- exit 1
- ;;
- esac
- done
- shift $((OPTIND -1))
- # ask for password. Will not be shown in the terminal
- read -sp "Enter password: " password
- MYSQL_PARAMS="-u ${user} --password=${password} -h ${host} -P ${port} -D ${database}"
- # retrieve all the triggers of a database schema and loop for the the CREATE statement of each of them
- "${MYSQL}" ${MYSQL_PARAMS} -NBe "SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = '${database}'" -D INFORMATION_SCHEMA | while read -a row;
- do
- trigger="${row[0]}"
- while read -r line; do
- triggerName=$(echo $line | awk '{print $1}')
- value=$(echo $line | awk '{print $1=""; print $0}' | sed ':a;N;$!ba;s/\n/ /g'| sed 's/^[ \t]*//g' | sed "s/STRICT.*trigger/DELIMITER ;;\\${newline}CREATE TRIGGER/g" | sed "s/end utf8 utf8_general_ci utf8_general_ci/\\${newline}END;;\\${newline}DELIMITER ;/g")
- echo "Exporting $triggerName"
- echo "$value" >> "${outputFolder}/${triggerName}.sql"
- done< <("${MYSQL}" ${MYSQL_PARAMS} -NBe "SHOW CREATE TRIGGER ${trigger}")
- done;
Add Comment
Please, Sign In to add comment