Activities

November 2015
M T W T F S S
« Sep   Jan »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Renaming database having innodb tables/Moving inoodb database

While you are working with Huge MySQL data, you will need to switch your tables to innodb storage engine to get fast transactions performance. Suppose if you have a requirement to restore the database which having +>400G volume and the target server having the same database name exist. So if you restore the database, the old database will become wiped out. In this scenario, you would think about renaming database having innodb tables. Innodb tables/database and other index entries stored in idbata file inside mysql data directory. The following commands will re-arrange the ibdata file entries rather moving files over the disk. So this will not consume much time and become easier.

How to move database having innoDB tables,

1. create a new database
2. Execute “ALTER TABLE old_db.tablename RENAME new_db.tablename;”

eg. ALTER TABLE hugedb.PlanData5500Raw RENAME bigdb.PlanData5500Raw;

alter_table

Suppose if you having more than 300 tables in your database, execution of this statement would become hard. So I created a script to rename the above statements.

#!/bin/bash
# this script will move all the innodb tables from old_db to new_db. Pls update the database name in db variables.
touch table_names.txt
> table_names.txt
old_db="bigdb"
new_db="hugedb"
mysql -uroot  -hlocalhost $old_db  --skip-column-names -e "SHOW TABLES" >>table_names.txt
while read table;
do
total=0
echo " Moving ${table} to $new_db now...... "
mysql -uroot  << EOF
ALTER TABLE $old_db.$table  RENAME $new_db.$table;
EOF

#mysql -e 'ALTER TABLE $old_db.$table   RENAME $new_db.$table;'
done < table_names.txt

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>