mysql匯出資料

標籤：ase   rac   res   value   variable   like   utf8   cli   dir   

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匯出資料