以下为mysql数据备份数据库脚本
1.备份数据库脚本并且通过ftp上传到windowsserver服务器
#!/bin/bash
#by zuoyuezong
#2014-1-09
DATE=`date "+%Y%m%d%H"`
IP="localhost"
DB_USER="root"
DB_PASS="mysqlpassword"
BIN_DIR="/data/local/mysql/bin"
BAK_DIR="/data/dbbk/local"
BAK_PATH=$BAK_DIR/$DATE
REMOTE_PATH_1=/dbbackup/sc_25
REMOTE_IP=192.168.1.100 #ftp服务器ip
OUTDATE_FILE=`date -d '1 days ago' +%Y%m%d%H` #一天前日期
FILE_MY_CNF=/etc/my.cnf
FILE_MYSQLD=/etc/rc.d/init.d/mysqld
ftp -n<<! #ftp连接远程删除一天前的备份的文件
open $REMOTE_IP
user crmtest 1qaz2wsx,,
prompt off
cd $REMOTE_PATH_1
mdelete $OUTDATE_FILE/
#mdelete *
rmdir $OUTDATE_FILE
close
bye
!
#echo "come here"
if [ ! -d $BAK_PATH ] ; then
mkdir -p $BAK_PATH
fi
cp -a $FILE_MY_CNF $BAK_PATH
cp -a $FILE_MYSQLD $BAK_PATH
variables="`$BIN_DIR/mysql -u$DB_USER -p$DB_PASS -h$IP -e "show create database mysql;" |awk '{print $9}' |sed -n '2'p`" #过滤出字符集字段我的为utf8
if $BIN_DIR/mysqldump --opt --single-transaction --default-character-set=$variables -h$IP -u$DB_USER -p$DB_PASS mysql user | gzip > $BAK_PATH/mysql_user-$DATE.sql.gz ; then
echo -e "\033[32m $DATE mysql.user backup success...\033[0m" >>$BAK_DIR/backup-log.txt
else
echo -e "\033[31;5m $DATE mysql.user backup false...\033[0m" >>$BAK_DIR/backup-log.txt
fi
### at "2014-1-09"以上为备份mysql.user表
$BIN_DIR/mysql -u$DB_USER -p$DB_PASS -e "show databases;" > $BAK_DIR/dbname.txt
for db_name in `cat $BAK_DIR/dbname.txt` #将所有库写到文件
do
if [ "$db_name" == "mysql" ] || [ "$db_name" == "information_schema" ] || [ "$db_name" == "performance_schema" ] || [ "$db_name" == "test" ] || [ "$db_name" == "Database" ];then
continue #以上所涉及的库不做备份
else
# echo -e "\033[32m$variables...\033[0m" >> $BAK_DIR/backup-sucess.txt
variables="`$BIN_DIR/mysql -u$DB_USER -p$DB_PASS -h$IP -e "show create database $db_name;" |awk '{print $9}' |sed -n '2'p`"
# variables="utf8"
if $BIN_DIR/mysqldump --opt --single-transaction --default-character-set=$variables -h$IP -u$DB_USER -p$DB_PASS -R --hex-blob --flush-logs --databases $db_name | gzip > $BAK_PATH/$db_name-$DATE.sql.gz ; then
echo -e "\033[32m $DATE $db_name backup success...\033[0m" >>$BAK_DIR/backup-log.txt
else
echo -e "\033[31;5m $DATE $db_name backup false...\033[0m" >>$BAK_DIR/backup-log.txt #以颜色的方式表示备份的成功与否记录到文件以供后期查看
fi
fi
done
#####
ftp -n<<!
open $REMOTE_IP
user crmtest 1qaz2wsx,,
cd $REMOTE_PATH_1
mkdir $DATE
cd $DATE
lcd $BAK_PATH
prompt
mput *
cd ..
close
bye
!
#将备份文件上传到ftp服务器
find $BAK_DIR -name "*" -mtime +7 |xargs rm -rf
find $BAK_DIR -type d -empty |xargs rm -rf
#删除7天前的文件和空目录
exit 0
本文出自 “左越宗” 博客，请务必保留此出处http://zuoyuezong.blog.51cto.com/6444039/1350006