一、数据导入导出
案例：
- 修改检索目录为/myload
- 将/etc/passwd文件导入db3库的user表里，并添加行号字段。
- 将db3库user表
- 所有记录导出, 存到/myload/user.txt 文件里。
步骤一：修改检索目录为/myload
1）修改配置文件，重启服务
1 ]# mkdir /myload 2 ]# chown mysql /myload 3 ]# vim /etc/my.cnf 4 [mysqld] 5 secure_file_priv="/myload” 6 :wq 7 ]# systemctl restart mysqld 8 mysql> show variables like “secure_file_priv”; //查看 9 +------------------+-----------------------+ 10 | Variable_name | Value | 11 +------------------+-----------------------+ 12 | secure_file_priv | /myload/ | 13 +------------------+-----------------------+ 14 Mysql>
2）新建db3库、user表
1 [root@dbsvr1 ~]# mysql -u root –p123456 2 mysql> CREATE DATABASE db3; 3 create table db3.user( 4 name char(50), 5 password char(1), 6 uid int, 7 gid int, 8 comment char(150), 9 homedir char(50), 10 shell char(50) 11 ); 12 Query OK, 0 rows affected (0.70 sec)
步骤二：将/etc/passwd文件导入db3库的user表里，并添加行号字段。
1）拷贝文件到检索目录下
1 [root@dbsvr1 ~]# cp /etc/passwd /myload/
2）导入数据
1 [root@dbsvr1 ~]# mysql –uroot –ptarena 2 mysql> load data infile "/myload/passwd" into table db3.user 3 fields terminated by ":" lines terminated by "\n" ; //导入数据 4 mysql> select * from db3.user; //查看表记录 5 mysql> alter table db3.user 6 -> add 7 -> id int primary key auto_increment first; //添加行号id 字段 8 mysql> select * from db3.user; //查看表记录