Online Backups with Percona Live Amsterdam - October 2016 2 / 42 - PowerPoint PPT Presentation

1 / 42 Online Backups with Percona Live Amsterdam - October 2016

2 / 42 Kenny Gryp @gryp MySQL Practice Manager

3 / 42 Agenda What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming, Encryption Cloud Backups - α Percona Server Specific Features

4 / 42 What is

5 / 42 What is Backups for:

6 / 42 What is Backups for:

7 / 42 What is https://www.percona.com/software/mysql-database/percona-xtrabackup Online (Non Blocking) InnoDB Backups Physical Datafiles Copied

8 / 42 What is https://www.percona.com/software/mysql-database/percona-xtrabackup Online (Non Blocking) InnoDB Backups Less Impact on Production Faster Restore Time Than Logical Backups Physical Datafiles Copied

9 / 42 What is https://www.percona.com/software/mysql-database/percona-xtrabackup Online (Non Blocking) InnoDB Backups Less Impact on Production Faster Restore Time Than Logical Backups Physical Datafiles Copied Open Source Blocking Support MyISAM... Incremental/Delta Compression/Encryption Cloud Backup

10 / 42 (Data) Backup Process

11 / 42 (Data) Backup Process - 1. Backup Backup .ibd files while in use /var/lib/mysql/ibdata1 /var/lib/mysql/imdb/aka_name.ibd /var/lib/mysql/imdb/aka_title.ibd ...

12 / 42 (Data) Backup Process - 1. Backup Backup .ibd files while in use /var/lib/mysql/ibdata1 /var/lib/mysql/imdb/aka_name.ibd /var/lib/mysql/imdb/aka_title.ibd ... track changes through trx logs /var/lib/mysql/ib_log�le0 /var/lib/mysql/ib_log�le1

13 / 42 (Data) Backup Process - 2. Prepare Apply changes from trx logs onto backupped .ibd files.

14 / 42 (Data) Backup Process - 1. Backup # xtrabackup --backup --target-dir=/data/backups/ 161001 22:16:49 Connecting to MySQL server host: localhost, user: root, password: not Using server version 5.7.14-8-log xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id xtrabackup: uses posix_fadvise(). ... InnoDB: Number of pools: 1 161001 22:16:49 >> log scanned up to (5147619414) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 161001 22:16:50 [01] Copying ./ibdata1 to /data/backups/ibdata1 161001 22:16:50 [01] Copying ./mysql/plugin.ibd to /data/backups/mysql/plugin.ibd 161001 22:16:50 [01] Copying ./mysql/servers.ibd to /data/backups/mysql/servers.ibd 161001 22:16:50 [01] Copying ./mysql/help_topic.ibd to /data/backups/mysql/help_topic.ibd ... 161001 22:17:13 [01] Copying ./imdb/users.ibd to /data/backups/imdb/users.ibd 161001 22:17:13 >> log scanned up to (5147619414) 161001 22:17:14 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 161001 22:17:14 Executing FLUSH TABLES WITH READ LOCK...

15 / 42 (Data) Backup Process - 1. Backup (2) 161001 22:17:14 Starting to backup non-InnoDB tables and �les 161001 22:17:14 [01] Copying ./mysql/db.opt to /data/backups/mysql/db.opt 161001 22:17:14 [01] Copying ./mysql/db.frm to /data/backups/mysql/db.frm 161001 22:17:14 [01] Copying ./mysql/db.MYI to /data/backups/mysql/db.MYI 161001 22:17:14 [01] Copying ./mysql/db.MYD to /data/backups/mysql/db.MYD 161001 22:17:17 [01] Copying ./imdb/users.frm to /data/backups/imdb/users.frm 161001 22:17:17 Finished backing up non-InnoDB tables and �les 161001 22:17:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '5147619454' xtrabackup: Stopping log copying thread. .161001 22:17:17 >> log scanned up to (5147619463) 161001 22:17:17 Executing UNLOCK TABLES 161001 22:17:17 All tables unlocked 161001 22:17:17 [00] Copying ib_buffer_pool to /data/backups/ib_buffer_pool 161001 22:17:17 [00] ...done 161001 22:17:17 Backup created in directory '/data/backups/' 161001 22:17:17 [00] Writing backup-my.cnf 161001 22:17:17 [00] ...done 161001 22:17:17 [00] Writing xtrabackup_info 161001 22:17:17 [00] ...done xtrabackup: Transaction log of lsn (5147619405) to (5147619463) was copied. 161001 22:17:18 completed OK!

16 / 42 (Data) Backup Process - 2. Prepare (1) # xtrabackup --prepare --target-dir=/data/backups/ xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2) xtrabackup: cd to /data/backups xtrabackup: This target seems to be not prepared yet. ... xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) ... InnoDB: Log scan progressed past the checkpoint lsn 5147619405 InnoDB: Doing recovery: scanned up to log sequence number 5147619463 (0%) InnoDB: Doing recovery: scanned up to log sequence number 5147619463 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog �le position 333, �le name perconaserver-bin.000007 ... InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 5147619463 InnoDB: xtrabackup: Last MySQL binlog �le position 333, �le name perconaserver-bin.000007 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 5147619482

17 / 42 (Data) Backup Process - 2. Prepare (2) ... InnoDB: Setting log �le ./ib_log�le101 size to 64 MB InnoDB: Setting log �le ./ib_log�le1 size to 64 MB InnoDB: Renaming log �le ./ib_log�le101 to ./ib_log�le0 InnoDB: New log �les created, LSN=5147619482 InnoDB: Highest supported �le format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 5147619852 InnoDB: Doing recovery: scanned up to log sequence number 5147619861 (0%) InnoDB: Doing recovery: scanned up to log sequence number 5147619861 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog �le position 333, �le name perconaserver-bin ... InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 5147619861 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 5147619880 161001 22:30:05 completed OK!

18 / 42 Restore Process

19 / 42 Restore Process After preparation, the backup directory == data directory

20 / 42 Restore Process After preparation, the backup directory == data directory # cp -R /data/backup/ /var/lib/mysql # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld

21 / 42 Incremental Backups

22 / 42 Incremental Backups

23 / 42 Incremental Backups Only Copy InnoDB Pages That Changed (still copies all non InnoDB Tables)

24 / 42 Incremental Backups Only Copy InnoDB Pages That Changed (still copies all non InnoDB Tables) Pro: Smaller backup size Faster To Backup Incremental & Delta

25 / 42 Incremental Backups Only Copy InnoDB Pages That Changed (still copies all non InnoDB Tables) Pro: Con: Smaller backup size Slower to restore than full Faster To Backup On , all data is read Incremental & Delta

26 / 42 Incremental Backups - Process

27 / 42 Incremental Backups - Process Take Incremental: # xtrabackup --backup \ --target-dir=/data/backups/inc/tuesday/ \ --incremental-basedir=/data/backups/full/sunday/

28 / 42 Incremental Backups - Process Take Incremental: # xtrabackup --backup \ --target-dir=/data/backups/inc/tuesday/ \ --incremental-basedir=/data/backups/full/sunday/ Prepare Full # xtrabackup --prepare \ --apply-log-only \ --target-dir=/data/backups/mysql/

29 / 42 Incremental Backups - Process Take Incremental: # xtrabackup --backup \ --target-dir=/data/backups/inc/tuesday/ \ --incremental-basedir=/data/backups/full/sunday/ Prepare Full # xtrabackup --prepare \ --apply-log-only \ --target-dir=/data/backups/mysql/ Apply Incremental # xtrabackup --prepare --apply-log-only \ --target-dir=/data/backups/full/sunday \ --incremental-dir=/data/backups/inc/tuesday/

30 / 42 Compression, Streaming, Encryption

31 / 42 Compression Compress using qpress # xtrabackup --backup --target-dir=/data/backups/\ --compress --compress-threads=2 Decompress (before prepare) # xtrabackup --decompress --target-dir=/data/backups/

32 / 42 Streaming # xtrabackup --backup \ --compress \ --stream=xbstream > �le.xb

33 / 42 Streaming # xtrabackup --backup \ --compress \ --stream=xbstream > �le.xb Over Network # xtrabackup --backup \ --compress \ --stream=xbstream ./ \ | ssh user@otherhost "xbstream -x"

34 / 42 Encryption # xtrabackup --backup --target-dir=/data/backups \ --encrypt=AES256 \ --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \ --encrypt-threads=2

35 / 42 Cloud Backups - α

36 / 42 Cloud Backups - α Stream to Cloud directly Swift α

37 / 42 Cloud Backups - α Put # xtrabackup --backup --stream=xbstream --target-dir=/data/backups | \ xbcloud put --storage=Swift \ --swift-container=test --swift-user=test:tester \ --swift-auth-url=http://192.168.8.80:8080/ \ --swift-key=testing \ full_backup