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 服务器即可。