mysql怎么通过outfile恢复_利用SELECT ... OUTFILE来备份MySQL数据库方法详解

我写这个脚本的目的是利用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