mysql中select into outfile问题实例分析

01 背景

选择到outfile 是开发人员和DBA 喜欢的导出数据的方式。与mysqldump相比，它可以限制需要导出的字段，可以很好地满足某些不需要导出主键字段的场景或者分库分表环境下数据的重新导入。而与load data infile结合起来，无疑是数据导入导出的利器。最近有开发伙伴在测试环境中使用select into file导出数据时遇到了一个问题，觉得有必要分享给大家。

02问题概述

某客户系统（以下简称ebank）repo的开发者rted 他的脚本之一使用 select into outfile 来导出数据。 。但是，数据无法导出。以下是该问题的排查流程。

03 故障排查流程及思路

1.检查数据库用户是否有文件权限

首先使用show grants命令检查ebank用户是否有导出数据的文件权限，代码如下：

mysql> 显示 ebank@"%";+------------------------ ------------------------ 的补助金-------------------------- ------------------------ -------------------------- ------------------------ -------------------------- ------------------------ ----------------+| ebank@% |+---- 的赠款 ------------------------------------------------------ ----------- ------------------------------------------ -------------------------------------------------------- ----- -------------------------------------------------------- ----- ---------+|将 *.* 的使用权授予 'ebank'@'%' ||授予选择、插入、更新、删除、创建、删除、索引、更改、创建临时表、执行、创建视图、显示视图、创建例程、更改例程、事件、在 `test` 上触发。* 到 'ebank'@'%' |+-- ----------- ------------------------------------------ ----------- ------------------------------------------ ----------- ------------------------------------------ ----------- ----------------------------------+2 行集中（0.00 秒）

由于用户没有文件权限，因此第一步是向用户授予文件权限。由于文件权限是全局权限，因此在向ebank用户授予文件权限时无需指定该权限具体分配给哪个schema。如果指定了schema，那么就会报错。

mysql> 将 test.* 上的文件授予“ebank”@“%”；错误 1221 (HY000)：DB GRANT 和 GLOBAL PRIVILEGES 的使用不正确

如果架构不是授予文件权限时受限制，可以授予成功。

mysql> gran*.* 上的 t 文件发送至“ebank”@“%”；查询正常，0 行受影响（0.00 秒）

2.查看数据库的全局参数secure_file_priv

已授予用户权限。接下来需要检查数据库的全局参数secure_file_priv是否开启。

mysql> show variables like "secure_file_priv";+------ --------- --+--------+|变量名 |值 |+----------------+----- --+|安全文件权限 | null |+------------------+--------+集合中的 1 行（0.01 秒）

Secure_file_priv 具有三个值。一是具体路径。使用select into outfile导出数据时，只能导出到secure_file_priv指定的路径。另一个是空字符串。在这个值下，那么数据库将不会限制导出路径。只要select into outfile指定的导出路径对操作系统级别的mysql用户具有读、写和执行权限，就可以正常导出数据；第三个值是无效的。本例中的值表明数据库无法使用select into outfile导出数据。

由于此时数据库secure_file_priv的值为null，因此无法导出数据。因此，需要在配置文件中重新指定secure_file_priv的值。由于数据库的数据目录为/data2，因此/data2将select的导出路径设置为outfile。

mysql> 显示诸如“secure_file_priv”之类的变量；+--------------------+---------+|变量名 |值 |+----------------+---------+|安全文件权限 | /data2/ |+------------ -------+---------+集合中的 1 行（0.01 秒）

测试数据是否可以正常导出。

[root@multi-master2 tmp]# mysql -uebank -pebank -h227.0.0.1mysql> 使用test读取表信息来补全表名和列名 可以关闭此功能以获得更快的启动速度with -ADatabase Changedmysql> 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.txt2 Messi sf agen4 neymar wf brazil6 ramos CB spain8 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]# lltotal 4drwxr-xr-x。 2 mysql mysql 4096 八月 21 03:54 数据 [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.1mysql> use test读取表信息完成表名和列名可以关闭此功能以获得更快的启动速度with -ADatabase Changedmysql> select * from player into outfile "/home/ebank/data/player.txt";ERROR 1290 (HY000): MySQL 服务器正在使用 --secure-file-pri 运行v选项所以无法执行这条语句

奇怪的是，这次数据无法导出。看到这个错误时，首先确认一下数据库配置是否有问题。

