MYSQL代码 定期备份Mysql数据库
程序员文章站
2023-12-09 23:59:27
相信很多个人网站的站长，或者是独立blog的博主，都有着备份数据库这样一个需求。当然wordpress这样插件非常丰富的blog系统有好多能帮你完成这项任务的插件。如果您需...
相信很多个人网站的站长，或者是独立blog的博主，都有着备份数据库这样一个需求。当然wordpress这样插件非常丰富的blog系统有好多能帮你完成这项任务的插件。如果您需要自己亲手来备份数据库的话，我贴一个mysql自动备份脚本供大家参考，在国外网站上看到的，还是不错，实现了定期备份mysql数据库，并且可以选在在每周的一天做指定目录下文件的全面备份，备份文件自动上传到你指定的ftp上，保证了备份的可靠性。如果只是把数据库备份在和数据库一起，我想意义也不是特别大了。
#!/bin/sh
# system + mysql backup script
# full backup day - sun (rest of the day do incremental backup)
# copyright (c) 2005-2006 nixcraft <http://www.cyberciti.biz/fb/>
# this script is licensed under gnu gpl version 2.0 or above
# automatically generated by
# http://bash.cyberciti.biz/backup/wizard-ftp-script.php
# ---------------------------------------------------------------------
### system setup ###
##备份本件本地目录##
dirs="/home/xxxx"
backup=/tmp/backup.$$
##日期格式 可以自己调节##
now=$(date +"%y-%m-%d")
incfile="/home/xxxx"
day=$(date +"%u")
##星期天完全备份 可以自己调节##
fullbackup="7"
### mysql setup ###
muser="chaos"
mpass="password"
mhost="host"
mysql="$(which mysql)"
mysqldump="$(which mysqldump)"
gzip="$(which gzip)"
### ftp server setup ###
##增量备份路径##
ftpd="/backup/xxxx/incremental"
ftpu="chaos"
ftpp="password"
ftps="xxx.xxx.com"
### other stuff ###
emailid="chaos@diablo.net"
### start backup for file system ###
[ ! -d $backup ] && mkdir -p $backup || :
### see if we want to make a full backup ###
if [ "$day" == "$fullbackup" ]; then
##完全备份路径##
ftpd="/backup/xxxx/full"
file="fs-full-$now.tar.gz"
tar -zcvf $backup/$file $dirs
else
i=$(date +"%hh%mm%ss")
file="fs-i-$now-$i.tar.gz"
tar -g $incfile -zcvf $backup/$file $dirs
fi
### start mysql backup ###
# get all databases name
##备份所有数据库 可自行调整 或备份指定数据库##
dbs="$($mysql -u $muser -h $mhost -p$mpass -bse 'show databases')"
for db in $dbs
do
file=$backup/mysql-$db.$now-$(date +"%t").gz
$mysqldump -u $muser -h $mhost -p$mpass $db | $gzip -9 > $file
done
### dump backup using ftp ###
#start ftp backup using lftp
##ubuntu上可以使用ncftp 自行调节##
#ncftp -u"$ftpu" -p"$ftpp" $ftps<<eof
lftp -u $ftpu,$ftpp $ftps<<eof
mkdir $ftpd
mkdir $ftpd/$now
cd $ftpd/$now
lcd $backup
mput *
quit
eof
### find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $backup/*
else
t=/tmp/backup.fail
echo "date: $(date)">$t
echo "hostname: $(hostname)" >>$t
echo "backup failed" >>$t
mail -s "backup failed" "$emailid" <$t
rm -f $t
fi
改一下里面的一些路径和账号密码等信息就可以了，希望对大家有所用处。
复制代码 代码如下:
#!/bin/sh
# system + mysql backup script
# full backup day - sun (rest of the day do incremental backup)
# copyright (c) 2005-2006 nixcraft <http://www.cyberciti.biz/fb/>
# this script is licensed under gnu gpl version 2.0 or above
# automatically generated by
# http://bash.cyberciti.biz/backup/wizard-ftp-script.php
# ---------------------------------------------------------------------
### system setup ###
##备份本件本地目录##
dirs="/home/xxxx"
backup=/tmp/backup.$$
##日期格式 可以自己调节##
now=$(date +"%y-%m-%d")
incfile="/home/xxxx"
day=$(date +"%u")
##星期天完全备份 可以自己调节##
fullbackup="7"
### mysql setup ###
muser="chaos"
mpass="password"
mhost="host"
mysql="$(which mysql)"
mysqldump="$(which mysqldump)"
gzip="$(which gzip)"
### ftp server setup ###
##增量备份路径##
ftpd="/backup/xxxx/incremental"
ftpu="chaos"
ftpp="password"
ftps="xxx.xxx.com"
### other stuff ###
emailid="chaos@diablo.net"
### start backup for file system ###
[ ! -d $backup ] && mkdir -p $backup || :
### see if we want to make a full backup ###
if [ "$day" == "$fullbackup" ]; then
##完全备份路径##
ftpd="/backup/xxxx/full"
file="fs-full-$now.tar.gz"
tar -zcvf $backup/$file $dirs
else
i=$(date +"%hh%mm%ss")
file="fs-i-$now-$i.tar.gz"
tar -g $incfile -zcvf $backup/$file $dirs
fi
### start mysql backup ###
# get all databases name
##备份所有数据库 可自行调整 或备份指定数据库##
dbs="$($mysql -u $muser -h $mhost -p$mpass -bse 'show databases')"
for db in $dbs
do
file=$backup/mysql-$db.$now-$(date +"%t").gz
$mysqldump -u $muser -h $mhost -p$mpass $db | $gzip -9 > $file
done
### dump backup using ftp ###
#start ftp backup using lftp
##ubuntu上可以使用ncftp 自行调节##
#ncftp -u"$ftpu" -p"$ftpp" $ftps<<eof
lftp -u $ftpu,$ftpp $ftps<<eof
mkdir $ftpd
mkdir $ftpd/$now
cd $ftpd/$now
lcd $backup
mput *
quit
eof
### find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $backup/*
else
t=/tmp/backup.fail
echo "date: $(date)">$t
echo "hostname: $(hostname)" >>$t
echo "backup failed" >>$t
mail -s "backup failed" "$emailid" <$t
rm -f $t
fi
改一下里面的一些路径和账号密码等信息就可以了，希望对大家有所用处。