adm_mysql_backup.sh

#!/bin/bash

# global variable of config of backup DB
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
charset="--default-character-set=utf8"
mysql="mysql -A $charset"
mysqldump="mysqldump --opt $charset"
mysqlcheck="mysqlcheck"
verbose=0
optimize=0
clean=0
perdb=0
compresslevel=9
gzip="gzip"
no_slave=0
slave_max_delay=60
mysql_option=()
db_option=()

NUMBER_PROCESSORS=$( cat /proc/cpuinfo | grep processor | wc -l )
NUMBER_TABLES=$(( $NUMBER_PROCESSORS/2 ))
BCKDIR=

which ionice >/dev/null && ionice -c3 -p$$

# echo -e par defaut
shopt -s xpg_echo

# 3 global variables 
# $backup_new: destination directory of creation of file of backup DB
# $charset   : [optional] full option for mysqldump
# $like		 : [optional] full string SQL eg: $like = "AND TABLE_NAME = 'db'" 
#
# 1 argument: le name of the MySQL DB to backup

function _log()
{
	if [ $verbose -gt 0 ]
	then
		echo "$( date -R ) :: $@"
	fi
}


function multi_file_mysql_backup()
{
	local database=$1
	local table
	local destfile
	local directory_ok=0
	
	if [ ! -d $backup_new ]
	then
		echo "error: directory not present: $backup_new"
		return 1
	fi
	if [ -z "$database" ]
	then
		echo "error: \$database not assigned"
		return 1
	fi
	cd $backup_new
	if [ ! -d $database ]
	then
		mkdir $database
		if [ $? -ne  0 ]
		then
			echo "error: creation of directory of database '$database': $PWD/$database"
		else
			directory_ok=1
		fi
	else
		directory_ok=1
	fi
	if [ $directory_ok -eq 1 ]
	then
		destfile=$backup_new/${database}/yop
		touch $destfile
		if [ ! -w $destfile ]
		then
			echo "impossible to write $destfile"
		else
			rm -f $destfile
			if [ $perdb -eq 1 ]
			then
				stop_slave
				$mysql --vertical -e "SHOW SLAVE STATUS" > $backup_new/${database}/backup-position.txt
			fi

			_log "Backup triggers and routines ..."
			# Dump triggers and routines :
			$mysqldump --compact --triggers --no-data --no-create-db --no-create-info $database >$backup_new/${database}/0triggers.sql
			$mysqldump --compact --routines --no-data --no-create-db --no-create-info $database >$backup_new/${database}/0routines.sql

			# Dumps :
			cmd="$mysqldump $database {} | $gzip -${compresslevel}c > $backup_new/${database}/{}.sql.gz"
			if [ $optimize == 1 ]
			then
				cmd="$mysqlcheck -s -o $database {} 2>/dev/null ; $cmd"
			fi
			cmd="$cmd; sync" # force sync after each write to reduce dirty pages
			_log "Backup tables ..."
			_log "$cmd"
			$mysql -ABN -e "SELECT TABLE_NAME FROM information_schema.tables WHERE ENGINE != 'CSV' $like AND TABLE_SCHEMA='$database' ORDER BY RAND();" | xargs -n1 -P $NUMBER_TABLES -I {} /bin/sh -c "$cmd"
			
			if [ $perdb -eq 1 ]
			then
				start_slave
				wait_slave
			fi
		fi
	fi
}
# Delete tempory file named by shell tmp_* variables and in the /tmp/ directory
# or in another directory, but the name of the file have to begin by */tmp_
#
# install in trap with: trap rm_all_tmp EXIT
rm_all_tmp()
{
	local f
	for f in $(set | perl -n -e 'if(m#^tmp_[^=]+=(.*/tmp.+)#) { print "$1\n"; }' )
	do
		rm -f $f
	done
}
trap rm_all_tmp EXIT

