【赵渝强老师】使用select...into outfile语句备份MySQL

MySQL可以使用select…into outfile语句将表的内容导出为一个文本文件。其基本的语法格式如下：

select [列名] from 表名 [WHERE 语句] into outfile '目标文件' [其他选项];

该语句分为两个部分。前半部分是一个普通的select语句，通过这个select语句来查询所需要的数据；后半部分是导出数据的。其中，“目标文件”参数指出将查询的记录导出到哪个文件中；“其他选项”参数为可选参数选项，下表列举了其可能的取值。

提示：FIELDS和LINES两个子句都是可选的，但是如果两个子句都被指定了，FIELDS必须位于LINES的前面。

视频讲解如下
【赵渝强老师】使用select...into outfile语句备份MySQL

下面以备份员工表（emp）的数据为例来演示如何使用select…into outfile语句进行数据的热备份
（1）创建备份文件存储的目录，并且将目录所属的组设置为mysql。

mkdir -p /databackup/select
chown mysql:mysql /databackup/select

（2）使用select…into outfile语句来导出demo1数据库下emp表的记录。其中，字段之间用“、”隔开，字符型数据用双引号括起来。每条记录以“>”开头。

mysql> use demo1;

mysql> select * from emp 
into outfile '/databackup/select/bk1.txt'
fields
	terminated by '\、'
	optionally enclosed by '\"'
lines
	starting by '\>'
	terminated by '\r\n';

# 输出的错误信息如下：
ERROR 1290 (HY000): 
The MySQL server is running with the --secure-file-priv option 
so it cannot execute this statement

（3）查看参数–secure-file-priv的值。

mysql> show variables like 'secure_file_priv';

# 输出的信息如下：
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

# 提示：secure_file_priv参数是用来限制load data, 
       select ... select...into outfile和load_file()在导出数据时使用的目录。 
# 当secure_file_priv的值为null ，表示不允许导入和导出数据。
# 当secure_file_priv的值为/tmp/ ，表示导入和导出只能发生在/tmp/目录下。
# 当secure_file_priv的值没有具体值时，表示不对导入和导出做限制。

（4）修改文件/etc/my.ini，在“[mysqld]”块下面加入下面一行。

secure_file_priv=

（5）重启MySQL数据库

systemctl restart mysqld

（6）重新查看参数–secure-file-priv的值。

mysql> show variables like 'secure_file_priv';

# 输出的信息如下：
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+

（7）重新执行第（2）步操作。

（8）查看文件/databackup/select/bk1.txt文件的内容。

cat /databackup/select/bk1.txt 

# 输出的信息如下：
>7369、"SMITH"、"CLERK"、7902、"1980/12/17"、800、\N、20
>7499、"ALLEN"、"SALESMAN"、7698、"1981/2/20"、1600、300、30
>7521、"WARD"、"SALESMAN"、7698、"1981/2/22"、1250、500、30
>7566、"JONES"、"MANAGER"、7839、"1981/4/2"、3670、\N、20
>7654、"MARTIN"、"SALESMAN"、7698、"1981/9/28"、1250、1400、30
>7698、"BLAKE"、"MANAGER"、7839、"1981/5/1"、2850、\N、30
>7782、"CLARK"、"MANAGER"、7839、"1981/6/9"、2550、\N、10
>7788、"SCOTT"、"ANALYST"、7566、"1987/4/19"、3000、\N、20
>7839、"KING"、"PRESIDENT"、-1、"1981/11/17"、5950、\N、10
>7844、"TURNER"、"SALESMAN"、7698、"1981/9/8"、1500、\N、30
>7876、"ADAMS"、"CLERK"、7788、"1987/5/23"、1100、\N、20
>7900、"JAMES"、"CLERK"、7698、"1981/12/3"、950、\N、30
>7902、"FORD"、"ANALYST"、7566、"1981/12/3"、3000、\N、20
>7934、"MILLER"、"CLERK"、7782、"1982/1/23"、1400、\N、10