mysql导入数据

数据导入
修改搜索路径
]# mkdir /myload
]# chown mysql /myload
]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload“
:wq
]# systemctl restart mysqld

mysql> show  variables  like  “secure_file_priv”;  //查看

作用:把系统文件的内容存储到数据的数据库
数据导入步骤
-默认只有root用户有数据导入权限
-建表
-导入数据

load data infile “目录/文件名”
into table 库名.表名
field terminaled by “分隔符”
lines terminaled by “\n”;

注意事项
具体如下:
-字段分隔符要与文件一致
-表字段类型和字段个数要与文件匹配-
-导入数据时指定文件的绝对路径

/etc/passwd

root❌0:0:root:/root:/bin/bash
用户:密码:uid:gid:用户描述信息:用户家目录:解析器

安装时已自动创建
mysql> show variables like “secure_file_priv”; //查看数据库在系统的存放路径
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+
1 row in set (0.00 sec)

[root@fay50 ~]# ls -ld /var/lib/mysql-files/ //查看存放数据的路径权限
nf ca.pem client-key.pem db2 ib_logfile0 ibdata1 mysql mysql.sock.lock private_key.pem server-cert.pem sys
ca-key.pem client-cert.pem db1 ib_buffer_pool ib_logfile1 ibtmp1 mysql.sock performance_schema public_key.pem server-key.pem

mysql> create database db3; //创建存储数据的表

mysql> create table db3.user( //定义表内容的类型
-> name char(50),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(150),
-> homedir char(50),
-> shell char(50)
-> );

mysql> desc db3.user; //查看表字段

mysql> select * from db3.user;

mysql> load data infile “/myload/passwd” into table db3.user fields terminated by “:” lines terminated by “\n”; //数据导入数据库

mysql> alter table db3.user add id int primary key auto_increment first; //增加id号,自增长

mysql> select * from db3.user;

mysql> select * from db3.user where id=1; //查看root的数据