list_db()
{
	_log "Listing databases..."
	tmp_list_db=$BCKDIR/tmp_backupdb_listdb$$
	tmp_alldb=/tmp/dump_db_all_db_list$$
	echo "SHOW DATABASES" | $mysql -BN | grep -vE '(information|performance|common)_schema' | tee $tmp_alldb | grep -v ^nix > $tmp_list_db
	grep ^nix $tmp_alldb >> $tmp_list_db
	# vérification du nombre de DB
	if [ $(cat $tmp_alldb | wc -l) -ne $(cat $tmp_list_db | wc -l) ]
	then
		echo "error number of listed DB, all the DBs are not in the list"
		diff $tmp_alldb $tmp_list_db
		exit 1
	fi
}

stop_slave()
{
	_log "Stopping slave ..."
	c=0
	while $mysql --vertical -e 'SHOW SLAVE STATUS' | grep 'Slave_SQL_Running:' | grep -iq yes
	do
		$mysql -e 'STOP SLAVE SQL_THREAD'
		let c++
		if [ $c -gt 100 ]
		then
			echo "[WARNING] UNABLE TO STOP SLAVE"
			break
		fi
	done
	if [ $c -gt 1 ]
	then
		_log "Slave stopped after $c tentatives"
	else
		_log "Slave stopped."
	fi
}

start_slave()
{
	_log "Starting slave ..."
	$mysql --vertical -e "START SLAVE"
}

# Attend que le slave ait récupérer son délais
wait_slave()
{
	tentatives=0
	nok=1
	while [ $nok -gt 0 ]
	do
		delay=$( $mysql --vertical -e 'SHOW SLAVE STATUS' | grep Seconds_Behind_Master: | awk '{print $2}' )
		if [ "$delay" = "NULL" ]
		then
			if [ $tentatives -gt 30 ]
			then
				_log "Replication is broken"
				$mysql --vertical -e 'SHOW SLAVE STATUS'
				exit 7
			fi
			start_slave
			let tentatives++
		fi

		if [ "$delay" -gt $slave_max_delay ]
		then
			_log "Waiting slave ... (${delay}s)"
			sleep 10
		else
			_log "Slave has recovered replication [delay: ${delay}s]."
			nok=0
		fi
	done
}

backup_db()
{
	# for db in * ; do ls -1 $db/[a-z]*.gz | xargs -r -P3 -I {} sh -c "zcat {} | $mysql $db"; done
	cat <<EOF >$BCKDIR/README
Ce backup a été généré par $0 $@

## Restauration ##

Exemple de procédure :
cd $BCKDIR
# data:
for db in * ; do ls -1 \$db/[a-z]*.gz | xargs -r -P3 -I {} sh -c "zcat {} | $mysql \$db"; done
# triggers:
for db in * ; do ls -1 \$db/0*.sql | xargs -r -P3 -I {} sh -c "cat {} | $mysql \$db"; done

# Fichier a completer au fur et a mesure dans ezscripts/bin/adm_mysql_backup.sh line 164
EOF
	for d in $databases
	do
	   	_log "Backupping database $d"
	    multi_file_mysql_backup $d
	done
}

