利用SELECT ... OUTFILE来备份MySQL数据库
来源:互联网 发布:php购物网站制作 编辑:程序博客网 时间:2024/06/06 12:59
我写这个脚本的目的是利用MySQL的select * from tablename into outfile ...语句来备份MySQL数据库,虽然没有MYSQLDUMP导出数据快,可是恢复的时候却非常快。
注意:
1)、我这个不包含表结构的备份,所以如果用之前先备份一下表结构。
2)、运行此脚本的用户必须具有select,insert,以及GLOBAL的FILE权限。
3)、注意导入时候的字符集要跟你的库一致。
1、备份脚本内容:
2、恢复脚本内容:
3、实际运行例子:
[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
注意:
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
- 利用SELECT ... OUTFILE来备份MySQL数据库
- mysql select into outfile 意表备份还原方式
- MySQL Select-outfile
- mysql select into outfile
- MySQL数据库按指定格式导出数据进行备份(select into outfile)和恢复数据的方法
- mysql的select into outfile
- MySQL select into outfile 语法
- mysql语句-select...into outfile
- mysql语句-select...into outfile
- 利用shell脚本来简化备份mysql数据库步骤
- mysql select * into OUTFILE 不会锁表
- MySQL使用SELECT...INTO OUTFILE导出文本文件
- MySQL使用SELECT INTO OUTFILE导出文本文件
- mysql select into outfile 语法 乱码问题
- MySQL 导出数据select into outfile用法
- MYSQL解决select ... into outfile '..' mysql写文件权限问题
- 解决select ... into outfile '..' mysql写文件权限问题
- mysql select into outfile报无权限,访问被拒绝
- 广告控件 AdRotator 所使用XML文件的格式示例(注意大小写 例如 Ad 写为ad会导致无效)
- 评测网页效率的工具-YSLOW
- 总结一下MySQL中的锁机制
- vs2005中 数据库读取 以及加载列表项的封装函数。
- 改良版本mysqldump来备份MYSQL数据库
- 利用SELECT ... OUTFILE来备份MySQL数据库
- ubuntu下realplayer画面有点卡
- Windows运行命令集锦
- load data infile 需要注意的一些地方
- Log4j使用浅析
- Java中的Set List Map 的各自特点
- 关于Apache的性能优化
- java集成apache 动态配置域名
- 显示Div提示框