Script for Rename Database

rename_db.sh

#!/bin/bash
# Copyright 2015 Sugeng Suprayogi
set -e
if [ -z "$5" ]; then
    echo "rename_db <server> <user> <password> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -u $2 -p$3 -e "show databases like '$5'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $5"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -u $2 -p$3 -e "show create database $4\G" -sss | grep ^Create | awk -F'CHARACTER SET ' '{print $2}' | awk '{print $1}'`
TABLES=`mysql -h $1 -u $2 -p$3 -e "select TABLE_NAME from information_schema.tables where table_schema='$4' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from $4"
    exit 1
fi
echo "create database $5 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -u $2 -p$3 -e "create database $5 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 -u $2 -p$3 $4 -e "show triggers\G" | grep Trigger: | awk '{print $4}'`
VIEWS=`mysql -h $1 -u $2 -p$3 -e "select TABLE_NAME from information_schema.tables where table_schema='$4' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h $1 -u $2 -p$3 $4 $VIEWS > /tmp/${4}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 -u $2 -p$3 $4 -d -t -R -E > /tmp/${4}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 -u $2 -p$3 $4 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table $4.$TABLE to $5.$TABLE"
    mysql -h $1 -u $2 -p$3 $4 -e "SET FOREIGN_KEY_CHECKS=0; rename table $4.$TABLE to $5.$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 -u $2 -p$3 $5 < /tmp/${4}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 -u $2 -p$3 $5 < /tmp/${4}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -u $2 -p$3 -e "select TABLE_NAME from information_schema.tables where table_schema='$4' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $4"
    mysql -h $1 -u $2 -p$3 $4 -e "drop database $4"
fi
if [ `mysql -h $1 -u $2 -p$3 -e "select count(*) from mysql.columns_priv where db='$4'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$5' WHERE db='$4';"
fi
if [ `mysql -h $1 -u $2 -p$3 -e "select count(*) from mysql.procs_priv where db='$4'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$5' WHERE db='$4';"
fi
if [ `mysql -h $1 -u $2 -p$3 -e "select count(*) from mysql.tables_priv where db='$4'" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$5' WHERE db='$4';"
fi
if [ `mysql -h $1 -u $2 -p$3 -e "select count(*) from mysql.db where db='$4'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$5' WHERE db='$4';"
fi
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"
fi