Slide 1

Slide 1 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup: From Zero to Hero Marcelo Altmann - Senior Software Engineer Vinicius Grippa - Senior Support Engineer Percona Live Denver / May 2023

Slide 2

Slide 2 text

© Copyright 2023 Percona® LLC. All rights reserved Intro

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

Vinicius Grippa ● Senior Support Engineer @ Percona ● MySQL and MongoDB specialist ● Working with databases for 18 years ● Co-Author of the book Learning MySQL

Slide 5

Slide 5 text

© Copyright 2023 Percona® LLC. All rights reserved Before we Start

Slide 6

Slide 6 text

© Copyright 2023 Percona® LLC. All rights reserved ● Download the PDF with examples to follow at https://bit.ly/pl_tutorial ● If you are on ARM - Ask presenter for a remote Instance credentials. Before we start

Slide 7

Slide 7 text

© Copyright 2023 Percona® LLC. All rights reserved ● This is an interactive session ● Ask questions ● We will rafle two signed copies of Learning MySQL ● Let’s have some fun! Before we start

Slide 8

Slide 8 text

© Copyright 2023 Percona® LLC. All rights reserved The Basics - How it works

Slide 9

Slide 9 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - General Componentes

Slide 10

Slide 10 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Disk Table Layout

Slide 11

Slide 11 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Disk Table Layout ● LSN - Last Sequence Number Number of bytes written on InnoDB system. Used as a version ID for InnoDB objects.

Slide 12

Slide 12 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Disk Table Layout

Slide 13

Slide 13 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Redo Log (WAL) ● Used to be ib_logfile[0-1]

Slide 14

Slide 14 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Redo Log (WAL)

Slide 15

Slide 15 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Buffer Pool

Slide 16

Slide 16 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Write workflow mysql> UPDATE TABLE table1 SET name = ‘Marcelo’ WHERE ID = 88

Slide 17

Slide 17 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Write workflow mysql> UPDATE TABLE table1 SET name = ‘Marcelo’ WHERE ID = 88

Slide 18

Slide 18 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Write workflow mysql> UPDATE TABLE table1 SET name = ‘Marcelo’ WHERE ID = 88

Slide 19

Slide 19 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 20

Slide 20 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 21

Slide 21 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 22

Slide 22 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 23

Slide 23 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 24

Slide 24 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 25

Slide 25 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --backup

Slide 26

Slide 26 text

© Copyright 2023 Percona® LLC. All rights reserved Percona Xtrabackup Workflow - --prepare

Slide 27

Slide 27 text

© Copyright 2023 Percona® LLC. All rights reserved Hands On!!! Full Backup

Slide 28

Slide 28 text

© Copyright 2023 Percona® LLC. All rights reserved Incremental Backup

Slide 29

Slide 29 text

© Copyright 2023 Percona® LLC. All rights reserved InnoDB - Disk Table Layout ● LSN - Last Sequence Number Number of bytes written on InnoDB system. Used as a version ID for InnoDB objects.

Slide 30

Slide 30 text

© Copyright 2023 Percona® LLC. All rights reserved Incremental Backups ● LSN from last backup (LSN 50) ● Copy pages from that LSN forward ● Redo Follow Thread

Slide 31

Slide 31 text

© Copyright 2023 Percona® LLC. All rights reserved Incremental Backups ● INSERT INTO table1 VALUES ();

Slide 32

Slide 32 text

© Copyright 2023 Percona® LLC. All rights reserved Incremental Backups ● UPDATE table1 SET name = ‘Marcelo’ WHERE ID = 88;

Slide 33

Slide 33 text

© Copyright 2023 Percona® LLC. All rights reserved Incremental Backups ● Brutal Force - Scan all pages and check LSN

Slide 34

Slide 34 text

© Copyright 2023 Percona® LLC. All rights reserved Incremental Backups ● Page Tracking (8.0.27+) ● Server Component to keep track of changed pages ● Xtrabackup calls server API and get list of changed pages between range of LSN

Slide 35

Slide 35 text

© Copyright 2023 Percona® LLC. All rights reserved Hands On!!!

Slide 36

Slide 36 text

© Copyright 2023 Percona® LLC. All rights reserved Streaming

Slide 37

