【代碼】linux下mysql每日自動備份並刪除7天前備份的代碼(原創)

此方法不是傳統的導出數據,而是直接從mysql的數據目錄對數據庫進行壓縮、備份。同時還會將所備份數據傳到你的遠端FTP備份服務器上。恢復時只需將要恢復的數據解壓回mysql數據目錄並重啓mysql服務即可。因此你必須要有服務器的root權限。

首先,進入/usr/sbin目錄下,用root權限新建一個文件backupmysql,代碼如下:

(以下代碼採用GNU General Public License v3.0許可授權,引用時請勿移除注釋)

#!/bin/bash

 

#   Shujen's Mysql Database Backup On Linux

#   Copyright (C) 2011 Shujen Chang

#

#   This program is free software: you can redistribute it and/or modify

#   it under the terms of the GNU General Public License as published by

#   the Free Software Foundation, either version 3 of the License, or

#   (at your option) any later version.

#

#   This program is distributed in the hope that it will be useful,

#   but WITHOUT ANY WARRANTY; without even the implied warranty of

#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

#   GNU General Public License for more details.

#

#   You should have received a copy of the GNU General Public License

#   along with this program.  If not, see <http://www.gnu.org/licenses/>.

 

#   This program will backup your Mysql database and remove the old backup

#   7 days ago. And upload them onto your remote FTP server. It will stop 

#   your mysql and apache service, and restart them when it will have been 

#   completed.

 

filename=`date +%Y%m%d`

oldfilename=`date -d "7 day ago" +%Y%m%d`

mysqldbpath="/var/lib/mysql" #Change it to your mysql datas path

mysqldbname=("aa" "bb" "cc") #Change it to your every mysql databases' name you want to backup

dbbackuppath="/home/databasebackup" #Change it to your local mysql backup path

backupftpserver="192.168.0.1" #Change it to the IP address of your remote FTP backup server

ftpusername="user" #Change it to the username of your remote FTP backup server

ftppassword="password" #Change it to the password of your remote FTP backup server

remotedbbackuppath="/databasebackup" #Change it to the remote mysql backup path of your remote FTP backup server

/etc/init.d/apache stop #To stop your apache service, it may be changed according to your system and the installation of apache

/etc/init.d/mysql stop #To stop your mysql service, it may be changed according to your system and the installation of mysql

cd $dbbackuppath

mkdir $filename

cd $mysqldbpath

for i in ${mysqldbname[*]}

do

tar zcvf $dbbackuppath/$filename/$i.tar.gz $i

done

/etc/init.d/mysql start #To start your mysql service, it may be changed according to your system and the installation of mysql

/etc/init.d/apache start #To start your apache service, it may be changed according to your system and the installation of apache

cd $dbbackuppath

rm -R $oldfilename

cd $dbbackuppath/$filename

ftp -i -in <<!

open $backupftpserver

user $ftpusername $ftppassword

cd $remotedbbackuppath

mkdir $filename

cd $filename

mput *.tar.gz

cd $remotedbbackuppath/$oldfilename

mdelete *

cd $remotedbbackuppath

rmdir $oldfilename

bye

!

 

然後根據藍色的注釋修改下前面的變量或語句。為了照顧某些英文不好的,我再用中文說一下:把變量mysqldbpath的值修改為你的mysql數據目錄;把變量mysqldbname的值修改為你每個需要備份的數據庫名;把變量dbbackuppath的值修改為你本地備份mysql數據庫的目錄;把變量backupftpserver的值修改為你想要上傳備份的遠端FTP服務器IP;把變量ftpusername的值修改為遠端FTP服務器用戶名;把變量ftppassword的值修改為遠端FTP服務器密碼;把變量remotedbbackuppath的值修改為遠端FTP服務器的備份路徑(注意格式要按照原有樣例的格式)然後根據你服務器的系統和mysql、apache的安裝情況修改下「/etc/init.d/apache stop」、「/etc/init.d/mysql stop」、「/etc/init.d/mysql start」、「/etc/init.d/apache start」這四個語句,就是改為你服務器上關閉、開啓mysql、apache的命令即可,如果你服務器上關閉、開啓mysql、apache的命令與之相同則不用修改。

然後保存該文件,並賦予755權限,所屬用戶和用戶組均設為root。

以後只要使用root權限執行/usr/sbin/backupmysql就會實現mysql數據庫的備份並上傳到遠端FTP服務器上。

如果需要每天自動備份的話,只需將其加入到cron裡面即可~~

Bookmark the permalink.

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *