var lib mysql files_mysql导出导入数据

导出数据：

mysql> select host,user from user where host<>'localhost' into outfile '/root/aa.txt';

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%';

+--------------------------+-----------------------+

| Variable_name            | Value                 |

+--------------------------+-----------------------+

| require_secure_transport | OFF                   |

| secure_auth              | ON                    |

| secure_file_priv         | /var/lib/mysql-files/ |

+--------------------------+-----------------------+

3 rows in set (0.00 sec)

发现secure_auth为on，可以导出，路径为/var/lib/mysql-files/

mysql> select host,user from user where host<>'localhost' into outfile '/var/lib/mysql-files/test.txt';

Query OK, 3 rows affected (0.00 sec)

导出成功

-------------

mysql> show variables like '%char%'; //查看编码

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8mb4                    |

| character_set_connection | utf8mb4                    |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | utf8mb4                    |

| character_set_server     | utf8mb4                    |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

导入数据：

mysql> load data infile "/root/ccc.txt" into table loginfo;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

目录为：| secure_file_priv         | /var/lib/mysql-files/ |

mysql> load data infile "/var/lib/mysql-files/aaa.txt" into table loginfo;

Query OK, 10000 rows affected, 10569 warnings (0.20 sec)

Records: 10000  Deleted: 0  Skipped: 0  Warnings: 10569