mysql数据导入导出

数据导入
导入的数据必须是有规律的
把系统文件的内容存储到数据库的表里
创建存储文件内容的表
执行导入数据的sql命令

1导入

在myql数据库中是指定了导入的文件需要存放的文件加，如果不进行自定义的时候，则可以用

用show variables like "secure_file_priv"查看该数据库指定导入导出的目录。
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

mysql> create table user (
    -> name char(30) default null,
    -> password char(1) default null,
    -> uid smallint(2) default null,
    -> gid smallint(2) default null,
    -> comment char(100) default null,
    -> homedir char(150) default null,
    -> shell char(50) default null,
    -> index(name)
    -> );
Query OK, 0 rows affected (0.47 sec)

mysql> system cp /etc/passwd /var/lib/mysql-files/      ----在数据库中调用系统的命令
mysql> system ls /var/lib/mysql-files/
passwd

mysql> load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines terminated by "\n";
Query OK, 41 rows affected (0.34 sec)
Records: 41  Deleted: 0  Skipped: 0  Warnings: 0

mysql> alter table user add id int(2) primary key auto_increment first; ---向表中添加一个id字段并设置为主键
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

自定义导入文件的时候，更改导入文件的目录

[root@host51 mysql-files]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"    -----添加
[root@host51 mysql-files]# mkdir /myload
[root@host51 mysql-files]# ls -ld /myload/
drwxr-xr-x. 2 root root 6 7月  24 08:08 /myload/
[root@host51 mysql-files]# chown mysql /myload
[root@host51 mysql-files]# systemctl restart mysqld
[root@host51 mysql-files]# cp /etc/passwd /myload
[root@host51 mysql-files]# ls /myload
passwd

mysql> load data infile "/myload/passwd" into table user1 fields terminated by ":" lines terminated by "\n";
Query OK, 41 rows affected (0.37 sec)
Records: 41  Deleted: 0  Skipped: 0  Warnings: 0

导出：表记录存储到系统文件里(不包括字段)
sql 查询
into outfile “目录/文件名（不需要新创建）”

mysql> select * from user into outfile "/myload/user2.txt";
Query OK, 41 rows affected (0.00 sec)

mysql> select id,name,password  from user where id< 4 into outfile "/myload/user4.txt";
Query OK, 3 rows affected (0.00 sec)

mysql> select id,name,password  from user where id< 4 into outfile "/myload/user5.txt" fields terminated by ";" lines terminated by "!!!";
Query OK, 3 rows affected (0.00 sec)

[root@host51 myload]# cat user4.txt 
1    root    x
2    bin    x
3    daemon    x

[root@host51 myload]# cat user5.txt 
1;root;x!!!2;bin;x!!!3;daemon;x!!!