List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:June 3 2010 9:10pm
Subject:Re: replication of RENAME TABLE d1.t TO d2.t
View as plain text  
Other way around.

Assuming the aplcation is expecting your table to be named 'db.table1'
 and your replacement is newdb.table1 you can do the following.

create a database for hold archives

CREATE olddb;

then

RENAME TABLE
db.table1 to olddb.table1, -- back up the current table
newdb.table1 to db.table1 -- rename the new table as the old one

You can have multiple oldtable TO newtable section, comma-separated.
MySQL guarantees that this is an atomic action which is to say that,
for all intents and purposed, all the table renames within a single
statement occur simultaneously or not at all.

I have swapped out quite larger database on the fly in production like
this with no reported problems.


 - michael







On Thu, Jun 3, 2010 at 5:01 PM, Tom Worster <fsb@stripped> wrote:
> i've a need to change the name of a database and haven't done this before in
> our live server.
>
> while the tables are myisam, i'm not inclined to rename the dirname of d1's
> datafiles because i'd rather not interrupt service for other databases and
> i'd prefer if the renaming would replicate.
>
> can i do it like this:
>
> in the application, kick users of d1 out
> create database d2
> foreach t in d1:
>    RENAME TABLE d1.t TO d2.t
> tell user to use d2
>
> ?
>
> tom
>
>
>
> --
> 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 the Source be with you.
Thread
replication of RENAME TABLE d1.t TO d2.tTom Worster3 Jun
  • Re: replication of RENAME TABLE d1.t TO d2.tMichael Dykman3 Jun