mysql中select into outfile问题的示例分析
这篇文章将为大家详细讲解有关mysql中select into outfile问题的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
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>showgrantsforebank@"%";+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Grantsforebank@%|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'ebank'@'%'||GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,CREATETEMPORARYTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,EVENT,TRIGGERON`test`.*TO'ebank'@'%'|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)
由于该用户不具有file权限，因此第一步首先为该用户赋予file权限，由于file权限属于全局权限，因此为ebank用户赋予file权限时不必指明该权限专门赋予哪一个schema，若指明schema，则会报错。
mysql>grantfileontest.*to"ebank"@"%";ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGES
若在赋予file权限时不对schema作限制，则可以赋权成功。
mysql>grantfileon*.*to"ebank"@"%";QueryOK,0rowsaffected(0.00sec)
2.查看数据库的全局参数secure_file_priv
用户权限已经赋予，接下来要看数据库的全局参数secure_file_priv是否打开。
mysql>showvariableslike"secure_file_priv";+------------------+-------+|Variable_name|Value|+------------------+-------+|secure_file_priv|null|+------------------+-------+1rowinset(0.01sec)
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>showvariableslike"secure_file_priv";+------------------+---------+|Variable_name|Value|+------------------+---------+|secure_file_priv|/data2/|+------------------+---------+1rowinset(0.01sec)
测试一下是否能够正常导出数据。
[root@multi-master2tmp]#mysql-uebank-pebank-h227.0.0.1mysql>usetestReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*fromplayerintooutfile"/data2/player.txt";QueryOK,4rowsaffected(0.00sec)mysql>^DBye[root@multi-master2tmp]#cat/data2/player.txt2messisfagen4neymarwfbrazil6ramosCBspain8xaviAMFspain
数据导出成功。
[root@multi-master2ebank]#chown-Rmysql:mysqldata/[root@multi-master2ebank]#lltotal4drwxr-xr-x.2mysqlmysql4096Aug2103:54data[root@multi-master2ebank]#[root@multi-master2ebank]#pwd/home/ebank
由于已经有了之前的测试，而这次的修改在本人看来，和前一次只有路径上的差别，因此，在配置文件中修改secure_file_priv的取值为/home/ebank/data后，重启数据库，并未手工进行select into outfile导出的测试，就通知开发可以进行数据的导出了。然而问题还是出现了，开发仍然反馈无法成功导出数据。
收到此反馈后，便手动进行了一次数据导出测试。
[root@multi-master2data]#mysql-uebank-pebank-h227.0.0.1mysql>usetestReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*fromplayerintooutfile"/home/ebank/data/player.txt";ERROR1290(HY000):TheMySQLserverisrunningwiththe--secure-file-privoptionsoitcannotexecutethisstatement
奇怪的是这次的确无法导出数据，看到这个报错，首先确认是否是数据库的配置出了问题。
mysql>showvariableslike"secure_file_priv";+------------------+-------------------+|Variable_name|Value|+------------------+-------------------+|secure_file_priv|/home/ebank/data/|+------------------+-------------------+1rowinset(0.00sec)mysql>showgrantsfor"ebank"@"%";+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Grantsforebank@%|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|GRANTFILEON*.*TO'ebank'@'%'||GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,CREATETEMPORARYTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,EVENT,TRIGGERON`test`.*TO'ebank'@'%'|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)
数据库的配置没有问题，且之前设置的/home/mysql/data目录的属主也为mysql，此时对于这个问题的研究陷入了瓶颈。
百思不得其解之际，请教了大佬，经过大佬的指点，方知此问题的关键点如下图所示：
[root@multi-master2data]#cd/home/[root@multi-master2home]#lltotal8drwx------.5ebankebank4096Aug2103:54ebankdrwx------.7mysqlmysql4096Aug2014:34mysql
ebank用户家目录在创建之初的访问权限为700,数据库导出数据的存放路径为/home/ebank/data,虽然data目录的属主为mysql，但由于上层路径ebank目录的属主为700，即除ebank用户外的所有用户都对此目录无执行权限，因此使用select into outfile导出数据时会报错。
根据此原因可以通过以下方法解决该问题：
将/home/ebank的访问权限改为701，即任何用户对/home/ebank目录都有执行权限。
[root@multi-master2home]#lltotal8drwx------.5ebankebank4096Aug2103:54ebankdrwx------.7mysqlmysql4096Aug2014:34mysql[root@multi-master2home]#chmod701ebank/[root@multi-master2home]#lltotal8drwx-----x.5ebankebank4096Aug2103:54ebankdrwx------.7mysqlmysql4096Aug2014:34mysql[root@multi-master2home]#mysql-uebank-pebank-h227.0.0.1mysql>usetestReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*fromplayerintooutfile"/home/ebank/data/player1.txt";QueryOK,4rowsaffected(0.01sec)mysql>^DBye[root@multi-master2home]#cat/home/ebank/data/player1.txt2messisfagen4neymarwfbrazil6ramosCBspain8xaviAMFspain[root@multi-master2home]#
通过上述配置，数据导出成功。
04selectinto outfile的替代方案
select into outfile可以方便的把表中的数据导出为csv文件，且可以根据需求筛选需要的字段。但有时需要导出多张表，且对表的字段没有筛选需求时，对每一张表一条条的去写导出的SQL语句未免显得麻烦。这时候就可以使用mysqldump来对数据进行导出。
使用mysqldump将数据导出成csv格式时，需要加一个参数--tab，该参数指定文件导出的路径。对于每一张表，会生成两个文件，一个txt文件，以csv格式保存了表中的数据，一个sql文件，保存了表结构。
#如下语句为导出test库下的所有表[root@multi-master2data]#mysqldump--single-transaction-uebank-pebank-h227.0.01--tab="/home/ebank/data"testmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.Warning:ApartialdumpfromaserverthathasGTIDswillbydefaultincludetheGTIDsofalltransactions,eventhosethatchangedsuppressedpartsofthedatabase.Ifyoudon'twanttorestoreGTIDs,pass--set-gtid-purged=OFF.Tomakeacompletedump,pass--all-databases--triggers--routines--events.SET@MYSQLDUMP_TEMP_LOG_BIN=@@SESSION.SQL_LOG_BIN;SET@@SESSION.SQL_LOG_BIN=0;----GTIDstateatthebeginningofthebackup--SET@@GLOBAL.GTID_PURGED='8d52b2f3-c316-11e9-8b39-000c29a27f67:1-40';SET@@SESSION.SQL_LOG_BIN=@MYSQLDUMP_TEMP_LOG_BIN;[root@multi-master2data]#lltotal16-rw-r--r--.1rootroot1623Aug2106:51player.sql-rw-rw-rw-.1mysqlmysql69Aug2106:51player.txt-rw-r--r--.1rootroot1426Aug2106:51team.sql-rw-rw-rw-.1mysqlmysql61Aug2106:51team.txt
关于“mysql中select into outfile问题的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
