Online Backups with

Percona Live Amsterdam - October 2016

1 / 42

Kenny Gryp@gryp

MySQL Practice Manager

2 / 42

AgendaWhat is Percona XtraBackupBackup ProcessRestore ProcessIncrementalCompression, Streaming, EncryptionCloud Backups - αPercona Server Specific Features

3 / 42

4 / 42

What is

What is

Backups for:

5 / 42

What is

Backups for:

6 / 42

Online (Non Blocking) InnoDB BackupsPhysical Datafiles Copied

What is

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

7 / 42

Online (Non Blocking) InnoDB BackupsPhysical 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

Online (Non Blocking) InnoDB BackupsPhysical Datafiles 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

9 / 42

10 / 42

(Data) Backup Process

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

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

12 / 42

(Data) Backup Process - 2. Prepare

Apply changes from trx logs onto backupped .ibd files.

13 / 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-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...

14 / 42

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

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

16 / 42

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

17 / 42

18 / 42

Restore Process

Restore Process

After preparation, the backup directory == data directory

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

20 / 42

21 / 42

Incremental Backups

Incremental Backups

22 / 42

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

23 / 42

Pro:

Smaller backup sizeFaster To BackupIncremental & Delta

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

24 / 42

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)

25 / 42

Incremental Backups - Process

26 / 42

Incremental Backups - ProcessTake Incremental:

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

27 / 42

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

28 / 42

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

Apply Incremental

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

29 / 42

30 / 42

Compression, Streaming, Encryption

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

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

32 / 42

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

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

33 / 42

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

34 / 42

35 / 42

Cloud Backups - α

Cloud Backups - αStream to Cloud directlySwiftα

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

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

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

39 / 42

Features

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

40 / 42

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 file, only reads changed pages

Why:

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

41 / 42

Thank you !

Questions ?

42 / 42