Updated by Juan Riano 2018-09-21

File clean_mysql_definers.sh Modified

 # (By|Maintained by) Juan Riano

-# Script name: clean_definers

+# Script name: clean_mysql_definers.sh

 # Usage: sudo $0 dbname.sql[.gz]

 # Usage: sudo $0 [-h|-help]

Created by Juan Riano 2018-09-21

View revision

File clean_mysql_definers.sh Added

+#!/bin/bash

+#set -x

+#

+# (By|Maintained by) Juan Riano

+#

+# Script name: clean_definers

+#

+# 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