MySQL简易备份方法(3)

real 0m33.898s
user 0m24.740s
sys 0m4.590s
[root@demo tmp]# time backup

real 0m32.808s
user 0m23.710s
sys 0m4.750s

测试机的硬体配备是Intel Celeron 600 + 256mb SDRAM, 作业系统是CLE Linux 1.0.

因为 Dump 备份的速度很快,大部份作 MySQL 的备份都是这样作的,假如是大型大量的DB 环境时,就需要考虑以 Incremental 的备份方式,而 MySQL 本身也提供这种备份方式, 这种备份方式,一周作一次完整的备份,每天只要作异动作业档(LOG)的备份就行,在备份的成本上,可以节省备份时间和储存空间.

完成了备份的工作安排,现在又可以和学园们一起快乐的上网冲浪去了...

#
# Variables define for your server
#

# Database name
DBNAME="phpbb2"

# Database admins name
DBUSER="root"

# Database admins password
DBPASS="password"

# MySQL commands path
# default: /usr/bin
BINPATH="/usr/bin"

# Backup date format
BAKDATE=`date +%w`

# Database tables list files name
TABLST="tables_list"

# Backup Type
# 0: All tables in one dump file
# 1: Pre table in one dump file
BAKTYPE="0"

#
# Script Start
#

#
# Create backup temp directory
#
cd /tmp
rm -rf $TMPDIR
mkdir $TMPDIR
cd $TMPDIR
mkdir $BAKDIR
cd $BAKDIR

#
# Create databases table list
#
$BINPATH/mysql $DBNAME -u$DBUSER -p$DBPASS -N -e "show tables" > $TABLST

#
# Flush databases LOG before dump
#
$BINPATH/mysqladmin -u$DBUSER -p$DBPASS flush-logs

#
# Choice one type to dump datebase
#
case $BAKTYPE in
0)
#
# Dump database all table in one file
#
$BINPATH/mysqldump $DBNAME -u$DBUSER -p$DBPASS opt > $DBNAME.sql
;;
1)
#
# Dump database pre table in one file
#
awk { print BINPATH"/mysqldump "DBNAME" -u"DBUSER" -p"DBPASS" \
opt " $1 " > " $1".sql" } \
BINPATH="$BINPATH" DBNAME="$DBNAME" DBUSER="$DBUSER" DBPASS="$DBPASS" \
$TABLST \
| /bin/sh
;;
*);;
esac

cd ..

#
# Compress backup files
#
tar cfz $BAKDIR.tgz $BAKDIR

cd ..
#
# Clean temp files and directory
#
rm -rf $TMPDIR

#
# Script End
#