MySQL Enterprise Backup Percona Xtrabackup Mariabackup Juan Pablo - - PowerPoint PPT Presentation

mysql enterprise backup percona xtrabackup mariabackup
SMART_READER_LITE
LIVE PREVIEW

MySQL Enterprise Backup Percona Xtrabackup Mariabackup Juan Pablo - - PowerPoint PPT Presentation

MySQL Enterprise Backup Percona Xtrabackup Mariabackup Juan Pablo Arruti, Iwo Panowicz Percona Backups There are two types of people, those who take backups and those who will be taking them in the future Database backups:


slide-1
SLIDE 1

MySQL Enterprise Backup Percona Xtrabackup Mariabackup

Juan Pablo Arruti, Iwo Panowicz Percona

slide-2
SLIDE 2

2

Backups

  • There are two types of people, those who take backups

and those who will be taking them in the future

  • Database backups:

○ Logical backups ■ mysqldump, mysqlpump, mydumper ○ Physical backups ■ MEB, Percona Xtrabackup, Mariabackup ○ Binlogs ■ point-in-time recovery

slide-3
SLIDE 3

3

Backups

  • At Percona, we recommend customers use all three

ways of taking a backup of their database

  • Schrödinger’s restore:

○ The condition of any backup is unknown until restore is attempted ○ You never known before trying

  • Always test backups:

○ pt-table-checksum

slide-4
SLIDE 4

4

Backups

  • There are two types of people, those who take backups

and those who will be taking them in the future

  • Database backups:

○ Logical backups ■ mysqldump, mysqlpump, mydumper ○ Physical backups ■ MEB, Percona Xtrabackup, Mariabackup ○ Binlogs ■ point-in-time recovery

slide-5
SLIDE 5

5

Percona Xtrabackup

  • The Percona XtraBackup tool provides a method of

performing a hot backup of your MySQL data while the system is running

  • Percona XtraBackup allows you to backup your

database while in production, without impacting uptime or data changes

slide-6
SLIDE 6

6

Percona Xtrabackup

  • Opensource
  • Supports InnoDB and MyRocks
  • Blocking support for MyISAM
  • Full & incremental backups
  • Partial backups
  • Compression
  • Encryption
  • Streaming
slide-7
SLIDE 7

7

Percona Xtrabackup

  • Backup stage:

○ PXB backups IBD files while in use ○ PXB tracks changes through redo logs

  • Prepare stage:

○ PXB applies changes from redo logs onto ibd files

  • Restore stage:

○ After the prepare stage is done, the backup directory is the new data directory

slide-8
SLIDE 8

8

  • XBStream

○ To support simultaneous compression and streaming, a new custom streaming format called xbstream was introduced to Percona XtraBackup in addition to the TAR format

$ xtrabackup --backup --stream=xbstream \

  • -compress --compress-threads=8 --parallel=8 \

2>backup.log | ssh newInstance "cat - > file.xbs"

Percona Xtrabackup

slide-9
SLIDE 9

9

Percona Xtrabackup

  • XBCloud

○ The purpose of xbcloud is to download and upload full or part of xbstream archive from/to cloud ○ xbcloud accepts input via a pipe from xbstream so that it can be invoked as a pipeline with xtrabackup to stream directly to the cloud without needing a local storage

slide-10
SLIDE 10

1

Percona Xtrabackup

  • XBCloud

○ In addition to Swift, which has been the only option for storing backups in a cloud storage until PXB 2.4.14, xbcloud supports ○ Amazon S3 ○ MinIO ○ Google Cloud Storage ○ Any other S3 compatible storages, such as Wasabi

  • r Digital Ocean Spaces
slide-11
SLIDE 11

1 1

  • XBCloud

$ xtrabackup --backup --stream=xbstream | \ xbcloud put \

  • -storage=s3 \
  • -s3-endpoint='s3-us-west-2.amazonaws.com' \
  • -s3-bucket='mysqlbucketprod' \
  • -parallel=10 \

$(date +"%Y%m%d%H%M%S")-full_backup

Percona Xtrabackup

slide-12
SLIDE 12

1 2

Restore Amazon RDS from Xtrabackup

  • Overview

○ Take backup from database ○ Upload into S3 bucket ○ Create new instance from the backup ■ Amazon MySQL RDS ■ Amazon Aurora MySQL

slide-13
SLIDE 13

1 3

Restore Amazon RDS from Xtrabackup

  • Limitations

○ Supported MySQL 5.6 and 5.7 ○ Source/Target major versions must match ■ Target minor version must be higher ○ Source tables defined within default datadir ○ 6 TB database size limit ○ Source database can't be encrypted ○ User accounts, functions, stored procedures and time zone info are not imported automatically

slide-14
SLIDE 14

1 4

Mariabackup

  • MariaDB is not just a fork of MySQL

○ It continues to diverge much

  • Percona Xtrabackup became incompatible with

MariaDB features like: ○ InnoDB Page Compression ○ MariaDB’s implementation of Data-At-Rest encryption

slide-15
SLIDE 15

1 5

Mariabackup

  • Mariabackup should be used with MariaDB
  • The syntax is the same as PXB, except for

innobackupex ○ --innobackupex is available though

slide-16
SLIDE 16

1 6