mysql> 显示“secure_file_priv”等变量；+--------------------------------+--------- ---------+|变量名 |值 |+--- --------------+--------------------+|安全文件权限 | /home/ebank/data/ |+-- ----------------+------------------+1 行设置（0.00 秒）mysql> 显示“ebank”@“%”的补助金；+-------------------------------------------- ---------- ---------------------------------------- ---------- ---------------------------------------- ---------- ---------------------------------------- ---------- -------+|为 ebank@% |+ 提供的赠款 ---------------------------------- --------- ---------------------------------------------------- --------- ---------------------------------------------------- --------- ---------------------------------------------------- --------- -----+|将 *.* 上的文件授予 'ebank'@'%' ||授予选择T、插入、更新、删除、创建、删除、索引、更改、创建临时表、执行、创建视图、显示视图、创建例程、更改例程、事件、触发“测试”。*至“ebank”@'% ' |+------------- ---------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- +2 rows in set (0.00 sec)

数据库配置没有问题，之前设置的/home/mysql/data目录的属主也是mysql。此时，这个问题的研究陷入了瓶颈。

正当我困惑的时候，我向老板请教。经过老大的指导，我意识到这个问题的关键点如下图所示：

[root@multi-master2 data]# cd /home/[root@multi-master2 home] # 总共 8drwx------。 5 电子银行 电子银行 4096 8 月 21 日 03:54 ebankdrwx------。 7 mysql mysql 4096 Aug 20 14 :34 mysql

访问权限创建之初ebank用户主目录的值为700，数据库导出数据的存储路径为/home/ebank/data。虽然数据目录的所有者是mysql，但是由于上层路径ebank目录的所有者是700，即除了ebank用户以外的所有用户都没有这个目录的执行权限，所以会报错使用 select into outfile 导出数据时报告。

基于这个原因，可以通过以下方法解决：

将/home/ebank的访问权限修改为701，即任何用户都有执行权限/home/ebank 目录。 。

[root@multi-master2 home]# lltotal 8drwx------。 5 电子银行 电子银行 4096 8 月 21 日 03:54 ebankdrwx------。 7 mysql mysql 4096 8月20日14:34 mysql[root@multi-master2 home]# chmod 701 ebank/[root@multi-master2 home]# lltotal 8drwx-----x。 5 电子银行 电子银行 4096 8 月 21 日 03:54 ebankdrwx------。 7 MySQL MySQL 4096 8 月 20 日 14:34 MySQL [Root@Multi-Master2Home]#MySQL-UEBANK -H227.0.0.0.0.0.1Mysql> 使用 Testreading 来完成表和列名称您可以使用 -A 更改数据库以加快启动速度 mysql> 从播放器中选择 * 到输出文件“/home/ebank/ data/player1.txt";查询正常，4行受影响（0.01秒）mysql> ^DBye[root@multi-master2 home]# cat /home /ebank/data/player1.txt2 Messi sf agen4 neymar wf brazil6 ramos CB spain8 xavi AMF spain[root@multi-master2 home]#

通过以上配置，数据导出成功。

04 select into outfile的替代方案

Select into outfile可以轻松将表中的数据导出到csv文件中，并且可以根据需要进行过滤。场地。但有时需要导出多个表，并且不需要对表的字段进行过滤。如果把导出的SQL语句逐个写出来的话，会很麻烦。这时候就可以使用mysqldump来导出数据了。

使用mysqldump导出数据时a 转为csv格式，需要添加一个参数--tab，指定导出文件的路径。对于每个表，会生成两个文件，一个txt文件，以csv格式保存表中的数据，一个sql文件，保存表结构。

#以下语句是导出测试库下的所有表[root@multi-master2 data]# mysqldump --single-transaction -uebank -pebank -h227.0.01 --tab="/home /ebank/data" testmysqldump: [警告] 在命令行界面上使用密码可能不安全。警告：来自具有 GTID 的服务器的部分转储默认情况下将包括所有事务的 GTID，甚至是那些更改了受抑制部分的事务数据库。如果您不想恢复 GTID，请传递 --set-gtid-purged=OFF。要进行完整转储，请传递 - -all-databases --triggers --routines --events.SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;SET @@SESSION.SQL_LOG_BIN= 0;---- 开头的 GTID 状态备份的--SET @@ GLOBAL.GTID_PURGED='8d52b2f3-c316-11e9-8b39-000c29a27f67:1-40';SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;[root@multi-master2 data]# lltotal 16-rw-r--r-- 。 1根根1623年8月21日06:51player.sql-rw-rw-rw-。 1 mysql mysql 69 八月 21 06:51player.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问题的示例分析》这篇文章分享到这里。希望以上内容能够对大家有所帮助，让大家能够学到更多的知识。如果您觉得文章不错，请转发出去，让更多的人看到。