Hacker News Reader

Comment by wanderr | Hacker News Reader

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.

View on HN

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.

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

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.

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.

Reply on news.ycombinator.com