List:General Discussion« Previous MessageNext Message »
From:dpgirago Date:September 19 2007 5:31pm
Subject:Re: Slave Misbehavin'
View as plain text  

dpgirago@stripped wrote:
> Howdy,
> I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 
> to our system that currently has one master and one slave, slave1, 
>  4.0.24, and somehow slave2 somehow ends up with too many records in 
> of the 30 tables in the database. 
> Steps taken:
> 1. Stopped new records from being inserted into the master, and 
> with count(*)'s that both master and slave1 were in a static state.
> 2. Stopped mysqld and commented out in my.cnf the master connection 
> parameters (user, host, password, port) on slave2.
> 3. Deleted, all mysql-bin and relay-bin files from the mysql 

> data directory on slave2. 
> 4. Deleted all .MYD, .MYI, and .frm files from the replication database 
> directory on slave2. 
> 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2.

- And in the meantime, slave1's data is being changed because the master 
- is sending it replication events, no?  You need to run STOP SLAVE on 
- slave1 before rsyncing it.  After STOP SLAVE, run SHOW SLAVE STATUS and 
- record the output, then rsync, then START SLAVE on slave1 again.

I don't think so. I stopped all activities on the master (step 1), and 
therefore shouldn't have any changes made to it. I should have noted that 
inserts are done on the master -  no updates or deletes. 

> 6. Restarted mysqld on slave2 (now not running as a slave).
> 7. Confirmed that record counts were consistent across master, slave1 
> slave2.
> 8. Stopped mysqld on slave2, uncommented master connection parameters in 

> my.cnf, and restarted mysqld.
> 9. Got log file and log position parameters with 'show master status' on 

> the master.

- TOO LATE.  The horse has left the barn and you're closing the door 
- behind it!  You should instead get the replication coordinates from 
- slave1 with SHOW SLAVE STATUS during step 5.  You're cloning slave2 from 

- slave1, so slave2 tells the truth, not the master, which has done a 
- whole bunch of work while you were going through these steps.

No, slave1 can't do any work except as directed by the master, which has 
all activities stopped on it. 
> 10. Ran 'Change master to... with all fields filled in.
> 11.  Ran 'slave start' on slave2.
> 12. Rechecked record counts on slave2, and they were too large and out 
> sync with slave1 and master.
> I poked around in the data on slave2 and found a number of records had 
> been duplicated, and that accounted for the higher record counts. 
> After starting the application that inserts data into the master, I 
> determined that new records are being inserted correctly into slave2.
> Seriously out of ideas here.
> Thanks,
> David 

Slave Misbehavin'dpgirago19 Sep
  • Re: Slave Misbehavin'Baron Schwartz19 Sep
Re: Slave Misbehavin'dpgirago19 Sep
  • Re: Slave Misbehavin'Scott Tanner19 Sep
Re: Slave Misbehavin'dpgirago24 Sep