利用SELECT ... OUTFILE来备份MySQL数据库
我写这个脚本的目的是利用MySQL的select * from tablename into outfile ...语句来备份MySQL数据库，虽然没有MYSQLDUMP导出数据快，可是恢复的时候却非常快。 注意：1）、我这个不包含表结构的备份，所以如果用之前先备份一下表结构。
2）、运行此脚本的用户必须具有select,insert,以及GLOBAL的FILE权限。
3）、注意导入时候的字符集要跟你的库一致。
1、备份脚本内容：
# 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、恢复脚本内容：
# 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）、备份过程：
# ./fast_full_backup t_girl
--------------
select* from admin into outfile '/home/mysql/backup/admin.dat' fieldsterminated 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
#
2）、恢复过程：
# ./fast_full_recovery t_girl /home/mysql/backup/t_girl20080707.tar.gz
admin.dat
...
ww.dat
--------------
loaddata infile '/home/mysql/backup/admin.dat' ignore into table admincharacter set utf8 fields terminated by ',' enclosed by '"' linesterminated by '\n'
--------------
Query OK, 0 rows affected (0.00 sec)
Records: 0Deleted: 0Skipped: 0Warnings: 0
Bye
...
Query OK, 9 rows affected, 3 warnings (0.00 sec)
Records: 9Deleted: 0Skipped: 0Warnings: 0
Bye
#
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
页:
[1]