List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:December 11 2009 9:54pm
Subject:Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
View as plain text  
No, not a loophole.  Just a plain-old management feature..  there is
nothing particularly hacky about it.. this is not trying to leverage
undocumented features: this has been a published part of the API for
at least a couple of years.

On the same file system, yes it should be pretty damned fast.
Depending on how your data is stored, it might now be 'quite' as
simple as a unix 'mv' command..  if this is a production system, I
would recommend you do a dry run with a replicant/slave. No amount of
theorizing will tell as much as the experiment.

 - michael dykman

On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent <daevid@stripped> wrote:
> Will this work in 5.0?
>
> If I'm reading this right, it seems like this is some kind of trick or
> loophole then right? If it works and solves my dilemna, I'm fine with that,
> but I'm just curious.
>
> How fast is this? I mean, if I have an 80GB database, is it like a real
> unix 'mv' command where it simply changing pointers or is it a full on
> copy/rm? (Assume same filesystem/directory)
>
>> -----Original Message-----
>> From: Michael Dykman [mailto:mdykman@stripped]
>> Sent: Friday, December 11, 2009 6:08 AM
>> To: MySql
>> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
>> RENAME DATABASE?
>>
>> If you want to move the database atomically,  a RENAME TABLE statement
>> may have multiple clauses.
>>
>> RENAME TABLE
>>      olddb.foo to newdb.foo,
>>      olddb.bar to newdb.bar;
>>
>> Here,  I hot-swap a  new lookup table 'active.geo' into a live system
>> confident that, at any given point, some version of this table always
>> exists:
>>
>> RENAME TABLE
>>      active.geo to archive.geo,
>>      standby.geo to active geo;
>>
>>  - michael dykman
>>
>>
>> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman
>> <vegivamp@stripped> wrote:
>> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio
>> <ken@stripped> wrote:
>> >
>> >> > rename table oldschema.table to newschema.table;
>> >>
>> >> Just to be 100% clear -- I assume you have to first create
>> the destination
>> >> database, and then do this for all the tables in the
>> source database?
>> >>
>> >
>> > Yep. Easily scriptable, though :-)
>> >
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdykman@stripped
>>
>> "May you live every day of your life."
>>     Jonathan Swift
>>
>> Larry's First Law of Language Redesign: Everyone wants the colon.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>
>



-- 
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
Thread
Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Daevid Vincent11 Dec
  • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Ken D'Ambrosio11 Dec
    • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Johan De Meersman11 Dec
      • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Ken D'Ambrosio11 Dec
        • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Johan De Meersman11 Dec
          • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Michael Dykman11 Dec
            • RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Daevid Vincent11 Dec
              • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Michael Dykman11 Dec
                • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Saravanan11 Dec
                  • RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Gavin Towey11 Dec
                    • RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Daevid Vincent11 Dec
              • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Johan De Meersman11 Dec
          • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Jim Lyons11 Dec
            • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Michael Dykman11 Dec