MySQL简易备份方法

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