使用load data导入数据时
mysql> load data infile '/myshare/area_info.txt' into table area_info; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement mysql> show variables like '%secure_file%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | secure_file_priv | NULL | +------------------+-------+ 1 row in set (0.00 sec) secure_file_prive=null -- 限制mysqld不允许导入导出 secure_file_priv=/tmp/ -- 限制mysqld导入导出只能发生在/tmp/目录下 secure_file_priv='' -- 不对mysqld导入导出做限制
此时需要修改配置文件my.cnf，[mysqld]下添加secure_file_priv='' ，重启数据库服务
#导入 mysql> load data infile '/myshare/area_info.txt' into table area_info fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'; Query OK, 3509 rows affected Records: 3509 Deleted: 0 Skipped: 0 Warnings: 0 #导出，如果指定outfile目录，注意对其有写入权限；默认保存在datadir mysql> select * from db_swzf.area_info into outfile 'area_info.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'; Query OK, 3509 rows affected (0.04 sec)
查找文件
#查找文件 [root@VMUest /]# find / -type f -name "area_info.txt" /usr/local/mysql/data/db_swzf/area_info.txt /myshare/area_info.txt [root@VMUest /]#