MySQL Backup and Restore at Facebook scale

1 .MySQL Backup and Restore at Facebook Scale Ola Berjak Production Engineer at MySQL Infrastructure, Facebook London 

2 .MySQL Backup and Restore at Facebook Scale …and how it’s not rocket science Ola Berjak Production Engineer at MySQL Infrastructure, Facebook London 

3 .3 

4 .When do we need backups? How do we perform backups? How do we restore backups? 4 

5 .When do we need backups? How do we perform backups? How do we restore backups? 5 

6 .When do we need backups? How do we perform backups? How do we restore backups? 6 

7 .When do we need backups? 7 

8 .MAL ICIOUS ATTACKE R H ARDWARE F AIL URE H UMAN E RROR 8 

9 .MAL ICIOUS ATTACKE R H ARDWARE F AIL URE H UMAN E RROR 9 

10 .FULL DUMPS DIFFS 10 

11 .How do we perform backups? 11 

12 .Every database, every day 12 

13 . LOGICAL BACKUPS PHYSICAL BACKUPS CUSTOMER LOGIC Easy Complex DEBUGGING Easy Complex SINGLE TABLE Easy Complex RESTORE PORTABILITY Consistent Inconsistent BACKUP AND Long Short RESTORE DURATION 13 

14 . Technical setup mysqldump number of rows for each table FU L L D U M P S zstd compression trailing index 14 

15 .mysqldump --single-transaction --skip-lock- tables (...) 15 

16 .mysqldump --single-transaction --skip-lock- tables (...) | compress and add index 16 

17 .mysqldump --single-transaction --skip-lock- tables (...) | compress and add index | upload 17 

18 .Trailing index { "size": 7331, "offset": 1337, "table_name": "foo" }, { "size": 223, "offset": 8668, "table_name": "bar" } 18 

19 .Open-source mysqldump: https://github.com/facebook/mysql-5.6 zstd: https://github.com/facebook/zstd 19 

20 .Open source tooling will get the job done 20 

21 .Open source tooling will get the job done automysqlbackup scheduling email notifications custom backup rotation 21 

22 .Open source tooling will get the job done automysqlbackup monitoring tools scheduling email notifications alerting custom backup rotation 22 

23 .23 

24 . Technical setup full dump format 2 files for a single diff backup D IFFS rows removed rows inserted and updated 24 

25 . most recent dump backup, new dump “base dump” DiffDatabase diff1 diff2 CREATE TABLE foo CREATE TABLE foo INSERT INTO foo INSERT INTO foo -- rows for foo: 1337 -- rows for foo: 7331 CREATE TABLE bar CREATE TABLE bar INSERT INTO bar INSERT INTO bar 25 

26 . diff1 diff2 CREATE TABLE foo CREATE TABLE foo INSERT INTO foo INSERT INTO foo -- rows for foo: 1337 -- rows for foo: 1337 CREATE TABLE bar CREATE TABLE bar INSERT INTO bar base dump INSERT INTO bar MergeDatabase new dump 26 

27 .F D D D D F D D D D F 27 

28 .2-3x+ less space used 28 

29 .Explore the open source tooling 29