[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]

[Author]

Posts