mysql 資料庫自動備份shell script與還原

* 自動備份資料庫 shell script

#/bin/bash
# backup databas to ftp site
# you need wput and mysql client/admin tools
# use yum or apt-get or get manually
# wput http://wput.sourceforge.net/
# write by mtchang.tw@gmail.com

# your ftp address and folder address
ftp_addr='240.112.69.29/sql'
# ftp account and password
account='cccccc'
acc_pw='cc'

# to backup mysql dbase ,account and password
db='mtchang'
dbuser='mtchang'
dbpassword='cccccccccc'

# get year,month,day,hour
da=$(date +%Y%m%d%H%k%M)
h=$(hostname)
# generate filename like linue.jangmt.com_200910101010_mtchang.sql
filename="$h"_"$da"_"$db".sql
# use mysqldump dump $db data to sql file
echo "mysqldump $db -u$dbuser -p$dbpassword --opt > $filename" | sh
# use bzip2 compress sql file
echo "bzip2 $filename" | sh
# upload file to ftp server
echo "wput ftp://$account:$acc_pw@$ftp_addr/$filename.bz2" |sh
# check success or fail ?
echo "upload $filename.bz2 to ftp is $?"
# erase file
echo "rm -rf $filename.bz2" |sh

* 先執行測試

mtchang@www:~/backup$ ./sqlbackup.sh
--10:50:59-- `www.cm.nsysu.edu.tw_20091022101050_mtchang.sql.bz2'
=> ftp://cccmbk:xxxxx@240.112.69.29:21/sql/www.cm.nsysu.edu.tw_20091022101050_mtchang.sql.bz2
Connecting to 240.112.69.29:21... connected!
Logging in as cccmbk ... Logged in!
Length: 6,093,043

10:51:00 (jangmt.com_20091022101050_mtchang.sql.bz2) - `84.88M/s' [6093043]

FINISHED --10:51:00--
Transfered 6,093,043 bytes in 1 file at 28.74M/s
upload www.cm.nsysu.edu.tw_20091022101050_mtchang.sql.bz2 to ftp is 0

* 寫入定時排成,每天早上 04:00 定時備份

# crontab -e
0 4 * * * /home/mtchang/backup/sqlbackup.sh

* 參考
# http://www.study-area.org/tips/mysql_backup.htm
# man mysqldump
# man mysql

還原待續.....