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

▶
online backups with
SMART_READER_LITE
LIVE PREVIEW

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

Oct 29, 2023 35 likes •456 views

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

slide-1
SLIDE 1

Online Backups with

Percona Live Amsterdam - October 2016

1 / 42

slide-2
SLIDE 2

Kenny Gryp

@gryp MySQL Practice Manager

2 / 42

slide-3
SLIDE 3

Agenda

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

3 / 42

slide-4
SLIDE 4

4 / 42

What is

slide-5
SLIDE 5

What is

Backups for:

5 / 42

slide-6
SLIDE 6

What is

Backups for:

6 / 42

slide-7
SLIDE 7

Online (Non Blocking) InnoDB Backups Physical Datafiles Copied

What is

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

7 / 42

slide-8
SLIDE 8

Online (Non Blocking) InnoDB Backups Physical Datafiles Copied Less Impact on Production Faster Restore Time Than Logical Backups

What is

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

8 / 42

slide-9
SLIDE 9

Online (Non Blocking) InnoDB Backups Physical Datafiles Copied Less Impact on Production Faster Restore Time Than Logical Backups

What is

https://www.percona.com/software/mysql-database/percona-xtrabackup Open Source Blocking Support MyISAM... Incremental/Delta Compression/Encryption Cloud Backup

9 / 42

slide-10
SLIDE 10

10 / 42

(Data) Backup Process

slide-11
SLIDE 11

(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 ...

11 / 42

slide-12
SLIDE 12

(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_logle0 /var/lib/mysql/ib_logle1

12 / 42

slide-13
SLIDE 13

(Data) Backup Process - 2. Prepare

Apply changes from trx logs onto backupped .ibd files.

13 / 42

slide-14
SLIDE 14

(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...

14 / 42

slide-15
SLIDE 15

(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!

15 / 42

slide-16
SLIDE 16

(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

16 / 42

slide-17
SLIDE 17

(Data) Backup Process - 2. Prepare (2)

... InnoDB: Setting log le ./ib_logle101 size to 64 MB InnoDB: Setting log le ./ib_logle1 size to 64 MB InnoDB: Renaming log le ./ib_logle101 to ./ib_logle0 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!

17 / 42

slide-18
SLIDE 18

18 / 42

Restore Process

slide-19
SLIDE 19

Restore Process

After preparation, the backup directory == data directory

19 / 42

slide-20
SLIDE 20

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

20 / 42

slide-21
SLIDE 21

21 / 42

Incremental Backups

slide-22
SLIDE 22

Incremental Backups

22 / 42

slide-23
SLIDE 23

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

23 / 42

slide-24
SLIDE 24

Pro: Smaller backup size Faster To Backup Incremental & Delta

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

24 / 42

slide-25
SLIDE 25

Pro: Smaller backup size Faster To Backup Incremental & 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)

25 / 42

slide-26
SLIDE 26

Incremental Backups - Process

26 / 42

slide-27
SLIDE 27

Incremental Backups - Process

Take Incremental:

# xtrabackup --backup \

  • -target-dir=/data/backups/inc/tuesday/ \
  • -incremental-basedir=/data/backups/full/sunday/

27 / 42

slide-28
SLIDE 28

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/

28 / 42

slide-29
SLIDE 29

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/

29 / 42

slide-30
SLIDE 30

30 / 42

Compression, Streaming, Encryption

slide-31
SLIDE 31

Compression

Compress using qpress

# xtrabackup --backup --target-dir=/data/backups/\

  • -compress --compress-threads=2

Decompress (before prepare)

# xtrabackup --decompress --target-dir=/data/backups/

31 / 42

slide-32
SLIDE 32

Streaming

# xtrabackup --backup \

  • -compress \
  • -stream=xbstream > le.xb

32 / 42

slide-33
SLIDE 33

Streaming

# xtrabackup --backup \

  • -compress \
  • -stream=xbstream > le.xb

Over Network

# xtrabackup --backup \

  • -compress \
  • -stream=xbstream ./ \

| ssh user@otherhost "xbstream -x"

33 / 42

slide-34
SLIDE 34

Encryption

# xtrabackup --backup --target-dir=/data/backups \

  • -encrypt=AES256 \
  • -encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
  • -encrypt-threads=2

34 / 42

slide-35
SLIDE 35

35 / 42

Cloud Backups - α

slide-36
SLIDE 36

Cloud Backups - α

Stream to Cloud directly Swift α

36 / 42

slide-37
SLIDE 37

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

37 / 42

slide-38
SLIDE 38

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

38 / 42

slide-39
SLIDE 39

39 / 42

Features

slide-40
SLIDE 40

Backup Locks

Implements LOCK TABLES FOR BACKUP to Avoid FLUSH TABLES WITH READ LOCK, used for: Non-InnoDB Tables (mysql schema... ) Binary Log Position Blocks DML to non-InnoDB Blocks DDL Allows Reads/Writes to InnoDB tables during

40 / 42

slide-41
SLIDE 41

Change Page Tracking - Incremental Backups

Incremental backup does no longer require reading all pages to determine if a page changed since the last full backup How: Percona Server tracks pages changed in InnoDB Tablespaces Incremental backup reads changed page file, only reads changed pages Why: Less load on database server (a lot less Disk IO)

41 / 42

slide-42
SLIDE 42

Thank you !

Questions ?

42 / 42