前提条件是要设置secure_file_priv,该参数是需要重启实例的
mysql> show variables like ‘%secure%‘;
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /opt/mysql5733/secure_file/ |
+--------------------------+-----------------------------+
3 rows in set (0.01 sec)
SELECT * FROM tb_hxl01 INTO OUTFILE ‘/opt/mysql5733/secure_file/tb_hxl01.txt‘ FIELDS TERMINATED BY ‘,‘;
或是
select * into outfile ‘/opt/mysql5733/secure_file/tb_hxl0222.txt‘ fields terminated by ‘,‘ from tb_hxl01 where id=2;
导出文件内容如下
[root@localhost secure_file]# more tb_hxl01.txt
1,name01,2021-05-20 08:50:56,2021-05-20 08:50:56
2,name02,2021-05-20 08:50:56,2021-05-20 08:50:56
3,name03,2021-05-20 08:50:56,2021-05-20 08:50:56
4,name04,2021-05-20 08:50:56,2021-05-20 08:50:56
5,name05,2021-05-20 08:50:56,2021-05-20 08:50:56
6,name06,2021-05-20 08:50:56,2021-05-20 08:50:56
7,name07,2021-05-20 08:50:57,2021-05-20 08:50:57
8,name08,2021-05-20 08:50:57,2021-05-20 08:50:57
9,name09,2021-05-20 08:50:57,2021-05-20 08:50:57
10,name10,2021-05-20 08:50:57,2021-05-20 08:50:57
11,name11,2021-05-20 08:50:57,2021-05-20 08:50:57
12,name12,2021-05-20 08:50:57,2021-05-20 08:50:57
13,name13,2021-05-20 08:50:57,2021-05-20 08:50:57
14,name14,2021-05-20 08:50:57,2021-05-20 08:50:57
15,name15,2021-05-20 08:50:57,2021-05-20 08:50:57
16,name16,2021-05-20 08:50:58,2021-05-20 08:50:58
17,name17,2021-05-20 08:50:58,2021-05-20 08:50:58
18,name18,2021-05-20 08:50:58,2021-05-20 08:50:58
19,name19,2021-05-20 08:50:58,2021-05-20 08:50:58
20,name20,2021-05-20 08:50:59,2021-05-20 08:50:59
尝试导入到另外一个表
create table tb_hxl02 like tb_hxl01;
LOAD DATA INFILE ‘/opt/mysql5733/secure_file/tb_hxl01.txt‘ INTO TABLE tb_hxl02
FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘
LINES TERMINATED BY ‘\n‘;
查看表数据
mysql> select * from tb_hxl02;
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 1 | name01 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 2 | name02 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 3 | name03 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 4 | name04 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 5 | name05 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 6 | name06 | 2021-05-20 08:50:56 | 2021-05-20 08:50:56 |
| 7 | name07 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 8 | name08 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 9 | name09 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 10 | name10 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 11 | name11 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 12 | name12 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 13 | name13 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 14 | name14 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 15 | name15 | 2021-05-20 08:50:57 | 2021-05-20 08:50:57 |
| 16 | name16 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 17 | name17 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 18 | name18 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 19 | name19 | 2021-05-20 08:50:58 | 2021-05-20 08:50:58 |
| 20 | name20 | 2021-05-20 08:50:59 | 2021-05-20 08:50:59 |
+----+--------+---------------------+---------------------+
20 rows in set (0.00 sec)