What is the principle of Xtrabackup backup and recovery? 01/17 Update SLTechnology News&Howtos

What is the principle of Xtrabackup backup and recovery?

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article introduces you what is the principle of Xtrabackup backup and recovery, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

First, the principle of Xtrabackup backup and recovery

When backing up innodb tables, several xtrabackup threads copy .ibd files from separate tablespaces, constantly monitoring changes in redo log during this process, and adding them to their own transaction log files (xtrabackup_logfile). In the process, what happens

The more physical writes, the larger the xtrabackup_logfile. In the first prepare phase after the copy is completed, xtrabackup uses a method similar to innodb crash recovery, restoring the data file to a state consistent with the log file, and putting the uncommitted

Please roll back. If you need to back up files such as myisam table and innodb table structure at the same time, you need to use flush tables with lock to obtain the global lock, start copying these unchanged files, get the binlog location at the same time, and release the lock after the copy is finished.

And stop monitoring redo log.

Many students are confused about the above understanding, thinking that copying .ibd files is the same as copying files by the operating system. In fact, the problem of fractured page is involved here, which should be reread (there should also be retries, after which the backup is not successful).

In fact, this principle is very simple. If you learn about doublewrite, you can understand "this paragraph is excerpted from MySQL Technology Insider: InnoDB Storage engine":

If insert buffering brings performance to the InnoDB storage engine, then two writes bring data reliability to the InnoDB storage engine. When the database goes down, it may occur that the database is writing a page that is only partially written (for example, 16K

Pages, which only write the first 4K pages), we call it partial write failure (partial page write). Before the InnoDB storage engine did not use double write technology, there were cases of data loss due to partial write failure.

One might think that if a write failure occurs, it can be recovered by redoing the log. This is one way. It must be clear, however, that the redo log records physical operations on the page, such as an offset of800, and writes a "aaaa" record. If the page itself

It has been damaged and there is no point in redoing it. That is to say, before applying the apply redo log, we need a copy of the page. When a write failure occurs, we restore the page through the copy of the page, and then redo it, which is called doublewrite.

II. Backup script

#! / bin/sh

# =

# # backup tools:

# # percona-xtrabackup-2.2.6

# #

# # backup Policy:

# # (1) make a full backup at 04: 20:00 every morning

# # (2) incremental backup every 1 hour

# #

# =

PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/sbin

# # DB backup Base path

BACKUP_BASE_DIR= "/ data/mysql_backup"

# # Base Directory list Files used in incremental backups

# # content format: base directory | backup directory | backup type [full | inc]

INC_BASE_LIST= "${BACKUP_BASE_DIR} / incremental_basedir_list.txt"

# # backup tool path

XTRABACKUP_PATH= "/ usr/local/xtrabackup/bin/innobackupex"

# # MySQL configuration path

MYSQL_CNF_PATH= "/ etc/my.cnf"

# # number of threads

THREAD_CNT=6

# =

Function print_help_info ()

{

Echo "- -"

Echo "Usage: $0 full | inc | help"

Echo "- -"

Echo ""

Exit 1

}

