Mysqlxtrabackup incremental backup _ MySQL

Mysqlxtrabackup incremental backup bitsCN.com

Mysql xtrabackup incremental backup

The incremental backup policy for mysql is full-backup on Monday. for other incremental backups, set the retention date based on business needs, such as January.

Incremental Backup steps;

1. create full backup

2. create incremental backup based on the full backup directory

3. the second incremental backup is based on the first incremental backup directory, and so on.

The following is the incremental backup script written today. it runs on your testing machine for several days.

#!/bin/shbegintime=`date +"%Y-%m-%d %H:%M:%S"`format_time=`date +"%Y-%m-%d_%H:%M:%S"`week=`date +%w`port=3306#ip=backdir=/backupfile_cnf=/etc/my.cnfuser_name=rootpasswd=out_log=$datadir/xtraback_log_$format_timetime_cost=$backdir/xtraback_time.txtif [ ! -d "$backdir" ]; then      mkdir -p $backdir fi if [ -d "$backdir/rec5" ];then            rm -rf $backdir-bak           mv $backdir $backdir-bak           mkdir $backdir fi#full backup if [ ! -d "$backdir/full" ];then     echo "#####start full backup at $begintime to directory full" >>$time_cost    innobackupex --defaults-file=$file_cnf --no-lock --no-timestamp --user=$user_name --port=3306 $backdir/full 1>$out_log-full 2>&1    tar -zcvf $backdir/full.tar.gz $backdir/full    break;    elif [ ! -d "$backdir/rec0" ];then     echo "######start 0 incremental backup at $begintime to directory rec0" >>$time_cost    innobackupex --default-file=$file_cnf --no-lock --no-timestamp --user=$user_name --port=$port --incremental --incremental-basedir=$backdir/full $backdir/rec0 1> $out_log-rec0 2>&1    break;    elif [ ! -d "$backdir/rec1" ];then      echo "#######start 1 incremental backup at $begintime to directory rec1" >>$time_cost     innobackupex --defaults-file=$file_cnf --no-lock --no-timestamp --user=$user_name --port=port --incremental --incremental-basedir=$backdir/full $backdir/rec1 1>$out_log-rec1 2>&1     break;     elif [ ! -d "$backdir/rec2" ]; then      echo "######start 2 incremental backup at $begintime to directory rec2" >>$time_cost     innobackupex --defaults-file=$file_cnf --no-lock --no-timestamp --user=$user_name --port=port --incremental --incremental-basedir=$backdir/rec1 $backdir/rec2 1> $out_log-rec2 2>&1     break;     elif [ ! -d "$backdir/rec3" ]; then      echo "#######start 3 incremental backup at $begintime to directory rec3" >$time_cost     innobackupex --defaults-file=$file_cnf --no-lock --no-timestamp --user=root --port=port --incremental --incremental-basedir=$/backdir/rec2 $backdir/rec3 1> $out_log-rec3 2>&1     break;     elif [! -d "$backdir/rec4" ];then      echo "########start 4 incremental backup at $begintime to directory rec4" >>$time_cost     innobackupex --defaults-file=$file_cnf --no-lock --no-timestamp --user=root --port=port --incremental --incremental-basedir=$backdir/rec4 1> $out_log-rec4 2>&1     break;     elif [ ! -d "$backdir/rec5" ];then       echo "#######start 5 incremental backup at $begintime to directory rec5" >>$time_cost /      innobackupex --defaults-file=$file_cnf --no-lock --no-timestamp --user=$user_name --port=port --incremental --incremental-basedir=$backdir/rec4 $backdir/rec5 1> $out_log-rec5 2>&1      break;    fi

Restore procedure

1. close the instance

2. application logs. if incremental logs exist, the full backup is restored first, and then the incremental logs are restored one by one.

3 copy data to the Directory

4. modify data file permissions. chown and chmod

5. start

BitsCN.com