Dump all triggers of a MySQL database, each in a seperate file

mysqldump_trigger.sh

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