Shulou(Shulou.com)06/01 Report--

In the production environment, we generally take mysqldump full database backup, but this is not available for large databases, because mysqldump is a logical backup, backup and recovery efficiency is slow, according to personal experience to give a critical value, databases below 50g can use mysqldump for full database backup, databases above 50g need physical full database backup, or even incremental backup. Xtrabackup is a physical backup, and its backup speed is much faster than that of logical backup because of its physical characteristics, and Xtrabackup is born for logical backup. Next, we will demonstrate how Xtrabackup completes and adds to the database.

First, let's install the xtrabackup software.

The tar package can be downloaded by Baidu.

[root@HE3 ~] # tar xvf percona-xtrabackup-2.1.8- 733 LinuxMurx8664.tar.gz [root @ HE3 ~] # mkdir-p / usr/local/xtrabackup [root@HE3 ~] # cp-rp percona-xtrabackup-2.1.8-Linux-x86_64/* / usr/local/xtrabackup add the above directory to the system environment variable [root@HE3 bin] # echo export PATH=$PATH:/usr/local/xtrabackup/bin > > / etc/profile [root] @ HE3 bin] # source / etc/profile [root@HE3 yum.repos.d] # yum install perl-Time-HiRes [root@HE3 bin] # yum install perl-DBD-MySQL*

Second: create a backup directory

[root@HE3] # mkdir-p / databackup/xtrabackup

[root@HE3] # mkdir-p / databackup/xtrabackuplog

Third, the database grants relevant permissions to backup users

Mysql > grantSELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *. * to backup@'localhost' identified by 'MANAGER'

Four: execute the automated backup script for this article

