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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

RE: Renaming a Database

If you have stored procedures, don't forget to update the db column with th=
e 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
redwards@logicworks.net

-----Original Message-----
From: Matt Neimeyer [mailto:matt@neimeyer.org]=20
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

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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
Edwards 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
> redwards@logicworks.net
>
> -----Original Message-----
> From: Matt Neimeyer [mailto:matt@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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dredwards@logi=
cworks.net
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg