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> 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 -h127.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 -h127.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 mysql

ebank使用者家目錄在建立之初的訪問許可權為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 -h127.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 -h127.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

05  小結

綜上所述，無論是要使用select into outfile匯出資料，還是要使用mysqldump --tab的方式匯出資料，都需要具有以下許可權：

1、訪問資料庫的使用者需要擁有匯出資料所需的file許可權。

2、資料庫引數secure_file_priv不能為null，要麼取值為空，即不對匯出的路徑做限制，要麼為具體指定的路徑。

3、資料匯出的路徑對於作業系統的mysql使用者必須是可寫的，且其上層目錄對於mysql使用者來說必須是可執行的。

| 作者簡介

許升輝·沃趣科技資料庫工程師
熟悉MySQL體系結構和innodb儲存引擎工作原理；擅長資料庫問題分析，效能調優；對mysql備份恢復、資料遷移有豐富的實踐。