1.select…into outfile 导出数据

把test库下的EMP表的数据全部导出，备份文件EMP.sql放到/data目录下

use test
select * from EMP;
select * from EMP into outfile ‘/data/EMP.sql’;
more /data/EMP.sql

注意：如果EMP.sql文件存在，再次执行会报文件存在错误。需要提前清理掉。
root@db 12:24: [test]> select * from EMP into outfile ‘/data/EMP.sql’;
ERROR 1086 (HY000): File ‘/data/EMP.sql’ already exists
root@db 12:24: [test]>

2.load data infile 恢复数据

select * from EMP;
delete from EMP;
select * from EMP;
load data infile ‘/data/EMP.sql’ into table test.EMP;
select * from EMP;

3.测试演示

3.1 select…into outfile 导出数据库

root@db 12:16:  [(none)]> use test
Database changed
root@db 12:17:  [test]> 
root@db 12:17:  [test]> select * from EMP;
+-------+--------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 | NULL |     10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)

root@db 12:17:  [test]> 
root@db 12:23:  [test]> select * from EMP into outfile '/data/EMP.sql';
Query OK, 14 rows affected (0.03 sec)

root@db 12:23:  [test]> 
root@db 12:23:  [test]> exit
Bye
[root@source data]# pwd
/data
[root@source data]# 

[root@source data]# ll
total 6140
drwxr-xr-x 2 mysql mysql    4096 Feb 23 12:07 backup
drwxr-xr-x 2 mysql mysql    4096 Jul  5  2019 backup2
-rw-rw-rw- 1 mysql mysql     767 Feb 23 12:23 EMP.sql
drwxr-xr-x 6 mysql mysql    4096 Feb 23 11:24 mysql
drwxr-xr-x 5 mysql mysql    4096 Feb 17 01:41 mysql_bak
-rw-rw-rw- 1 mysql mysql 6264322 Jul  5  2019 sbtest.txt
[root@source data]# 
[root@source data]# more EMP.sql 
7369    SMITH   CLERK   7902    1980-12-17 00:00:00     800     \N      20
7499    ALLEN   SALESMAN        7698    1981-02-20 00:00:00     1600    300     30
7521    WARD    SALESMAN        7698    1981-02-22 00:00:00     1250    500     30
7566    JONES   MANAGER 7839    1981-04-02 00:00:00     2975    \N      20
7654    MARTIN  SALESMAN        7698    1981-09-28 00:00:00     1250    1400    30
7698    BLAKE   MANAGER 7839    1981-05-01 00:00:00     2850    \N      30
7782    CLARK   MANAGER 7839    1981-06-09 00:00:00     2450    \N      10
7788    SCOTT   ANALYST 7566    1987-04-19 00:00:00     3000    \N      20
7839    KING    PRESIDENT       \N      1981-11-17 00:00:00     5000    \N      10
7844    TURNER  SALESMAN        7698    1981-09-08 00:00:00     1500    0       30
7876    ADAMS   CLERK   7788    1987-05-23 00:00:00     1100    \N      20
7900    JAMES   CLERK   7698    1981-12-03 00:00:00     950     \N      30
7902    FORD    ANALYST 7566    1981-12-03 00:00:00     3000    \N      20
7934    MILLER  CLERK   7782    1982-01-23 00:00:00     1300    \N      10
[root@source data]# 

3.2 load data infile恢复数据

root@db 12:24:  [test]> select * from EMP;
+-------+--------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 | NULL |     10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)

root@db 12:24:  [test]> 
root@db 12:24:  [test]> delete from EMP;
Query OK, 14 rows affected (0.00 sec)

root@db 12:24:  [test]> 
root@db 12:24:  [test]> select * from EMP;
Empty set (0.00 sec)

root@db 12:24:  [test]> 
root@db 12:24:  [test]> load data infile '/data/EMP.sql' into table test.EMP;
Query OK, 14 rows affected (0.00 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0

root@db 12:24:  [test]> 
root@db 12:24:  [test]> select * from EMP;
+-------+--------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 | NULL |     10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)

root@db 12:24:  [test]>