Percona Xtrabackup Best Practices Marcelo Altmann Senior Support - - PowerPoint PPT Presentation

▶
percona xtrabackup best practices
SMART_READER_LITE
LIVE PREVIEW

Percona Xtrabackup Best Practices Marcelo Altmann Senior Support - - PowerPoint PPT Presentation

May 04, 2023 432 likes •811 views

Percona Xtrabackup Best Practices Marcelo Altmann Senior Support Engineer - Percona Agenda Agenda Intro The basics Compression Encryption Incremental Backup Performance Streaming Examples 3 Who is

slide-1
SLIDE 1

Percona Xtrabackup Best Practices

Marcelo Altmann Senior Support Engineer - Percona

slide-2
SLIDE 2

Agenda

slide-3
SLIDE 3

3

Agenda

  • Intro
  • The basics
  • Compression
  • Encryption
  • Incremental Backup
  • Performance
  • Streaming
  • Examples
slide-4
SLIDE 4

Who is speaking ?

slide-5
SLIDE 5

5

Who is Speaking ?

  • Marcelo Altmann
  • Senior Support Engineer @ Percona
  • MySQL DBA @ IE Domain Registry
  • Certifications
  • Oracle Certified Professional, MySQL 5.6 Database Administrator
  • Oracle Certified Professional, MySQL 5.6 Developer
  • Oracle Certified Professional, MySQL 5 Database Administrator
  • Oracle Certified Professional, MySQL 5 Developer
  • Oracle Certified Associate, MySQL 5.0/5.1/5.5
  • Oracle ACE Associate
  • blog.marceloaltmann.com
  • @altmannmarcelo
slide-6
SLIDE 6

Intro

slide-7
SLIDE 7

7

Intro

  • Hot Backup utility for MySQL
  • Support
  • InnoDB
  • XtraDB (Percona XtraDB Cluster / Galera Cluster)
  • MyISAM
slide-8
SLIDE 8

The basics

slide-9
SLIDE 9

9

The basics

  • Take a full backup

xtrabackup --backup --target-dir=/data/backups/

  • Prepare a backup

xtrabackup --prepare --target-dir=/data/backups/

  • Copy / Move back

xtrabackup --copy-back --target-dir=/data/backups/ xtrabackup --move-back --target-dir=/data/backups/

slide-10
SLIDE 10

Compressing backup

slide-11
SLIDE 11

11

Compressing backup

  • Uses qpress
  • Will generate .qp files
  • Take a compressed backup

xtrabackup --backup --compress --target-dir=/data/backups/

  • Decompress a backup

xtrabackup --decompress --target-dir=/data/backups/

  • Remove .qp files (2.3.7+ / 2.4.6+)

xtrabackup --decompress --remove-original \

  • -target-dir=/data/backups/
slide-12
SLIDE 12

Encrypting backup

slide-13
SLIDE 13

13

Encrypting backup

  • Uses libgcrypt
  • Will generate .xbcrypt files
  • Can be used with --compress
  • Compress -> Encrypt | Decrypt -> Decompress
  • --encrypt=ALGORITHM
  • Algorithms: AES128, AES192 and AES256
  • Generate a key
  • penssl rand -base64 24
  • --encrypt-key=ENCRYPTION_KEY
  • --encrypt-key-file=KEYFILE
slide-14
SLIDE 14

14

Encrypting backup

  • --encrypt-key=ENCRYPTION_KEY

[root@localhost ~]# ps -ef | grep xtrabackup root 2653 2541 64 01:52 pts/1 00:00:23 xtrabackup --backup --target-dir=/backups/1

  • -encrypt=AES256
  • -encrypt-key=GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs
slide-15
SLIDE 15

15

Encrypting backup

  • --encrypt-key=ENCRYPTION_KEY

[root@localhost ~]# history | grep xtrabackup 40 xtrabackup --backup --target-dir=/backups/1

  • -encrypt=AES256
  • -encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs"
slide-16
SLIDE 16

16

Encrypting backup

  • --encrypt-key-file=KEYFILE

echo -n $(openssl rand -base64 24) > /root/.my_backup_key chmod 400 /root/.my_backup_key

  • Take encrypted backups

xtrabackup --backup --target-dir=/backups/1

  • -encrypt=AES256
  • -encrypt-key-file=/root/.my_backup_key
slide-17
SLIDE 17

17

Encrypting backup

  • Decrypt backups

xtrabackup --target-dir=/backups/1 --decrypt=AES256

  • -encrypt-key-file=/root/.my_backup_key --remove-original
slide-18
SLIDE 18

Incremental backups

slide-19
SLIDE 19

19

