# 备份文件介绍  - backup-my.cnf：包含了my.cnf中备份时需要的信息。 - ib_buffer_pool：buffer pool 中的热数据，当设置 innodb_buffer_pool_dump_at_shutdown=1 ，在关闭 MySQL 时，会把内存中的热数据保存在磁盘里 ib_buffer_pool 文件中，位于数据目录下。 - ibdata1：备份的共享表空间文件； - xtrabackup_binlog_info：mysql 服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置 - xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和 LSN (日志序列号)范围信息； - from：备份中包含 LSN 号的起点，全备是0，增量是上次备份的结束位置 - to：CKPT 时的 LSN - last：备份结束时的 LSN - xtrabackup_info：记录备份的基本信息，uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息。 - xtrabackup_logfile：备份的重做日志文件。 # 全量备份 1. 授权用户 ```sql mysql> CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'Sunda@123'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost'; mysql> FLUSH PRIVILEGES; create user xtrabackup@localhost identified by 'Sunda@123'; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* to xtrabackup@localhost; ``` 2. 创建全备 ```bash xtrabackup --user=root --password=mysql --port=3306 --socket=/tmp/mysql.sock --backup --target --datadir=/data ``` # 增量备份 1. 在全备的基础上创建增量备份。 从全备文件的xtrabackup_checkpoints中，有一行to_lsn，备份结束时的 last_lsn，如下图从 lsn=19107798 开始变化， 基于全备我们进行增量备份。 ```bash xtrabackup \ --user=suke \ --password=123 \ --socket=/tmp/mysql.sock \ --backup -target-dir=/home/mysql/backup/inc1 \ --incremental-basedir=/home/mysql/backup/full ```  ```bash xtrabackup \ --user=suke \ --password=123 \ --socket=/tmp/mysql.sock \ --backup -target-dir=/home/mysql/backup/inc2 \ --incremental-basedir=/home/mysql/backup/inc1 ``` # xtrabackup 备份脚本。 1. 全备脚本 ```bash #!/bin/bash XTRABACKUPEX=xtrabackup XTRABACKUPEXFULL=/usr/bin/$XTRABACKUPEX TODAY=`date +%Y%m%d%H%M` YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M` USEROPTIONS="--user=root --password=123456" TMPFILE="/home/iap/xtrabackup_$TODAY.$$.tmp" SOCKET='/data/mysql/mysql3306.sock' MYCNF=/etc/my.cnf MYSQL=/usr/local/mysql/bin/mysql MYSQLADMIN=/usr/local/mysql/bin/mysqladmin BACKUPDIR=/data/mysqlbackup/backup # 备份的主目录 FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录 FULLZIP=$BACKUPDIR/fullzip # 全库包备份的目录 ############################################################################# # error message and exit! ############################################################################# error(){ echo "$1" 1>&2 exit 1 } # Check options before proceeding if [ ! -x $XTRABACKUPEXFULL ]; then error "$XTRABACKUPEXFULL does not exist." fi if [ ! -d $BACKUPDIR ]; then error "Backup destination folder: $BACKUPDIR does not exist." fi if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then error "HALTED: MySQL does not appear to be running." fi if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)." fi # Some info output echo "----------------------------" echo "$0: MySQL backup script" echo "started: `date`" echo "----------------------------" # Create full backup directories if they are not exist. for i in $FULLBACKUPDIR $FULLZIP do if [ ! -d $i ]; then mkdir -pv $i fi done # Compress and upload the previous day's backup! echo "compress the backup and scp to remote machine!" $XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target-dir=$FULLBACKUPDIR > $TMPFILE 2>&1 cd $FULLZIP tar -zcvf $YESTERDAY.tar.gz $FULLBACKUPDIR/* if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then echo "$XTRABACKUPEX failed:"; echo echo "---------- ERROR OUTPUT from $XTRABACKUPEX ----------" fi # get the backup directory! THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE` echo "THISBACKUP=$THISBACKUP" rm -f $TMPFILE echo "Databases backed up successfully to: $THISBACKUP, and tar to $YESTERDAY.tar.gz" # Cleanup echo "delete tar files of 7 days ago" find $FULLZIP/ -mtime +7 -name "*.tar.gz" -exec rm -rf {} \; rm -rf $FULLBACKUPDIR/* echo echo "completed: `date`" #exit 0 ``` 2. 增量备份脚本。 ```bash #!/bin/bash XTRABACKUPEX=xtrabackup XTRABACKUPEXFULL=/usr/bin/$XTRABACKUPEX TODAY=`date +%Y%m%d%H%M` BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"` YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M` USEROPTIONS="--user=root --password=root123" TMPFILE="/itdev/xtrabackup/tmp/xtrabackup_$TODAY.$$.tmp" SOCKET='/tmp/mysql.sock' MYCNF=/etc/my.cnf MYSQL=/usr/local/mysql/bin/mysql MYSQLADMIN=/usr/local/mysql/bin/mysqladmin BACKUPDIR=/itdev/xtrabackup # 备份的主目录 FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录 INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录 FULLZIP=$BACKUPDIR/fullzip # 全库包备份的目录 INCRZIP=$BACKUPDIR/incrzip # 增量包备份的目录 ############################################################################# # error message and exit! ############################################################################# error(){ echo "$1" 1>&2 exit 1 } # Check options before proceeding if [ ! -x $XTRABACKUPEXFULL ]; then error "$XTRABACKUPEXFULL does not exist." fi if [ ! -d $BACKUPDIR ]; then error "Backup destination folder: $BACKUPDIR does not exist." fi if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then error "HALTED: MySQL does not appear to be running." fi if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)." fi # Some info output echo "----------------------------" echo "$0: MySQL backup script" echo "started: `date`" echo "----------------------------" # Create full backup directories if they are not exist. for i in $INCRBACKUPDIR $INCRZIP do if [ ! -d $i ]; then mkdir -pv $i fi done # fullbacuup echo "start 1 incremental backup at $BEGINTIME to directory incr1" >>$TMPFILE rm -rf $INCRBACKUPDIR $XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target-dir=$INCRBACKUPDIR --incremental-basedir=$FULLBACKUPDIR >> $TMPFILE 2>&1 cd $INCRZIP tar -zcvf $YESTERDAY.tar.gz $FULLBACKUPDIR/* if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ]; then echo "$XTRABACKUPEX failed:"; echo echo "---------- ERROR OUTPUT from $XTRABACKUPEX ----------" fi # get the backup directory! THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE` echo "THISBACKUP=$THISBACKUP" rm -f $TMPFILE echo "Databases backed up successfully to: $THISBACKUP" # Cleanup echo "delete tar files of 7 days ago" find $FULLZIP/ -mtime +7 -name "*.tar.gz" -exec rm -rf {} \; rm -rf $INCRBACKUPDIR/* echo echo "completed: `date`" #exit 0 ```