Hi Ross,
On Wed, 2003-06-04 at 16:35, Ross Simpson wrote:
> This sounds like the best idea yet :)
>
> I have a couple of questions:
>
> - I need to keep the data in x_shadow while still creating table x..
> will copying accomplish the same thing? I'm guessing it will be slower,
> but keeping the 'shadow' table around is important.
>
I'm not sure I understand the question, but I think you're saying
you want to keep a shadow table after the rename. Erm, you could
create a shadow of the shadow, or create a new shadow after the
switchover.
You have two options for creating the shadow tables:
* inside MySQL
> create table x_shadow( ... );
> insert into x_shadow select * from x;
This SQL will be replicated to your slaves (ie. prod and replicas),
but depending on the amount of data involved might hammer your
slaves and make your production system temporarily unusable.
* outside MySQL, using OS-level copy (only works for MyISAM tables)
- in mysql "flush tables" and possibly lock them to prevent anyone
else updating them
- cd mysql-datadir
- cp x.MYD x_shadow.MYD
- cp x.MYI x_shadow.MYI
- cp x.frm x_shadow.frm
But note that this will have to be repeated on each replica. You
could tar up the x_shadow files on stage, copy the tarball to the
replicas and unpack them in the correct directory. mysqlhotcopy
can be used to do the locking and copying for you.
Once the copied shadow tables are in place, a rename issued on the
stage server should replicate fine, swapping x and shadow_x on all
replicas.
> - Will either / both of these (rename and copy) preserve indexes? Each
> table has ~5 indexes, and I don't want to reindex.
>
Rename will correctly handle the indexes. An OS-level copy of
MyISAM files will also preserve the indexes (as long as you get
all the files belonging to a table).
regards,
Martin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
