Thursday, August 27, 2009

How do I quickly rename a mysql database (change schema name)?

SkyHi @ Thursday, August 27, 2009
three options:

1) create the new database, bring down the server, move the files from one database folder to the other, and restart the server. note that this will only work if ALL of your tables are myisam.

2) create the new database, use CREATE TABLE ... LIKE statements, then use INSERT ... SELECT * FROM statements.

3) use mysqldump and reload with that file.


3 vote down

the simple way

Change to DB directory:

cd /var/lib/mysql/

Shut down SQL...this is important!

/etc/init.d/mysql stop

Okay,this way doesn't work for InnoDB or BDB-Databases

Rename Database:

mv old-name new-name

...or the table...

cd database/

mv old-name.frm new-name.frm

mv old-name.MYD new-name.MYD

mv old-name.MYI new-name.MYI

Restart MySQL

/etc/init.d/mysql start


OK,this way doesn't work with InnoDB or BDB-DBs. In this case you have to dump the db and re-import it


In MySQL Administrator do the following:

1. Under Catalogs, create a new database schema.
2. Go to Backup and create a backup of the old schema.
3. Execute backup.
4. Go to Restore and open the file created in step 3.
5. Select 'Another Schema' under Target Schema and select the new database schema.
6. Start Restore.
7. Verify new schema and, if it looks good, delete the old one.


mysqldump -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql


When you rename a database in PHPMyAdmin it creates a dump, then drops and recreates the database with the new name.