List:Replication« Previous MessageNext Message »
From:Jesse Date:July 15 2008 10:35pm
Subject:Re: Questions about replication
View as plain text  
> Regarding your snapshots, you expressed concerns that someone changed the 
> data during the export. Since you're using innodb you should always take 
> your export with -single-transaction -master-data to get a consistent 
> non-blocking backup. Since you believe the data to be inconsistent I'm 
> guessing you didn't use these options?

Sorry, I'm kind of new to some of this, but I'm assuming that 
the -singe-transaction -master-data you are referring to refers to using the 
mysqldump utility?  Actually, first few times, I didn't use that at all. we 
are using Microsoft servers, and I simply used the GUI Administrator utility 
to back up all of the databases, and restored them using the mysql command 
line utility on the slave. This time around, however, I do plan on using the 
mysql dump utility, and will make sure I add the two parameters above to the 

> You said your main server is 5.0, what is your slave? There is a bug in 
> replication of LOAD DATA INFILE that looks like what you described, though 
> AFAIK it only affects  specific versions  - do you use this command, 
> perhaps in a cron or batch job?

I'm using version 5.0.45-community-nt on the older slave.  We have since 
shut the other server down since I'm not using it anything right now, so I'm 
not sure what exact version that one was, but I believe it was 
5.0.45-community-nt as well, which is a slightly newer version than the 
5.0.17-nt-log that we are using on our main master server.
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.

> Marcus was referring to your replication master. Many folks configure 
> their servers as both master and slave to eachother, as this can make 
> crash recovery easier. However, what I think he was really asking abou is 
> If  someone could have inserted a record directly into the slave (this 
> would be more likely if you had master-master repl going), when it read 
> the next insert operation on that table from the  master, replication 
> would break- though I have always seen an error reported when this 
> happens.  If  this is the case, your solution is simple: auto Inc offset 
> and autoinc increment.

Oh.  Then, the answer is no. I've got one master, which is on one computer, 
and I've got one slave, which is a completely different computer.  The slave 
machine's database isn't used for ANYTHING at all. It's simply there as a 
real time backup for the database, which is what I have in mind for 
replication (if I can get it working).

> If all else fails, use mysqlbinlog to read the master logs at the position 
> that the slave stopped and see if anything looks wrong.

I did that last time, and either I didn't do it correctly, or it ended up on 
a command that didn't make any sense at all regarding the breaking of 
replication.  I will simply need to start the process again and see what 
happens, then I can be more detailed in my messages.

> Hope some of that helps,

Thanks so much for your reply, it was helpful.


Questions about replicationJesse15 Jul
  • RE: Questions about replicationRick James15 Jul
  • Re: Questions about replicationMarcus Bointon15 Jul
  • Re: Questions about replicationJesse15 Jul
    • Re: Questions about replicationDevananda15 Jul
  • Re: Questions about replicationJesse15 Jul
    • RE: Questions about replicationRick James15 Jul
  • Re: Questions about replicationJesse16 Jul
  • Re: Questions about replicationJesse16 Jul
    • Re: Questions about replicationAndrew Garner16 Jul
  • Re: Questions about replicationJesse16 Jul