Cacomania: MySQL rename a database with bash

Cacomania

MySQL rename a database with bash

Guido Krömer - 4. February 2013 - Tags: ,

Renaming an MySQL table is straightforward with RENAME TABLE, but renaming a whole database with RENAME DATABASE has been removed in MySQL 5.1.23 because it could be dangerous.

Since I needed this functionality some lines of bash does nearly the same by creating a new database and renaming each table like this: RENAME TABLE old_database.my_table new_database.my_table.

#!/bin/bash
execute_sql () {
  echo "$1" | mysql
}

rename_database () {
  FROM=$1
  TO=$2

  execute_sql "CREATE DATABASE IF NOT EXISTS $TO"

  TABLES=`execute_sql "SHOW TABLES FROM $FROM"`

  for TABLE in $TABLES; do
    execute_sql "RENAME TABLE $FROM.$TABLE TO $TO.$TABLE"
  done;

  echo "Done, drop the old database ($FROM) if needed."
}

rename_database $1 $2