we have a MySQL system in Master-slave replication mode. Currently we are doing full backups with mysqldumps to a remote disk server.

I think is best to do one full dump and many small incremental backups. According to the MySQL manual, MySQL uses the binlogs to make theses incremental backups, flushing binlogs each time an incremental backup is completed. But we already use binlogs to achieve Master-Slave replication.

My question is: how does this incremental backup and binlogs flushing affect the master-slave replication?

P.S:forgot to say, most of the tables use InnoDB, the others use MyISAM


Well a packed question, let me try to answer all the issues you raised there.

Binary backups vs dumps

Right now you're using mysqldump to do full backups of your database, that's a good practice indeed but it can get a bit endearing when dealing with huge MySQL installations. Binary backups are good point in time backups, but you need two parts, one full binary backup (which needs to be consistent) and the binary diff logs. Of course the time for recovery for one an the other is drastically different too, it all depends as said on the size of your database and your turnover times.

How flushing the binlogs will affect replication

It won't affect replication if you flush the logs always after they've been transmitted to the slave, otherwise you'll run into some nasty issues due to the slave not being able to request the exact block of the binlog that it needs

  • thanks for your answer, our installation stores critical medical data. Luckily, it's not too big, that's why currently are using full dumps every few hours. I wanted to study the possibility to make incremental backups more often so that, in case of failure, we lost less hours of info. I asked because I don't know if the command to flush the binlogs checks if the slave has replicated the last changes before flushing or if it flushes without any check? if it's the first case, I can use it without worries. if it's the second case, I must force a replication before each incremental backup. – Juan Clavero Jan 28 '11 at 7:49
  • As far as I know it doesn't check, I flushed logs myself a couple of times and caught my fingers badly – lynxman Jan 28 '11 at 8:03
  • Ok,that's just the info that I need! so the right thing to do is to check that slaves are up-to-date before any incremental backup and any binlog flushing. Thank you very much! – Juan Clavero Jan 29 '11 at 7:55
  • @lynxman what is a "full binary backup"? I have a mysqldump --all-databases --master-data , and the bin logs. – Tom H Jan 19 '12 at 17:45

It should not affect the replication process. You can copy these binary logs to a new machine (backup machine). When needed, you just use them. So, there is no intervention to the replication process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.