Snippets

Juan Riano Mysql Definer Cleaner

Created by Juan Riano last modified
#!/bin/bash
#set -x

#
# (By|Maintained by) Juan Riano
#

# Script name: clean_mysql_definers.sh
#
# Usage: sudo $0 dbname.sql[.gz]
# Usage: sudo $0 [-h|-help]
#
# What does it do?
# Takes a mysql db dump (even .gz) and updates all DEFINERs to be root

###########################################################

usage() {
    echo "Usage: $0 [-h|-help]"
    echo "Usage $0 dbname"
    echo "Takes a mysql.sql or mysql.sql.gz database dump and updates all DEFINERs to be root"
}

# Minimal parameters check
if ! [ "$#" = 1 ]; then
    usage
    exit
fi

# Show help
if [[ "${1}" =~ ^(help|-help|h|-h)$ ]]; then
    usage
    exit 0
fi


###########################################################

DB=$1
DBDIR=$( dirname "${DB}" )
DBBASENAME=$( basename "${DB}" )
DBNAME=$( echo "${DBBASENAME%.*}" )     # ecluding the .gz if existing
DBTYPE=$( echo "${DBBASENAME##*.}" )

# Extract if a .gz
if [ "${DBTYPE}" == "gz" ]; then
    echo "Unzipping ${DB}..."
    gunzip "${DB}"
    DB="${DBDIR}/${DBNAME}"
    echo "Unzipped, new file: ${DB}"
fi

# Find definers other than root and admin, and replace them by root
NEWDEFINER='DEFINER=`root`'
DEFINERS=$( grep -oE "DEFINER=\`[a-zA-Z]+\`" "${DB}" | grep -viE "DEFINER=\`(root|admin)\`" | sort | uniq )
DEFINERCOUNT=0
if ! [ -z "${DEFINERS}" ]; then
    DEFINERCOUNT=$( echo "${DEFINERS}" | wc -l )
    # Put definers in a single line after trimming (sed breakes without trimming!)
    DEFINERS=$( echo "${DEFINERS}" | awk 1 ORS=' ' |  awk '{$1=$1}1' )
fi

if [[ "${DEFINERCOUNT}" = "0" ]]; then
    echo "No definers to fix"
    exit 0
fi

echo "Cleaning definers, ${DEFINERCOUNT} found..."
echo "${DEFINERS}"
for d in "${DEFINERS}"; do
    echo "Replacing ${d} -with- ${NEWDEFINER}"
    sed -i.bak "s/${d}/${NEWDEFINER}/g" "$DB"   # .bak is a hack to make it work in Mac
    echo "Cleaned ${d}"
done
# Remove temp file and finish
rm "${DB}.bak"
echo "Job done"
exit 0

Comments (0)