[wanderr]

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.

[jivid]

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

[mathnode]

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.

[jivid]

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

[mathnode]

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

[jivid]

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.

[mathnode]

Thanks

[wanderr]

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

[jivid]

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.

[evanelias]

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.