此方法不是傳統的導出數據,而是直接從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裡面即可~~