MySql 自动化备份脚本代码,MYSQL代码库,德仔网

代码语言
代码分类
【MYSQL】 MySql 自动化备份脚本
作者:Dezai.CN / 发布于2013/5/28/ 1746

	-a: backup all database #全库备份
	-e: backup each database#分库备份
	-d: backup single/multi database#备份指定的一个库或者多个库
	-t: backup single/multi table of single database#备份一个库下面的一个表或者多个表
	-b: backup binlog#备份binlog日志,备份过程,每次记录最后的二进制文件号,将之前范围内的binlog打包(bz2格式),放在$DIR_BACKUP目录下
	-r: recover all database(!require password!)#恢复全库,为保安全,恢复时需要输入密码
	-o: recover single database/talbe,you should be designation database name(!require password!)#恢复单库或单表
	-p: create connect mysql password#创建连接mysql的密码文件,存放位置$DIR_MySQL/etc目录下,权限是600
	-s: configuration rsyncd#创建rsyncd服务(需要时,可修改参数创建,因backup机上有rsyncd服务,故不需要在每台DB server上创建rsyncd服务)
	 If you want ceate a rsyncd, you should enter 'bakrec_mysql.sh -s cet'
	 If you want restart rsyncd, you should enter 'bakrec_mysql.sh -s rst'
	-c: sync to backup center#同步到backup1
	#!/bin/bash
	# email: lianjie.ning@qunar.com
	# last change time: 2011-08-03
	set -e
	set -u
	TIME=`date +%Y%m%d%H%M%S`
	TIME_7=`date -d '7 days ago' +%Y%m%d%H%M%S`
	TIME_YM=`date +%Y%m`
	DIR_MYSQL='/usr/local/mysql'
	DIR_BACKUP="/tmp/backup"
	DIR_DATA="$DIR_MYSQL/data"
	DIR_PASSWD="$DIR_MYSQL/etc"
	FILE_PASSWD="$DIR_PASSWD/passwordfile"
	BINLOG_NAME='mysql-bin'
	CMD_MYSQLBINLOG="$DIR_MYSQL/bin/mysqlbinlog"
	CMD_MYSQLDUMP="$DIR_MYSQL/bin/mysqldump"
	CMD_MYSQL="$DIR_MYSQL/bin/mysql"
	LIST_EXCLUDE_DB='(test|information_schema|performance_schema)'
	if [ ! -d $DIR_BACKUP/$TIME_YM ]; then
	mkdir -p $DIR_BACKUP/$TIME_YM
	fi
	cd $DIR_BACKUP/$TIME_YM
	function result_status()
	{
	if [ $? -eq 0 ]; then
	echo "[`date +%Y%m%d%H%M%S`] SUCCESS! "|tee -a log.$TIME_YM
	else
	echo "[`date +%Y%m%d%H%M%S`] ERROR! "|mail -s "backup error $HOSTNAME" ning_lianjie@163.com|tee -a log.$TIME_YM
	fi
	}
	function usage_error()
	{
	echo "Usage: $0 RUN ERROR"
	echo "
	-a: backup all database
	-e: backup each database
	-d: backup single/multi database
	-t: backup single/multi table of single database
	-b: backup binlog
	-r: recover all database(!require password!)
	-o: recover single database/talbe,you should be designation database name(!require password!)
	-p: create connect mysql password
	-s: configuration rsyncd
	If you want ceate a rsyncd, you should enter '$0 -s cet'
	If you want restart rsyncd, you should enter '$0 -s rst'
	-c: sync to backup center
	"
	exit 0
	}
	function read_pwd()
	{
	read USER PASSWD < $FILE_PASSWD
	}
	function backup()
	{
	read_pwd
	LOGBIN_STATUS=`$CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW VARIABLES LIKE 'log_bin'" | gawk '{print $2}'`
	if [ $LOGBIN_STATUS = "ON" ]; then
	MASTER='--master-data=2'
	else
	MASTER=' '
	fi
	}
	function backup_all()
	{
	backup
	$CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R -A --add-drop-database $MASTER |gzip >$HOSTNAME.all.$TIME.sql.gz
	}
	function backup_each()
	{
	backup
	for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB)
	do
	$CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER $db --databases |gzip >$HOSTNAME.$db.$TIME.sql.gz
	done
	# delete 7 days ago
	for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB)
	do
	if [ ! -f $HOSTNAME.$db.$TIME_7.sql.gz ]; then
	echo
	else
	rm $HOSTNAME.$db.$TIME_7.sql.gz -f
	fi
	done
	}
	function backup_db()
	{
	shift
	backup
	$CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER --databases $@| gzip>$HOSTNAME.$OPTARG.$TIME.sql.gz
	}
	function backup_dt()
	{
	shift
	if [ $# -ge 2 ]; then
	backup
	$CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER $@| gzip>$HOSTNAME.$OPTARG.$TIME.sql.gz
	else
	usage_error
	fi
	}
	function backup_binlog()
	{
	if [ -s $DIR_BACKUP/mysql-bin.queue ]; then
	read POS < $DIR_BACKUP/mysql-bin.queue
	cd $DIR_DATA
	tar -jcvf $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME.bz2 `gawk -F'/' '{print $2}' $BINLOG_NAME.index |sed -n "/$POS/,//p"`
	cd -
	if [ -f $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME_7.bz2 ]; then
	rm $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME_7.bz2 -f
	fi
	fi
	# write last pos
	gawk -F'/' '{print $2}' $DIR_DATA/$BINLOG_NAME.index | tail -n 1 >$DIR_BACKUP/mysql-bin.queue
	}
	function recover_all()
	{
	read_pwd
	shift
	$CMD_MYSQL -u$USER -p -e "source $@"
	}
	function recover_dt()
	{
	read_pwd
	shift
	if [ $# -eq 2 ]; then
	$CMD_MYSQL -u$USER -p -D $1 -e "source $2"
	else
	usage_error
	fi
	}
	function passwd_create()
	{
	if [ ! -e "$DIR_PASSWD" ]; then
	mkdir -p $DIR_PASSWD
	fi
	echo -n "Please enter MySQL(user=root)'s password:"
	read -s MYSQL_FASSWD
	cat >$FILE_PASSWD <<+
	root $MYSQL_FASSWD
	+
	chmod 600 $FILE_PASSWD
	}
	function rsyncd()
	{
	shift
	if [ $# -eq 0 ]; then
	usage_error
	else
	DIR_RSYNCD='/usr/local/rsync'
	FILE_RSYNCD_PASSWORD="$DIR_RSYNCD/rsyncd.password"
	case "$1" in
	'cet')
	if [ ! -d $DIR_RSYNCD ]; then
	mkdir -p $DIR_RSYNCD
	fi
	if [ ! -e "$DIR_RSYNCD/rsyncd.conf" ]; then
	touch $DIR_RSYNCD/rsyncd.conf
	fi
	mv $DIR_RSYNCD/rsyncd.conf $DIR_RSYNCD/rsyncd.conf.$TIME.bak
	cat >$DIR_RSYNCD/rsyncd.conf <<+
	uid = root
	gid = root
	use chroot = no
	max connections = 5
	lock file = $DIR_RSYNCD/rsyncd.lock
	log file = $DIR_RSYNCD/rsyncd.log
	pid file = $DIR_RSYNCD/rsyncd.pid
	hosts allow = 192.168.250.251
	hosts deny = *
	ignore errors
	read only = yes
	list = no
	auth users = backupdbuser
	secrets file = $DIR_RSYNCD/rsyncd.password
	[BINLOG]
	path = $DIR_DATA
	include = $BINLOG_NAME.*
	exclude = *
	[DUMPDB]
	path = $DIR_BACKUP
	+
	cat >$FILE_RSYNCD_PASSWORD <<+
	username:password
	+
	chmod 600 $FILE_RSYNCD_PASSWORD
	exit 0
	;;
	'rst')
	if [ -s "$DIR_RSYNCD/rsyncd.pid" ]; then
	rsyncd_pid=`cat "$DIR_RSYNCD/rsyncd.pid"`
	if (kill -0 $rsyncd_pid 2>/dev/null); then
	echo "Shutting down rsyncd"
	kill $rsyncd_pid
	else
	echo "rsyncd #$rsyncd_pid is not running!"
	rm "$DIR_RSYNCD/rsyncd.pid"
	fi
	fi
	sleep 2
	rsync --daemon --config=$DIR_RSYNCD/rsyncd.conf --port=873
	echo "rsync --daemon --config=$DIR_RSYNCD/rsyncd.conf --port=873"
	echo "netstat -tunlp | grep rsync"
	netstat -tunlp | grep rsync
	;;
	*)
	usage_error
	;;
	esac
	fi
	}
	#main
	if [ $# -eq 0 ]; then
	usage_error
	else
	while getopts :aed:t:r:o:bpsc varname
	do
	case $varname in
	a)
	backup_all
	;;
	e)
	backup_each
	;;
	d)
	backup_db $@
	;;
	t)
	backup_dt $@
	;;
	b)
	backup_binlog
	;;
	r)
	recover_all $@
	;;
	o)
	recover_dt $@
	;;
	p)
	passwd_create
	;;
	s)
	rsyncd $@
	;;
	c)
	# rsync -czrptgoD --password-file=/tmp/.passwd $HOSTNAME.*.$TIME.sql.gz backupdbuser@192.168.250.251::DUMPDB/$HOSTNAME/$TIME_YM
	rsync -czrpt --password-file=/tmp/.passwd $DIR_BACKUP/$TIME_YM backupdbuser@192.168.250.251::DUMPDB/$HOSTNAME
	result_status
	;;
	:)
	echo "$varname: 缺少参数"
	usage_error
	;;
	\?)
	echo "$varname: 非法选项"
	usage_error
	;;
	esac
	done
	fi
试试其它关键字
　自动化备份　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)