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

21–29 of 29 posts

Re: Continuous MySQL backup validation: Restoring backups

#21

Post by wanderr »

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... )

xtrabackup does differential backups as well...is there another advantage to doing logical backups? are the diffs significantly smaller?

Re: Continuous MySQL backup validation: Restoring backups

#22

Post by snuxoll »

evanelias wrote: post #6
babuskov wrote: post #2

Interesting. I assumed FB would have hot replicas (kept up to date with live master-slave replication at all times) - ready to go any time a main database fails. Cascade that to another layer of slaves and there's no need to restore anything ever.

Facebook has hot replicas in every region. But replicas and backups serve completely different purposes. Replicas are for failover and read scalability. In terms of failover, when a master dies unexpectedly Facebook's automation fails over to promote a replica to be the new master in under 30 seconds and with no loss of committed data. Backups are for when something goes horribly wrong -- i.e. due to human error -- a…

I suppose at Facebook scale it might be infeasible, but couldn't you get the same effect by archiving log segments and a periodic binary full backup? This is precisely what I do with my PostgreSQL databases (though with some friendly automation with pg barman), I assume you could do the same with some tooling around MySQL's binlog facilities.

Re: Continuous MySQL backup validation: Restoring backups

#23

Post by [deleted] »

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…

[deleted]

Re: Continuous MySQL backup validation: Restoring backups

#24

Post by mathnode »

jivid wrote: post #20
mathnode wrote: post #19

Earlier quoted context omitted.

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.

Thanks, how big is a single instance? All out to a single file?

Re: Continuous MySQL backup validation: Restoring backups

#25

Post by evanelias »

snuxoll wrote: post #22
evanelias wrote: post #6

Earlier quoted context omitted.

Facebook has hot replicas in every region. But replicas and backups serve completely different purposes. Replicas are for failover and read scalability. In terms of failover, when a master dies unexpectedly Facebook's automation fails over to promote a replica to be the new master in under 30 seconds and with no loss of committed data. Backups are for when something goes horribly wrong -- i.e. due to human error -- a…

I suppose at Facebook scale it might be infeasible, but couldn't you get the same effect by archiving log segments and a periodic binary full backup? This is precisely what I do with my PostgreSQL databases (though with some friendly automation with pg barman), I assume you could do the same with some tooling around MySQL's binlog facilities.

Yes, although if using the binlogs as-is, that's effectively incremental backup instead of differential. The disadvantages of incremental solutions are that they require more storage and take longer to restore (especially if only doing full backups every few days); the upside is less complexity.

Re: Continuous MySQL backup validation: Restoring backups

#26

Post by evanelias »

wanderr wrote: post #21
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... )

xtrabackup does differential backups as well...is there another advantage to doing logical backups? are the diffs significantly smaller?

Yes, logical backups are smaller due to lack of index overhead. And since logical backups are textual, they can also be used for other clever purposes, such as ETL pipelines.

Re: Continuous MySQL backup validation: Restoring backups

#27

Post by jivid »

mathnode wrote: post #24
jivid wrote: post #20

Earlier quoted context omitted.

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

Thanks, how big is a single instance? All out to a single file?

Instance size varies a lot because we've got a lot of different MySQL workloads, some with very different configurations.

Remember we're backing up each database separately though, not the entire MySQL instance at once. Each database's backup is in a separate file.

Re: Continuous MySQL backup validation: Restoring backups

#28

Post by jivid »

wanderr wrote: post #21
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... )

xtrabackup does differential backups as well...is there another advantage to doing logical backups? are the diffs significantly smaller?

In addition to what evanelias said, a logical dump also means we can load it into a MySQL instance running a different storage engine as well. In our case, it allows us to take a mysqldump from an InnoDB instance and load it into a MyRocks instance if we wish.

Re: Continuous MySQL backup validation: Restoring backups

#29

Post by mathnode »

jivid wrote: post #27
mathnode wrote: post #24

Earlier quoted context omitted.

Thanks, how big is a single instance? All out to a single file?

Instance size varies a lot because we've got a lot of different MySQL workloads, some with very different configurations. Remember we're backing up each database separately though, not the entire MySQL instance at once. Each database's backup is in a separate file.

Thanks
Post reply on HN