36

Hi I am using mysql 5.0.x

How do I rename a schema?

Charles Faiga
  • 11,665
  • 25
  • 102
  • 139

3 Answers3

73

Don't use RENAME DATABASE!!!

Early 5.1 versions had this command but it's been removed since it can corrupt data (reference).

The only way at present is 

mysqladmin create new_db_name
mysqldump db_name | mysql new_db_name

as referred to here

Edit: Obviously this answer will become dated once this gets fixed.

Community
  • 1
  • 1
pufferfish
  • 16,651
  • 15
  • 56
  • 65
  • Dumping the content from my old schema and importing it into new one (as described above) transferred my tables and views but not functions! – sbrbot Apr 04 '15 at 20:33
  • 1
    If a username & password is needed, this can be modified to: `mysqladmin -u root -pmypassword create new_db_name` followed by `mysqldump -u root -pmypassword db_name | mysql -u root -pmypassword new_db_name`. If a rename rather than copy is required, complete it with `mysqladmin -u root -pmypassword drop db_name`. – Steve Chambers Jan 13 '16 at 16:49
  • Database charset didn't get transferred either. e.g. charset for my old db was utf8 and new db is latin1. – Hussain Jan 18 '16 at 12:17
4

It's easy. Export the database to file, then import it again in workbench you can specify the name of the db there.

In workbench go to the Server tab, select Data Export. Select the DB you want to rename, select export to self contained file, and give the file a name. make sure you have Dump structure and Data selected. Hit start export.

In workbench go to the Server tab, select Data Import. Select Import from self contained file. Select the file you created. In the section titled Default Schema to be imported to click the new option. Enter the new name for the DB, then select it from the drop down. Hit Start Import.

Voila, new database with the name you want plus all the tables and data from the old one.

OmegaOdie
  • 373
  • 2
  • 8
-7

Under 5.0, you need to dump the database and reload it into a new database with a new name. If you upgrade to 5.1, you can lookup and use the RENAME DATABASE command.

Grant Limberg
  • 20,913
  • 11
  • 63
  • 84