转载

MySQL命令行下导出/入数据

1.登录Linux

2.登录MySQL:mysql -h192.168.1.1 -uroot  -p123123 【h-服务器IP,u-用户名,p-密码】

3.use mysqldemo; 【切换到表数据所在库】

4.1导出数据:select * from TABLE into outfile '/var/lib/mysql-files/DEMO.SQL' fields terminated by ","; 【把TABLE查询的结果输出到/var/lib/mysql-files/DEMO.SQL;fields terminated by ","已逗号分隔,方便后面导入,也可以不加分隔】

  ①路径/var/lib/mysql-files/是因为若不指定则会报错:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement,

    show variables like '%secure%';  【其他目录对MySQL的权限限制,只能输出到默认】

+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

4.2导入数据:load data infile '/var/lib/mysql-files/DEMO.SQL' into table TABLE fields terminated by ",";【如果不加分隔符会导致多列插入第一列】

参考:https://www.cnblogs.com/regit/p/8041762.html

转载于:https://www.cnblogs.com/stuka/p/11321256.html

正文到此结束
本文目录