mysql运维脚本_mysql备份脚本

备份目录:/data/mysqlbackup/

脚本目录:/data/mysqlbackup/

#vim mysql_backup

#!/bin/bash

DBUser=backup

DBPasswd="123456"

DBHost=192.168.0.250

DBName=`echo "select SCHEMA_NAME from information_schema.SCHEMATA where schema_name not in ('information_schema','test');" | mysql -h$DBHost -u$DBUser -p$DBPasswd --skip-column-names --silent`

BackupPath=/data/mysqlbackup

LogFile=/data/mysqlbackup/log/mysql_backup.log

NewFile=$(date +%Y%m%d).tgz

OldFile=$(date +%Y%m%d --date='7 days ago').tgz

pushd $BackupPath >> $LogFile

echo $OldFile

echo "-------------------------------------------" >> $LogFile

echo $(date +"%Y-%m-%d %H:%M:%S") >> $LogFile

echo "--------------------------" >> $LogFile

#Delete Old File

if [ -f $OldFile ]

then

rm -f $OldFile >> $LogFile 2>&1

echo "[$OldFile]Delete Old File Success!" >> $LogFile

else

echo "[$OldFile]No Old Backup File!" >> $LogFile

fi

#backup mysql

if [ -f $NewFile ]

then

echo "[$NewFile]The Backup File is exists,Can't Backup!" >> $LogFile

else

for item in $DBName

do

DumpFile=$item".sql"

mysqldump -h$DBHost -u$DBUser -p$DBPasswd -R --triggers --single-transaction --flush-logs --master-data --database $item > $DumpFile

done

/bin/tar czvf $NewFile *.sql >> $LogFile 2>&1

/bin/rm -f *.sql

fi

echo "-------------------------------------------" >> $LogFile

popd >> $LogFile

#crontab -e

0 1 * * * /bin/bash /data/mysqlbackup/mysql_backup

每天凌晨备份