Skip to content

Instantly share code, notes, and snippets.

@t-anjan
Forked from ryantm/rename_db
Last active December 11, 2019 20:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save t-anjan/0bfd0c14824814fb09975ee855164b3f to your computer and use it in GitHub Desktop.
Save t-anjan/0bfd0c14824814fb09975ee855164b3f to your computer and use it in GitHub Desktop.
Shell script to rename a database, based on https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/ - Please note you need to ensure you have access to MySQL via mysql_config_editor (https://www.percona.com/blog/2014/11/25/more-secure-local-passwords-in-mysql-5-6-and-up/).
#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
# @see https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/
set -e
if [ -z "$3" ]; then
echo "rename_db <server> <database> <new_database>"
exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
echo "ERROR: New database already exists $3"
exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = '$2'" -sss`
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
echo "Error retrieving tables from $2"
exit 1
fi
echo "create database $3 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 $2 -e "show triggers\G" | grep Trigger: | awk '{print $2}'`
VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 $2 --no-data --no-create-info --triggers --routines --events --set-gtid-purged=OFF > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
echo "drop trigger $TRIGGER"
mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
echo "rename table $2.$TABLE to $3.$TABLE"
mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
done
if [ -n "$VIEWS" ]; then
echo "loading views"
mysql -f -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -f -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
echo "Dropping database $2"
mysql -h $1 $2 -e "drop database $2"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
echo "Updating column privileges."
mysql -h $1 -e "UPDATE mysql.columns_priv set db='$3' WHERE db='$2'"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
echo "Updating procs privileges."
mysql -h $1 -e "UPDATE mysql.procs_priv set db='$3' WHERE db='$2'"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
echo "Updating tables privileges."
mysql -h $1 -e "UPDATE mysql.tables_priv set db='$3' WHERE db='$2'"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
echo "Updating DB privileges."
mysql -h $1 -e "UPDATE mysql.db set db='$3' WHERE db='$2'"
fi
echo "Flushing privileges."
mysql -h $1 -e "flush privileges"
echo "All done."
@t-anjan
Copy link
Author

t-anjan commented Feb 6, 2018

Updated the script to handle DBs with GTID switched on.

And running the "GRANT" changes automatically at the end, instead of leaving it optional to the user.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment