1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What...

46
Online Backups with Percona Webinar - May 2017 1 / 46

Transcript of 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What...

Page 1: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Online Backups with

Percona Webinar - May 2017

1 / 46

Page 2: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Dimitri Vanoverbeke@dim0

MySQL Solution Engineer

2 / 46

Page 3: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

AgendaWhy do I need backups again I have replicationWhat is Percona XtraBackupBackup ProcessRestore ProcessIncrementalCompression, Streaming, EncryptionCloud Backups - αPercona Server Speci�c Features

3 / 46

Page 4: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Replication is not a DR solution!Make sure you understand what your requirements are:

RTORPO

Discuss this with your business owners, what is acceptable?

4 / 46

Page 5: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Having backups is not enough! TEST THEM!Populate test systems using your backupsHave automation in place to perform the backups and restoresDocument them!

5 / 46

Page 6: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

6 / 46

What is

Page 7: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Backup challenges that existMySQL is multi-engineMySQL can be quite busyDo we need to shut it down?How much load will we generate?How quickly can we restore?

7 / 46

Page 8: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

What is

Backups for:

8 / 46

Page 9: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

What is

Backups for:

9 / 46

Page 10: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Online (Non Blocking) InnoDB BackupsPhysical Data�les Copied

What is

https://www.percona.com/software/mysql-database/percona-xtrabackup

10 / 46

Page 11: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Online (Non Blocking) InnoDB BackupsPhysical Data�les Copied

Less Impact on Production Faster Restore Time Than Logical Backups

What is

https://www.percona.com/software/mysql-database/percona-xtrabackup

11 / 46

Page 12: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Online (Non Blocking) InnoDB BackupsPhysical Data�les Copied

Less Impact on Production Faster Restore Time Than Logical Backups

What is

https://www.percona.com/software/mysql-database/percona-xtrabackup

Open SourceBlocking Support MyISAM...Incremental/DeltaCompression/EncryptionCloud Backup

12 / 46

Page 13: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

13 / 46

(Data) Backup Process

Page 14: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(Data) Backup Process - 1. BackupBackup .ibd �les while in use

/var/lib/mysql/ibdata1/var/lib/mysql/imdb/aka_name.ibd/var/lib/mysql/imdb/aka_title.ibd...

14 / 46

Page 15: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(Data) Backup Process - 1. BackupBackup .ibd �les 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

15 / 46

Page 16: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(Data) Backup Process - 2. Prepare

Apply changes from trx logs onto backupped .ibd �les.

16 / 46

Page 17: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(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-logxtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision idxtrabackup: uses posix_fadvise()....InnoDB: Number of pools: 1161001 22:16:49 >> log scanned up to (5147619414)xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0161001 22:16:50 [01] Copying ./ibdata1 to /data/backups/ibdata1161001 22:16:50 [01] Copying ./mysql/plugin.ibd to /data/backups/mysql/plugin.ibd161001 22:16:50 [01] Copying ./mysql/servers.ibd to /data/backups/mysql/servers.ibd161001 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.ibd161001 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...

17 / 46

Page 18: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(Data) Backup Process - 1. Backup (2)161001 22:17:14 Starting to backup non-InnoDB tables and �les161001 22:17:14 [01] Copying ./mysql/db.opt to /data/backups/mysql/db.opt161001 22:17:14 [01] Copying ./mysql/db.frm to /data/backups/mysql/db.frm161001 22:17:14 [01] Copying ./mysql/db.MYI to /data/backups/mysql/db.MYI161001 22:17:14 [01] Copying ./mysql/db.MYD to /data/backups/mysql/db.MYD161001 22:17:17 [01] Copying ./imdb/users.frm to /data/backups/imdb/users.frm161001 22:17:17 Finished backing up non-InnoDB tables and �les161001 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 TABLES161001 22:17:17 All tables unlocked161001 22:17:17 [00] Copying ib_buffer_pool to /data/backups/ib_buffer_pool161001 22:17:17 [00] ...done161001 22:17:17 Backup created in directory '/data/backups/'161001 22:17:17 [00] Writing backup-my.cnf161001 22:17:17 [00] ...done161001 22:17:17 [00] Writing xtrabackup_info161001 22:17:17 [00] ...donextrabackup: Transaction log of lsn (5147619405) to (5147619463) was copied.161001 22:17:18 completed OK!

18 / 46

Page 19: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(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/backupsxtrabackup: 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 5147619405InnoDB: 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 startInnoDB: 5.7.13 started; log sequence number 5147619463InnoDB: xtrabackup: Last MySQL binlog �le position 333, �le name perconaserver-bin.000007

xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 5147619482

InnoDB: Number of pools: 1

19 / 46

Page 20: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

(Data) Backup Process - 2. Prepare (2)...InnoDB: Setting log �le ./ib_log�le101 size to 64 MBInnoDB: Setting log �le ./ib_log�le1 size to 64 MBInnoDB: Renaming log �le ./ib_log�le101 to ./ib_log�le0InnoDB: New log �les created, LSN=5147619482InnoDB: Highest supported �le format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 5147619852InnoDB: 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 startInnoDB: 5.7.13 started; log sequence number 5147619861xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 5147619880161001 22:30:05 completed OK!

20 / 46

Page 21: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

21 / 46

Restore Process

Page 22: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Restore Process

After preparation, the backup directory == data directory

22 / 46

Page 23: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

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

23 / 46

Page 24: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

24 / 46

Incremental Backups

Page 25: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Incremental Backups

25 / 46

Page 26: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

26 / 46

Page 27: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Pro:

Smaller backup sizeFaster To BackupIncremental & Delta

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

27 / 46

Page 28: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Pro:

Smaller backup sizeFaster To BackupIncremental & Delta

Con:

Slower to restore than full

On , all data is read

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

28 / 46

Page 29: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Incremental Backups - Process

29 / 46

Page 30: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Incremental Backups - ProcessTake Incremental:

# xtrabackup --backup \ --target-dir=/data/backups/inc/tuesday/ \ --incremental-basedir=/data/backups/full/sunday/

30 / 46

Page 31: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Incremental Backups - ProcessTake 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/full/sunday/

31 / 46

Page 32: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Incremental Backups - ProcessTake 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/full/sunday/

Apply Incremental

# xtrabackup --prepare --apply-log-only \ --target-dir=/data/backups/full/sunday \ --incremental-dir=/data/backups/inc/tuesday/

32 / 46

Page 33: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

33 / 46

Compression, Streaming, Encryption

Page 34: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Compression

Compress using qpress# xtrabackup --backup --target-dir=/data/backups/\ --compress --compress-threads=2

Decompress (before prepare)# xtrabackup --decompress --target-dir=/data/backups/

34 / 46

Page 35: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

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

35 / 46

Page 36: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

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

Over Network# xtrabackup --backup \ --compress \ --stream=xbstream ./ \ | ssh user@otherhost "xbstream -x"

36 / 46

Page 37: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

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

37 / 46

Page 38: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Decrypt# xtrabackup --decrypt=AES256 \ --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs"\ --target-dir=/data/backups/

After decryption the backups can be prepared the standard way to prepare backups.

38 / 46

Page 39: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

39 / 46

Cloud Backups - α

Page 40: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Cloud Backups - αStream to Cloud directlySwiftα

40 / 46

Page 41: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

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

41 / 46

Page 42: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

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

Get# xbcloud get --storage=Swift \ --swift-container=test --swift-user=test:tester \ --swift-auth-url=http://192.168.8.80:8080/ \ --swift-key=testing \ full_backup | xbstream -xv -C /tmp/downloaded_full# xtrabackup --prepare --target-dir=/tmp/downloaded_full# xtrabackup --copy-back --target-dir=/tmp/downloaded_full

42 / 46

Page 43: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

43 / 46

Features

Page 44: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Backup LocksImplements LOCK TABLES FOR BACKUP toAvoid FLUSH TABLES WITH READ LOCK, used for:

Non-InnoDB Tables (mysql schema... )Binary Log Position

Blocks DML to non-InnoDBBlocks DDLAllows Reads/Writes to InnoDB tables during

44 / 46

Page 45: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Change Page Tracking - Incremental Backups

Incremental backup does no longer require reading all pages to determine if a pagechanged since the last full backup

How:

Percona Server tracks pages changed in InnoDB TablespacesIncremental backup reads changed page �le, only reads changed pages

Why:

Less load on database server (a lot less Disk IO)

45 / 46

Page 46: 1 / 46 Online Backups with - Percona · Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming,

Thank you !

Questions ?

46 / 46