Shell脚本备份所有MySQL数据库,单个文件中的每个表,并上传到远程FTP
该脚本将为每个数据库(每个表中的一个文件)创建每个表的备份,将其压缩并将其上传到远程ftp。
首先使用select和lock表特权创建一个mysql用户(或使用root)。
然后每个小时在你的crontab中使用这个脚本:
#!/bin/sh # System + MySQL backup script # Copyright (c) 2008 Marchost # This script is licensed under GNU GPL version 2.0 or above # --------------------------------------------------------------------- ######################### ######TO BE MODIFIED##### ### System Setup ### BACKUP=YOUR_LOCAL_BACKUP_DIR ### MySQL Setup ### MUSER="MYSQL_USER" MPASS="MYSQL_USER_PASSWORD" MHOST="localhost" ### FTP server Setup ### FTPD="YOUR_FTP_BACKUP_DIR" FTPU="YOUR_FTP_USER" FTPP="YOUR_FTP_USER_PASSWORD" FTPS="YOUR_FTP_SERVER_ADDRESS" ######DO NOT MAKE MODIFICATION BELOW##### ######################################### ### Binaries ### TAR="$(which tar)" GZIP="$(which gzip)" FTP="$(which ftp)" MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" ### Today + hour in 24h format ### NOW=$(date +"%d%H") ### Create hourly dir ### mkdir $BACKUP/$NOW ### Get all databases name ### DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')" for db in $DBS do ### Create dir for each databases, backup tables in individual files ### mkdir $BACKUP/$NOW/$db for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`; do FILE=$BACKUP/$NOW/$db/$i.sql.gz echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE done done ### Compress all tables in one nice file to upload ### ARCHIVE=$BACKUP/$NOW.tar.gz ARCHIVED=$BACKUP/$NOW $TAR -cvf $ARCHIVE $ARCHIVED ### Dump backup using FTP ### cd $BACKUP DUMPFILE=$NOW.tar.gz $FTP -n $FTPS <<END_SCRIPT quote USER $FTPU quote PASS $FTPP cd $FTPD mput $DUMPFILE quit END_SCRIPT ### Delete the backup dir and keep archive ### rm -rf $ARCHIVED