Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Continuous MySQL backup validation: Restoring backups

code.facebook.com

Post reply on HN
Print view View original

11–20 of 29 posts

Re: Continuous MySQL backup validation: Restoring backups

#11

Post by eloff »

jivid wrote: post #9
mappu wrote: post #8

> We do these by taking another full dump and only storing the difference between it and the last full backup that was taken. Do you diff against the same base, or create an incremental chain? How many diffs do you take in between recapturing a full image? At $DAYJOB we always take full backups into a fast in-house deduplicating store. > Periodically, each peon syncs with the ORC DB to look for new jobs assigned to i…

> Do you diff against the same base, or create an incremental chain? How many diffs do you take in between recapturing a full image? At $DAYJOB we always take full backups into a fast in-house deduplicating store. We always diff against the same base and have 5 days in between subsequent full dumps. The number of days just comes from a trade off between space occupied by the backups and time it takes to generate them…

I understood it to mean that if you restore table A and table B in parallel, if there is a foreign key between them, then referential integrity checks would cause one of the loading operations to fail. How do you deal with that?

Re: Continuous MySQL backup validation: Restoring backups

#12

Post by evanelias »

TimWolla wrote: post #10
jivid wrote: post #9

Earlier quoted context omitted.

> Do you diff against the same base, or create an incremental chain? How many diffs do you take in between recapturing a full image? At $DAYJOB we always take full backups into a fast in-house deduplicating store. We always diff against the same base and have 5 days in between subsequent full dumps. The number of days just comes from a trade off between space occupied by the backups and time it takes to generate them…

> I'm not sure what you mean by parallelism through disabling FK integrity. Say you have a `user` table and a `post` table with `post.user_id` being a FOREIGN KEY on `user.user_id`. Without disabling FK integrity you would not be able to restore a post without restoring the user first. When restoring in parallel this might or might not work out.

Facebook (along with almost everyone else using MySQL at massive scale) doesn't use foreign keys.

They scale poorly in MySQL, and they lose a lot of purpose in a massively sharded environment anyway. For example, say you like a status post on Facebook, or friend another user. It's very unlikely that the liked status or friended user exists on the same shard as your account, and there's no way to enforce a foreign key relationship in an inherently non-distributed database like MySQL.

So instead integrity is handled at the application layer, with additional background processes to fix the occasional integrity problem and detect integrity anomalies.

Re: Continuous MySQL backup validation: Restoring backups

#13

Post by danudey »

eloff wrote: post #11
jivid wrote: post #9

Earlier quoted context omitted.

> Do you diff against the same base, or create an incremental chain? How many diffs do you take in between recapturing a full image? At $DAYJOB we always take full backups into a fast in-house deduplicating store. We always diff against the same base and have 5 days in between subsequent full dumps. The number of days just comes from a trade off between space occupied by the backups and time it takes to generate them…

I understood it to mean that if you restore table A and table B in parallel, if there is a foreign key between them, then referential integrity checks would cause one of the loading operations to fail. How do you deal with that?

It's likely that foreign key checks aren't handled at the RDBMS level, but rather at the application level.

Re: Continuous MySQL backup validation: Restoring backups

#14

Post by predakanga »

Interesting, though I was hoping for a bit more focus on the validation part - unless I'm missing something, there's nothing protecting the actual SQL dumps against bitrot.

I had to design against that particular problem recently, ended up taking a pt-table-checksum at the time-of-dump and verifying newly restored backups against that to ensure the backup's integrity.

Unfortunately that requires halting replication temporarily, so I was hoping to hear of a more ingenious solution.

Re: Continuous MySQL backup validation: Restoring backups

#15

Post by jivid »

predakanga wrote: post #14

Interesting, though I was hoping for a bit more focus on the validation part - unless I'm missing something, there's nothing protecting the actual SQL dumps against bitrot. I had to design against that particular problem recently, ended up taking a pt-table-checksum at the time-of-dump and verifying newly restored backups against that to ensure the backup's integrity. Unfortunately that requires halting replication t…

There's a few different ways to verify, a few of them involving stopping replication, like you pointed out. These can also sometimes be quite expensive, so depending on the type of verification required, the verification method can be tuned.

We also implemented table checksums inside of mysqldump, allowing us to dump out the restored data and compare checksums as well, if required. https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0...

Re: Continuous MySQL backup validation: Restoring backups

#16

Post by predakanga »

jivid wrote: post #15
predakanga wrote: post #14

Interesting, though I was hoping for a bit more focus on the validation part - unless I'm missing something, there's nothing protecting the actual SQL dumps against bitrot. I had to design against that particular problem recently, ended up taking a pt-table-checksum at the time-of-dump and verifying newly restored backups against that to ensure the backup's integrity. Unfortunately that requires halting replication t…

There's a few different ways to verify, a few of them involving stopping replication, like you pointed out. These can also sometimes be quite expensive, so depending on the type of verification required, the verification method can be tuned. We also implemented table checksums inside of mysqldump, allowing us to dump out the restored data and compare checksums as well, if required. https://github.com/facebook/mysql-5…

Very nice, thanks for the link!

I'll definitely be seeing if I can replace my system with that; reducing the overhead to a single transaction would be a big win.

Re: Continuous MySQL backup validation: Restoring backups

#18

Post by jivid »

wanderr wrote: post #17

Why mysqldump instead of Percona's xtrabackup/innobackupex? I was under the impression that the latter had a lot of advantages and have been considering switching, wondering if there is a good reason not to.

We take logical backups, not physical, and mysqldump is the best option for that. Having logical backups means we can do logical diffs as well (see https://youtu.be/Fe2oLZ4CWD4?t=951), and we've added table checksum support to mysqldump in our branch of MySQL (https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0...)

Re: Continuous MySQL backup validation: Restoring backups

#19

Post by mathnode »

jivid wrote: post #18
wanderr wrote: post #17

Why mysqldump instead of Percona's xtrabackup/innobackupex? I was under the impression that the latter had a lot of advantages and have been considering switching, wondering if there is a good reason not to.

We take logical backups, not physical, and mysqldump is the best option for that. Having logical backups means we can do logical diffs as well (see https://youtu.be/Fe2oLZ4CWD4?t=951 ), and we've added table checksum support to mysqldump in our branch of MySQL ( https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0... )

Is the FB branch version of mysqldump still single threaded? How do you cope with that?

I currently "fake it", using "START TRANSACTION WITH CONSISTENT SNAPSHOT", with multiple mysqldump processes running, where I can't get mydumper deployed.

Re: Continuous MySQL backup validation: Restoring backups

#20

Post by jivid »

mathnode wrote: post #19
jivid wrote: post #18

Earlier quoted context omitted.

We take logical backups, not physical, and mysqldump is the best option for that. Having logical backups means we can do logical diffs as well (see https://youtu.be/Fe2oLZ4CWD4?t=951 ), and we've added table checksum support to mysqldump in our branch of MySQL ( https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0... )

Is the FB branch version of mysqldump still single threaded? How do you cope with that? I currently "fake it", using "START TRANSACTION WITH CONSISTENT SNAPSHOT", with multiple mysqldump processes running, where I can't get mydumper deployed.

We run a single mysqldump with --single-transaction for each database on the server, nothing special.
Post reply on HN