這篇文章將為大家詳細講解有關(guān)MySQL中select into outfile問題的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
創(chuàng)新互聯(lián)建站從2013年成立，先為耿馬等服務建站，耿馬等地企業(yè)，進行企業(yè)商務咨詢服務。為耿馬企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務解決您的所有建站問題。
01 背景
select into outfile無論對于開發(fā)或DBA來說，都是一個愛不釋手的導出數(shù)據(jù)的方式。相比mysqldump，它能夠?qū)π枰獙С龅淖侄巫鱿拗疲芎玫臐M足了某些不需要導出主鍵字段的場景或分庫分表的環(huán)境下數(shù)據(jù)的重新導入。且與load data infile配合起來，無疑是一款數(shù)據(jù)導入導出的利器。最近，開發(fā)小伙伴在測試環(huán)境使用select into file進行數(shù)據(jù)導出時，碰到了一個問題，覺得很有必要跟大家分享一下。
02 問題概述
客戶某系統(tǒng)（以下簡稱ebank）開發(fā)小伙伴報告說自己的一個腳本使用了select into outfile對數(shù)據(jù)進行導出。然而數(shù)據(jù)無法導出。以下是該問題的排查過程。
03 排查過程及思路
1.查看數(shù)據(jù)庫用戶是否具有file權(quán)限
首先通過show grants命令查看ebank用戶是否具有導出數(shù)據(jù)的file權(quán)限，如下代碼所示：
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權(quán)限，因此第一步首先為該用戶賦予file權(quán)限，由于file權(quán)限屬于全局權(quán)限，因此為ebank用戶賦予file權(quán)限時不必指明該權(quán)限專門賦予哪一個schema，若指明schema，則會報錯。
mysql> grant file on test.* to "ebank"@"%"; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
若在賦予file權(quán)限時不對schema作限制，則可以賦權(quán)成功。
mysql> grant file on *.* to "ebank"@"%"; Query OK, 0 rows affected (0.00 sec)
2.查看數(shù)據(jù)庫的全局參數(shù)secure_file_priv
用戶權(quán)限已經(jīng)賦予，接下來要看數(shù)據(jù)庫的全局參數(shù)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導出數(shù)據(jù)時，則只能導出在secure_file_priv指定的路徑下；其二為一個空字符串，在此種取值下，那么數(shù)據(jù)庫不會對導出的路徑做限制，只要select into outfile指定導出的路徑對于操作系統(tǒng)層面的mysql用戶具有讀寫執(zhí)行的權(quán)限，則數(shù)據(jù)即可正常導出；其三取值為null，此種取值代表數(shù)據(jù)庫不能使用select into outfile導出數(shù)據(jù)。
由于此時數(shù)據(jù)庫secure_file_priv的取值為null,是不能導出數(shù)據(jù)的，因此需要在配置文件中重新指定secure_file_priv的取值，由于數(shù)據(jù)庫的數(shù)據(jù)目錄為/data2，因此將/data2設(shè)置select into outfile的導出路徑。
mysql> show variables like "secure_file_priv"; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | secure_file_priv | /data2/ | +------------------+---------+ 1 row in set (0.01 sec)
測試一下是否能夠正常導出數(shù)據(jù)。
[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
數(shù)據(jù)導出成功。
[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
由于已經(jīng)有了之前的測試，而這次的修改在本人看來，和前一次只有路徑上的差別，因此，在配置文件中修改secure_file_priv的取值為/home/ebank/data后，重啟數(shù)據(jù)庫，并未手工進行select into outfile導出的測試，就通知開發(fā)可以進行數(shù)據(jù)的導出了。然而問題還是出現(xiàn)了，開發(fā)仍然反饋無法成功導出數(shù)據(jù)。
收到此反饋后，便手動進行了一次數(shù)據(jù)導出測試。
[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
奇怪的是這次的確無法導出數(shù)據(jù)，看到這個報錯，首先確認是否是數(shù)據(jù)庫的配置出了問題。
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)
數(shù)據(jù)庫的配置沒有問題，且之前設(shè)置的/home/mysql/data目錄的屬主也為mysql，此時對于這個問題的研究陷入了瓶頸。
百思不得其解之際，請教了大佬，經(jīng)過大佬的指點，方知此問題的關(guān)鍵點如下圖所示：
[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 mysql
ebank用戶家目錄在創(chuàng)建之初的訪問權(quán)限為700,數(shù)據(jù)庫導出數(shù)據(jù)的存放路徑為/home/ebank/data,雖然data目錄的屬主為mysql，但由于上層路徑ebank目錄的屬主為700，即除ebank用戶外的所有用戶都對此目錄無執(zhí)行權(quán)限，因此使用select into outfile導出數(shù)據(jù)時會報錯。
根據(jù)此原因可以通過以下方法解決該問題：
將/home/ebank的訪問權(quán)限改為701，即任何用戶對/home/ebank目錄都有執(zhí)行權(quán)限。
[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]#
通過上述配置，數(shù)據(jù)導出成功。
04 select into outfile的替代方案
select into outfile可以方便的把表中的數(shù)據(jù)導出為csv文件，且可以根據(jù)需求篩選需要的字段。但有時需要導出多張表，且對表的字段沒有篩選需求時，對每一張表一條條的去寫導出的SQL語句未免顯得麻煩。這時候就可以使用mysqldump來對數(shù)據(jù)進行導出。
使用mysqldump將數(shù)據(jù)導出成csv格式時，需要加一個參數(shù)--tab，該參數(shù)指定文件導出的路徑。對于每一張表，會生成兩個文件，一個txt文件，以csv格式保存了表中的數(shù)據(jù)，一個sql文件，保存了表結(jié)構(gòu)。
#如下語句為導出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
關(guān)于“mysql中select into outfile問題的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
