Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
Date: Wed Sep 19 2007 - 12:31:38 CDT
> 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 master.info, 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
> 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.