mysql导出数据

数据导出
作用:把表记录存储到系统文件里

格式1
mysql> select命令 into outfile “目录名/文件” ; //文件名不需要提前建立,使用默认分隔符

格式2
mysql>　select命令 into outfile “目录名/文件”
fields terminated by “分隔符”;

格式3
mysql>　select命令 into outfile “目录名/文件”
fields terminated by “分隔符”　lines terminated by “分隔符”;

mysql> select * from db1.user into outfile “/root/user1.txt”; //出错,导出只能导出到secure-file目录
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_priv”;
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+

mysql> select * from db1.user into outfile “/var/lib/mysql-files/user1.txt”;
Query OK, 21 rows affected (0.00 sec)

可以手动修改secure-file目录
]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload“
:wq
]# systemctl restart mysqld

mysql> show  variables  like  “secure_file_priv”;  查看