Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/bin/bash
- # Author: Andrey Miroshnichenko <zentavr.trafford.com.ua>
- # Created for: Silicom Internet SL 2012, 2013
- # ICSW Group: http://www.icsw.com/en/
- # Created for: Trafford Group 2012, 2013
- # Created for: Localcircles India 2013
- #
- # Introducion:
- # The script does incremental backup using Percona's xtrabackup utilities.
- # You can download them from this site:
- # http://www.percona.com/downloads/XtraBackup/LATEST/
- # Yum repository: http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html
- # APT repository: http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html
- #
- # FreeBSD users can find the percona xtrabackup in the ports (seems like rather old version):
- # [root@cassiopeia ~]# cd /usr/ports/
- # [root@cassiopeia /usr/ports]# make search key=xtrabackup
- # Port: xtrabackup-1.1
- # Path: /usr/ports/databases/xtrabackup
- # Info: OpenSource version of InnoDB backup with support of Percona extensions
- # Maint: aleksandr.kuzminsky@percona.com
- # B-deps: gettext-0.18_1 gmake-3.81_4 libiconv-1.13.1_1
- # R-deps:
- # WWW: http://www.percona.com/docs/wiki/percona-xtrabackup:start
- trap bashtrap INT
- function bashtrap() {
- echo "CTRL+C Detected !..."
- exit 1
- }
- function usage() {
- cat << EOF
- usage: $0 options
- The script does incremental MySQL backup.
- OPTIONS:
- -h, --help
- Show this message
- -j, --job <job>
- What job to do: "backup" or "restore"?
- Default is "backup".
- -t, --type <type>
- Backup type: "full" or "incremental".
- Default is "full".
- -d, --database <database>
- Database name
- -l, --lsndir <directory>
- LSN (Log Seek Number) Directory Name.
- Default is "/tmp"
- -T, --threads <threads>
- How many threads to use?
- Default is: "amount of CPUs + 1"
- --throttle <IOPS>
- How many IOPS to produce
- --ionice-class <ionice_class>
- ionice (1) class to set up during the operation (pass -c ionice_class to ionice):
- 0: none, 1: realtime, 2: best-effort, 3: idle
- --ionice-classdata <ionice_class>
- ionice (1) classdata to set up during the operation (pass -n ionice_class to ionice):
- class data (0-7, lower being higher prio)
- -L, --log <logfile>
- Path to the logfile.
- Default is "/tmp/logfile.log".
- -D, --path <path>
- Where to store/pick the backup results.
- Default is "Current Directory"
- -r, --dest <path>
- Where to place the retored MySQL data
- Default is "Current Directory"
- -x, --xtrabin <xtrabackup_binary>
- Sets up the Xtrabackup binary
- -s, --stream <streamtype>
- Sets what the stream to use: "tar" or "xbstream"
- Default is "xbstream"
- -n, --do-not-delete
- Do not delete the packed files during restore
- -e, --export
- Do an export of InnoDB tablespaces to separate .exp files.
- Only for Percona Server.
- Review this: http://www.percona.com/doc/percona-xtrabackup/innobackupex/importing_exporting_tables_ibk.html
- Set up this into your my.cnf:
- innodb_file_per_table=1 # For any version of Percona Server
- innodb_expand_import=1 # For Percona Sevrer to version 5.5.10-20.1
- innodb_import_table_from_xtrabackup=1 # For Percona Sevrer from version 5.5.10-20.1 and after
- Importing tables:
- To import a table to other server, first create a new table with the same structure as the one that will be imported at that server:
- OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;
- then discard its tablespace:
- OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
- After this, copy mytable.ibd and mytable.exp files to database’s home, and import its tablespace:
- OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
- Once this is executed, data in the imported table will be available.
- EOF
- }
- function checkpipe() {
- STATUS=$1
- SOFT1=$2
- SOFT2=$3
- if [ "$STATUS" != "0 0" ]; then
- echo "The pipe of $SOFT1 and $SOFT2 was finished unseccussfully: $STATUS"
- exit 1
- else
- echo "The pipe operation was completed successfully"
- fi
- }
- function checkexit() {
- STATUS=$1
- SOFT=$2
- if [ "$STATUS" != "0" ]; then
- echo "The $SOFT was finished unseccussfully: $STATUS"
- exit 1
- else
- echo "The $SOFT operation was completed successfully"
- fi
- }
- # Extracting the options
- # TODO: (Low) If this fails, parse only the short options with bash built-in getopt
- # http://linuxwell.com/2011/07/14/getopt-in-bash/
- ARGS=$(getopt -o "hj:t:d:l:T:L:D:r:x:nes:" -l "help,job:,type:,database:,lsndir:,threads:,log:,path:,dest:,xtrabin:,do-not-delete,export,stream:,throttle:,ionice-class:,ionice-classdata:" -n "MyXtraBackup" -- "$@")
- if [ $? -ne 0 ]; then
- echo "Bad argument to getopt was given"
- exit 1
- fi
- eval set -- "$ARGS"
- #
- # Set up the defaults :)
- #
- # Job type: backup or restore
- JOB="backup"
- # Backup type: "full" or "incremental"
- TYPE="full"
- # Path to where LSN position will be stored
- LSNDIR="/tmp"
- # The amount of the threads to use
- THREADS=$(( $(grep -c processor /proc/cpuinfo) + 1 ))
- # Where to store the logfile
- LOGFILE="/tmp/logfile.log"
- # Where to put the dump
- BACKUPRES=$(pwd)
- # Where to restore the data
- DEST=$(pwd)
- # Type of the stream
- STREAMTYPE="xbstream"
- # Location of the utilities
- INNOBACKUPEX=$(which innobackupex)
- LZOP=$(which lzop)
- XBSTREAM=$(which xbstream)
- TAR=$(which tar)
- MYSQLDUMP=$(which mysqldump)
- MYSQL=$(which mysql)
- IONICE=$(which ionice)
- OPTS=""
- EXPORT=""
- LZOP_OPTS="-dcfU"
- IONICE_CMD=""
- IONICE_CLASS=""
- IONICE_CLASSDATA=""
- # Extract the options
- while true; do
- case "$1" in
- -h|--help)
- usage
- exit;;
- -j|--job)
- if [ -n "$2" ];then
- JOB="$2"
- else
- echo "No job type was specified (-j | --job)."
- exit 1;
- fi
- shift 2;;
- -t|--type)
- if [ -n "$2" ];then
- TYPE="$2"
- else
- echo "No backup type was specified (-t | --type)."
- exit 1;
- fi
- shift 2;;
- -d|--database)
- if [ -n "$2" ];then
- DATABASE="$2"
- else
- echo "No database was specified (-d|--database)."
- exit 1;
- fi
- shift 2;;
- -l|--lsndir)
- if [ -n "$2" ];then
- LSNDIR="$2"
- else
- echo "No LSN Directory was specified (-l|--lsndir)."
- exit 1;
- fi
- shift 2;;
- -T|--threads)
- if [ -n "$2" ];then
- THREADS="$2" # TODO: (Low) Check, if the parameter is an integer?
- else
- echo "No amount of the threads was specified (-T | --threads)."
- exit 1;
- fi
- shift 2;;
- -L|--log)
- if [ -n "$2" ];then
- LOGFILE="$2" # TODO: (Low) Check, if the basename of logfile exists??
- else
- echo "No logfile was given (-L | --log)."
- exit 1;
- fi
- shift 2;;
- -D|--path)
- if [ -n "$2" ];then
- BACKUPRES="$2"
- else
- echo "No directory for backup results was specified (-D | --path)."
- exit 1;
- fi
- shift 2;;
- -r|--dest)
- if [ -n "$2" ];then
- DEST="$2"
- else
- echo "No directory where to place restored data was specified (-r | --dest)."
- exit 1;
- fi
- shift 2;;
- -x|--xtrabin)
- if [ -n "$2" ];then
- OPTS="$OPTS --ibbackup=$2"
- else
- echo "No xtrabackup name was specified (-x | --xtrabin)."
- exit 1;
- fi
- shift 2;;
- --throttle)
- if [ -n "$2" ];then
- OPTS="$OPTS --throttle=$2"
- else
- echo "No throttle value was specified (--throttle)."
- exit 1;
- fi
- shift 2;;
- --ionice-class)
- if [ -n "$2" ];then
- IONICE_CLASS="-c$2"
- else
- echo "No ionice class value was specified (--ionice-class)."
- exit 1;
- fi
- shift 2;;
- --ionice-classdata)
- if [ -n "$2" ];then
- IONICE_CLASSDATA="-n$2"
- else
- echo "No ionice class data value was specified (--ionice-classdata)."
- exit 1;
- fi
- shift 2;;
- -s|--stream)
- if [ -n "$2" ];then
- STREAMTYPE="$2"
- else
- echo "No streamtype name was specified (-s | --stream)."
- exit 1;
- fi
- shift 2;;
- -n|--do-not-delete)
- LZOP_OPTS="-dcf"
- shift
- ;;
- -e|--export)
- EXPORT="--export"
- shift
- ;;
- --)
- shift
- break;;
- esac
- done
- # Remove trailing slashes from the end
- BACKUPRES=$(echo "$BACKUPRES" | sed 's/\/*$//')
- LSNDIR=$(echo "$LSNDIR" | sed 's/\/*$//')
- DEST=$(echo "$DEST" | sed 's/\/*$//')
- case "${STREAMTYPE}" in
- "tar")
- # Filename Suffix
- echo "Using tar as a streamer"
- FILENAMESUFFIX=".tar.lzo"
- STREAMER_BIN=$TAR
- ;;
- "xbstream")
- # Filename Suffix
- echo "Using xbstream as a streamer"
- FILENAMESUFFIX=".xbs.lzo"
- STREAMER_BIN=$XBSTREAM
- ;;
- *)
- echo "Unknown type of backup was specified: ${STREAMTYPE}"
- exit 1
- ;;
- esac
- # Determine for whether we need to use ionice(1)
- if [[ -n "$IONICE_CLASS" ]] || [[ -n "$IONICE_CLASSDATA" ]]; then
- if [[ -n "$IONICE" ]] && [[ -x "$IONICE" ]]; then
- IONICE_CMD="$IONICE $IONICE_CLASS $IONICE_CLASSDATA -- "
- else
- echo "No ionice(1) executable was found or it has no executable flag"
- fi
- fi
- # The names will look like this:
- # DATABASE-20120810_221058-full-169:12587456.xbs.lzo
- # DATABASE-20120811_221055-incremental-169:12599569.xbs.lzo
- # What needs to be done?
- case "$JOB" in
- "backup")
- echo "Doing a backup."
- # Check, if the database was specified?
- if [ -z "$DATABASE" ]; then
- echo "Database was not specified"
- exit 1
- fi
- # Checking if the destination path exists
- if [ ! -d $BACKUPRES ]; then
- echo "Creating $BACKUPRES"
- mkdir -p "$BACKUPRES"
- if [ $? -ne 0 ]; then
- echo "Creation of $BACKUPRES was failed"
- exit 1
- fi
- fi
- if [ -z "$INNOBACKUPEX" ]; then
- echo "No innobackupex was found in $PATH"
- exit 1
- fi
- if [ -z "$MYSQL" ]; then
- echo "No mysql was found in $PATH"
- exit 1
- fi
- if [ -z "$MYSQLDUMP" ]; then
- echo "No mysqldump was found in $PATH"
- exit 1
- fi
- if [ -z "$LZOP" ]; then
- echo "No lzop archiver was found in $PATH"
- exit 1
- fi
- # Inventing a name for the file
- FILENAME=$DATABASE"-"$(date "+%Y%m%d_%H%M%S")
- TMPNAME=$(mktemp --tmpdir="$BACKUPRES")
- # What type of the backup do we have?
- case "$TYPE" in
- "full")
- echo "Doing a full backup to $TMPNAME"
- if [ "$STREAMTYPE" == "xbstream" ]; then
- echo "...xbstream"
- $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --extra-lsndir="$LSNDIR" --stream=$STREAMTYPE --no-timestamp $OPTS "$BACKUPRES" 2>>"$LOGFILE" | $LZOP -c > $TMPNAME
- # Status of the pipe
- checkpipe "${PIPESTATUS[*]}" "innobackupex ($INNOBACKUPEX)" "lzop:($LZOP)"
- else
- echo "...tar: Doing a backup"
- rm -rf --preserve-root "$BACKUPRES"/tmp
- $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --extra-lsndir="$LSNDIR" --no-timestamp $OPTS "$BACKUPRES"/tmp 2>>"$LOGFILE"
- echo "...tar: Packing"
- ( cd "$BACKUPRES"/tmp; $TAR -cf - . | $LZOP -c > $TMPNAME )
- # TODO: Check for pipe status here
- echo "Pipe status is: ${PIPESTATUS[*]}"
- echo "...tar: Removing an old data"
- rm -rf --preserve-root "$BACKUPRES"/tmp
- fi
- ;;
- "incremental")
- echo "Doing an incremental backup to $TMPNAME"
- # Check if we have full backup (check LSN, the file could be moved away to another server)?
- TOLSN=$(grep "to_lsn" "$LSNDIR/xtrabackup_checkpoints" | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//')
- if [ -z "$TOLSN" ]; then
- echo "Cannot find LSN position of the previous backup in $LSNDIR"
- exit 1
- fi
- if [ "$STREAMTYPE" == "xbstream" ]; then
- echo "...xbstream"
- $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --incremental --extra-lsndir="$LSNDIR" --incremental-lsn=$TOLSN --stream=$STREAMTYPE $OPTS "$BACKUPRES" 2>>"$LOGFILE" | $LZOP -c > $TMPNAME
- # Status of the pipe
- STATUS=${PIPESTATUS[*]}
- if [ "$STATUS" != "0 0" ]; then
- echo "The pipe of innobackupex ($INNOBACKUPEX) and lzop:($LZOP) was finished unseccussfully: $STATUS"
- exit 1
- else
- echo "The pipe operation was completed successfully"
- fi
- else
- echo "Removing ${BACKUPRES}/tmp"
- rm -rf --preserve-root "$BACKUPRES"/tmp
- echo "...tar: Doing a backup"
- echo $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --incremental --extra-lsndir="$LSNDIR" --no-timestamp --incremental-lsn=$TOLSN $OPTS "$BACKUPRES"/tmp
- $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --incremental --extra-lsndir="$LSNDIR" --no-timestamp --incremental-lsn=$TOLSN $OPTS "$BACKUPRES"/tmp 2>>"$LOGFILE"
- echo "...tar: Packing"
- ( cd "$BACKUPRES"/tmp; $TAR -cf - . | $LZOP -c > $TMPNAME )
- echo "...tar: Removing an old data"
- rm -rf --preserve-root "$BACKUPRES"/tmp
- fi
- ;;
- *)
- echo "A wrong backup type was specified ($TYPE)"
- exit 1
- ;;
- esac
- # Checking what we have in the logs
- LOGISOK=$(tail -1 "$LOGFILE" | grep -c 'completed OK!')
- if [ $LOGISOK -eq 1 ]; then
- tail -1 "$LOGFILE"
- else
- echo '##### ALERT #####'
- echo 'We don\'t have \'completed OK!\' status in the end of a logfile, so probably an error happened'
- echo '##### ALERT #####'
- fi
- echo "Renaming $TMPNAME"
- # Fetch the latest LSN number
- FROMLSN=$(grep "from_lsn" "$LSNDIR/xtrabackup_checkpoints" | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//')
- TOLSN=$(grep "to_lsn" "$LSNDIR/xtrabackup_checkpoints" | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//')
- mv -f $TMPNAME $BACKUPRES"/"$FILENAME"-"$TYPE"-"$FROMLSN"-"$TOLSN""$FILENAMESUFFIX
- echo "$TYPE backup is done. The result is in "$BACKUPRES"/"$FILENAME"-"$TYPE"-"$FROMLSN"-"$TOLSN""$FILENAMESUFFIX
- # Storing table definitions
- echo "Dumping table definitions"
- mkdir -p $BACKUPRES/definitions
- for TABLE in $(echo "show tables" | $MYSQL --defaults-extra-file=/root/.my.cnf -B -N "$DATABASE"); do
- echo "Dumping table definition for ${DATABASE}.${TABLE}"
- $IONICE_CMD $MYSQLDUMP --defaults-extra-file=/root/.my.cnf -q -Q --no-data "$DATABASE" "$TABLE" > $BACKUPRES/definitions/${TABLE}.sql 2>> "$LOGFILE"
- done
- echo "Doing a backup of $DATABASE routies"
- $IONICE_CMD $MYSQLDUMP --defaults-extra-file=/root/.my.cnf -q -Q --routines --no-create-info --no-data --no-create-db --skip-opt "$DATABASE" > $BACKUPRES/definitions/__routines.sql 2>> "$LOGFILE"
- echo "Archiving the definitions"
- ( cd $BACKUPRES/definitions; $TAR -cf - . | $LZOP -c > $BACKUPRES/definitions-$FILENAME.tar.lzo )
- rm -rf --preserve-root $BACKUPRES/definitions
- echo "Done"
- ;;
- "restore")
- echo "Restoring $DATABASE" | tee "${LOGFILE}"
- if [ -z "$XBSTREAM" ]; then
- echo "No xbstream (the part of percona's xtrabackup was found in $PATH" | tee -a "${LOGFILE}"
- exit 1
- fi
- # Review what files of full backups do we have (we take the latest one...)
- FULLBACKUP=$(find "$BACKUPRES" -type f -name "$DATABASE-*-full-*$FILENAMESUFFIX" -exec basename {} \; | sort -r | head -1)
- if [ -z "$FULLBACKUP" ]; then
- echo "No full backup was found in $BACKUPRES for database $DATABASE" | tee -a "${LOGFILE}"
- exit 1
- else
- echo "Considering $FULLBACKUP to be as a full backup for $DATABASE" | tee -a "${LOGFILE}"
- fi
- # Searching for the incremental backups (We must !!!! apply the increments one by one to the base backup in the right order)
- echo "Searching for the incremental backups" | tee -a "${LOGFILE}"
- # We need to search files recursively. The age the files should be younger then the age of a full backup
- PREV_FILE=$FULLBACKUP
- while true; do
- NEXT_LSN=$(basename $PREV_FILE $FILENAMESUFFIX | cut -f5 -d-) # 479:2100086046
- NEXT_FILE="$DATABASE-*-incremental-$NEXT_LSN-*$FILENAMESUFFIX"
- echo " Searching for $NEXT_FILE" | tee -a "${LOGFILE}"
- #INC=$(find "$BACKUPRES" -type f -name "$NEXT_FILE" -newer "$BACKUPRES/$PREV_FILE" -exec basename {} \; | sort -r | head -1)
- INC=$(find "$BACKUPRES" -type f -name "$NEXT_FILE" -exec basename {} \; | sort -r | head -1)
- if [ -z "$INC" ]; then
- echo " No next incremental found" | tee -a "${LOGFILE}"
- break
- fi
- echo " Found $INC" | tee -a "${LOGFILE}"
- # Adding the file to the list
- INCREMENTALS="$INCREMENTALS $INC"
- PREV_FILE=$INC
- done
- FULLRESTORE=$DEST/full
- # Restore a full backup
- mkdir -p $FULLRESTORE
- echo "Unpacking a full backup to $FULLRESTORE" | tee -a "${LOGFILE}"
- $LZOP $LZOP_OPTS $BACKUPRES/$FULLBACKUP | $STREAMER_BIN -x --directory=$FULLRESTORE
- # Status of the pipe
- checkpipe "${PIPESTATUS[*]}" "lzop:($LZOP)" "$STREAMER_BIN" | tee -a "${LOGFILE}"
- # Replay the commited transactions on full (base) backup
- echo "Replaying the commited transactions on full (base) backup" | tee -a "${LOGFILE}"
- $IONICE_CMD $INNOBACKUPEX --apply-log --redo-only $FULLRESTORE --use-memory=1G $OPTS $EXPORT 2>>"$LOGFILE"
- checkexit $? "$INNOBACKUPEX" | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo "====================================================================================================================" | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- # Processing the increments
- for INC in $INCREMENTALS; do
- echo "Processing $INC" | tee -a "${LOGFILE}"
- INCRESTORE=$DEST/$(basename $INC $FILENAMESUFFIX)
- mkdir -p $INCRESTORE
- echo "Unpacking an incremental backup to $INCRESTORE" | tee -a "${LOGFILE}"
- $LZOP $LZOP_OPTS $BACKUPRES/$INC | $STREAMER_BIN -x --directory=$INCRESTORE
- # Status of the pipe
- checkpipe "${PIPESTATUS[*]}" "lzop:($LZOP)" "$STREAMER_BIN" | tee -a "${LOGFILE}"
- echo "Applying incremental backup log to a full backup" | tee -a "${LOGFILE}"
- $IONICE_CMD $INNOBACKUPEX --apply-log --redo-only $FULLRESTORE --incremental-dir=$INCRESTORE --use-memory=1G $OPTS $EXPORT 2>>"$LOGFILE"
- checkexit $? "$INNOBACKUPEX" | tee -a "${LOGFILE}"
- echo "Removing the folder whith incremental deltas" | tee -a "${LOGFILE}"
- rm -rf $INCRESTORE
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo "====================================================================================================================" | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- done
- # At last - prepare the baclup for usage
- echo "Preparing again the full backup (base + incrementals) once again to rollback the pending transactions" | tee -a "${LOGFILE}"
- $IONICE_CMD $INNOBACKUPEX --apply-log $FULLRESTORE --use-memory=1G $OPTS $EXPORT 2>>"$LOGFILE"
- checkexit $? "$INNOBACKUPEX" | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo "====================================================================================================================" | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo | tee -a "${LOGFILE}"
- echo "The restore is done. The result is in $FULLRESTORE folder" | tee -a "${LOGFILE}"
- ;;
- *)
- echo "Wrong job-type was specified"
- exit 1
- ;;
- esac
- exit 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement