MySQL数据库的导入与导出

导入

命令格式:
load data infile “检索目录/需导入的文件名” into table 表名 fields terminated by “指定分隔符” lines terminated by “\n”;

1.修改检索目录

  # mysql -uroot -p'123456'       //进入数据库
  
    mysql> show variables like "%file%";           //查看当前包含file的变量
    +-----------------------------+---------------------------------+
    | Variable_name               | Value                           |
    +-----------------------------+---------------------------------+
    |...                          |...                              |
    | secure_file_priv            | /var/lib/mysql/mysql50-slow.log |
    |...                          |...                              |
    +-----------------------------+---------------------------------+

    # vim /etc/my.cnf                //修改主配置文件
    [mysqld]
    secure_file_priv="/myload"                       //修改默认检索路径为/myload
    ...

2.给检索目录加执行权限

   # mkdir /myload    
   # chown mysql /myload/           //myload目录需要有w权限

3.启服务

   # systemctl restart mysqld       //重启mysqld服务

4.验证是否修改成功

mysql> show variables like "secure_file_priv";   //验证检索路径是否修改成功
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+

5.将文件导入到数据库的表中,并添加行号

     mysql> create database db3;                  //创建数据库
     mysql> create table db3.user(                
     -> name char(50),
     -> password char(1),
     -> uid int,
     -> gid int,
     -> comment varchar(150),
     -> homedir char(60),
     -> shell char(50));                       //建表

6.将文件拷贝到检索目录

  > system cp /etc/passwd /myload/       //在数据库登录状态下,system可以执行Linux系统命令

7.导入数据

   > load data infile "/myload/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
    //导入数据。导入/myload/passwd文件的内容到db3.user的表中,分隔符为":",到每行的结尾换行

8.给表添加行号字段

      > alter table db3.user add id int primary key auto_increment first;
      > select * from db3.user;
      > select * from db3.user where id = 1;  

导出

命令格式,有如下三种格式：
①. sql查询命令 into outfile “目录名/文件名";

②. sql查询命令 into outfile “目录名/文件名" fields terminated by “分隔符”;

③. sql查询命令 into outfile “目录名/文件名” fields terminated by “分隔符” lines terminated by “\n” ;

注意事项:
①. 导出数据行数由SQL查询决定
②. 导出的是表记录,不包括字段名
③. 自动创建存储数据的文件
④. 存储数据文件具有唯一性

例:
①. > select * from db3.user where id <= 2 into outfile “/myload/user1.txt” ; //第一种格式导出

②. > select * from db3.user where id <= 2 into outfile “/myload/user2.txt” fields terminated by “#”;
//第二种格式导出

③. > select * from db3.user where id <= 2 into outfile “/myload/user3.txt” fields terminated by “#”
lines terminated by “!!!”; //第三种格式导出

④. > select name,uid,shell from db3.user where id <= 3 into outfile “/myload/user4.txt”;
> system ls /myload
> system cat /myload/user1.txt