Slide 37 text

© Copyright 2023 Percona® LLC. All rights reserved ● Used to stream data to another machine or Object Storage ● Currently supported Object Storages: ○ S3 / Azure / GCP / Swift ● Uses custom xbstream format - https://bit.ly/xbstream-format ● Introduces two new binaries ○ Xbstream - responsible for interpret xbstream chunks and reassembly the original file ○ Xbcloud - responsible for downloading and uploading chunks to object storage Streaming

Slide 38

Slide 38 text

© Copyright 2023 Percona® LLC. All rights reserved Streaming

Slide 39

Slide 39 text

© Copyright 2023 Percona® LLC. All rights reserved Hands On!!!

Slide 40

Slide 40 text

© Copyright 2023 Percona® LLC. All rights reserved Encrypted and compressed

Slide 41

Slide 41 text

© Copyright 2023 Percona® LLC. All rights reserved ● Uses GnuPG (GPG) library ● Uses Advanced Encryption Standard (AES) algorithm ● Support key lengths of 128 / 192 / 256 Encrypted Backups

Slide 42

Slide 42 text

© Copyright 2023 Percona® LLC. All rights reserved ● Currently support for QuickLZ, LZ4 and ZSTD ○ QuickLZ no longer maintained ○ Deprecated on 8.0.32 ○ ZSTD will become default on 8.0.34 ○ Ability to --backup will be removed at 8.0.35 (Backward compatibility) ● Use LZ4 and ZSTD instead Compressed Backups

Slide 43

Slide 43 text

© Copyright 2023 Percona® LLC. All rights reserved Compressed Backups

Slide 44

Slide 44 text

© Copyright 2023 Percona® LLC. All rights reserved ● Can be tuned with: ○ --parallel - number of parallel copy threads ○ --compress-threads - number of threads responsible for compression work ○ --encrypt-threads - number of threads responsible for encryption work ● Rule of thumbs - parallel > compress > encrypt Encrypted & Compressed Backups

Slide 45

Slide 45 text

© Copyright 2023 Percona® LLC. All rights reserved Hands On!!!

Slide 46

Slide 46 text

© Copyright 2023 Percona® LLC. All rights reserved Partial Backup / Restore

Slide 47

Slide 47 text

© Copyright 2023 Percona® LLC. All rights reserved ● Intentionally copy just required tables ● Can be done by providing: ○ Regular Expression of matching tables ○ File with list of tables ○ File with a list of databases Partial Backups

Slide 48

Slide 48 text

© Copyright 2023 Percona® LLC. All rights reserved ● Restore just specific table(s) from full backup ● Works using Transportable Tablespace ● xtrabackup --prepare --export - produces exportable table spaces files ● For each table, user needs to: ○ Create table with same definition ○ Discard Tablespace ○ Copy table files (.ibd, .cfg) to server datadir ○ Import Tablespace Partial Restore

Slide 49

Slide 49 text

© Copyright 2023 Percona® LLC. All rights reserved Hands On!!!

Slide 50

Slide 50 text

© Copyright 2023 Percona® LLC. All rights reserved Point In Time Recovery (PITR)

Slide 51

Slide 51 text

© Copyright 2023 Percona® LLC. All rights reserved ● Allows for restoring a backup and replay transactions up to a specific point in time ● Useful when a disaster has occurred and the data between the last backup and time of disaster cannot be lost ● Requires copy of binary logs Point In Time Recovery

Slide 52

Slide 52 text

© Copyright 2023 Percona® LLC. All rights reserved Hands On!!!

Slide 53

Slide 53 text

© Copyright 2023 Percona® LLC. All rights reserved What is next - We want to hear from you

Slide 54

Slide 54 text

© Copyright 2023 Percona® LLC. All rights reserved ● Join at slido.com ● #4260 437 What is next - We want to hear from you

Slide 55

Slide 55 text

© 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 Join at slido.com #4260 437

Slide 56

Slide 56 text

© Copyright 2023 Percona® LLC. All rights reserved Rate this tutorial!

Slide 57

Slide 57 text

© Copyright 2023 Percona® LLC. All rights reserved Thank you! ObrigadoMerci Danke Grazie Dhanyavaad Mam'noon Xièxiè Arigato