利用SELECT ... OUTFILE来备份MySQL数据库

利用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