List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:December 11 2009 10:40pm
Subject:RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
View as plain text  
In our case, we purposely avoid using any of those features. Just straight
up INNODB tables. Permissions would be an issue, but in my case, I have a
new dump of a database that I want to 'swap' with the existing one. A
simple rename old, rename new to old would have solved it. Hence this
thread. :) Therefore permissions should be fine as they go by DB name AFAIK
and not some "pointer". 

> -----Original Message-----
> From: Gavin Towey [mailto:gtowey@stripped] 
> Sent: Friday, December 11, 2009 2:18 PM
> To: Saravanan; MySql; Michael Dykman
> Subject: RE: Are you serious? mySQL 5.0 does NOT have a 
> RENAME DATABASE?
> 
> Don't forget triggers, stored routines, views, database/table 
> specific user permissions, and replication/binlog options!
> 
> Regards,
> Gavin Towey
> 
> -----Original Message-----
> From: Saravanan [mailto:suzuki_babu@stripped]
> Sent: Friday, December 11, 2009 2:02 PM
> To: MySql; Michael Dykman
> Subject: Re: Are you serious? mySQL 5.0 does NOT have a 
> RENAME DATABASE?
> 
> if you have myisam alone tables you can rename the folder of 
> the database. That can work like rename database. If you have 
> innodb table you have to move one by one table because 
> details of those tables will be stored in innodb shared table 
> space. Moving folder cannot work.
> 
> Thanks,
> Saravanan
> 
> --- On Fri, 12/11/09, Michael Dykman <mdykman@stripped> wrote:
> 
> From: Michael Dykman <mdykman@stripped>
> Subject: Re: Are you serious? mySQL 5.0 does NOT have a 
> RENAME DATABASE?
> To: "MySql" <mysql@stripped>
> Date: Friday, December 11, 2009, 10:54 PM
> 
> 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.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
> 
> This message contains confidential information and is 
> intended only for the individual named.  If you are not the 
> named addressee, you are notified that reviewing, 
> disseminating, disclosing, copying or distributing this 
> e-mail is strictly prohibited.  Please notify the sender 
> immediately by e-mail if you have received this e-mail by 
> mistake and delete this e-mail from your system. E-mail 
> transmission cannot be guaranteed to be secure or error-free 
> as information could be intercepted, corrupted, lost, 
> destroyed, arrive late or incomplete, or contain viruses. The 
> sender therefore does not accept liability for any loss or 
> damage caused by viruses or errors or omissions in the 
> contents of this message, which arise as a result of e-mail 
> transmission. [FriendFinder Networks, Inc., 220 Humbolt 
> court, Sunnyvale, CA 94089, USA, FriendFinder.com
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 

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