今天完成了一个数据库备份的脚本,主要功能如下,希望大家给一些改进的建议
-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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
#!/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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.
- 159.
- 160.
- 161.
- 162.
- 163.
- 164.
- 165.
- 166.
- 167.
- 168.
- 169.
- 170.
- 171.
- 172.
- 173.
- 174.
- 175.
- 176.
- 177.
- 178.
- 179.
- 180.
- 181.
- 182.
- 183.
- 184.
- 185.
- 186.
- 187.
- 188.
- 189.
- 190.
- 191.
- 192.
- 193.
- 194.
- 195.
- 196.
- 197.
- 198.
- 199.
- 200.
- 201.
- 202.
- 203.
- 204.
- 205.
- 206.
- 207.
- 208.
- 209.
- 210.
- 211.
- 212.
- 213.
- 214.
- 215.
- 216.
- 217.
- 218.
- 219.
- 220.
- 221.
- 222.
- 223.
- 224.
- 225.
- 226.
- 227.
- 228.
- 229.
- 230.
- 231.
- 232.
- 233.
- 234.
- 235.
- 236.
- 237.
- 238.
- 239.
- 240.
- 241.
- 242.
- 243.
- 244.
- 245.
- 246.
- 247.
- 248.
- 249.
- 250.
- 251.
- 252.
- 253.
- 254.
- 255.
- 256.
- 257.
- 258.
- 259.
- 260.
- 261.
原文链接：http://blog.chinaunix.net/space.php?uid=16844903&do=blog&id=2010853
【编辑推荐】
