Xtrabackup physical backup of MySQL production library

In a production environment, we generally take mysqldump full-database backups, but this is not available for large databases, because mysqldump is a logical backup, the efficiency of backup and recovery is slow, and according to personal experience a critical value, Databases below 500G can be backed up with mysqldump for full-database backups, and more than 500G of databases require physical full-library backups or even incremental backups. Xtrabackup is a physical backup, and its backup speed is much faster than logical backup because of its physical characteristics, and xtrabackup is for the logical backup, the next step is to demonstrate how the Xtrabackup to complete the database and the preparation.

One: First we install xtrabackup software

The TAR package can be downloaded by Baidu itself.

[[Email protected] ~]# tar xvf percona-xtrabackup-2.1.8-733-linux-x86_64.tar.gz[[email protected] ~]# mkdir-p/usr/ Local/xtrabackup[[email protected] ~] #cp-RP percona-xtrabackup-2.1.8-linux-x86_64/*/usr/local/ Xtrabackup Add the above directory to the system environment variable [[email protected] bin]# echo export path= $PATH:/usr/local/xtrabackup/bin >>/etc/ Profile[[email protected] bin]# source/etc/profile[[email protected] yum.repos.d]# Yum install Perl-time-hires[[email Protected] bin]# Yum install perl-dbd-mysql*

II: Create a backup directory

[email protected] ~]#mkdir-p/databackup/xtrabackup

[email protected] ~]#mkdir-p /databackup/xtrabackuplog

Three: Database grants backup user-related permissions

MySQL > Grantselect,reload,show databases,super,lock tables,replication client,show view,event,file on * * to [email p rotected] ' localhost ' identified by ' MANAGER ';

IV: Perform automated backup scripts for this article

[[email protected] ~] #vi  xtrabackup.sh#backup.sh#!/bin/sh#on xtrabackup 2.1.8#  The first time it is executed it checks for a full backup, otherwise it first creates a full-library backup #  when you run it again, it will be based on the settings in the script to make an incremental backup of the previous full or incremental backup ################## #author   [email protected]################## #INNOBACKUPEX_PATH =innobackupex  # Innobackupex's command innobackupexfull=/usr/local/xtrabackup/bin/$INNOBACKUPEX _path   #INNOBACKUPEX的命令路径 # 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 user name and password tmplog= "/tmp/innobackupex.$$.log" my_cnf=/etc/my.cnf  #mysql的配置文件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 backup incrbackup_dir= $BACKUP _dir/incre #  Incremental backup directory fullbackup_interval=82800 #  full-Library backup interval, time: SEC keep_fullbackup=3 #  keep at least a few full-library backup logfiledate=backup. ' date +%y%m%d%h%m '. txt# start time started_time= ' date +%s ' ########## ####################################################################  display error and exit ############################# ############################################### #error () {    echo  "$"  1>& 2    exit 1} #  Check execution Environment if [ ! -x  $INNOBACKUPEXFULL  ] ; then  error  "$INNOBACKUPEXFULL not installed or not 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  does not start running." fi if !  ' echo  ' exit  |  $MYSQL  -s  $MYSQL _cmd '  ; then  error " The database user name or password provided is incorrect! " fi #  Backup Header information echo  "----------------------------" echoecho  "$0: mysql backup Script" echo  "starts at :  ' date +%f '   '%T '   '%w ' "echo  #新建全备和差异备份的目录mkdir  -p  $FULLBACKUP _dirmkdir  -p  $INCRBACKUP _dir# Find the latest full backup latest_full_backup= ' find  $FULLBACKUP _dir -mindepth 1  -maxdepth 1 -type d -printf  "%p\n"  | sort -nr | head  -1 '  #  find the most recently modified backup time latest_full_backup_created_time= ' stat -c %y  $FULLBACKUP _dir/ $LATEST _full_backup ' #如果全备有效进行增量备份否则执行完全备份if  [  $LATEST _full_backup  -a  ' expr  $LATEST _ full_backup_created_time +  $FULLBACKUP _interval + 5 '  -ge  $STARTED _time ]  ; then#  if the latest full-time is not expired, name the new directory under the incremental backup directory with the latest full-file name echo -e  "full backup $latest_full_backup not expired, will be based on the Latest_full_backup name as incremental backup base directory name "echo "     "new_incrdir= $INCRBACKUP _dir/$LATEST _full_ Backupmkdir -p  $NEW _incrdir#  to find out whether the latest incremental backup exists. Specify a backup path as the basis for an incremental backup 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 be $incrbasedir as the backup base directory" echo  "   " elseincrbasedir= $INCRBACKUP _dir/${latest_full_backup}/${latest_incr_backup}echo -e  "Incremental backup will be Incrbasedir as the backup base directory "echo "     "fiecho " uses $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>&1# Keep a detailed log of backups cat  $TMPLOG >/databackup/xtrabackuplog/$logfiledateif  [ -z  "' Tail -1   $TMPLOG  | grep  ' innobackupex: completed ok! ' '  ] ; 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#  hint should keep the backup file starting point latest_full_backup= ' find  $FULLBACKUP _dir - mindepth 1 -maxdepth 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} ' echoecho -e  ' \e[31m  note:---------------------------------------------------------------------------------. \e[m '  # Red echo -e  "must keep all incremental backups in the $keep_fullbackup full standby ${res_full_backup} and ${res_incre_backup} directories." echo -e  ' \e[31m note:-------------------------------------------------------------------------- -------. \e[m '   #红色echoelseecho    "*********************************" echo -e  " Performing a completely new full backup ... Please wait a moment ... "echo  " ********************************* "$INNOBACKUPEXFULL  --defaults-file= $MY _cnf   --use-memory=4G   $MYSQL _cmd  $FULLBACKUP _dir >  $TMPLOG  2>&1  #保留一份备份的详细日志cat   $TMPLOG >/databackup/xtrabackuplog/$logfiledateif  [ -z  "' Tail -1   $TMPLOG  | grep  ' innobackupex: completed ok! ' '  ] ; then echo  ' $INNOBACKUPEX command execution 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#  the start of the backup file that should be kept latest_ full_backup= ' find  $FULLBACKUP _dir -mindepth 1 -maxdepth 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 '   #红色echo  -e  "No incremental backup, you must keep $keep_fullbackup full standby ${res_full_ BACKUP}. " echo -e  ' \e[31m note:-------------------------------------------------------------------------- -------. \e[m '   #红色echofi # Delete expired full standby echo -e  "find expire backup file...........waiting ..." echo  -e  "Look for expired full files and delete" >>/databackup/xtrabackuplog/$logfiledatefind   $FULLBACKUP _dir -type  d -name  "201*"  -mtime +2| xargs rm -fr {}; #删除过期的增备echo  -e   "Looking for outdated add-on files and deleting"  >>/databackup/xtrabackuplog/$logfiledatefind   $INCRBACKUP _dir -type  d -name  "201*"  -mtime +2| xargs rm -fr {};echoecho  "completed in:   ' date +%f '   '%T '   '%w ' "exit 0

V: Validation Results

When the script finishes executing, Two folders are created under/databackup/xtrabackup, full represents the whole library backup and Incre incremental backup

For the first time, a full-library backup is created, which creates a full-library backup every day, and the incremental backup executes according to the interval at which the script is executed.

Six: Planning Task reference

Here is a case for reference only:

Edit Crontab Scheduled Tasks

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

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

This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1852587

Xtrabackup physical backup of MySQL production library