2

How can we ensure MySQL backups are consistent when they are taken as full backups as well as incremental backups using binlogs?

According to me, I record the COUNT(*) from random tables after issuing flush logs on the database. I then take binlogs generated until it flushes the binary file and ensuring the table is not altered while doing SELECT COUNT(*). I then restore the binary logs in different database server and cross check COUNT(*) to ensure the random tables are the same or not against production database and restored database.

Any other thoughts? Keep in mind that hits from application to tables can be paused only for a few minutes.

Hannah Vernon ♦
71.1k22 gold badges178 silver badges324 bronze badges
asked Sep 5, 2012 at 8:16

1 Answer 1

1

Are you using innodb? If so, Percona's XtraBackup is a great choice for automating backups while keeping the server operational and responsive. It also supports incremental backups to shorten the backup duration.

Another option is to have another database that is a replication slave and you backup this slave. You can shut down replication, backup, and resume, never impacting the production data.

I personally use a combination of the two solutions. We have a backup sync and we use xtrabackup to backup this server, thus allowing us consistant backups on demand without impacting production load.

answered Nov 5, 2012 at 2:59

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.