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