From: Michael Dykman Date: December 11 2009 9:54pm Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? List-Archive: http://lists.mysql.com/mysql/219675 Message-Id: <814b9a820912111354l5a5f1130id1a252a0ab15ff95@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 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 tha= t, > 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, =A0a RENAME TABLE statement >> may have multiple clauses. >> >> RENAME TABLE >> =A0 =A0 =A0olddb.foo to newdb.foo, >> =A0 =A0 =A0olddb.bar to newdb.bar; >> >> Here, =A0I hot-swap a =A0new lookup table 'active.geo' into a live syste= m >> confident that, at any given point, some version of this table always >> exists: >> >> RENAME TABLE >> =A0 =A0 =A0active.geo to archive.geo, >> =A0 =A0 =A0standby.geo to active geo; >> >> =A0- michael dykman >> >> >> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman >> wrote: >> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio >> 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 :-) >> > >> >> >> >> -- >> =A0- michael dykman >> =A0- mdykman@stripped >> >> "May you live every day of your life." >> =A0 =A0 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=3Ddaevid@stripped >> > > --=20 - 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.