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

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 speaking ?

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 5

Intro

Intro • Hot Backup utility for MySQL • Support - InnoDB - XtraDB (Percona XtraDB Cluster / Galera Cluster) - MyISAM 7

The basics

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/ 9

Compressing backup

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/ 11

Encrypting backup

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 13

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 14

Encrypting backup • --encrypt-key=ENCRYPTION_KEY [root@localhost ~]# history | grep xtrabackup 40 xtrabackup --backup --target-dir=/backups/1 --encrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" 15

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 16

Encrypting backup • Decrypt backups xtrabackup --target-dir=/backups/1 --decrypt=AES256 --encrypt-key-file=/root/.my_backup_key --remove-original 17

Incremental backups

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 19

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 20

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 21

Performance

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 23

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 24

Streaming

Streaming • --stream • tar • xbstream - allows parallel stream - allows compression 26

Examples - Building a Slave

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 28

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 29

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; 30

Examples - Multiple Stream

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 32

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 33

Thank You Sponsors! 34

April 23-25, 2018 SAVE THE DATE! Santa Clara Convention Center CALL FOR PAPERS OPENING SOON! www.perconalive.com 35

Questions ? Marcelo Altmann @altmannmarcelo