共计 7843 个字符，预计需要花费 20 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 mysql 中 select into outfile 问题的示例分析，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
01 背景
select into outfile 无论对于开发或 DBA 来说，都是一个爱不释手的导出数据的方式。相比 mysqldump，它能够对需要导出的字段做限制，很好的满足了某些不需要导出主键字段的场景或分库分表的环境下数据的重新导入。且与 load data infile 配合起来，无疑是一款数据导入导出的利器。最近，开发小伙伴在测试环境使用 select into file 进行数据导出时，碰到了一个问题，觉得很有必要跟大家分享一下。
02 问题概述
客户某系统（以下简称 ebank）开发小伙伴报告说自己的一个脚本使用了 select into outfile 对数据进行导出。然而数据无法导出。以下是该问题的排查过程。
03 排查过程及思路
1. 查看数据库用户是否具有 file 权限
首先通过 show grants 命令查看 ebank 用户是否具有导出数据的 file 权限，如下代码所示：
mysql show grants for ebank@ %
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for ebank@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ebank @ % |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO ebank @ % |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)由于该用户不具有 file 权限，因此第一步首先为该用户赋予 file 权限，由于 file 权限属于全局权限，因此为 ebank 用户赋予 file 权限时不必指明该权限专门赋予哪一个 schema，若指明 schema，则会报错。
mysql grant file on test.* to ebank @ %
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES若在赋予 file 权限时不对 schema 作限制，则可以赋权成功。
mysql grant file on *.* to ebank @ %
Query OK, 0 rows affected (0.00 sec)2. 查看数据库的全局参数 secure_file_priv
用户权限已经赋予，接下来要看数据库的全局参数 secure_file_priv 是否打开。
mysql show variables like secure_file_priv
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | null |
+------------------+-------+
1 row in set (0.01 sec)secure_file_priv 的取值有三种，其一为一个具体的路径，那么使用 select into outfile 导出数据时，则只能导出在 secure_file_priv 指定的路径下；其二为一个空字符串，在此种取值下，那么数据库不会对导出的路径做限制，只要 select into outfile 指定导出的路径对于操作系统层面的 mysql 用户具有读写执行的权限，则数据即可正常导出；其三取值为 null，此种取值代表数据库不能使用 select into outfile 导出数据。
由于此时数据库 secure_file_priv 的取值为 null, 是不能导出数据的，因此需要在配置文件中重新指定 secure_file_priv 的取值，由于数据库的数据目录为 /data2，因此将 /data2 设置 select into outfile 的导出路径。
mysql show variables like secure_file_priv
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| secure_file_priv | /data2/ |
+------------------+---------+
1 row in set (0.01 sec)测试一下是否能够正常导出数据。
[root@multi-master2 tmp]# mysql -uebank -pebank -h227.0.0.1
mysql use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql select * from player into outfile /data2/player.txt
Query OK, 4 rows affected (0.00 sec)
mysql ^DBye
[root@multi-master2 tmp]# cat /data2/player.txt
2 messi sf agen
4 neymar wf brazil
6 ramos CB spain
8 xavi AMF spain数据导出成功。
3. 开发的特殊需求测试完 select into outfile 导出数据成功后，对于 DBA 的工作已然完成。将数据库调整的结果告诉开发后，但开发仍然认为不符合需求。由于开发在此测试环境的服务器上也有一个操作系统用户 ebank，因此开发需要将数据导出的目录设置为 /home/ebank/data。遵从开发的需求，将 secure_file_priv 的取值改为 /home/ebank/data，并将 /home/ebank/data 目录的属主改为 mysql。
[root@multi-master2 ebank]# chown -R mysql:mysql data/
[root@multi-master2 ebank]# ll
total 4
drwxr-xr-x. 2 mysql mysql 4096 Aug 21 03:54 data
[root@multi-master2 ebank]#
[root@multi-master2 ebank]# pwd
/home/ebank由于已经有了之前的测试，而这次的修改在本人看来，和前一次只有路径上的差别，因此，在配置文件中修改 secure_file_priv 的取值为 /home/ebank/data 后，重启数据库，并未手工进行 select into outfile 导出的测试，就通知开发可以进行数据的导出了。然而问题还是出现了，开发仍然反馈无法成功导出数据。
收到此反馈后，便手动进行了一次数据导出测试。
[root@multi-master2 data]# mysql -uebank -pebank -h227.0.0.1
mysql use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql select * from player into outfile /home/ebank/data/player.txt
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement奇怪的是这次的确无法导出数据，看到这个报错，首先确认是否是数据库的配置出了问题。
mysql show variables like secure_file_priv
+------------------+-------------------+
| Variable_name | Value |
+------------------+-------------------+
| secure_file_priv | /home/ebank/data/ |
+------------------+-------------------+
1 row in set (0.00 sec)
mysql show grants for ebank @ %
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for ebank@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO ebank @ % |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO ebank @ % |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)数据库的配置没有问题，且之前设置的 /home/mysql/data 目录的属主也为 mysql，此时对于这个问题的研究陷入了瓶颈。
百思不得其解之际，请教了大佬，经过大佬的指点，方知此问题的关键点如下图所示：
[root@multi-master2 data]# cd /home/
[root@multi-master2 home]# ll
total 8
drwx------. 5 ebank ebank 4096 Aug 21 03:54 ebank
drwx------. 7 mysql mysql 4096 Aug 20 14:34 mysqlebank 用户家目录在创建之初的访问权限为 700, 数据库导出数据的存放路径为 /home/ebank/data, 虽然 data 目录的属主为 mysql，但由于上层路径 ebank 目录的属主为 700，即除 ebank 用户外的所有用户都对此目录无执行权限，因此使用 select into outfile 导出数据时会报错。
根据此原因可以通过以下方法解决该问题：
将 /home/ebank 的访问权限改为 701，即任何用户对 /home/ebank 目录都有执行权限。
[root@multi-master2 home]# ll
total 8
drwx------. 5 ebank ebank 4096 Aug 21 03:54 ebank
drwx------. 7 mysql mysql 4096 Aug 20 14:34 mysql
[root@multi-master2 home]# chmod 701 ebank/
[root@multi-master2 home]# ll
total 8
drwx-----x. 5 ebank ebank 4096 Aug 21 03:54 ebank
drwx------. 7 mysql mysql 4096 Aug 20 14:34 mysql
[root@multi-master2 home]# mysql -uebank -pebank -h227.0.0.1
mysql use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql select * from player into outfile /home/ebank/data/player1.txt
Query OK, 4 rows affected (0.01 sec)
mysql ^DBye
[root@multi-master2 home]# cat /home/ebank/data/player1.txt
2 messi sf agen
4 neymar wf brazil
6 ramos CB spain
8 xavi AMF spain
[root@multi-master2 home]#通过上述配置，数据导出成功。
04 select into outfile 的替代方案
select into outfile 可以方便的把表中的数据导出为 csv 文件，且可以根据需求筛选需要的字段。但有时需要导出多张表，且对表的字段没有筛选需求时，对每一张表一条条的去写导出的 SQL 语句未免显得麻烦。这时候就可以使用 mysqldump 来对数据进行导出。
使用 mysqldump 将数据导出成 csv 格式时，需要加一个参数 –tab，该参数指定文件导出的路径。对于每一张表，会生成两个文件，一个 txt 文件，以 csv 格式保存了表中的数据，一个 sql 文件，保存了表结构。
# 如下语句为导出 test 库下的所有表
[root@multi-master2 data]# mysqldump --single-transaction -uebank -pebank -h227.0.01 --tab= /home/ebank/data test
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED= 8d52b2f3-c316-11e9-8b39-000c29a27f67:1-40
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
[root@multi-master2 data]# ll
total 16
-rw-r--r--. 1 root root 1623 Aug 21 06:51 player.sql
-rw-rw-rw-. 1 mysql mysql 69 Aug 21 06:51 player.txt
-rw-r--r--. 1 root root 1426 Aug 21 06:51 team.sql
-rw-rw-rw-. 1 mysql mysql 61 Aug 21 06:51 team.txt关于“mysql 中 select into outfile 问题的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
