Share
Embed
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
Transcript
46 slides
-
© 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 -
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 -
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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© Copyright 2023 Percona® LLC. All rights reserved • After
each backup checkpoint LSN Is noted • LSN 6 Page Tracking -
© Copyright 2023 Percona® LLC. All rights reserved • Without
page tracking • Brutal force • Full scan Page Tracking -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© Copyright 2023 Percona® LLC. All rights reserved • Join
at slido.com • #4260 437 What is next - What you want ? -
© 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 -
© 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 -
© 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 -
© 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 -
© 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 -
© Copyright 2023 Percona® LLC. All rights reserved • Use
the Console UI to restore from S3 Migration to AWS RDS - Restore