[root@HE3 ~] # vi xtrabackup.shallowbackup.shedding backup.the first time it executes it, it will check if there is a full backup, otherwise create a full backup first # when you run it again It will perform incremental backups based on previous full or incremental backups based on the settings in the script # author rrhelei@126.com#INNOBACKUPEX_PATH=innobackupex # INNOBACKUPEX command INNOBACKUPEXFULL=/usr/local/xtrabackup/bin/$INNOBACKUPEX_PATH # INNOBACKUPEX command path # mysql target server and username and password MYSQL_ CMD= "--host=localhost-- user=backup-- password=MANAGER-- port=3306" MYSQL_UP= "--user=backup-- password='MANAGER'-- port=3306" # mysqladmin username and password TMPLOG= "/ tmp/innobackupex.$$.log" MY_CNF=/etc/my.cnf # mysql configuration file MYSQL=/usr/local/mysql/bin/mysqlMYSQL_ADMIN=/usr/local/mysql/bin/mysqladminBACKUP_DIR=/databackup/xtrabackup # backup home directory FULLBACKUP_DIR=$BACKUP_DIR/full # full library Directories backed up INCRBACKUP_DIR=$BACKUP_DIR/incre # directories for incremental backups FULLBACKUP_INTERVAL=82800 # interval for full library backups Time: second KEEP_FULLBACKUP=3 # keep at least several full library backups logfiledate= backup.`date +% Y% m% d% H% M`.txt # start time STARTED_TIME= `date +% s` # # display an error and exit # error () {echo "$1" 1 > & 2 exit 1} # check the execution environment if [!-x $INNOBACKUPEXFULL] Then error "$INNOBACKUPEXFULL is not installed or linked to / usr/bin." fi if [!-d $BACKUP_DIR]; then error "backup destination folder: $BACKUP_DIR does not exist." fi mysql_status= `netstat-nl | awk'NR > 2 {if ($4 ~ /. *: 3306 /) {print "Yes"; exit 0} '`if ["$mysql_status"! = "Yes"]; then error "MySQL is not running." fi if! `echo' exit' | $MYSQL-s $MYSQL_ CMD` The database username or password provided by then error "is incorrect!" fi # backup header information echo-- "echoecho" $0: MySQL backup script "echo" starts with: `date +% F'% T'% w` "echo # New directory mkdir-p $FULLBACKUP_DIRmkdir-p $INCRBACKUP_DIR# for full and differential backups Latest full backup LATEST_FULL_BACKUP= `find $FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1` # find the latest modified backup time LATEST_FULL_BACKUP_CREATED_TIME= `stat-c% Y $FULLBACKUP_DIR/$LATEST_FULL_ BACKUP` # otherwise perform a full backup if ["$LATEST_FULL_BACKUP"-a `backup $LATEST_FULL_BACKUP_CREATED_ TIME + $FULLBACKUP_INTERVAL + 5`-ge $STARTED_TIME] Then# if the latest full backup is not expired, create a new directory echo-e under the incremental backup directory with the latest full file name. "full backup $LATEST_FULL_BACKUP is not expired. The incremental backup base directory name" echo "" NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUPmkdir-p $NEW_INCRDIR# will be based on the $LATEST_FULL_BACKUP name to find whether the latest incremental backup exists. Specify the path of a backup as the base LATEST_INCR_BACKUP= `find $NEW_INCRDIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`if [! $LATEST_INCR_BACKUP] ThenINCRBASEDIR=$FULLBACKUP_DIR/$LATEST_FULL_BACKUPecho-e "incremental backup will use $INCRBASEDIR as the backup base directory" echo "elseINCRBASEDIR=$INCRBACKUP_DIR/$ {LATEST_FULL_BACKUP} / ${LATEST_INCR_BACKUP} echo-e" incremental backup will use $INCRBASEDIR as the backup base directory "echo"fiecho" use $INCRBASEDIR as the base directory for this incremental backup. "$INNOBACKUPEXFULL-defaults-file=$MY_CNF-use-memory=4G $MYSQL_CMD-incremental $NEW_INCRDIR-- incremental-basedir $INCRBASEDIR > $TMPLOG 2 > & keep the detailed cat of a backup cat $TMPLOG > / databackup/xtrabackuplog/$logfiledateif [- z "`log-1$ TMPLOG | grep 'innobackupex: completed OKlogs' `"] Then echo "$INNOBACKUPEX command execution failed:"; echo echo-e "- $INNOBACKUPEX_PATH error -" cat $TMPLOG rm-f $TMPLOG exit 1fiTHISBACKUP = `awk-"/ Backup created in directory/ {split (\\ $0, p,\"'\ ") Print p [2]} "$TMPLOG`rm-f $TMPLOGecho-n" Database successfully backed up to: $THISBACKUP "echo# prompt the starting point of the backup file LATEST_FULL_BACKUP= `find $FULLBACKUP_DIR-mindepth 1-type d-printf"% P\ n "| sort-nr | head-1 `new _ INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUPLATEST_INCR_BACKUP= `find $NEW_INCRDIR-mindepth 1-maxdepth 1-type d-printf"% P\ n "| sort-nr | head-1`RES _ FULL" _ BACKUP=$ {FULLBACKUP_DIR} / ${LATEST_FULL_BACKUP} RES_INCRE_BACKUP= `dirname ${INCRBACKUP_DIR} / ${LATEST_FULL_BACKUP} / ${LATEST_INCR_BACKUP} `dirname-e'\ e [31m NOTE:- -.\ e [m'# red echo-e "must keep a full copy of $KEEP_FULLBACKUP, that is, all incremental backups in the full ${RES_FULL_BACKUP} and ${RES_INCRE_BACKUP} directories." echo-e'\ e [31m NOTE:-- -.\ e [m'# red echoelseecho "* *" echo-e "is performing a new full backup. Just a moment, please. "echo" * * "$INNOBACKUPEXFULL-- defaults-file=$MY_CNF-- use-memory=4G $MYSQL_CMD $FULLBACKUP_DIR > $TMPLOG 2 > & 1 # keep a backup of the detailed log cat $TMPLOG > / databackup/xtrabackuplog/$logfiledateif [- z" `log-1$ TMPLOG | grep 'innobackupex: completed OKroom` "]; then echo" $INNOBACKUPEX command failed: " Echo echo-e "- $INNOBACKUPEX_PATH error -" cat $TMPLOG rm-f $TMPLOG exit 1fi THISBACKUP= `awk-"/ Backup created in directory/ {split (\\ $0, p,\"'\ ") Print p [2]} "$TMPLOG`rm-f $TMPLOGecho-n" Database successfully backed up to: $THISBACKUP "echo# prompt the starting point of the backup file LATEST_FULL_BACKUP= `find $FULLBACKUP_DIR-mindepth 1-type d-printf"% P\ n "| sort-nr | head-1`RES _ FULL_BACKUP=$ {FULLBACKUP_DIR} / ${LATEST_FULL_BACKUP} echoecho-e'\ e [31m NOTE:- -.\ e [m'# red echo-e "No incremental backup You must keep a complete copy of $KEEP_FULLBACKUP, that is, ${RES_FULL_BACKUP}. "echo-e'\ e [31m NOTE:----.\ e [m' # Red echofi# deletes expired full echo-e "find expire backup file.waiting."echo-e" looks for expired full files and deletes "> / databackup/xtrabackuplog/$logfiledatefind $FULLBACKUP_DIR-type d-name" 201* "- mtime + 2 | xargs rm-fr {} # delete expired add-up echo-e "find expired add-up files and delete" > / databackup/xtrabackuplog/$logfiledatefind $INCRBACKUP_DIR-type d-name "201*"-mtime + 2 | xargs rm-fr {}; echoecho "completed at: `date +% F'% T'% w`" exit 0

Five: verify the results

After the script is executed, two folders are created under / databackup/xtrabackup, namely full for full library backup and incre incremental backup

The first time it is executed, a full library backup is created, the script creates a full library backup every day, and the incremental backup is performed according to the interval at which the script is executed.

Six: planning task reference

Here is an example for reference only:

Edit crontab scheduled tasks

00 * / 1 * / root/shell/xtrabackup.sh 1 > / dev/null 2 > & 1 &

This means that the script is executed automatically every hour, and the interval between incremental backups is 1 hour.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.