Percona Xtrabackup Best Practices

This presentation shows the best practices when using Percona Xtrabackup to take backups. It shows how to compress, encrypt, stream and speed up backups.

Transcript

1. Percona Xtrabackup Best Practices Marcelo Altmann Senior Support Engineer - Percona
2. Agenda
3. 3 Agenda • Intro • The basics • Compression • Encryption • Incremental Backup • Performance • Streaming • Examples
4. Who is speaking ?
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
6. Intro
7. 7 Intro • Hot Backup utility for MySQL • Support - InnoDB - XtraDB (Percona XtraDB Cluster / Galera Cluster) - MyISAM
8. The basics
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/
10. Compressing backup
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/
12. Encrypting backup
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 openssl rand -base64 24 •--encrypt-key=ENCRYPTION_KEY •--encrypt-key-file=KEYFILE
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
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"
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
17. 17 Encrypting backup •Decrypt backups xtrabackup --target-dir=/backups/1 --decrypt=AES256 -- encrypt-key-file=/root/.my_backup_key --remove-original
18. Incremental backups
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
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
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
22. Performance
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
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
25. Streaming
26. 26 Streaming •--stream •tar •xbstream - allows parallel stream - allows compression
27. Examples - Building a Slave
28. 28 Examples - Building a Slave Replica> nc -l 9999 | xbstream -x -C /var/lib/mysql/; Master> xtrabackup --backup --parallel=6 --compress \ -- compress-threads=4 --stream=xbstream \ --target-dir=./ | nc replica.ip 9999
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
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;
31. Examples - Multiple Stream
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
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
34. 34 Thank You Sponsors!
35. 35 SAVE THE DATE! CALL FOR PAPERS OPENING SOON! www.perconalive.com April 23-25, 2018 Santa Clara Convention Center
36. Questions ? Marcelo Altmann @altmannmarcelo