Write a script to enable Xtrabackup to back up Mysql data tutorial _mysql

When

xtrabackup Backup Restore principle
backs up the InnoDB table, xtrabackup several threads to copy the. ibd Files of the isolated tablespaces and monitor the redo log changes during this process. Add to your own transaction log file (Xtrabackup_logfile). In this process, the more physical write operations occur, the greater the xtrabackup_logfile. In the first prepare phase after the copy is completed, Xtrabackup uses a method similar to InnoDB crash recovery to restore the data file to the same state as the log file and rollback the uncommitted transaction. If you need to back up the MyISAM table and the INNODB table structure and other files, then you need to use the flush tables with lock to obtain the global lock, start copying these no longer change files, while obtaining the Binlog location, the copy after the end of the release of the lock, also stop the redo Log monitoring.
 
Many students are confused by the above understanding that the copy. ibd file is the same as the operating system copy file. In fact, this involves fractured page problem, he should be read again (should also have the number of retries, more than the backup is unsuccessful).
 
In fact, this principle is very simple, understand the next doublewrite can understand "this excerpt from the MySQL Technology Insider: InnoDB Storage Engine":
If the insert buffer to the INNODB storage engine is performance, So two times write to InnoDB storage engine is the reliability of the data. When the database is down, it is possible that the database is writing a page that only writes a portion of the page (such as a 16K page that writes only the first 4K pages), which we call partial write invalidation (partial page write). Before the InnoDB storage engine used the double write technique, there were cases where data was lost because of a partial write failure.
 
One might think that if write invalidation occurs, it can be recovered by the redo log. This is a way. It must be clear, however, that the redo log records physical operations on the page, such as offset 800, which writes "AAAA" records. If the page itself is corrupted, it makes no sense to redo it. That is to say, before applying the redo log, we need a copy of the page, and when the write failure occurs, the page is restored by the doublewrite of the page and then redo.

First, download and install
Xtrabackup is now the latest version of 2.1.5, the official also provided a 2.0 version available for download, official link address: http://www.percona.com/software/percona-xtrabackup , you can download the source compilation installation, or you can download the appropriate RPM package or use Yum for installation. Since there is no need for special customization, it is recommended to use the RPM installation directly.

Installation method One:
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

Installation dependencies:

[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 Two:
Install Percona Source:

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

Install Percona-xtrabackup using yum:

[root@localhost ~]# yum-y Install Percona-xtrabackup

To view the installation results:

[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

The xtrabackup consists of two tools:
Xtrabackup: is a tool for hot backup InnoDB, xtradb table data, can not back up other types of tables, and can not back up the data table structure;

Innobackupex: A Perl script that encapsulates Xtrabackup, providing the ability to back up MyISAM tables.

Second, backup script

#!/bin/sh #=================================================================================================== # # Backup tool: # # percona-xtrabackup-2.2.6 # # # # # # # # # # # # # # # # # # # (1), 04:20 every day for a full amount of backup, # # (2), incremental backup every 1 hours; # # #==================== =============================================================================== PATH=/sbin:/bin:/usr/sbin:/usr/ Bin:/usr/local/bin:/usr/local/sbin # # DB backup Base path backup_base_dir= "/data/mysql_backup" # # Incremental backup, use the base directory list file # # Content format: Base Directory | This 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 "# # thread number thread_cnt=6 #==============
===================================================================================== function Print_help_info () {echo '--------------------------------------------------------------' echo ' Usage: $ full | inc | Help "echo"--------------------------------------------------------------"
  echo "" Exit 1} [[$#-lt 1]] && print_help_info [[D-${backup_base_dir}]] | | Mkdir-p ${backup_base_dir} # # Xtrabackup base kit does not exist, automatic download installation 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/ta Rball/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 into ' ls-1t/usr/local/xtrabackup/bin ' do rm-f NAME} && ln-s/usr/local/xtrabackup/bin/${fname}/usr/bin/${fname} done Fi # allows only one copy to run to avoid the intersection of full and incremental backups, data The possibility of confusion ##[[-n ' ps Uax | grep innobackupex | grep-v grep ']] && exit 1 # The directory name defaults to minutes, to avoid unexpected circumstances, resulting in a backup task failure, can be accurate to the second CU Rrent_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-amount backup if [[' = ' = ' [']]; 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 [['] = = ' INC ']; Then # # The base directory list file does not exist or is empty, you need to do a full volume 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} # # No directory exists, you need to do a full backup to avoid incremental backups 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} # # On the basis of the last backup, make an incremental backup else prev_backup_ dir= ' sed '/^$/d ' ${inc_base_list} | Tail-1 | Awk-f ' | '  ' {print $} ' # The last backup directory does not exist or the directory is empty, in order to avoid the possibility of human deletion "for partial malicious deletion, there is currently no good check method" 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 ${CURR Ent_bak_path} echo "Null|${current_bak_path}|full" >> ${inc_base_list} else ${xtrabackup_path}--use R=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 fi elif [["$] =" help "]; Then Print_help_info else print_help_info fi # delete data backup 2 weeks ago rm-rf ${backup_base_dir}/' date-d ' days ago ' + '%F ' _* sed-i "/' date-d ' Ago ' + '%F '/d ' ${inc_base_list} #================================================================================ =================== # #The End ################################################################################### ################# # # need to add crontab information: # # (1), full backup # * * * */data/scripts/mysql_backup.sh fully >/dev/null 2> &1 # # # (2), incremental backup # * * */data/scripts/mysql_backup.sh Inc >/dev/null 2>&1 # # ################### #################################################################################
 
############################## ###################################################################### # # DB 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 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 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/m Y.CNF--copy-back/data/mysql_backup/full #######################################################################
 #############################

Third, the effect shows