Renaming Database

[chaparral]

Can anyone out there please tell me how to rename a database?

[peterlaursen]

It is not possible! It is not a restriction with SQLyog, but with MySQL itself. The reason is that the database names exists not only in MySQL tables but as filenames in the filesystem as well. Changing both system tables and filenames is not without problems in case of a server crash in between. You may be left with nothing then! So for security reasons a 'rename DB' is not available.

However, you can copy your database to a new one in different ways. At least:

1) make a backup with SQLyog and change the 'use' SQL-statment in the dump-file to new DB-name before importing.

2) create a new empty database and “copy DB to other host” using SQLyog. Don't be confused – it works with two DBs on the same host as well! When it is done you can drop the old one.

But no matter how you do it involves copying all data, and it will take a lot of time with big DBs. And if you have ONE DB ONLY at a webhost you will need to backup, drop current DB, change 'use'-stmt and import. Be sure to use a 'Bulk Insert' setting that you know works with the server configuration. Probably it is safest NOT to use BULK INSERTS at all! Though that would increment the time for restore.

I think I read in the MySQL that they plan to implement a 'rename DB' (in MySQL 5.1 I believe), but it will still be a create-copy-drop operation (but implemented in one command) – not just a rename operation. And it will still take a lot (the same) amount of time.

[chaparral]

I'm presently using SQLyog to design a new database on my personal IIS webserver and so data volume is not an issue.

Thankyou Peter, for your quick and clear answer.

Chuck Milbourne, Los Angeles, CA

[cathyli99]

Is it also impossible to change the column name of the table?

peterlaursen wrote on Sep 27 2005, 09:18 AM:

It is not possible!  It is not a restriction with SQLyog, but with MySQL itself.  The reason is that the database names exists not only in MySQL tables but as filenames in the filesystem as well.  Changing both system tables and filenames is not without problems in case of a server crash in between.  You may be left with nothing then!  So for security reasons a 'rename DB' is not available.

However, you can copy your database to a new one in different ways.  At least:

1) make a backup with SQLyog and change the 'use' SQL-statment in the dump-file to new DB-name before importing.

2) create a new empty database and “copy DB to other host” using SQLyog.  Don't be confused – it works with two DBs on the same host as well!  When it is done you can drop the old one.

But no matter how you do it involves copying all data, and it will take a lot of time with big DBs.  And if you have ONE DB ONLY at a webhost you will need to backup, drop current DB, change 'use'-stmt and import.  Be sure to use a 'Bulk Insert' setting that you know works with the server configuration.  Probably it is safest NOT to use BULK INSERTS at all!  Though that would increment the time for restore.

I think I read in the MySQL that they plan to implement a 'rename DB' (in MySQL 5.1 I believe), but it will still be a create-copy-drop operation (but implemented in one command) – not just a rename operation.  And it will still take a lot (the same) amount of time.

[post=”7318″]<{POST_SNAPBACK}>[/post]

[vygi]

cathyli99 wrote on Oct 3 2005, 01:45 PM:

Is it also impossible to change the column name of the table?

[post=”7398″]<{POST_SNAPBACK}>[/post]

It must be easily possible:

ALTER TABLE tablename CHANGE COLUMN oldname newname column_definition

so it should be also easily possible via SQLyog

[peterlaursen]

Quote:

so it should be also easily possible via SQLyog

It is! use ALTER TABLE (from menu or popup menu) and type in the new name

[Ritesh]

For the sake of completion, you can rename a table by selecting the table in the Object Browser and pressing F2 to rename it 😀

[Author]

Posts