mysql 备份方法_MySQL简易备份方法

八.结语

目前这个 Script 在测试机上测试的效果还不错,以酷!学园讨论区的2万多篇讨论文章, DB 总大小有30几 MB, 跑这个 Script 大约只需要30几秒就完成,下面是以 time 指令测试数次计算的结果:

[root@demo tmp]# time backup

real 0m32.788s

user 0m22.770s

sys 0m4.610s

[root@demo tmp]# time backup

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)的备份就行,在备份的成本上,可以节省备份时间和储存空间. 完成了备份的工作安排,现在又可以和学园们一起快乐的上网冲浪去了...

九.参考资料

1.MySQL中文参考手册

http://cnpa.yzu.edu.tw/~cfc/docs/mysqldoc_big5/manual_toc.html

2.MySQL man page

3.Study-area - Shell和Shell Script

http://www.study-area.org/linux/linuxfr1.htm

十.附录-备份Script原始档

#!/bin/sh

#

# Title: MySQL Backup Script

# Created: 2002.06.01

# Current: 2002.06.23

# Contact: Duncan Lo

# Note: This script just only backup one database!

#

#

# 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`

# Path for backup files save to

BAKPATH="/usr/backup/phpbb"

# Temp directorys name

TMPDIR="tmp.db_bak".$BAKDATE

# Backup filess directory

BAKDIR="$DBNAME"_$BAKDATE

# 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

#

# Move file to backup directory

#

mv $BAKDIR.tgz $BAKPATH

cd ..

#

# Clean temp files and directory

#

rm -rf $TMPDIR

#

# Script End

#