MariaDB and Percona Xtrabackup

  • MariaDB 10.1

○ If instance is using InnoDB Page Compression ■ PXB will not work ○ If instance is using Data-at-Rest encryption ■ PXB will not work ○ When innodb_page_size is changed from the default value (16KB) ■ PXB will not work

slide-17
SLIDE 17

1 7

MariaDB and Percona Xtrabackup

  • MariaDB 10.2

○ Same as MariaDB 10.1 ○ You can hit issues due to the MySQL 5.7 undo log format incompatibility bug that was fixed in MariaDB 10.2.2 ■ Backups prepared with XtraBackup may fail to recover some transactions

slide-18
SLIDE 18

1 8

MariaDB and Percona Xtrabackup

  • MariaDB 10.3+

○ PXB is not working

slide-19
SLIDE 19

1 9

Mariabackup

  • Mariabackup was originally forked and extended from

PXB 2.3.8 version

  • It supports feature that PXB was missing for MariaDB
  • It is also used in Mariadb’s Galera cluster SST
  • Can be used on Microsoft Windows
slide-20
SLIDE 20

2

Mariabackup

  • It however lacks of some PXB features:

○ When doing the backup of InnoDB tables, it’s possible to omit the secondary index pages. This will make the backups more compact and this way they will take less space on disk. ■ --compact and --rebuild_indexes are not available in Mariabackup

slide-21
SLIDE 21

2 1

Mariabackup

  • It however lacks of some PXB features:

○ Percona XtraBackup has implemented support for encrypted backups. It can be used to encrypt/decrypt local or streaming backup with xbstream option in order to add another layer of protection to the backups. ■ --encrypt is not available in Mariabackup

slide-22
SLIDE 22

2 2

Mariabackup

mariabackup --user=root --backup \

  • -stream=xbstream | openssl \

enc -aes-256-cbc -k mypass

slide-23
SLIDE 23

2 3

Mariabackup

  • It however lacks of some PXB features:

○ Support for --stream=tar was removed from Mariabackup in MariaDB 10.1.24 ○ xbstream is available, the name was changed to mbstream ○ However, to select this output format when creating a backup, Mariabackup's --stream option still expects the xbstream value

slide-24
SLIDE 24

2 4

Mariabackup

  • It however lacks of some PXB features:

○ Percona XtraBackup implemented support for lock-less binary log information in 2.3.2. If possible

  • n the server, PXB can avoid executing LOCK

BINLOG FOR BACKUP. ○ Mariabackup does not support lockless binlog

slide-25
SLIDE 25

2 5

MySQL Enterprise Backup

  • Overview

○ Online Backup for MySQL Databases ○ Replacement of innobackup ○ High Performance ○ Multi-Platform (Unix, Linux, Windows) ○ Licensed software (MySQL Enterprise Edition) ○ Cloud Storage Support ○ LZ4 compression algorithm

slide-26
SLIDE 26

2 6

MEB and XtraBackup

  • Available features in MEB not present in XtraBackup

○ Reports backup progress ■ --show-progress

... RPR1 INFO: Progress: 0 of 641 MB; state: Copying system tablespace; compression: - RPR1 INFO: Progress: 183 of 641 MB; state: Copying .ibd files; compression: 95% RPR1 INFO: Progress: 439 of 641 MB; state: Copying .ibd files; compression: 60% RPR1 INFO: Progress: 671 of 671 MB; state: Copying binlogs; compression: 69% ...

slide-27
SLIDE 27

2 7

MEB and XtraBackup

  • Available features in MEB not present in XtraBackup

○ Performs Offline backups ■ --no-connection

slide-28
SLIDE 28

2 8

MEB and XtraBackup

  • Available features in MEB not present in XtraBackup

○ Backup to Tape with Oracle Secure Backup ■ Single file backup --backup-image=sbt:name ■ --sbt-database-name=storage_selector ■ --sbt-lib-path=/path/to/libobk.so

slide-29
SLIDE 29

2 9

MEB and XtraBackup

  • Available features in MEB not present in XtraBackup

○ Binary and Relay log files backups ■ Enabled by default ■ Allows Point in Time Recovery ■ Limited Auditing ■ Avoids pulling again replication events ■ --skip-binlog --skip-relaylog

slide-30
SLIDE 30

3

MEB and XtraBackup

  • Available features in MEB not present in XtraBackup

○ Rename table at restore ■ --rename="OLD-NAME to NEW-NAME" ■ Transportable Tablespace Backup --use-tts

  • Used with Selective Backups
slide-31
SLIDE 31

3 1

MEB and XtraBackup

  • Available features in XtraBackup not present in MEB

○ Open Source ○ Supports MariaDB, MySQL and Percona Server ○ Parallel streaming backups ○ Parallel compressed Backups ○ Backup locks ○ Create AWS RDS instances from backups

slide-32
SLIDE 32

3 2

MEB and XtraBackup

  • Available features in XtraBackup not present in MEB

○ Buffer pool state backups ○ Individual partitions export ○ Data & index file statistics ○ InnoDB secondary indexes defragmentation ○ rsync support to minimize lock time ○ Improved FTWRL handling

slide-33
SLIDE 33

33

Rate My Session

slide-34
SLIDE 34

Questions

slide-35
SLIDE 35

Thank you!