Percona Xtrabackup: What’s new, Cloud Backups and Migrations

Xtrabackup is a fantastic tool for creating MySQL backups locally. But what if you are in a cloud environment? How can you take advantage of cloud technologies by using Percona Xtrabackup?

In this session we cover the latest developments to XtraBackup

Transcript

1. © Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup: What’s new, Cloud Backups and Migrations Marcelo Altmann - Senior Software Engineer Matthew Boehm - Principal MySQL Architect Percona Live Denver / May 2023
2. © Copyright 2023 Percona® LLC. All rights reserved Intro
3. Marcelo Altmann • Senior Software Engineer @ Percona • Working on Xtrabackup Project • Author of key features ◦ FIFO Datasink / xbcloud multi-thread ◦ Memory Estimation ◦ ZSTD Compression Support ◦ Xbcloud exponential Backoff ◦ Xbcloud instance profile ◦ KMIP & KMS Keyring Component Integration
4. Matthew Boehm • Principal Architect (MySQL) @ Percona • Consultant in Professional Services ◦ <Redacted>, Inc. - Fortune 100 ◦ <Redacted>, Corp. - Fortune 10 ◦ <Redacted>, LLC. - Fortune 200 ◦ <Redacted>, Inc. - Fortune 10 ◦ <Redacted>, Inc. - Fortune 100 • Lead Trainer, https://percona.com/training ◦ Operations and Troubleshooting ◦ Scaling and Optimizations ◦ ProxySQL ◦ Group Replication ◦ Percona XtraDB Cluster
5. © Copyright 2023 Percona® LLC. All rights reserved • Who is still on MySQL 5.7? ◦ What's holding you back from MySQL 8.0? • Who here has… ◦ … heard of Percona Xtrabackup? ◦ … used Xtrabackup? ◦ … configured automated backups using Xtrabackup? ▪ Destination? (ie: local host, mounted storage, cloud-block storage) ◦ … used something other than Xtrabackup? Quick Survey of Hands
6. © Copyright 2023 Percona® LLC. All rights reserved • Percona Xtrabackup is the leading tool for taking hot, online, physical backups. • Quick Feature List ◦ Hot, Online (ie: no locking/blocking) ◦ Incremental / Differential ◦ Direct-to-compressed ◦ Streaming (ie: no local disk required) ◦ Encrypted Tables Support Just in Case You Don't Know
7. © Copyright 2023 Percona® LLC. All rights reserved What’s new
8. © Copyright 2023 Percona® LLC. All rights reserved • MySQL TDE - Transparent Data Encryption • Each Entity (Table, Redo, Undo, Binlog, DoubleWrite) has a set of keys on its header • This pair of keys are used to encrypt / decrypt the data inside the entity • This pair of keys are stored encrypted on entity • Master key used to decrypt the pair of keys is controlled by a keyring component / plugin • Up to 8.0.26 - Keyring File (not suitable for production) and Vault (PS only) Encryption - Keyring Components
9. © Copyright 2023 Percona® LLC. All rights reserved • Key Management Interoperability Protocol - KMIP • 8.0.27 - Introduced as Tech Preview • 8.0.30 - Transitioned to GA • Widely used Open Standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management. • 42+ Commercially Available KMIP products* • IBM / Oracle / VMWare / Hashicorp * https://wiki.oasis-open.org/kmip/KnownKMIPImplementations Encryption - Keyring Components - KMIP
10. © Copyright 2023 Percona® LLC. All rights reserved • Amazon Key Management Service - KMS • 8.0.28 - Introduced as Tech Preview • 8.0.30 - Transitioned to GA • Centralized control service for cryptographic keys used to protect data • Similar to keyring file, but MK is kept encrypted by the KMS key Encryption - Keyring Components - KMS
11. © Copyright 2023 Percona® LLC. All rights reserved • 8.0.30 - Completely redesigned on server • FROM: Reusable fixed size clock-type pair/or more of files (ib_logfile0, ib_logfile1) • TO: Set of 32* dynamic size ever incremental files (#innodb_redo/#ib_redo0, …) • Xtrabackup is still capable of backup both layouts • Added the ability to Register redo log consumer REDO log follow thread
12. © Copyright 2023 Percona® LLC. All rights reserved • 8.0.18 - Component Added on MySQL->PS server • 8.0.27 - Introduced as Tech Preview • 8.0.30 - Transitioned to GA • Used to discover data changed from Full to Incremental • Percona Server had its own implementation. ◦ Removed at 8.0.30 in favor of upstream implementation Page Tracking
13. © Copyright 2023 Percona® LLC. All rights reserved • After each backup checkpoint LSN Is noted • LSN 6 Page Tracking
14. © Copyright 2023 Percona® LLC. All rights reserved • Without page tracking • Brutal force • Full scan Page Tracking
15. © Copyright 2023 Percona® LLC. All rights reserved • With page tracking • PXB asks Page Tracking for pages Changed since last backup up to now • Gets direct access to changed pages • Useful for big dataset where only a small % of the data changes Page Tracking
16. © Copyright 2023 Percona® LLC. All rights reserved • Test: ◦ Data Set: 100G ◦ Changed 1% ◦ WithOUT Page Tracking (300 seconds - 5 minutes) ◦ With Page Tracking (30 seconds) • Useful up to 50% • Faster Backups -> less redo log to apply -> faster --prepare Page Tracking
17. © Copyright 2023 Percona® LLC. All rights reserved • Introduced on 8.0.30 • Fast lossless compression algorithm • Developed by Facebook • Performs better than LZ4 Compression - ZSTD
18. © Copyright 2023 Percona® LLC. All rights reserved • Current 8.0.32 - Tech Preview • Plan to transition to GA and default at 8.0.34 • Plans for compression at 8.0.35 ◦ Remove qpress at --backup - no longer maintained ◦ --decompress will still be possible for backwards compatibility with old compressed backups Compression - ZSTD
19. © Copyright 2023 Percona® LLC. All rights reserved Compression - ZSTD
20. © Copyright 2023 Percona® LLC. All rights reserved Compression - ZSTD
21. © Copyright 2023 Percona® LLC. All rights reserved Compression - ZSTD
22. © Copyright 2023 Percona® LLC. All rights reserved • Introduced on 8.0.30 • How --prepare works ◦ 512 pages for database pages ◦ Remaining is used for in memory hash group of records ◦ If memory is full, apply the records we have and start a new batch • By defaults, 128MB is allocated • Not a fit for most workloads • Can be adjusted via --use-memory but there is no indication of how much is necessary Smart Memory Estimation
23. © Copyright 2023 Percona® LLC. All rights reserved • Memory and number of pages changed via redo log is calculated during --backup • Can be controlled with: ◦ --estimate-memory - enable/disable the calculations during --backup ◦ --use-free-memory-pct - indicates the % of free memory that can be used for --prepare • If enough memory is available, --prepare can be done in a single batch (fast) Smart Memory Estimation
24. © Copyright 2023 Percona® LLC. All rights reserved Smart Memory Estimation
25. © Copyright 2023 Percona® LLC. All rights reserved • Introduced at 8.0.31 • Requires EC2 instance with Instance Profile attached • Allows xbcloud to operate without specifying any credentials • Credentials are fetched from internal Instance metadata API • Tokens are valid for 6 hours and are renewed automatically by xbcloud • Useful for automation / CI / K8s IAM Instance profile
26. © Copyright 2023 Percona® LLC. All rights reserved • Introduced at 8.0.32 • Each Minor Release will have two point release. They are called: ◦ Merge Release - Merge upstream code base on top of last release ◦ Feature Release - Bug fix and new features added on top of last merge release • Short the gap between Upstream release and a compatible PXB version • QA is the most important phase (and longest) of a release. Dual Release Cycle
27. © Copyright 2023 Percona® LLC. All rights reserved Dual Release Cycle
28. © Copyright 2023 Percona® LLC. All rights reserved • Introduced at 8.0.33-28 (feature release) - TBD • Changes how tables are opened • Before Dictionary cache, all tables were opened at once, and remained open by the duration of the --prepare • Causes high memory usage. Dictionary Cache
29. © Copyright 2023 Percona® LLC. All rights reserved Dictionary Cache
30. © Copyright 2023 Percona® LLC. All rights reserved • Test 3M tables • Without dictionary cache ◦ Time: 1h58m ◦ Memory: ~40G • With Dictionary Cache: ◦ Time: 1h15m - 36% faster ◦ Memory: ~2G - 20x less memory Dictionary Cache
31. © Copyright 2023 Percona® LLC. All rights reserved • Introduced at 8.0.33-28 (feature release) - TBD • Uses Named PIPE (FIFO) for streaming • Current STDOUT: ◦ Throughput around 1.8 Gbps / 2.8 Gbps ◦ Bottleneck on certain setup (high speed disk and network). • FIFO: ◦ Multiple threads can read/write simultaneously ◦ 10Gbps+ Streaming - FIFO Datasink
32. © Copyright 2023 Percona® LLC. All rights reserved Streaming - FIFO Datasink
33. © Copyright 2023 Percona® LLC. All rights reserved Streaming - FIFO Datasink
34. © Copyright 2023 Percona® LLC. All rights reserved Streaming - FIFO Datasink
35. © Copyright 2023 Percona® LLC. All rights reserved Streaming - FIFO Datasink - 1TB PUT test • STDOUT - 239 MBps (1.8 Gbps) - 01:25:24 • FIFO ( 8 pipes) - 1.15 GBps (9.2 Gbps) - 00:16:01
36. © Copyright 2023 Percona® LLC. All rights reserved Streaming - FIFO Datasink - 1TB GET test • STDOUT - 369 MBps (2.8 Gbps) - 00:57:05 • FIFO ( 8 pipes) - 1.15 GBps (9.2 Gbps) - 00:15:44
37. © Copyright 2023 Percona® LLC. All rights reserved What is next?
38. © Copyright 2023 Percona® LLC. All rights reserved • Join at slido.com • #4260 437 What is next - What you want ?
39. © Copyright 2023 Percona® LLC. All rights reserved • Which feature(s) would you like to be added to xtrabackup? • PXB-2901 - Add Full page tracking feature in PXB • PXB-3034 - Reduce the time the instance remains locked during the backup • PXB-2745 - Selective table backup/restore What is next - What you want ? Join at slido.com #4260 437
40. © Copyright 2023 Percona® LLC. All rights reserved Backups and Migrations
41. © Copyright 2023 Percona® LLC. All rights reserved mysql1# xtrabackup --backup --compress-threads 4 --parallel 4 --compress=ZSTD \ --stream=xbstream > backup.xbs Simple Backups - Local • Backup Entire Database to Single File ◦ Don't stream to tar: ▪ Cannot handle parallelism ▪ Requires local temp space = to largest table
42. © Copyright 2023 Percona® LLC. All rights reserved mysql1# xtrabackup --backup --compress-threads 4 --parallel 4 --compress \ --stream=xbstream | ssh user@otherhost "cat - >backup.xbs" Simple Backups - Streaming • Backup Entire Database to Remote • Using socat, extract on destination hostname#: socat - TCP-LISTEN:3307 | xbstream -vx -C /var/lib/mysql mysql1# xtrabackup --backup --compress-threads 4 --parallel 4 --compress \ --stream=xbstream | socat - TCP:hostname:3307
43. © Copyright 2023 Percona® LLC. All rights reserved mysql1# xtrabackup --backup --compress-threads 4 --parallel 4 --compress \ --stream=xbstream | openssl des3 -salt -k "password" >backup.xbs.des3 Simple Backups - Encryption • Backup Entire Database with Encryption
44. © Copyright 2023 Percona® LLC. All rights reserved • AWS supports creating/restoring RDS MySQL from S3 • Use Percona Xtrabackup + AWS CLI to store backup in S3 ◦ Must end with .xbstream ◦ Use split for databases larger than 5TB • Restore RDS using S3 bucket Migration to AWS RDS - Backup mysql1# xtrabackup --backup --compress-threads 4 --parallel 4 \ --stream=xbstream | aws s3 cp - s3://mybucket/db_backup_230520.xbstream --expected-size <sizeInBytes> • --expected-size required if >1TB due to AWS limit of 10,000 shards
45. © Copyright 2023 Percona® LLC. All rights reserved • Use the Console UI to restore from S3 Migration to AWS RDS - Restore
46. Thank you! Don’t forget to rate this talk