Tutorial _ MySQL

This article describes how to write a script to allow Xtrabackup to back up MySQL data. The example environment is based on the CentOS system. For more information, see Xtrabackup backup recovery principle
When backing up the innodb table, xtrabackup several threads copy the. ibd file of the independent tablespace, constantly monitor the changes in the redo log during this process, and add it to their own transaction log file (xtrabackup_logfile. In this process, the more physical write operations occur, the larger the xtrabackup_logfile. In the first prepare phase after the copy is completed, xtrabackup adopts a method similar to innodb crash recovery to restore the data file to the same state as the log file and roll back the uncommitted transactions. 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 files that are no longer changing, and get the binlog location, release the lock after the copy ends, and stop monitoring the redo log.

Many of you may be confused about the above and think that copying a. ibd file is the same as copying a file in the operating system. In fact, this involves the fractured page problem. it should be re-read (there should also be retries, and the backup will fail if it exceeds the limit ).

In fact, this principle is very simple. after understanding doublewrite, you can understand "this section is excerpted from" MySQL Technology Insider: InnoDB storage engine ":
If the insert buffer is performance-oriented to the InnoDB storage engine, the two writes bring data reliability to the InnoDB storage engine. When the database goes down, the database may write a page, but this page only writes a part (for example, 16 K pages, only the first 4 K pages, this is called partial page write ). Before using double write technology, the InnoDB storage engine experienced data loss due to some invalid writes.

Some may think that if a write failure occurs, you can restore it by redoing the log. This is a solution. However, it must be clear that redo logs record physical page operations, such as offset 800, and write "aaaa" records. If the page itself is damaged, it makes no sense to redo it. This means that a copy of the page is required before the application retries the log. when the write failure occurs, the page is restored through the copy of the page before redoing, this is doublewrite.

1. download and install
The latest version of XtraBackup is 2.1.5, and the official version 2.0 is available for download. official link: Release. As there is no need for special customization, we recommend that you directly use RPM for installation.

Installation Method 1:
Download the RPM installation package:

wget http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel6/x86_64/percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm

Install dependency:

[root@localhost ~]# yum install -y perl-DBD-MySQL perl-DBI perl-Time-HiRes libaio*

Installation:

[root@localhost ~]# rpm -ivh percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm 

Preparing...           ########################################### [100%] 1:percona-xtrabackup   ########################################### [100%]

Installation Method 2:
Install percona Source:

[root@localhost ~]# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Use yum to install percona-xtrabackup:

[root@localhost ~]# yum -y install percona-xtrabackup

View the installation result:

[root@localhost ~]# rpm -qa |grep xtrabackup 

percona-xtrabackup-2.1.5-680.rhel6.x86_64 

[root@localhost ~]# rpm -ql percona-xtrabackup-2.1.5-680.rhel6.x86_64 

/usr/bin/innobackupex /usr/bin/innobackupex-1.5.1 /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/bin/xtrabackup_55 /usr/bin/xtrabackup_56 /usr/share/doc/percona-xtrabackup-2.1.5 /usr/share/doc/percona-xtrabackup-2.1.5/COPYING

Xtrabackup mainly includes two tools:
Xtrabackup: a tool used to hot back up data in innodb and xtradb tables. it cannot back up other types of tables or data table structures;

Innobackupex: a perl script that encapsulates xtrabackup and provides the ability to back up myisam tables.

II. backup script

#! /Bin/sh #=============================================== ========================================================== ========================================## backup tools: # percona-xtrabackup-2.2.6 #### backup policy: # (1), a full backup at every day; # (2), every 1 hour incremental backup once; ###================================================= ========================================================== ==================================== PATH =/sbin: /bin:/usr/sbin:/usr/bin:/usr/local/sbin # BACKUP_BASE_DIR = "/data/mys Ql_backup "# reference directory list files used for incremental backup # content format: reference Directory | current 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" success "echo" "exit 1} [[$ #-lt 1] & print_help_info [[-d $ {BACKUP_BASE_DIR}] | mkdir-p $ {BACKUP_BASE_DIR }## when the xtrabackup basic suite does not exist, automatically download and install 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-1 t /usr/local/xtrabackup/bin'do rm-f/usr/bin/$ {FNAME} & ln-s/usr/local/xtrabackup/bin/$ {FNAME} /usr/bin/$ {FNAME} donefi # Only one copy is allowed to run, to avoid crossover between full backup and incremental backup and the possibility of data disorder # [-n 'PS uax | grep innob Ackupex | grep-v grep '] & exit 1 # The directory name is accurate to minutes by default. to avoid unexpected situations, the backup task fails, accurate to seconds: CURRENT_BAK_PATH = "$ {BACKUP_BASE_DIR}/" 'date + % F _ % H-% M' [[-d $ {CURRENT_BAK_PATH}] & CURRENT_BAK_PATH =" $ {BACKUP_BASE_DIR}/"'date + % F _ % H-% M-% s' #================ ========================================================== ========================================================== ### full backup if [["$1" = "full"]; then $ {XTRABACKUP_PATH} -- user = root -- defaults-fil E =$ {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 reference directory list file does not exist or is empty, you need to perform a full backup 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, A full backup is required 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} ## based on the previous backup, perform incremental 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 manual deletion [for some cases of malicious deletion, there is no good check method yet] 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} fi elif [[" $1 "=" help "]; then print_help_info else print_help_infofi # delete the data backup 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 ############## ######################################## ######################################## ######################################## ######################################## ############################ database data recovery steps: # (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) apply the benchmark again # innobackupex -- user = root -- defaults-file =/etc/my. cnf -- use-memory = 8G -- apply-log/data/mysql_backup/full #### (5), recovery # innobackupex -- user = root -- defaults-file =/etc/my. cnf -- copy-back/data/mysql_backup/full ############################# ######################################## ###############################

III. effect display