[[$#-lt 1]] & & print_help_info

[[- d ${BACKUP_BASE_DIR}]] | | mkdir-p ${BACKUP_BASE_DIR}

# # download and install automatically when the xtrabackup basic suite does not exist

If [[!-e ${XTRABACKUP_PATH}]]; then

Cd / usr/local

Wget-Q-O xtrabackup-2.2.6.tgz http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/tarball/percona-xtrabackup-2.2.6-5042-Linux-x86_64.tar.gz

Tar xvzf xtrabackup-2.2.6.tgz > / dev/null 2 > & 1 & & rm-f xtrabackup-2.2.6.tgz

Mv percona-xtrabackup-2.2.6-Linux-x86_64 xtrabackup-2.2.6

Rm-rf xtrabackup & & ln-s xtrabackup-2.2.6 xtrabackup

For FNAME in `ls-1t / usr/local/xtrabackup/ bin`

Rm-f / usr/bin/$ {FNAME} & & ln-s / usr/local/xtrabackup/bin/$ {FNAME} / usr/bin/$ {FNAME}

Done

# # only one copy is allowed to run to avoid the possibility of data confusion due to the intersection of full backup and incremental backup

# # [[- n `grep` | grep innobackupex | grep-v grep`]] & & exit 1

# # the default directory name is accurate to minutes, which can be accurate to seconds to avoid unexpected situations that cause backup tasks to fail.

CURRENT_BAK_PATH= "${BACKUP_BASE_DIR} /" `Fang +% Fang% Hmuri% M`

[[- d ${CURRENT_BAK_PATH}]] & & CURRENT_BAK_PATH= "${BACKUP_BASE_DIR} /" `date +% Flavor% Hmuri% MMI% S`

# =

# # full backup

If [["$1" = = "full"]; then

${XTRABACKUP_PATH}-user=root-defaults-file=$ {MYSQL_CNF_PATH}-parallel=$ {THREAD_CNT}-no-timestamp ${CURRENT_BAK_PATH}

Echo "NULL | ${CURRENT_BAK_PATH} | full" > > ${INC_BASE_LIST}

# # incremental backup

Elif [["$1" = = "inc"]; then

# # if the benchmark directory list file does not exist or is empty, a full backup is required

If [[!-f ${INC_BASE_LIST} | | `sed'/ ^ $/ d' ${INC_BASE_LIST} | wc-l`-eq 0]; then

${XTRABACKUP_PATH}-user=root-defaults-file=$ {MYSQL_CNF_PATH}-parallel=$ {THREAD_CNT}-no-timestamp ${CURRENT_BAK_PATH}

Echo "NULL | ${CURRENT_BAK_PATH} | full" > > ${INC_BASE_LIST}

# # if no directory exists, you need to make a full backup to avoid incremental backup failure

Elif [[`find ${BACKUP_BASE_DIR}-maxdepth 1-type d | wc-l`-eq 1]]; then

${XTRABACKUP_PATH}-user=root-defaults-file=$ {MYSQL_CNF_PATH}-parallel=$ {THREAD_CNT}-no-timestamp ${CURRENT_BAK_PATH}

Echo "NULL | ${CURRENT_BAK_PATH} | full" > > ${INC_BASE_LIST}

# # incremental backup based on the last backup

Else

PREV_BACKUP_DIR= `sed'/ ^ $/ d' ${INC_BASE_LIST} | tail-1 | awk-F'|'{print $2}'`

# # the last backup directory does not exist or the directory is empty to avoid the possibility of artificial deletion [there is no good check method for some malicious deletions]

If [[!-d ${PREV_BACKUP_DIR} | |-z `ls ${PREV_BACKUP_DIR} `]; then

${XTRABACKUP_PATH}-user=root-defaults-file=$ {MYSQL_CNF_PATH}-parallel=$ {THREAD_CNT}-no-timestamp ${CURRENT_BAK_PATH}

Echo "NULL | ${CURRENT_BAK_PATH} | full" > > ${INC_BASE_LIST}

Else

${XTRABACKUP_PATH}-user=root-defaults-file=$ {MYSQL_CNF_PATH}-parallel=$ {THREAD_CNT}-no-timestamp-incremental ${CURRENT_BAK_PATH}-incremental-basedir=$ {PREV_BACKUP_DIR}

Echo "${PREV_BACKUP_DIR} | ${CURRENT_BAK_PATH} | inc" > > ${INC_BASE_LIST}

Elif [["$1" = = "help"]; then

Print_help_info

Else

Print_help_info

# # deleting data backups from 2 weeks ago

Rm-rf ${BACKUP_BASE_DIR} / `date-d'14 days ago' +'% F'` _ *

Sed-I "/ `date-d'14 days ago' +'% F'` / d" ${INC_BASE_LIST}

# =

# # The End

# # crontab information to be added:

# # (1) full backup

# # 20 04 * / data/scripts/mysql_backup.sh full > / dev/null 2 > & 1

# #

# # (2), incremental backup

# # 00 * / data/scripts/mysql_backup.sh inc > / dev/null 2 > & 1

# #

# # steps for DB data recovery:

# # (1) Application benchmark

# # innobackupex-user=root-defaults-file=/etc/my.cnf-use-memory=8G-apply-log-redo-only / data/mysql_backup/full

# #

# # (2) apply the first incremental backup

# # innobackupex-user=root-defaults-file=/etc/my.cnf-use-memory=8G-apply-log-redo-only / data/mysql_backup/full-incremental-dir=/data/mysql_backup/inc_one

# #

# # (3) apply the second incremental backup

# # innobackupex-user=root-defaults-file=/etc/my.cnf-use-memory=8G-apply-log / data/mysql_backup/full-incremental-dir=/data/mysql_backup/inc_two

# #

# # (4) reapply the benchmark

# # innobackupex-user=root-defaults-file=/etc/my.cnf-use-memory=8G-apply-log / data/mysql_backup/full

# #

# # (5), restore

# # innobackupex-user=root-defaults-file=/etc/my.cnf-copy-back / data/mysql_backup/full

Third, effect display

# cat mysql_backup.sh