Renaming a database?
It looks like there’s no way to rename a database. Is that true? I have two reasons for wanting to do this: - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. - I think I have a database that isn’t being used (don’t ask), and I want to hide it to see if anything blows up. Suggestions? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com
Re: Renaming a database?
Instead of renaming it, revoke permissions to it. If it's being used you should see some problems in the application due to access denied. Andy On Tue, May 24, 2011 at 4:19 PM, Jerry Schwartz je...@gii.co.jp wrote: It looks like there’s no way to rename a database. Is that true? I have two reasons for wanting to do this: - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. - I think I have a database that isn’t being used (don’t ask), and I want to hide it to see if anything blows up. Suggestions? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com
Re: Renaming a database?
- Original Message - From: Jerry Schwartz je...@gii.co.jp It looks like there’s no way to rename a database. Is that true? Yes. Silly oversight, although there's probably complexity reasons behind it. - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. That precludes rename table db1.blah to db2.blah, I guess - it basically copies the whole thing and then kills the old one. Sloww for big tables :-) - I think I have a database that isn’t being used (don’t ask), and I want to hide it to see if anything blows up. If you are in a position to shut your server down, you may rename the database directory while it's down IF you only use MyISAM tables. If you have InnoDB tables, you're stuck with dump/reload or rename table. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Renaming a database?
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, May 24, 2011 11:52 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Renaming a database? - Original Message - From: Jerry Schwartz je...@gii.co.jp It looks like there's no way to rename a database. Is that true? Yes. Silly oversight, although there's probably complexity reasons behind it. [JS] According to the documentation, this was implemented at one time but it caused problems so the feature was removed. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. That precludes rename table db1.blah to db2.blah, I guess - it basically copies the whole thing and then kills the old one. Sloww for big tables :-) - I think I have a database that isn't being used (don't ask), and I want to hide it to see if anything blows up. If you are in a position to shut your server down, you may rename the database directory while it's down IF you only use MyISAM tables. If you have InnoDB tables, you're stuck with dump/reload or rename table. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Renaming a database?
-Original Message- From: Andrew Moore [mailto:eroomy...@gmail.com] Sent: Tuesday, May 24, 2011 11:31 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Renaming a database? Instead of renaming it, revoke permissions to it. If it's being used you should see some problems in the application due to access denied. [JS] That's a good suggestion. It takes care of one case, but not the other. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Andy On Tue, May 24, 2011 at 4:19 PM, Jerry Schwartz je...@gii.co.jp wrote: It looks like there's no way to rename a database. Is that true? I have two reasons for wanting to do this: - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. - I think I have a database that isn't being used (don't ask), and I want to hide it to see if anything blows up. Suggestions? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a database?
Could you try restoring the other db with another name and changing the connection string in the app? On 24 May 2011 16:20, Jerry Schwartz je...@gii.co.jp wrote: It looks like there’s no way to rename a database. Is that true? I have two reasons for wanting to do this: - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. - I think I have a database that isn’t being used (don’t ask), and I want to hide it to see if anything blows up. Suggestions? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com
RE: Renaming a database?
From: Andrew Moore [mailto:eroomy...@gmail.com] Sent: Tuesday, May 24, 2011 2:56 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Renaming a database? Could you try restoring the other db with another name and changing the connection string in the app? [JS] I could, but it would be a nuisance. The app is MS Access, so there isn’t just one string to change. Fortunately my database only takes about 30 minutes to load, so this wasn’t a huge problem. I just wanted to make sure I wasn’t missing something. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com http://www.the-infoshop.com/ On 24 May 2011 16:20, Jerry Schwartz je...@gii.co.jp wrote: It looks like there’s no way to rename a database. Is that true? I have two reasons for wanting to do this: - I want to get a current version of a database out of the way so that I can load in an older version, without having to pay the penalty of reloading the current version. - I think I have a database that isn’t being used (don’t ask), and I want to hide it to see if anything blows up. Suggestions? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com
RE: Renaming a database?
-Original Message- On Tue, May 24, 2011 at 4:19 PM, Jerry Schwartz je...@gii.co.jp wrote: It looks like there’s no way to rename a database. Is that true? I know. Retarded right? Version 5.1.x and still what would seem the most basic of tasks is still impossible. Maybe Oracle can fix what MontyAB and Sun couldn't... that feature alone would be well worth the $5.6 Billion dollars spent. I just had a need to do this last week and was astonished to find it's still not implemented after all these... well ever! I unfortunately had to dump the database to a .sql file, edit said file, re-dump in, then dump in the new database, then run a SQL diff tool to find out what schemata and data was different. The whole process delayed me a few hours that I didn't budget for. Talk about a hindrance. - And before anyone gets all upset with my use of the word Retarded... Read this: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html And then the definition: re•tard•ed –verb (used with object) 1. to make slow; delay the development or progress of (an action, process, etc.); hinder or impede. –verb (used without object) 2. to be delayed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a Database
That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. Nope. These are entirely MyISAM tables... So I will probably give this a try then. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Renaming a Database
If you have stored procedures, don't forget to update the db column with the new db in mysql.proc as that does not automatically change. Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Wednesday, August 19, 2009 9:27 AM To: Paul DuBois Cc: mysql@lists.mysql.com Subject: Re: Renaming a Database That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. Nope. These are entirely MyISAM tables... So I will probably give this a try then. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a Database
No... I've pretty much avoided those. This WAS a Visual FoxPro app so we're still at the stage where we really aren't leveraging MySQL specific features. We're still pretty much just eliminating VFP specific things. Hence the one time nature of this renaming. Now that we're 5-10 customers in to upgrading we decided to actually define a naming convention for the databases themselves so I've got to fix the 5-10 that are already out there (I could not change it... but then we have to remember that these are special cases) On Wed, Aug 19, 2009 at 10:28 AM, Rolando Edwardsredwa...@logicworks.net wrote: If you have stored procedures, don't forget to update the db column with the new db in mysql.proc as that does not automatically change. Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Wednesday, August 19, 2009 9:27 AM To: Paul DuBois Cc: mysql@lists.mysql.com Subject: Re: Renaming a Database That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. Nope. These are entirely MyISAM tables... So I will probably give this a try then. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Renaming a Database
I know the best way to rename a database is to use mysqldump, extract the database and then reload to the new database. (At least based on what I can find in the 12.1.32. RENAME DATABASE Syntax section of the documentation) That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. I know in the past I've used a similar method with single tables (stop service, create a folder, drop in backups of tables, start service muck with them) and I've had no problems... but I'm hoping wiser minds will confirm I'll be okay OR that I shouldn't even try. All in all, I'm trying to find a way to minimize OUR development time as well as minimizing down time for the client. This would be a one time thing to bring the database name in line with the new product's newly picked conventions. (After we deployed four customers) If it matters two of the installations are on OSX running a stock MySQL 4.x installation and two are on Windows and I'm not certain the version without checking. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a Database
On Aug 17, 2009, at 4:22 PM, Matt Neimeyer wrote: I know the best way to rename a database is to use mysqldump, extract the database and then reload to the new database. (At least based on what I can find in the 12.1.32. RENAME DATABASE Syntax section of the documentation) That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: renaming the database
Octavian Rasnita wrote: Is there a command for renaming a MySQL database? Sorry, there is no command for that. For small databases you can use administration tools like phpMyAdmin, etc. which will do it for you by duplicating the database structure and data, and then drop the 'old' database. For big databases this will take quite long, so it's probably better to stick with the old name (what's in a name? ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Thanks. I want to rename the database in order to keep it as a backup, then to temporarily create a new one with the same name as a test, because more programs use that database name. Teddy - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 17, 2005 12:11 PM Subject: Re: renaming the database Octavian Rasnita wrote: Is there a command for renaming a MySQL database? Sorry, there is no command for that. For small databases you can use administration tools like phpMyAdmin, etc. which will do it for you by duplicating the database structure and data, and then drop the 'old' database. For big databases this will take quite long, so it's probably better to stick with the old name (what's in a name? ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
What about renaming the folder indicating the database name. I work with Windows XP and renaming a folder works well. Regards, Cor - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 17, 2005 12:11 PM Subject: Re: renaming the database Octavian Rasnita wrote: Is there a command for renaming a MySQL database? Sorry, there is no command for that. For small databases you can use administration tools like phpMyAdmin, etc. which will do it for you by duplicating the database structure and data, and then drop the 'old' database. For big databases this will take quite long, so it's probably better to stick with the old name (what's in a name? ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
C.R. Vegelin wrote: What about renaming the folder indicating the database name. I work with Windows XP and renaming a folder works well. Cor, Have you tried it with InnoDB tables or anything other than MyISAM tables? InnoDB uses a single tablespace per server (unless specified that it should use a file per table, but then it still uses a general tablespace), so renaming a directory or folder will probably only confuse InnoDB and prevent it from starting. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
I think you can just rename the directory that that database lives in. If you read the documentation for CREATE DATABASE in dev.mysql.com, you'll see they discuss renaming the directory (although it does not directly say this can be done to rename the database, but it comes really close to that.) Based on the documentation the database name is simply a directory name, no more and no less. Renaming tables has its own command syntax, you can look it up. Bob Cochran Octavian Rasnita wrote: Hi, Is there a command for renaming a MySQL database? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Robert L Cochran [EMAIL PROTECTED] wrote on 10/17/2005 07:37:26 AM: I think you can just rename the directory that that database lives in. If you read the documentation for CREATE DATABASE in dev.mysql.com, you'll see they discuss renaming the directory (although it does not directly say this can be done to rename the database, but it comes really close to that.) Based on the documentation the database name is simply a directory name, no more and no less. Renaming tables has its own command syntax, you can look it up. Bob Cochran Octavian Rasnita wrote: Hi, Is there a command for renaming a MySQL database? Thank you. Teddy The only way I have done it has been to create an empty database with the name I want. Then I used RENAME TABLE to move all of the tables into the new database. Sure it takes a lot of RENAME TABLE statements but it works. If these are InnoDB tables, all I am doing is moving metadata and that is FAST. For MyISAM or other file-based storage engines, it copies files from one folder to another. For some file systems, that is also just a metadata shift and will still be FAST. Others will require a physical move of the data from one location to another (good thing that those filesystems are becoming quite rare these days) I have a 2.1GB database with all InnoDB tables in it that I wanted to change the name of. It took me longer to write my RENAME TABLE script than it did to actually move the data. http://dev.mysql.com/doc/refman/4.1/en/rename-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: renaming the database
And, of course, you need to grant permissions for the new database name. Bob [EMAIL PROTECTED] wrote: Robert L Cochran [EMAIL PROTECTED] wrote on 10/17/2005 07:37:26 AM: I think you can just rename the directory that that database lives in. If you read the documentation for CREATE DATABASE in dev.mysql.com, you'll see they discuss renaming the directory (although it does not directly say this can be done to rename the database, but it comes really close to that.) Based on the documentation the database name is simply a directory name, no more and no less. Renaming tables has its own command syntax, you can look it up. Bob Cochran Octavian Rasnita wrote: Hi, Is there a command for renaming a MySQL database? Thank you. Teddy The only way I have done it has been to create an empty database with the name I want. Then I used RENAME TABLE to move all of the tables into the new database. Sure it takes a lot of RENAME TABLE statements but it works. If these are InnoDB tables, all I am doing is moving metadata and that is FAST. For MyISAM or other file-based storage engines, it copies files from one folder to another. For some file systems, that is also just a metadata shift and will still be FAST. Others will require a physical move of the data from one location to another (good thing that those filesystems are becoming quite rare these days) I have a 2.1GB database with all InnoDB tables in it that I wanted to change the name of. It took me longer to write my RENAME TABLE script than it did to actually move the data. http://dev.mysql.com/doc/refman/4.1/en/rename-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Robert L Cochran [EMAIL PROTECTED] wrote on 10/17/2005 11:00:33 AM: And, of course, you need to grant permissions for the new database name. Bob [EMAIL PROTECTED] wrote: Robert L Cochran [EMAIL PROTECTED] wrote on 10/17/2005 07:37:26 AM: I think you can just rename the directory that that database lives in. If you read the documentation for CREATE DATABASE in dev.mysql.com, you'll see they discuss renaming the directory (although it does not directly say this can be done to rename the database, but it comes really close to that.) Based on the documentation the database name is simply a directory name, no more and no less. Renaming tables has its own command syntax, you can look it up. Bob Cochran Octavian Rasnita wrote: Hi, Is there a command for renaming a MySQL database? Thank you. Teddy The only way I have done it has been to create an empty database with the name I want. Then I used RENAME TABLE to move all of the tables into the new database. Sure it takes a lot of RENAME TABLE statements but it works. If these are InnoDB tables, all I am doing is moving metadata and that is FAST. For MyISAM or other file-based storage engines, it copies files from one folder to another. For some file systems, that is also just a metadata shift and will still be FAST. Others will require a physical move of the data from one location to another (good thing that those filesystems are becoming quite rare these days) I have a 2.1GB database with all InnoDB tables in it that I wanted to change the name of. It took me longer to write my RENAME TABLE script than it did to actually move the data. http://dev.mysql.com/doc/refman/4.1/en/rename-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Good catch! Alternatively, if he wanted to move all of his permissions to the new database name, he could simply update any old entries in the `mysql.db` and `mysql.columns_priv` tables then issue a FLUSH PRIVILEGES command. UPDATE `mysql.db` SET `Db` = 'new_dbname` WHERE `Db` = 'old_dbname'; UPDATE `mysql.columns_priv` SET `Db` = 'new_dbname` WHERE `Db` = 'old_dbname'; FLUSH PRIVILEGES; This way if he wants to archive his old database, he can grant a new set of permissions (with new GRANT statements). And if he just wants to rename his database (migrating any existing permissions to the new name) he can do that, too. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: renaming the database
Hello. This is a frequently asked question. See: http://lists.mysql.com/mysql/188094 For more solutions search in the archives at: http://lists.mysql.com Octavian Rasnita wrote: Hi, Is there a command for renaming a MySQL database? Thank you. Teddy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Shawn wrote
I have a 2.1GB database with all InnoDB tables in it that I wanted
to
change the name of. It took me longer to write my RENAME TABLE
script than it did to actually move the data.
It's easier less error-prone to do it in an sproc which prepares
executes the rename cmds. Only hitch is that MySQL doesn't yet
let you prepare a drop database cmd.
SET GLOBAL log_bin_trust_routine_creators=TRUE;
DROP PROCEDURE IF EXISTS RenameDatabase;
DELIMITER |
CREATE PROCEDURE RenameDatabase (IN oldname CHAR(64), IN newname
CHAR(64))
BEGIN
DECLARE sname CHAR(64) DEFAULT NULL;
DECLARE rows INT DEFAULT 1;
CREATE DATABASE IF NOT EXISTS newname;
REPEAT
SELECT table_name INTO sname
FROM information_schema.tables AS t
WHERE t.table_schema = oldname
LIMIT 1;
SET rows = FOUND_ROWS();
IF rows = 1 THEN
SET @scmd = CONCAT( 'RENAME TABLE ', oldname, '.', sname,
' TO ', newname, '.', sname );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
ELSE
SET @scmd = CONCAT( "UPDATE mysql.db SET Db = '",
@newname,
"' WHERE Db = '", @oldname, "'" );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
SET @scmd = CONCAT( "UPDATE mysql.columns_priv SET Db = '",
@newname,
"' WHERE Db = '", @oldname, "'" );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
FLUSH PRIVILEGES;
-- MySQL does not yet support PREPARE DROP DATABASE:
-- SET @scmd = CONCAT( 'DROP DATABASE ', oldname );
-- PREPARE cmd FROM @scmd;
-- EXECUTE cmd;
-- DEALLOCATE PREPARE cmd;
END IF;
UNTIL rows = 0 END REPEAT;
END;
|
DELIMITER ;
PB
-
[EMAIL PROTECTED] wrote:
Robert L Cochran [EMAIL PROTECTED] wrote on 10/17/2005 07:37:26 AM:
I think you can just rename the directory that that database lives in.
If you read the documentation for CREATE DATABASE in dev.mysql.com,
you'll see they discuss renaming the directory (although it does not
directly say this can be done to rename the database, but it comes
really close to that.) Based on the documentation the database name is
simply a directory name, no more and no less.
Renaming tables has its own command syntax, you can look it up.
Bob Cochran
Octavian Rasnita wrote:
Hi,
Is there a command for renaming a MySQL database?
Thank you.
Teddy
The only way I have done it has been to create an empty database with the
name I want. Then I used RENAME TABLE to "move" all of the tables into the
new database. Sure it takes a lot of RENAME TABLE statements but it works.
If these are InnoDB tables, all I am doing is moving metadata and that is
FAST. For MyISAM or other file-based storage engines, it copies files from
one folder to another. For some file systems, that is also just a metadata
shift and will still be FAST. Others will require a physical move of the
data from one location to another (good thing that those filesystems are
becoming quite rare these days)
I have a 2.1GB database with all InnoDB tables in it that I wanted to
change the name of. It took me longer to write my RENAME TABLE script than
it did to actually move the data.
http://dev.mysql.com/doc/refman/4.1/en/rename-table.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Errm, needed to take the grants table updates out of the repat loop: CREATE PROCEDURE RenameDatabase (IN oldname CHAR(64), IN newname CHAR(64)) BEGIN DECLARE sname CHAR(64) DEFAULT NULL; DECLARE rows INT DEFAULT 1; DECLARE total INT DEFAULT 0; CREATE DATABASE IF NOT EXISTS newname; REPEAT SELECT table_name INTO sname FROM information_schema.tables AS t WHERE t.table_schema = oldname LIMIT 1; SET rows = FOUND_ROWS(); IF rows = 1 THEN SET @scmd = CONCAT( 'RENAME TABLE ', oldname, '.', sname, ' TO ', newname, '.', sname ); PREPARE cmd FROM @scmd; EXECUTE cmd; DEALLOCATE PREPARE cmd; SET total = total + 1; END IF; UNTIL rows = 0 END REPEAT; IF total 0 THEN SET @scmd = CONCAT( UPDATE mysql.db SET Db = ', @newname, ' WHERE Db = ', @oldname, ' ); PREPARE cmd FROM @scmd; EXECUTE cmd; SET @scmd = CONCAT( UPDATE mysql.columns_priv SET Db = ', @newname, ' WHERE Db = ', @oldname, ' ); PREPARE cmd FROM @scmd; EXECUTE cmd; DEALLOCATE PREPARE cmd; FLUSH PRIVILEGES; -- MySQL does not yet support PREPARE DROP DATABASE: -- SET @scmd = CONCAT( 'DROP DATABASE ', oldname ); -- PREPARE cmd FROM @scmd; -- EXECUTE cmd; -- DEALLOCATE PREPARE cmd; END IF; END; | DELIMITER ; PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
renaming the database
Hi, Is there a command for renaming a MySQL database? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming a database
Hello. One possible solution is to create a new database, and then RENAME TABLE each table from the original database into the new database. Then drop the original database. Octavian Rasnita [EMAIL PROTECTED] wrote: Hi, Please tell me how to rename a database. I couldn't find this in the manual. I have tried to rename manually the name of the directory that holds that database, but some tables can't be accessed after that. (I have found that they are InnoDB tables, even though I never specified that I want this type of engine when creating the tables). Thank you. Teddy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
renaming a database
Hi, Please tell me how to rename a database. I couldn't find this in the manual. I have tried to rename manually the name of the directory that holds that database, but some tables can't be accessed after that. (I have found that they are InnoDB tables, even though I never specified that I want this type of engine when creating the tables). Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming a database
Octavian Please tell me how to rename a database. As you already found, there are database features that don't survive renaming its directory. Best method I know is to mysqldump it and recreate it from the dump under the desired name. PB http://www.artfulsoftware.com - Octavian Rasnita wrote: Hi, Please tell me how to rename a database. I couldn't find this in the manual. I have tried to rename manually the name of the directory that holds that database, but some tables can't be accessed after that. (I have found that they are InnoDB tables, even though I never specified that I want this type of engine when creating the tables). Thank you. Teddy No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.13/78 - Release Date: 8/19/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Renaming a database
How do I rename a database? The help online is pretty ambigous. -- Power to people, Linux is here.
Re: Renaming a database
Scott Hamm [EMAIL PROTECTED] wrote on 07/18/2005 12:01:49 PM: How do I rename a database? The help online is pretty ambigous. -- Power to people, Linux is here. I wouldn't say ambiguous. I would rather say indirect. Renaming a database is nearly the same thing as moving tables to a new database. you can do that with RENAME TABLE (http://dev.mysql.com/doc/mysql/en/rename-table.html). The only thing to do is to migrate permissions from the old database to the new one (and any table-specific or column-specific permissions, too) by either performing a set of GRANT and REVOKE statements or by updating the `mysql`.`db`, `mysql`.`tables_priv`, and `mysql`.`columns_priv` tables manually and issuing a FLUSH PRIVILEGES statement to put your updates into effect. Normally, the names of databases aren't as nearly as important or mutable as the names of the tables and columns within them. That's why there is no simpler method for renaming a database with MySQL. One shortcut, just for MyISAM tables, might be to shut down the server, rename the folder for the database to the new name, then restart the server. This still leaves any permissions pointing to the old name and will not work for InnoDB and probably won't work for BDB databases but it could save you the trouble of scripting all of those RENAME TABLE statements. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Renaming a database
A database in MySQL is simply a directory. So just rename the directory with appropriate tool for your platform. On my test box this becomes mysql show databases; ++ | Database | ++ | information_schema | | lois | | mailprint | | mysql | | test | ++ 5 rows in set (0.22 sec) mysql show databases; ++ | Database | ++ | information_schema | | lois | | mailprint | | mysql | | test1 | ++ 5 rows in set (0.00 sec) -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: Monday, July 18, 2005 11:02 AM To: 'Mysql ' Subject: Renaming a database How do I rename a database? The help online is pretty ambigous. -- Power to people, Linux is here.
**newbie question** renaming a database
using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **newbie question** renaming a database
Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a You may think this is simple, but you have to make sure all users are out of the database, and all records are flushed before it could be renamed. It is much safer to shutdown the server and rename the directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **newbie question** renaming a database
At 11:16 -0500 10/7/04, gerald_clark wrote: Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a You may think this is simple, but you have to make sure all users are out of the database, and all records are flushed before it could be renamed. It is much safer to shutdown the server and rename the directory. It's simple conceptually, but complex to actually implment. In addition to the issues gerald mentions, remember that if you have InnoDB tables, they aren't actually stored in the database directory unless you're using individual tablespaces -- and even then, there is an entry for them in the InnoDB data dictionary that is stored in the shared tablespace. And those entries include the database name. If you rename the database directory, those entries become invalid. Also, if you have foreign key relationship, there are similar difficulties. Might be better to create a new database and then RENAME TABLE each table from the original database into the new database. Then drop the original database. The strategy of shutting down the server, renaming the database directory, and restartingg the server does work fine _if_ your database contains only MyISAM (or ISAM) tables, though. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
renaming a database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm kind of interested to find if there is a way to rename a database with something like the alter command. Looking in the index of the New Riders MySQL book I don't see anything other than renaming tables Looking through the Manual for version 3 Nothing shows up Looking at the mailing list archieve I don't see anything either I'm running 3.23.46-nt running on windows 2000 advanced server as a service. I'm thinking that the only way around this would be to do a mysqldump for that database and then creating a new database with the desired name and importing the contents of the dump file. I'd like to be able to just rename the database from the command line just like you would a table. Thanks in advance, JC -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.5.8 for non-commercial use http://www.pgp.com iQA/AwUBPFhE+XX+hJvt5DtWEQLSDQCgozPP+RqD56w4BQa8V+d/EviLH3EAnjN0 1TJ0ZkSsYXT0wiGWDarMwNRb =zrmt -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: renaming a database
At 11:10 -0800 1/30/02, JC wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm kind of interested to find if there is a way to rename a database with something like the alter command. Looking in the index of the New Riders MySQL book I don't see anything other than renaming tables Looking through the Manual for version 3 Nothing shows up Looking at the mailing list archieve I don't see anything either I'm running 3.23.46-nt running on windows 2000 advanced server as a service. I'm thinking that the only way around this would be to do a mysqldump for that database and then creating a new database with the desired name and importing the contents of the dump file. I'd like to be able to just rename the database from the command line just like you would a table. If your database doesn't contain InnoDB tables, you can try this: - Shut down the MySQL server - Rename the database directory - Restart the MySQL server But remember that if you have entries in the grant tables that pertain specifically to that database, you'll need to update them to use the new database name. Thanks in advance, JC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: renaming a database
This may already be implemented, but the MySQL Technical Reference for Version 3.23.41 states: 1.6.2 Things that must be done in the real near future: - Implement RENAME DATABASE. To make this safe for all table handlers, it should work as follows: - Create the new database. - For every table do a rename of the table to another database, as we do with the RENAME command. - Drop the old database. Of course, you should be able to simply rename the folder that contains your tables you could script it if you had to, either in Perl or a Win32 batch file, depending on your system. # Nathan - Original Message - From: JC [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 30, 2002 12:10 PM Subject: renaming a database -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm kind of interested to find if there is a way to rename a database with something like the alter command. Looking in the index of the New Riders MySQL book I don't see anything other than renaming tables Looking through the Manual for version 3 Nothing shows up Looking at the mailing list archieve I don't see anything either I'm running 3.23.46-nt running on windows 2000 advanced server as a service. I'm thinking that the only way around this would be to do a mysqldump for that database and then creating a new database with the desired name and importing the contents of the dump file. I'd like to be able to just rename the database from the command line just like you would a table. Thanks in advance, JC -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.5.8 for non-commercial use http://www.pgp.com iQA/AwUBPFhE+XX+hJvt5DtWEQLSDQCgozPP+RqD56w4BQa8V+d/EviLH3EAnjN0 1TJ0ZkSsYXT0wiGWDarMwNRb =zrmt -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: renaming a database
What would I do if there are InnoDB tables ? -- Dimitry Wednesday, January 30, 2002, 9:31:09 PM, Paul DuBois wrote: At 11:10 -0800 1/30/02, JC wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm kind of interested to find if there is a way to rename a database with something like the alter command. Looking in the index of the New Riders MySQL book I don't see anything other than renaming tables Looking through the Manual for version 3 Nothing shows up Looking at the mailing list archieve I don't see anything either I'm running 3.23.46-nt running on windows 2000 advanced server as a service. I'm thinking that the only way around this would be to do a mysqldump for that database and then creating a new database with the desired name and importing the contents of the dump file. I'd like to be able to just rename the database from the command line just like you would a table. If your database doesn't contain InnoDB tables, you can try this: - Shut down the MySQL server - Rename the database directory - Restart the MySQL server But remember that if you have entries in the grant tables that pertain specifically to that database, you'll need to update them to use the new database name. Thanks in advance, JC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: renaming a database
Hi! Can you rename a database? If so, how does it handle all the tables inside it? By shutting down the server, renaming the directory of that database and restarting the server. The server will be able to handle the change just fine. Sorry, but InnoDB tables do not get renamed inside the internal data dictionary of InnoDB this way. For them currently the way is, for exapmle, to create the tables to the new database and use INSERT INTO new_database.table1 SELECT * FROM old_database.table1. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq Regards, Heikki http://www.innodb.com/ibman.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
renaming a database
Can you rename a database? If so, how does it handle all the tables inside it? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: renaming a database
Can you rename a database? If so, how does it handle all the tables inside it? By shutting down the server, renaming the directory of that database and restarting the server. The server will be able to handle the change just fine. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Renaming a database
Hi! I'm running MySQL on Win NT 4. I'm trying to rename a database from my (Delphi) program, but I haven't found any inforamtion about this on the MySQL site. Can anybody help me? P.S.: Can a database named 2001 be created? regards, m@rko - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
