List:Replication« Previous MessageNext Message »
From:Michael Bunzel Date:July 22 2008 7:49pm
Subject:Re: Best way to recover from Replication Failure
View as plain text  
> I'm 100% positive that I don't have a client writing to the slave.

You could still try setting the read_only flag in the config file (see
section 5.1.3. in the manual) to eliminate this possibility.

> My assumption is that the "READ LOCK" keeps all changes locked down
> until I either close the client or perform a UNLOCK TABLES, is that correct?

Yes, that is correct. The exact behavior is described in the manual in
section 12.5.5.2. for the LOCK TABLES specific stuff and section
12.4.5. for general locking behavior.

> I hadn't considered that. It hadn't really been that long since I initiated
> replication, and I still had the original dump file, but I think I didn't save
> the position. I would need to keep track of the log position at the time
> I initiated that dump, I suppose.

It's pretty much always a good idea to keep those. Depending on
variables like max_binlog_size and expire_logs_days (section 5.1.3.)
the binlog can be kept for long periods of time. Unless your server
has a high number of writes it's feasible to initiate a slave with a
dump that's a few months old. For example, I have a server that has
about 1500 writes per hour on average and it only takes a couple of
minutes to catch up with the master (over the Internet) when I use a
dump from the previous month.

I read through your previous threads again and noticed the following thing:

> Yes, I do use LOAD DATA INFILE periodically, but I don't use cron (
> or Windows scheduler in this case) to execute it.  When I use it, it's
> usually to import several hundred records into a certain table.

That could be the problem right here since there were a bunch of bugs
related to LOAD DATA INFILE and the binlog. Some were corrected
already, others are scheduled for upcoming releases. Please have a
look at the output of the following commands on *both* the master and
slave:

SHOW VARIABLES LIKE "read%buffer%";
SHOW VARIABLES LIKE "%packet%";

IIRC your read_buffer_size (and possibly read_rnd_buffer_size) should
be sane and should not be greater than max_allowed_packet. Also,
max_allowed_packet should be pretty large and should be quite a bit
larger on the slave than on the master. In theory, this should work
around those bugs.

Michael Bunzel
LintraNex Systems
Thread
Best way to recover from Replication FailureJesse22 Jul
  • Re: Best way to recover from Replication FailureMichael Bunzel22 Jul
  • Re: Best way to recover from Replication FailureJesse22 Jul
    • Re: Best way to recover from Replication FailureMarcus Bointon22 Jul
    • Re: Best way to recover from Replication FailureMichael Bunzel22 Jul
  • Re: Best way to recover from Replication FailureJesse23 Jul