mysql数据导入与导出基本操作

数据导入

[root@host50~]# setenforce 0

mysql>show variables like "secure_file_priv";

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

|Variable_name | Value |

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

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

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

1row in set (0.01 sec)

mysql>

Systemcp /etc/passwd /var/lib/mysql-files/

Systemls /var/lib/mysql-files/

mysql>load data infile "/var/lib/mysql-files/passwd" into tableuser fields terminated by ":" lines terminated by "\n";

mysql>alter table user add id int(2) primary key auto_increment first;

Sed-n “30p” passwd

Altertable user

Modify Uid int(2),

Modifygid int(2);

[root@host50~]# vim /etc/my.cnf

secure_file_priv=/myload

[root@host50~]# systemctl restart mysqld

mysql>alter table user drop primary key;

mysql>load data infile "/myload/passwd" into table user fieldsterminated by ":" lines terminated by "\n";

mysql>alter table user add id int(2) primary key auto_increment first;

数据导出

mysql>select * from user into outfile "/dirdata/user1.txt";

[root@host50~]# ls /myload

passwd user1.txt

mysql>select * from user into outfile "/myload/user5.txt" fieldsterminated by ";" lines terminated by "!!!";

QueryOK, 82 rows affected (0.00 sec)