MySQL 如何将表数据备份到文本文件

SELECT … INTO OUTFILE 简介

使用MySQL时，数据库的数据备份是一个值得我们关注的话题。按照不同的维度，数据库的数据备份可以分为物理备份与逻辑备份，也可以分为全量备份与增量备份，等等。而关于这些备份类型的区别以及适用场景，可以参考 MySQL 5.7 官方手册的详细介绍 Backup and Recovery Types。
本文讲述的数据库的数据备份方式，属于逻辑备份，也属于增量备份，其适用于场景为：需要将一个表中特定的行记录数据按照一定格式备份到文本文件中，即 SELECT … INTO OUTFILE 的使用。

SELECT … INTO OUTFILE 语法

• SELECT … INTO OUTFILE 语法使用示例如下，详细情况可参考 MySQL 5.7 官方手册 SELECT … INTO Syntax

  -- 将年龄大于30岁的用户的相关信息导出到一个文本文件中
  select user_id, user_name, age into outfile '/var/lib/mysql-files/test_backup.txt'
  fields terminated by ',' enclosed by ''
  lines terminated by '\n'
  from tb_user 
  where age > 30;
  

• 语法使用注意事项

1. SELECT … INTO OUTFILE ‘file_name’ 指定的文件将在 MySQL 服务器主机上创建，因此使用时得先具备主机相关目录的权限。另外指定的文件不能已经存在，该语法不能覆盖同名文件，主要是为了防止主机上的重要文件（如/etc/passwd和数据库表）被销毁。
2. fields 子句以及 lines 子句如果不显示指定相关值，则采用如下默认设置

   FIELDS TERMINATED BY '\t' -- 字段分隔符，生成的文本文件中每个字段(列)以什么字符分隔，默认是 \t
   	ENCLOSED BY '' -- 文本限定符，生成的文本文件中每个字段被什么字符包围，默认是空字符
   	ESCAPED BY '\\' -- 转义符，默认是 \
   LINES TERMINATED BY '\n' -- 记录分隔符，数据库表数据每条记录在生成的文本文件中如何分隔，一般都习惯用行分隔符，即一条表数据记录对应文件文件的一行
   	STARTING BY '' -- 起始字符串，数据库表数据每条记录保存到文本文件时，每条记录开头填充的字符串，一般无特别要求，设置为空字符
   

3. 如果需要在文本文件中存储 BLOB 类型的值，建议使用 SELECT … INTO DUMPFILE语法处理。

SELECT … INTO OUTFILE 问题处理

1. 问题： ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
   解决： ①检查 SELECT … INTO OUTFILE ‘file_name’ 指定的文件的父目录，确保目录对应MySQL 服务器主机的，如不能将目录指定为客户端的；②该问题的出现主要与安装MySQL时限制了数据导入与导出的 FILE 权限有关。运行命令 show variables like 'secure_file_priv';查看 secure_file_priv 的值，该参数用以限制数据导入和导出的操作，例如执行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD_FILE()函数等。

   secure_file_priv=NULL – 参数为NULL，MySQL禁止导入和导出操作
   secure_file_priv=/tmp/ – 参数设为一个目录名，MySQL限制导入和导出操作只能发生在/tmp/目录下
   secure_file_priv=’ ’ – 参数为空，MySQL不限制导入和导出操作，即参数无效

   ③所以，处理该问题时可以根据实际情况采取相应方案，如secure_file_priv值指定了一个目录名，那么SELECT … INTO OUTFILE ‘file_name’ 指定的文件的父目录可以相应修改为该目录；如希望secure_file_priv参数失效或者指定另外一个目录，则可以先找到MySQL的配置文件，在配置文件中加入或更新参数secure_file_priv的设置，重启MySQL服务器即可。