windows/linux自動備份mysql並刪除7天前的備份

自動備份mysql資料庫指令碼程式

備份會不斷佔用磁碟大小,需要刪除早期的備份

一.windows系統

1.D盤根目錄建立bkupmysql.bat檔案,複製以下內容

@echo off
cls
set dbuser=root
set dbpass=passwd
set DaysAgo=7
set mysqlpath=d:\mysqlbak
set mysql=D:\SOFT_PHP_PACKAGE\mysql\bin\mysql.exe
set mysqldump=D:\SOFT_PHP_PACKAGE\mysql\bin\mysqldump.exe
set dt=%date:~0,4%%date:~5,2%%date:~8,2%0%time:~1,1%%time:~3,2%%time:~6,2%
set logfile=d:\bkinfo.log
set logfail=d:\bkfail.log
if not exist "%mysqlpath%"  md "%mysqlpath%"
for /f "delims=" %%i in ('%mysql% -u%dbuser% -p%dbpass% -e"show databases"^|findstr -v "^Database$ ^test$ ^information_schema$ ^performance_schema$ ^mysql$"') do (
%mysqldump% -f -u%dbuser% -p%dbpass% %%i >%mysqlpath%\%%i-%dt%.sql 2>>%logfail% 
If  %errorlevel%==0 (
forfiles /p %mysqlpath% /s /m %%i-*.* /d -%DaysAgo% /c "cmd /c  del /f /q /a @path"
echo %date%   sucess    %%i >>%logfile%
) else (
echo %date%   fail      %%i >>%logfile%
)
)

 

2.新增任務計劃每天4點執行

schtasks /create /tn "bkupmysql" /ru system /tr d:\bkupmysql.bat /sc daily /st 04:00

 

二.linux系統

1.建立bkupmysql.sh,複製以下內容

#!/bin/bash
dbuser='root'
dbpass='passwd'
DaysAgo=7
mysqlpath='/home/backup/mysql'
mysql='/www/wdlinux/mysql/bin/mysql'
mysqldump='/www/wdlinux/mysql/bin/mysqldump'
exclude_db='^Database$\|^mysql$\|^information_schema$\|^performance_schema$\|^test$'
logfile='/root/bkinfo.log'
#logfail='/root/bkfail.log'
now=`date "+%Y-%m-%d %H:%M:%S"`
echo $now
test -e $mysqlpath||mkdir -p $mysqlpath
db_array=`$mysql -u$dbuser -p$dbpass -e'show databases;'|grep -v $exclude_db`
if [ $? -eq 0 ];then
   for dbname in ${db_array[*]}
   do
      $mysqldump -f -u$dbuser -p$dbpass $dbname >$mysqlpath/$dbname-$(date +%Y%m%d%H%M%S).sql
      if [ $? -eq 0 ];then
          find $mysqlpath -type f -mtime +$[DaysAgo-1] -name "$dbname-*" -exec rm -f {} \;
          echo $now 'sucess'    $dbname >> $logfile
      else
          echo $now 'fail  '    $dbname >> $logfile
      fi
   done
else
   echo $now 'All fail' >> $logfile
fi

2.新增任務計劃每天4點執行

crontab -e
0 4 * * * /bin/sh /root/bkupmysql.sh >>/root/bkfail.log 2>&1