利用SELECT ... OUTFILE来备份与恢复数据库 - MySQL & PostgreSQL & Sybase

社区导航: 专家门诊网络技术操作系统数据库程序设计系统应用考试认证 CIO及信息化站长交流综合交流下载基地 51CTO产品服务

【设为首页 | 收藏本站】

51CTO技术论坛» MySQL & PostgreSQL & Sybase » 利用SELECT ... OUTFILE来备份与恢复数据库 [ 打印] [ 订阅] [ 收藏] [ 推荐给朋友] [ 本帖文本页]

论坛跳转:

david_yeung 性别:男

新新人类

帖子 33
精华 0
无忧币 1693
积分 37
阅读权限 20

注册日期 2008-6-5

最后登录 2008-10-3

离线

[查看资料] [发短消息] [Blog]

[个人主页]

发表于:2008-7-7 17:34　　标题：利用SELECT ... OUTFILE来备份与恢复数据库
＜上一帖 | 下一帖＞

今天写的，大家去提提意见吧。

我写这个脚本的目的是利用MySQL的select * from tablename into outfile ...语句来备份MySQL数据库，虽然没有MYSQLDUMP导出数据快，可是恢复的时候却非常快。
注意：
1）、我这个不包含表结构的备份，所以如果用之前先备份一下表结构。
2）、运行此脚本的用户必须具有select,insert,以及GLOBAL的FILE权限。
3）、注意导入时候的字符集要跟你的库一致。
1、备份脚本内容：
[root@localhost mysql]# cat fast_full_backup
#!/bin/sh
#
# Created by david yeung.
#
# 20080707.
#
# Use outfile syntax to backup mysql's full data.
#

DBNAME=$1
BACKUPDIR=/home/mysql/backup
USERNAME=backup_file_user
PASSWD=123456
TARNAME=$1`date '+%Y%m%d'`.tar
# Add your own database name here.
case "$1" in
  t_girl);;
  *) exit;;
esac
# Get all the tables' name.
NUM=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME|wc -l`
HEADNUM=`expr ${NUM} - 3`
TAILNUM=`expr ${NUM} - 7`
ARR1=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME| head -n"$HEADNUM" | tail -n "$TAILNUM"`
ARR2=($ARR1)

i=0
while [ "$i" -lt "${#ARR2[@]}" ]
do
tmpFileName=${ARR2[$i]}
# The real dump process.
/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -D$DBNAME -vv -e "select * from $tmpFileName into outfile '"$BACKUPDIR/$tmpFileName".dat' fields terminated by ',' enclosed by '\"' lines terminated by '\n'"
let "i++"
done

# Compress all the files.
#

cd $BACKUPDIR
tar cvf $TARNAME `ls *.dat`
gzip -f $TARNAME
rm -rf *.dat
2、恢复脚本内容：

[root@localhost mysql]# cat fast_full_recovery
#!/bin/sh
#
# Created by david yeung.
#
# 20080707.
#
# Use outfile syntax to restore mysql's full data.
#

DBNAME=$1
GZNAME=$2
GZDIR=`dirname $GZNAME`
USERNAME=backup_file_user
PASSWD=123456
if [ -z ${DBNAME} ]
then
exit
fi
if [ -z ${GZNAME} ]
then
  exit
fi
TARNAME=`gzip -l "$GZNAME" | awk '{ print $4 }'|tail -n1`
gzip -d "$GZNAME"
tar xvf "$TARNAME" -C "$GZDIR"
ARR1=(`ls "$GZDIR" | grep '.dat' | grep -v 'grep'`)

i=0
while [ "$i" -lt "${#ARR1[@]}" ]
do
TMPFILENAME=${ARR1[$i]}
TBNAME=`echo $TMPFILENAME | cut -d '.' -f1`
/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -D$DBNAME -vv -e "load data infile '"$GZDIR"/$TMPFILENAME' ignore into table "$TBNAME" character set utf8 fields terminated by ',' enclosed by '\"' lines terminated by '\n'"
let "i++"
done

rm -rf "$GZDIR"/*.dat

3、实际运行例子：

1）、备份过程：

[root@localhost mysql]# ./fast_full_backup t_girl
--------------
select * from admin into outfile '/home/mysql/backup/admin.dat' fields terminated by ',' enclosed by '"' lines terminated by '\n'
--------------

Query OK, 0 rows affected (0.00 sec)

Bye
...

Bye
--------------
select * from ww into outfile '/home/mysql/backup/ww.dat' fields terminated by ',' enclosed by '"' lines terminated by '\n'
--------------

Query OK, 9 rows affected (0.00 sec)

Bye
admin.dat
...
ww.dat
[root@localhost mysql]#

2）、恢复过程：

[root@localhost mysql]# ./fast_full_recovery t_girl /home/mysql/backup/t_girl20080707.tar.gz
admin.dat
...
ww.dat
--------------
load data infile '/home/mysql/backup/admin.dat' ignore into table admin character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n'
--------------

Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

Bye
...

Query OK, 9 rows affected, 3 warnings (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

Bye
[root@localhost mysql]#

4、与MYSQLDUMP导出导入时间比较：

前提：2G的数据量。
1）、用OUTFILE 方式花费。
导出：
real 5m19.003s
user 2m20.211s
sys    0m11.053s
导入：
real 6m28.006s
user 0m19.723s
sys    0m13.647s
2）、用MYSQLDUMP 方式花费。
导出：
real 4m16.682s
user 2m52.976s
sys    0m13.026s
导入：
real 7m49.480s
user 1m2.702s
sys    0m10.545s

[ 本帖最后由特爱蚂蚁于 2008-7-7 17:57 编辑 ]