replication of RENAME TABLE d1.t TO d2.t

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

Re: replication of RENAME TABLE d1.t TO d2.t

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 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 an=
d
> 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:
> =A0 =A0RENAME 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--
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