xRg3c58P

1#!/bin/bash
2
3# Copyright 2013 Percona LLC and/or its affiliates
4
5POSITIONAL=()
6while [[ $# -gt 0 ]]
7do
8key="$1"
9
10case $key in
11    -h|--host)
12    HOST="$2"
13    shift # past argument
14    shift # past value
15    ;;
16    -u|--user)
17    USER="$2"
18    shift # past argument
19    shift # past value
20    ;;
21    -p|--password)
22    PASSWORD="$2"
23    shift # past argument
24    shift # past value
25    ;;
26    --no-password)
27    NOPW=YES
28    shift # past argument
29    ;;
30    --help)
31    HELP=YES
32    shift # past argument
33    ;;
34    *)    # unknown option
35    POSITIONAL+=("$1") # save it in an array for later
36    shift # past argument
37    ;;
38esac
39done
40
41set -- "${POSITIONAL[@]}" # restore positional parameters
42DBFROM=$1
43DBTO=$2
44
45echo "Renaming database ${DBFROM} to ${DBTO} on host ${HOST} with user name ${USER}..."
46if [ -n "$NOPW" ]; then
47	echo "Not using password."
48else
49	echo "Using password."
50fi
51
52if [ -n "$HELP" ]; then
53    echo "rename_db <old_db> <new_db> -h <server> -u <user> -p <password> or --no-password"
54    exit 1
55fi
56
57if [ -n "$NOPW" ]; then
58    MYSQL="mysql -h $HOST -u $USER"
59else
60    MYSQL="mysql -h $HOST -u $USER -p$PASSWORD"
61fi
62
63db_exists=`$MYSQL -e "show databases like '$DBTO'" -sss`
64if [ -n "$db_exists" ]; then
65    echo "ERROR: New database already exists: $DBTO"
66    exit 1
67fi
68echo "OK: New database doesn't exist"
69
70TIMESTAMP=`date +%s`
71character_set=`$MYSQL -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = '$DBFROM'" -sss`
72TABLES=`$MYSQL -e "select TABLE_NAME from information_schema.tables where table_schema='$DBFROM' and TABLE_TYPE='BASE TABLE'" -sss`
73STATUS=$?
74if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
75    echo "Error retrieving tables from $DBFROM"
76    exit 1
77fi
78echo "create database $DBTO DEFAULT CHARACTER SET $character_set"
79$MYSQL -e "create database $DBTO DEFAULT CHARACTER SET $character_set"
80TRIGGERS=`$MYSQL $DBFROM -e "show triggers" | grep Trigger: | awk '{print $DBFROM}'`
81VIEWS=`$MYSQL -e "select TABLE_NAME from information_schema.tables where table_schema='$DBFROM' and TABLE_TYPE='VIEW'" -sss`
82if [ -n "$VIEWS" ]; then
83    mysqldump -h $HOST $DBFROM $VIEWS > /tmp/${DBFROM}_views${TIMESTAMP}.dump
84fi
85mysqldump -h $HOST $DBFROM -d -t -R -E > /tmp/${DBFROM}_triggers${TIMESTAMP}.dump
86for TRIGGER in $TRIGGERS; do
87    echo "drop trigger $TRIGGER"
88    $MYSQL $DBFROM -e "drop trigger $TRIGGER"
89done
90for TABLE in $TABLES; do
91    echo "rename table $DBFROM.$TABLE to $DBTO.$TABLE"
92    $MYSQL $DBTO -e "SET FOREIGN_KEY_CHECKS=0; rename table $DBFROM.$TABLE to $DBTO.$TABLE"
93done
94if [ -n "$VIEWS" ]; then
95    echo "Loading views..."
96    $MYSQL $DBTO < /tmp/${DBFROM}_views${TIMESTAMP}.dump
97    echo "Done."
98fi
99echo "Loading triggers, routines and events..."
100$MYSQL $DBTO < /tmp/${DBFROM}_triggers${TIMESTAMP}.dump
101TABLES=`$MYSQL -e "select TABLE_NAME from information_schema.tables where table_schema='$DBFROM' and TABLE_TYPE='BASE TABLE'" -sss`
102if [ -z "$TABLES" ]; then
103    echo "Dropping database $DBFROM"
104    $MYSQL $DBFROM -e "drop database $DBFROM"
105fi
106if [ `$MYSQL -e "select count(*) from mysql.columns_priv where db='$DBFROM'" -sss` -gt 0 ]; then
107    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$DBTO' WHERE db='$DBFROM';"
108fi
109if [ `$MYSQL -e "select count(*) from mysql.procs_priv where db='$DBFROM'" -sss` -gt 0 ]; then
110    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$DBTO' WHERE db='$DBFROM';"
111fi
112if [ `$MYSQL -e "select count(*) from mysql.tables_priv where db='$DBFROM'" -sss` -gt 0 ]; then
113    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$DBTO' WHERE db='$DBFROM';"
114fi
115if [ `$MYSQL -e "select count(*) from mysql.db where db='$DBFROM'" -sss` -gt 0 ]; then
116    DB_PRIV="    UPDATE mysql.db set db='$DBTO' WHERE db='$DBFROM';"
117fi
118echo "Rename Done."
119if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
120    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
121    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
122    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
123    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
124    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
125    echo "    flush privileges;"
126fi