Incremental Backups

  • Backup only changes since last backup
  • --incremental-lsn=LSN
  • end of backup output - xtrabackup: The latest check point (for incremental): ‘XXXXXXX'
  • xtrabackup_checkpoints (to_lsn)
  • --incremental-basedir
  • Copy only the delta changes based on LSN
  • Check LSN of all InnoDB pages
  • Percona Server - Change Page Tracker
  • Add innodb_track_changed_pages to my.cnf
slide-20
SLIDE 20

20

Incremental Backups

  • Monday Full Backup

xtrabackup --backup --target-dir=/backup/Mon-full

  • Tuesday Incremental

xtrabackup --backup --target-dir=/backup/Tue-inc \

  • -incremental-basedir=/backup/Mon-full
  • Wednesday Incremental

xtrabackup --backup --target-dir=/backup/Wed-inc \

  • -incremental-basedir=/backup/Tue-inc
slide-21
SLIDE 21

21

Incremental Backups

  • --apply-log-only to skiip rollback of transactions

xtrabackup --prepare --apply-log-only \

  • -target-dir=/backup/Mon-full

xtrabackup --prepare --apply-log-only \

  • -target-dir=/backup/Mon-full --incremental-dir=/backup/Tue-inc

xtrabackup --prepare --target-dir=/backup/Mon-full \

  • -incremental-dir=/backup/Wed-incr
slide-22
SLIDE 22

Performance

slide-23
SLIDE 23

23

Performance

  • Copy multiple files in parallel
  • -parallel=N_THREADS
  • Compress multiple files in parallel (requires --parallel)
  • -compress-threads=N_THREADS
  • Decompress multiple files in parallel
  • -parallel=N_THREADS
slide-24
SLIDE 24

24

Performance

  • Encrypt multiple files in parallel (requires --parallel)
  • -encrypt-threads=N_THREADS
  • Decrypt multiple files in parallel
  • -parallel=N_THREADS
  • Increase memory used on --prepare
  • -use-memory=SIZE
slide-25
SLIDE 25

Streaming

slide-26
SLIDE 26

26

Streaming

  • --stream
  • tar
  • xbstream
  • allows parallel stream
  • allows compression
slide-27
SLIDE 27

Examples - Building a Slave

slide-28
SLIDE 28

28

Examples - Building a Slave

Replica> nc -l 9999 | xbstream -x -C /var/lib/mysql/; Master> xtrabackup --parallel=6 --compress \

  • -compress-threads=4 --stream=xbstream \
  • -target-dir=./ | nc replica.ip 9999
slide-29
SLIDE 29

29

Examples - Building a Slave

Replica> xtrabackup --decompress --remove-original \

  • -parallel=4 --target-dir=/var/lib/mysql/

Replica> xtrabackup --prepare --use-memory=4G

  • -target-dir=/var/lib/mysql

Replica> chown --recursive mysql.mysql /var/lib/mysql Replica> service mysql start

slide-30
SLIDE 30

30

Examples - Building a Slave

Replica> cat /var/lib/mysql/xtrabackup_binlog_info

mysql-bin.000005 13446 00056888-1111-1111-1111-111111111111:1-838

mysql> CHANGE MASTER TO [...] MASTER_LOG_FILE=’mysql-bin.000005’, MASTER_LOG_POS=13446 mysql> SET GLOBAL gtid_purged="00056888-1111-1111-1111-111111111111:1-838"; mysql> CHANGE MASTER TO [...] MASTER_AUTO_POSITION = 1;

slide-31
SLIDE 31

Examples - Multiple Stream

slide-32
SLIDE 32

32

Examples - Multiple Stream

node3> nc -l 9999 | xbstream -x -C /var/lib/mysql/; node2> mkfifo xbackup.fifo; node2> nc NODE3_IP 9999 < xbackup.fifo & node2> nc -l 9999 | tee xbackup.fifo | \ xbstream -x -C /var/lib/mysql/ node1> xtrabackup --backup --compress \

  • -stream=xbstream --target-dir=./| nc NODE2_IP 9999
slide-33
SLIDE 33

33

Examples - Multiple Stream

node[2-3]> xtrabackup --decompress --remove-original

  • -parallel=4 --target-dir=/var/lib/mysql/

node[2-3]> xtrabackup --prepare --use-memory=4G

  • -target-dir=/var/lib/mysql

xtrabackup: Recovered WSREP position: 31a3e0f4-98b5-11e7-bead-37e53ca238cf:567662

slide-34
SLIDE 34

34

Thank You Sponsors!

slide-35
SLIDE 35

35

SAVE THE DATE!

CALL FOR PAPERS OPENING SOON!

www.perconalive.com

April 23-25, 2018

Santa Clara Convention Center

slide-36
SLIDE 36

Questions ?

Marcelo Altmann @altmannmarcelo