parse_opts()
{
	while [ $# -gt 0 ]
	do
		case "$1" in
		--no-slave)
			no_slave=1
			;;
		-o)
			shift
			mysql_option=( "${mysql_option[@]}" "$1" )
			;;
		--optimize)
			optimize=1
			;;
		--clean)
			clean=1
			;;
		--perdb)
			perdb=1
			;;
		-v)
			verbose=1
			;;
		--db|-d)
			shift
			db_option=( "${db_option[@]}" "$1" )
			;;
		--number_tables|--number-tables|-n)
			shift
			NUMBER_TABLES=$1
			;;
		--compresslevel)
			shift
			if [ $1 -ge 0 -a $1 -le 9 ]
			then
				compresslevel=$1
			else
				echo "Invalid level: $1"
				exit 2
			fi
			;;
		--compress)
			shift
			gzip=$1
			;;
		--help|-h)
			echo "Syntax: $0 [-o OPTIONS_MYSQL --db DATABASES]* [--clean] [--no-slave] <backup directory>"
			echo "Advanced options: "
			echo "\t--compresslevel 9 : set compress level for gzip [default 9]"
			echo "\t--compress pigz   : set compress program [default gzip]"
			echo "\t----number-tables : number tables in parallel"
			echo
			echo "How-to restore:"
			echo 'HOST=dragon-09.local ; cd backup_dir; for db in * ; do ssh $HOST "mysqladmin -f drop $db; mysqladmin create $db"; ls -1r $db/* | xargs -n1 -P4 -I{} sh -c "cat {} | ssh $HOST '"'zcat | $mysql $db'"
			exit 1
			;;
		-*)
			echo "Unknown option: $1"
			exit 1
			;;
		*)
			if [ -z "$BCKDIR" ]
			then
				BCKDIR="$1"
			else
				echo "Error with option: $1"
				exit 1
			fi
			;;
		esac
		shift
	done
}

backup_directory_manage()
{
	if [ -d $backup_new ]
	then
		_log "Moving NEW => OLD ..."
		if [ -d $backup_old ]
		then
			rm -rf $backup_old
		fi

		mv $backup_new $backup_old
		if [ $? -ne 0 ]
		then
			echo "error during the move of the directory '$backup_new' => '$backup_old'"
			exit 1
		fi
		if [ "$clean" -gt 0 ]
		then
			_log "Cleaning ..."
			rm -rf $backup_new $backup_old
			if [ $? -ne 0 ]
			then
				echo "error while cleaning the directories '$backup_new' and '$backup_old'" >&2
			fi
		fi
	fi
	mkdir -p $BCKDIR/new
}

dbs_check()
{
	for db in ${db_option[@]} 
	do
		if [ $( echo "SHOW DATABASES" | $mysql | grep -e "^${db}\$" | wc -l ) == 0 ]
		then
			echo "[WARNING] ${db} DOES NOT EXIST"
		fi
	done
}

options_manage()
{
	has_slave=0

	if [ $no_slave -eq 0 ]
	then
		has_slave=$( $mysql --vertical -e "SHOW SLAVE STATUS" | wc -l )
	fi

	if [ "${#db_option[@]}" = "0" ]
	then
		_log "Backup in $BCKDIR/" 
		list_db
		databases=$( cat $tmp_list_db )
	else
		_log "Backup of ( ${db_option[@]} ) tables in $BCKDIR/" 
		databases=${db_option[@]}
	fi
}


# 
# MAIN
#

parse_opts $*

if [ -z "$BCKDIR" ]
then
	exec $0 --help
fi

mkdir -p $BCKDIR 
backup_old=$BCKDIR/old
backup_new=$BCKDIR/new
options_manage
# CHECK IF DATABASES EXIST
dbs_check
# MOVE OLD BACKUP IN ANOTHER DIRECTORY
backup_directory_manage

if [ $has_slave != "0" ]
then
	#SLAVE TO STOP
	if [ $( $mysql --vertical -e "SHOW SLAVE STATUS" | grep 'Slave_.*_Running' | grep -i yes | wc -l ) -ne "2" ]
	then
		echo "[WARNING] SLAVE DOESN'T WORK"
	fi
	if [ $perdb -eq 0 ]
	then
		stop_slave
		$mysql --vertical -e "SHOW SLAVE STATUS" > $BCKDIR/backup-position.txt
	else
		# pour ne pas preter a confusion...
		test -e $BCKDIR/backup-position.txt && rm -f $BCKDIR/backup-position.txt
	fi
	trap "start_slave; exit 1" SIGINT SIGTERM
	backup_db
	start_slave
else
	#NO SLAVE TO STOP
	backup_db
fi

exit 0