使用load data infile来导入导出数据

2017-01-09 19:20:56来源:作者:人点击

先导出数据
mysql> select * from timer_gps_posinfo where time>'2017-01-01 00:00' and time <'2017-01-02 00:00' into outfile '/home/posinfo.txt' fields terminated by ',';
报错:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement解决:两种方法1.使用
mysql>SHOW VARIABLES LIKE "secure_file_priv";+------------------+-----------------------+| Variable_name | Value  |+------------------+-----------------------+| secure_file_priv | /var/lib/mysql-files/ |+------------------+-----------------------+1 row in set (0.00 sec)
查看授权路径。2.禁用secure-file-priv可通过修改配置文件生效。再试
mysql> select * from timer_gps_posinfo where time>'2017-01-01 00:00' and time <'2017-01-02 00:00' into outfile '/var/lib/mysql-files/posinfo.txt' fields terminated by ',';Query OK, 4532130 rows affected (16.18 sec)
成功导出,用时16秒。同一台机器,起了个docker-mysql,在新mysql中建立空数据库和表结使用
mysql> load data infile '/var/lib/mysql-files/posinfo.txt' into table timer_gps_posinfo fields terminated by ',';Query OK, 4532130 rows affected (17.28 sec)Records: 4532130  Deleted: 0  Skipped: 0  Warnings: 0
导入完成。
本文出自 “牛掰的技术博客” 博客,请务必保留此出处http://newbye.blog.51cto.com/5197506/1890406

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台