Re: Fixing Replication

[Joseph Bueno]

Keith Bussey wrote:
Hi all,

I was wodnering if someone has a better way of re-sync'ing a slave when
replication fails.
Here's my setup:

1 - MASTER
5 - SLAVES
Now sometimes, one of the slaves will fail. Usually when it's the sql that stops
running, I can fix it and get it back. However sometimes, I also get the "master
binlog corruption" error and haven't found a way to fix that.
What I do is:

- stop mysql on the master and all the slaves
- remove the server-bin files on the master
- remove the mysql data dir on each slave
- copy the master's datadir to each slave
- restart mysql everywhere and everything is fine
Now while this method works, it is extremely time-consuming and I was wondering
if anyone knows a better solution to only fix the affected slave??
I have tried stopping just the affected slave and one other good slave, taking
its datadir and renaming the bin files to the affected server's name. That
method doesn't seem to work though.
Thanks in advance!

Hi,

My setup is: 1 master, 6 slaves.

I have a different approach:

On master:
- "FLUSH TABLES WITH READ LOCK"
- mysqldump relevant tables (we don't replicate all tables)
- "SHOW MASTER STATUS" and write down binary log file and offset
- "UNLOCK TABLES"
On broken slave:
- "SLAVE STOP" (just in case)
- rebuild tables using mysqldump data
- "CHANGE MASTER TO ....  MASTER_LOG_FILE=xxx, MASTER_LOG_POS=yyy"
- "SLAVE START"
It has on big advantage: it doesn't stop the master and other slaves.
There is only a slowdown while tables are locked on the master but I
do it early in the morning when database activity is low (I can wait
to repair the broken slave : it is removed from the server pool
used by the applications when it fails).
I am sure that it is possible to further optimize this procedure
but, since I had to use it only 3 times since I have setup replication
(18 months ago), I haven't invested more time to it.
However, I like your idea to use another slave instead of the master;
it may be possible to adapt my approach to do it.
PS: I use mysql 3.23, mysql 4.x has a "LOAD DATA FROM MASTER"
that makes it much easier ;)
Hope this helps,
Joseph Bueno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]