Percona XtraBackup at Alibaba Cloud

1 .Percona XtraBackup at Alibaba Cloud Bo Wang Alibaba Cloud 

2 .About Me • Bo Wang (Fungo Wang) • Hangzhou, China • Joined Alibaba Cloud at Apr 2014 after got Master’s in CS at Zhejiang University • Senior Engineer at Alibaba Cloud, develop and maintain AliSQL, TokuDB, XtraBackup 2 

3 .Agenda • ApsaraDB on Alibaba Cloud • How we use XtraBackup • How we improve XtraBackup 3 

4 .ApsaraDB on Alibaba Cloud 

5 .ApsaraDB on Alibaba Cloud Database As A Service, for your data safety, for your application stability. 2003 2011 2014 2017 7 5 61 66 * 5 * * 5 * * 5 * * . . .171 5 * https://github.com/alibaba/alisql 5 

6 .ApsaraDB on Alibaba Cloud 0 Hardware Hardware B cost A 7C cost 3 A 0 Management 3 Management 7 A cost cost 0 % % 0 A B B 0 B % 6 

7 .ApsaraDB on Alibaba Cloud Backup is a fundamental facility, it's a basic requirement for our database products. 7 

8 .How we use XtraBackup 

9 .Backup Type Our MySQL instances can be provisioned on physical machines or ECS VMs. • Physical backup, used for physical machine instance (XtraBackup) • Cloud disk snapshot, used for ECS VM instance (disk snapshot) • Logical backup, an additional product feature available on user portal (mysqldump) 9 

10 .Backup Strategy • Full backup • Backup regularly on daily base, the cycle is configurable • Stream backup, no intermediate temp files on local disk • Stream to OSS (Object Storage Service) • Stream between hosts, in some migrating/rebuilding scenarios 10 

11 .Backup Strategy • Backup on slave node by default, can also on master node when slave node is not available/suitable • Backup result can be downloaded and recovered locally by our customers, not locked by ApsaraDB 11 

12 .RDS XtraBackup Evolution • At the early time of ApsaraDB, we just download PXB (Percona XtraBackup) rpm package and use it, such as percona-xtrabackup-2.0.6-521.rhel6.x86_64.rpm • We start to fork our own RXB (RDS XtraBackup) branch from PXB 2.1.9, develop on RXB and merge upstream PXB occasionally. 12 

13 .Backup/Recover Command • Backup innobackupex --defaults-file=my.cnf --host=host --user=user --port=port --password=pass --slave-info --stream=tar | gzip | backup_agent stream upload to OSS • Download and extract backup_agent fetch from OSS | gzip | tar xvf -C restore_dir/ • Recover innobackupex --apply-log --use-memory=bp_memory_size restore_dir/ • Restore mv files to directories specified in my.cnf 13 

14 .How we improve XtraBackup 

15 .Multiple Engines • ApsaraDB provides multiple storage engines for MySQL, RXB can backup data files in all these engines • InnoDB • MyISAM, CSV, ARCHIVE • TokuDB • MyRocks 15 

16 .Multiple Engines - Basics 1. Backup result must be recovered to a consistent point (binlog pos) • Tables inside one storage engine • Tables across all storage engines • Server layer data (frm, par, etc.) 2. Backup should avoid affecting mysqld as much as possible 3. Each storage engine has its own characteristics, and should be fully leveraged when design backup solution 16 

17 .Multiple Engines - Basics 17 

18 .Multiple Engines - MyISAM • MyISAM is a non-transactional storage engine, it is simple compared to InnoDB • No WAL and crash recover process, so the MYDs and MYIs must be in clean/consistent state when copying • A rough and brute way: freeze MyISAM engine (FTWRL), then copy data • Simple copy, no need to understand engine detail, and no recover process when prepare 18 

19 .Multiple Engines - MyISAM • FTWRL is too heavy, all engines are frozen (read only), and all tables are closed (flush). • This operation affects all engines, even they do not need it. InnoDB/TokuDB/MyRocks are victims when copying MyISAM files • The global lock is only needed to get consistent point • Use a lightweight way, percona-server has backup locks (MDL): • LOCK TABLES FOR BACKUP // block non-transactional IUD and all DDL • LOCK BINLOG FOR BACKUP (freezing point) // block binlog position or Exec_Master_Log_Pos advance 19 

20 .Multiple Engines - MyISAM 20 

21 .Multiple Engines - TokuDB • TokuDB is a transactional storage engine, like InnoDB • Sharp checkpoint, variable length block, COW at block level • Use BTT (Block Translation Table) to maintain mapping between block number and block coord(offset, size), BTT is persistent to disk by checkpoint • Each FT data file contains two copy of data (two BTTs), at least one copy is valid and the data corresponding to the very last checkpoint • TokuDB redo log is like binlog, it’s logical log, so the engine data must be in consistent state before applying redo log • Checkpoint lock can grabbed by user to prevent server from performing checkpoint 21 

22 .Multiple Engines - TokuDB • Use TokuDB sharp checkpoint and COW features, hold TokuDB checkpoint lock while copying TokuDB FT data. • Redo copying finished before copying data • We may backup many future blocks, TokuDB can’t see them when recover, treat them as garbage (unused space). Because checkpoint is blocked, and BTT is not flushed and updated. 22 

23 .Multiple Engines - TokuDB • Holding checkpoint lock for a long time may be dangerous • Long recover time if crash • No checkpoint, no redo log purging, accumulated redo logs will occupy too much disk space • TokuDB redo logs and FT data files are copied at a coarse level (like MyISAM), RXB do not understand TokuDB format. Redo log recovery is performed by mysqld, not RXB(--apply-log). • No validation for redo log entry, and FT block • Limiting future feature development 23 

24 .Multiple Engines - TokuDB 2.0 • Checkpoint lock is too heavy, what we need is a FT snapshot. We add a FT snapshot feature to TokuDB to relieve dependence on checkpoint • Maintain a backup BTT in memory, which is a copy of latest checkpoint BTT, block in backup BTT is protected and will not be free and reused 24 

25 .Multiple Engines - TokuDB 2.0 • Checkpoint lock is also needed, but hold for a very short time. • TokuDB backup procedure is symmetrical with InnoDB • TokuDB engine is embedded into RXB just like InnoDB • Redo log entry is verified • Only copy necessary FT data • Redo recover is performed by RXB --apply-log 25 

26 .Multiple Engines - MyRocks • MyRocks is a transactional storage engine, like InnoDB/TokuDB • COW at file level (SST files), and MyRocks can create a snapshot to a specified dir • SET GLOBAL rocksdb_create_checkpoint = '/path/to/snapshost' 26 

27 .Multiple Engines - MyRocks • SET GLOBAL rocksdb_create_checkpoint = ‘/path/to/backup’, to create a snapshot under backup dir, contains MyRocks data, redo log and meta file. • Currently, MyRocks data is handled at a coarse level, RXB do not understand MyRocks format, recover is performed by mysqld. 27 

28 .Multiple Engines - All In One 28 

29 . Table Level Recover • For PITR (Point-In-Time Recovery), the customer may want to recover just a few tables. But the whole backup result file must be downloaded and recovered. • The time to download backup result take the majority part in the whole recovery procedure, so if we can fetch only the table needed, the PITR will be much faster. 29