Dan Casey wrote:
> mysql 4.0.20
> myIsam tables..
> I have two slave setups. One slave is fine and up
> to date. The second one which also updates off the
> same master showed the following.
> Slave_IO_Running: Yes
> Slave_SQL_Running: No
> I have had this issue before. In which case I run
> slave stop; slave start; and then it starts
> working again. This time around, Slave_SQL_Running
> remained as "NO"..
> There was a duplicate entry (which im not sure how
> that happened here if it didn't on the other
> slave). Im guessing thats where it stopped?
> Right now im reloading lastnights master
> snapshot.. just to give this slave a more current
> My questions are these.
> 1. I know im going to have to make a new mysqldump
> of the master, and record its current position in
> order to get replication started again. Is there a
> way that I can do this without disrupting service
> on my web site.. Or am I going to have to expect
> some downtime while the master is out?
> 2. How did this happen, or better yet, what can or
> should i do in the future?
> 3. almost forget, but since i am completely making
> a new slave here, is it safe to rm -rf all the
> bin-log and relay-bin-logs on this slave
If at all possible, I would upgrade to version 4.1. It makes life so much easier when it comes to setting up replication. There are more switches to the mysqldump command to make life easier.
But, for version 4.0, I would use the flags '--master-data' and '--single-transaction' for mysqldump (check this, I think it works for version 4.0). In your dump file, a 'CHANGE MASTER' command is written with the right log file name and position, taking the guess work out. The '--single-transaction' flag will minimize the effect of locking, but not completely eliminate it.
If you find that the SQL thread is stopped, due to some SQL error, you can skip the current sql statement and have the SQL thread try to continue. You can do this by issuing a 'SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1' command on the slave, then start the slave again.
If you issue a 'RESET SLAVE', then all the relay logs arepurged automatically.
Senior Consultant, MySQL Inc.