Logical backup with mysqldump

Facebook's MySQL 5.6 includes enhancements to mysqldump to support MyRocks consistent backup. To take MyRocks backup with mysqldump, you need to use mysqldump built from Facebook's MySQL 5.6.

Like InnoDB, MyRocks supports consistent logical backup with mysqldump, with same options. Here is an example.

mysqldump -h 127.0.0.1 -P 3306 --default-character-set=binary --single-transaction --master-data=2

Facebook's mysqldump checks default_storage_engine global variable. It default_storage_engine is RocksDB, mysqldump takes consistent RocksDB backup. If it is InnoDB, mysqldump takes consistent InnoDB backup.

One thing to keep in mind when taking logical backups is the --order-by-primary option, which dumps each table's rows sorted by it's primary key. This is extremely useful during bulk load, as it allows MyRocks to write directly to SST files in RocksDB, bypassing the need to write data into MemTable (and hence skipping all the following compactions as well). One requirement of bulk load is that the data for the Primary Key is loaded in order.

We have added a --rocksdb-bulk-load option to our version of mysqldump which will wrap the dumps with set rocksdb_bulk_load ON/OFF, which enables bulk load in MyRocks.