gistfile1.txt

#!/bin/sh

# mysqlbackup
# Developed In: bash — Contributed by: Partha Dutta
# http://forge.mysql.com/tools/tool.php?id=14

# mysqlbackup - Perform mysql backup

slave_backup=0
backup_host=localhost
backup_port=3306

# Change the following 2 variables to the MySQL user/password used for backups
backup_user=backup_user
backup_password=change_password

common_opts=
mysqldump_opts=
args_mysqldump_opts=
tmp_file=/tmp/slave_status.$$
backup_file=
log_file=/var/log/mysql/backup.log
tmp_tarball=
no_compress=0

Log()
{
	echo "`date` : $*" >> $log_file
}

parse_args()
{
	for arg do
		case "$arg" in
			--host=*)
				backup_host=`echo "$arg" | sed -e 's/^[^=]*=//'`
				;;
			--port=*)
				backup_port=`echo "$arg" | sed -e 's/^[^=]*=//'`
				;;
			--user=*)
				backup_user=`echo "$arg" | sed -e 's/^[^=]*=//'`
				;;
			--password=*)
				backup_password=`echo "$arg" | sed -e 's/^[^=]*=//'`
				;;
			--args-mysqldump=*)
				if [ -z "$args_mysqldump_opts" ]; then
					args_mysqldump_opts=`echo "$arg" | sed -e 's/^[^=]*=//'`
				else
					zz=`echo "$arg" | sed -e 's/^[^=]*=//'`
					args_mysqldump_opts="$args_mysqldump_opts $zz"
				fi
				;;
			--slave)
				slave_backup=1
				;;
			--db=*)
				db=`echo "$arg" | sed -e 's/^[^=]*=//'`
				if [ -z "$backup_dbs" ]; then
					backup_dbs=$db
				else
					backup_dbs="$backup_dbs $db"
				fi
				;;
			--backup-file=*)
				backup_file=`echo "$arg" | sed -e 's/^[^=]*=//'`
				;;
			--no-compress)
				no_compress=1
				;;
			*)
				echo "Invalid argument $arg"
				exit 1
				;;
		esac
	done

	if [ -z "$backup_file" ]; then
		backup_file="backup.sql"
	fi
}

mysqldump_options()
{
	common_opts="--user=$backup_user --password=$backup_password"
	if [ "$backup_host" != "localhost" ]; then
		common_opts="$common_opts --host=$backup_host --port=$backup_port"
	fi
	if [ $slave_backup -eq 1 ]; then
		mysqldump_opts="$mysqldump_opts --skip-opt --quick --extended-insert \
		--create-options --add-drop-table --single-transaction --compress"
	else
		mysqldump_opts="$mysqldump_opts --skip-opt --quick --extended-insert \
		--create-options --add-drop-table --single-transaction --master-data=2 --compress"
	fi
	if [ -z "$backup_dbs" ]; then
		mysqldump_opts="$mysqldump_opts --all-databases"
	else
		mysqldump_opts="$mysqldump_opts --databases"
	fi
	if [ ! -z "args_mysqldump_opts" ]; then
		mysqldump_opts="$mysqldump_opts $args_mysqldump_opts"
	fi
	mysqldump_opts="$common_opts $mysqldump_opts"
}

is_this_a_slave()
{
	slave_status=`mysql $common_opts --batch --skip-column-names mysql -e "show slave status"`
	if [ -z "$slave_status" ]; then
		return 1
	else
		return 0
	fi
}

clean_start()
{
	# Synchronize with the start_backup function
	if [ $slave_backup ]; then
		Log "Pausing slave & flushing logs..."
		mysql $common_opts mysql -e "select GET_LOCK('BACKUP1', 10);stop slave;system sleep 10;flush tables;flush logs;show slave status;select RELEASE_LOCK('BACKUP1');select GET_LOCK('BACKUP2', 10);system sleep 10;start slave;select RELEASE_LOCK('BACKUP2')" > $tmp_file 2>&1
		Log "Slave re-started..."
	fi
}

start_backup()
{
	# Synchronize with the clean_start function
	if [ $slave_backup -eq 1 ]; then
		clean_start &
		sleep 1
		Log "Synchronizing with STOP SLAVE command"
		# Are there any temporary files from replicated LOAD DATA INFILE?
		mysql_tmpdir=`mysql $common_opts --skip-column-names --batch mysql -e "show variables like 'tmpdir'" | cut -f2`
		/bin/ls -1 $mysql_tmpdir/SQL_LOAD* > /dev/null 2>&1
		if [ $? -eq 0 ]; then
			Log "Saving pending LOAD DATA INFILE temp files"
			ls -1 $mysql_tmpdir/SQL_LOAD-* > /tmp/tar.list.$$
			tar cvf /tmp/SQL_LOAD.$$.tar -T /tmp/tar.list.$$ > /dev/null 2>&1
			tmp_tarball=/tmp/SQL_LOAD.$$.tar
			rm -f /tmp/tar.list.$$
			Log "Pending LOAD DATA INFILE temp files in /tmp/SQL_LOAD.$$.tar"
		fi
		mysql $common_opts --batch --skip-column-names mysql -e "select GET_LOCK('BACKUP2', 20);select GET_LOCK('BACKUP1', 20);select RELEASE_LOCK('BACKUP1');select RELEASE_LOCK('BACKUP2');" > /dev/null 2>&1
	fi

	Log "Starting mysqldump..."
	if [ $slave_backup -eq 1 ]; then
		mysqldump $mysqldump_opts $backup_dbs > $backup_file
	else
		mysqldump $mysqldump_opts --flush-logs $backup_dbs > $backup_file
	fi
}

################################################################################

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH

parse_args $*
mysqldump_options
Log "[INIT] mysqlbackup started on host `hostname` for db(s) $backup_dbs"
if [ $slave_backup -eq 1 ]; then
	is_this_a_slave || { Log "[ERR] --slave specified, but server is not a slave" ; exit 1; }
fi

start_backup
if [ $? -ne 0 ]; then
	Log "[ERR] mysqldump command was not successful."
	rm -f $tmp_file
	exit 1
else
	# Wait for any background jobs to finish
	wait
	# Add a final CHANGE MASTER command to the backup file (For slaves)
	if [ $slave_backup -eq 1 ]; then
		awk -F'\t' 'BEGIN { a = 0 }; /^Slave_IO/ { a = 1; next }; { if (a==1) { printf("-- CHANGE MASTER TO MASTER_HOST='%s', MASTER_PORT=%d, MASTER_LOG_FILE='%s', MASTER_LOG_POS=%d;\n", $2,$4,$10,$22); a=0; last } }' $tmp_file >> /tmp/chg_master.$$
		Log `cat /tmp/chg_master.$$`
		cat /tmp/chg_master.$$ >> $backup_file
	fi

	Log "mysqldump finished successfully."
	if [ $no_compress -ne 1 ]; then
		Log "Compressing backup file..."
		gzip $backup_file
		Log "Finished compressing backup file"
	fi
	if [ ! -z $tmp_tarball ]; then
		cp_dir=`dirname $backup_file`
		Log "Moving temporary LOAD DATA INFILE tarball to $cp_dir"
		mv $tmp_tarball $cp_dir
	fi
	rm -f $tmp_file /tmp/chg_master.$$
fi