MySQL database rename script

mysql-rename-db.sh

#!/bin/bash

# Disclaimer - make backups, use at your own risk.
#
# Based on this comment: http://stackoverflow.com/a/13944924/843067
# Views and stored procedures have to be done separately.

OLDDB="old_db_name"
NEWDB="new_db_name"
MYSQL="mysql -u root -pyour_password "

$MYSQL -e "CREATE DATABASE \`$NEWDB\`;"

for table in `$MYSQL -B -N -e "SHOW TABLES" $OLDDB`
do 
  $MYSQL -e "RENAME TABLE \`$OLDDB\`.\`$table\` to \`$NEWDB\`.\`$table\`"
done

# You *might not* want to uncoment line below since
# in case of problems while renaming tables
# you will lose all database

#mysql -e "DROP DATABASE \`$OLDDB\`;"

Worked great, thanks!

I would like to see this handle view as well, but then again, it is just a gist. Worked great. Thanks!

@bean5 - never noticed that someone picked up this gist. I should probably keep maintaining it.

Thanks

Why do you use a for loop instead of mysqldump? :

mysqldump -R -uroot -p old_db|mysql -uroot -p new_db

If the DB doesn't already exist
mysql -u root -p -e "create database new_db"; mysqldump -R -uroot -p old_db | mysql -uroot -p new_db

@tadas-s thank you

I've written a SQL script for the same since sql would definitely be faster than bash on a DB with a large number of tables.
To use it simply call the renameDatabase procedure.

CALL renameDatabase('old_name', 'new_name');

Tested on MariaDB and should work ideally on all RDBMS using InnoDB transactional engine.
Hopefully, it comes useful for others!