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

tubaobaoya3

我写这个脚本的目的是利用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' 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
　　[root@localhost mysql]#
　　2）、恢复过程：
　　[root@localhost mysql]# ./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: 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