备份语法:select * from 表名 where 条件 into outfile ‘/tmp/表名.txt‘;
#注意保存位置/tmp的权限;
#注意备份文件的格式;
恢复语法:load data infile ‘表名.txt‘ into table 表名 ;
#注意恢复时备份文件的位置要存放在mysql 数据库 data 库目录下;
范例:根据时间字段检索数据:
备份outfile:select * from PageView where ActionTime >=‘2015-01-01 00:00‘ and
ActionTime <=‘2015-01-01 23:59‘ into outfile ‘/tmp/PageView7.txt‘;
#注意时间的字段; desc 查看时间字段;
恢复load data infile:load data infile ‘PageView7.txt‘ into table PageView ;
使用SELECT * INTOOUTFILE 备份:
SELECT * INTOOUTFILE ‘/tmp/xx.txt‘ FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\n‘ FROM 表名;
FIELDS TERMINATED BY‘,‘字段间分割符
OPTIONALLY ENCLOSED BY‘"‘将字段包围 对数值型无效
LINES TERMINATED BY‘\n‘ 换行符
使用LOAD DATA INFILE恢复:
LOAD DATA INFILE ‘/tmp/xx.txt‘ INTO TABLE test.fii FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\n‘;
参考链接:
http://blog.chinaunix.net/uid-24373487-id-3191050.html
http://www.cnblogs.com/stublue/archive/2012/07/02/2573860.html
mysqldump 备份参考链接:
http://linuxboys.blog.51cto.com/9150052/1579346
本文出自 “思想大于技术” 博客,谢绝转载!
原文:http://linuxboys.blog.51cto.com/9150052/1620024