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

Online Backups with

Percona Webinar - May 2017

1 / 46

Dimitri Vanoverbeke@dim0

MySQL Solution Engineer

2 / 46

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

3 / 46

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

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

6 / 46

What is

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

What is

Backups for:

8 / 46

What is

Backups for:

9 / 46

Online (Non Blocking) InnoDB BackupsPhysical Data�les Copied

What is

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

10 / 46

Less Impact on Production Faster Restore Time Than Logical Backups

What is

11 / 46

Less Impact on Production Faster Restore Time Than Logical Backups

What is

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

12 / 46

13 / 46

(Data) Backup Process

(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

(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

(Data) Backup Process - 2. Prepare

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

16 / 46

(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

(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

(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

(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

21 / 46

Restore Process

After preparation, the backup directory == data directory

22 / 46

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

24 / 46

Incremental Backups

25 / 46

Incremental Backups

Only Copy InnoDB Pages That Changed

(still copies all non InnoDB Tables)

26 / 46

Smaller backup sizeFaster To BackupIncremental & Delta

Incremental Backups

27 / 46

Smaller backup sizeFaster To BackupIncremental & Delta

Slower to restore than full

On , all data is read

Incremental Backups

28 / 46

Incremental Backups - Process

29 / 46

Incremental Backups - ProcessTake Incremental:

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

30 / 46

Prepare Full

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

31 / 46

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

33 / 46

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/

34 / 46

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

35 / 46

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

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

36 / 46

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

37 / 46

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

39 / 46

Cloud Backups - α

Cloud Backups - αStream to Cloud directlySwiftα

40 / 46

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

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

43 / 46

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

44 / 46

Change Page Tracking - Incremental Backups

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

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

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

45 / 46

Thank you !

Questions ?

46 / 46

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

Documents

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

Schottky Barrier Heigh oriented process integration 2012

Getting started with Logitech® Wireless Solar …...System Preferences>Keyboard>Keyboard Shortcuts>Mission Control>Restore Defaults. If you are using Leopard (Mac OS 10.5) or Snow

Outlier Detection with Dirichlet Process Mixtures · Outlier Detection with Dirichlet Process Mixtures ... outlier detection with Dirichlet process mixtures is ... I we proposed a

Online MySQL Backups with Percona XtraBackup

CAE DATA & PROCESS MANAGEMENT WITH ANSA

Χρονοπρογραμματισμός Διεργασιών ( Process Scheduling )

Process log Status of working Monitor data during EP & Rinsing process Comparison of EP1 Period : 2012/2/23 ~ 2/27 Process : Degreasing (FM-20,

Micro-fabrication Process

process product applications requirements performance.

1 / 42 Online Backups with - Percona · Agenda What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming, Encryption Cloud Backups - α Percona

Six Sigma in Customer Facing Process

Module 4 - P L Dhar's Web Page · isothermal process & rev. adiabatic process, it is valid for any reversible SSSF process since Any rev. process ≡ series of alternate adiabatic

Process Vessels - meds.inoxstyle.gr

SMEAS 2007 Bex, Rob, Georgios, Jason. Fabrication Process status...looks like ~45% of the process is completed... Hmmm....

Transforming ASEME Roles Models to Process Models

Process Capability shift over time

PROCESS - Daum

Ληψη Αποφασης Military Decision Making Process (MDMP)

Chapter 4 : ULSI Process Integration (0.18μm CMOS Process)

PROCESS CAPABILITY