Rename Database in MySQL

Today I am writing about how to rename MySQL Database.

So If you want to rename a database in MySQL dont forget taking backup of your Views,Procedures and Functions of current database. Because while you renaming changes wont apply for Views,Procedures and functions.

After you have taken the backup , We can go for renaming with the command below.

RENAME TABLE old_db.table TO new_db.table;
Here old_db is your old database name and new_db is your new database name. You have to execute this command for each table. And this is the way.

But what if we have a big database with 1000 tables ? executing the same above command for 1000 times is a stupid idea.

If you are using Linux you can use following BASH script to do that for you

for table in $(mysql -p -s -N -e "show tables from db1"); do mysql -s -N -e "rename table db1.$table to db2.$table"; done;

db1 is your old database name and db2 is your new database name and remaining everything will tool care by script only except giving password.:P

After executing the command successfully without any errors , you must have to restore that backup of Views,Proc's and Functions.

Errors: I have got one error with triggering. Generally some tables have triggers. So you copy the code of triggers and then delete those triggers. Then try to rename the table again.

After it got successful , just recreate the triggers for the same tables in new database.

I have done this trigger creation in MYSQL Workbench.

If you want to backup only Functions and not data of database then you can use this command
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt  <database> > outputfile.sql
Here : 

--routines                 =       for including routines ( views,functions,procedures)
--no-create-info       =       Do not write CREATE TABLE statements that re-create each dumped
                              table.
--no-data                  =        Do not write any row information for the table. This is very useful
                        if you want to get a dump of only the structure for a table.
 --no-create-db         =        This option suppresses the CREATE DATABASE /*!32312 IF NOT EXISTS*/
                        db_name statements that are otherwise included in the output if the
--databases or --all-databases option is given.
--skip-out        = --opt
                    This option is shorthand; it is the same as specifying
--add-drop-table --add-locks --create-options --disable-keys
--extended-insert --lock-tables --quick --set-charset. It should
give you a fast dump operation and produce a dump file that can be
reloaded into a MySQL server quickly. As of MySQL 4.1, --opt is on
by default, but can be disabled with --skip-opt. To disable only
certain of the options enabled by --opt, use their --skip forms; for
example, --skip-add-drop-table or --skip-quick.




0 Comments