共計 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 問題的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
