Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Percona Xtrabackup: What’s new, Cloud Backups a...

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

Marcelo Altmann

June 09, 2023
Tweet

More Decks by Marcelo Altmann

Other Decks in Programming

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. 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
  3. 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
  4. © 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
  5. © 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
  6. © 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
  7. © 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
  8. © 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
  9. © 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
  10. © 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
  11. © Copyright 2023 Percona® LLC. All rights reserved • After

    each backup checkpoint LSN Is noted • LSN 6 Page Tracking
  12. © Copyright 2023 Percona® LLC. All rights reserved • Without

    page tracking • Brutal force • Full scan Page Tracking
  13. © 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
  14. © 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
  15. © 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
  16. © 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
  17. © 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
  18. © 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
  19. © 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
  20. © 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
  21. © 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
  22. © 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
  23. © 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
  24. © 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
  25. © 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
  26. © Copyright 2023 Percona® LLC. All rights reserved • Join

    at slido.com • #4260 437 What is next - What you want ?
  27. © 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
  28. © 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
  29. © 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
  30. © 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
  31. © 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
  32. © Copyright 2023 Percona® LLC. All rights reserved • Use

    the Console UI to restore from S3 Migration to AWS RDS - Restore