Share
Embed
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.
Marcelo Altmann
October 09, 2019
Transcript
36 slides
-
3 Agenda • Intro • The basics • Compression •
Encryption • Incremental Backup • Performance • Streaming • Examples -
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 -
7 Intro • Hot Backup utility for MySQL • Support
- InnoDB - XtraDB (Percona XtraDB Cluster / Galera Cluster) - MyISAM -
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/ -
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/ -
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 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 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 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 -
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 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 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 -
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 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 -
26 Streaming •--stream •tar •xbstream - allows parallel stream -
allows compression -
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 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 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; -
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 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 -
35 SAVE THE DATE! CALL FOR PAPERS OPENING SOON! www.perconalive.com
April 23-25, 2018 Santa Clara Convention Center