mysql中select into outfile問(wèn)題的示例分析

這篇文章將為大家詳細(xì)講解有關(guān)mysql中select into outfile問(wèn)題的示例分析，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

01 背景

select into outfile無(wú)論對(duì)于開發(fā)或DBA來(lái)說(shuō)，都是一個(gè)愛不釋手的導(dǎo)出數(shù)據(jù)的方式。相比mysqldump，它能夠?qū)π枰獙?dǎo)出的字段做限制，很好的滿足了某些不需要導(dǎo)出主鍵字段的場(chǎng)景或分庫(kù)分表的環(huán)境下數(shù)據(jù)的重新導(dǎo)入。且與load data infile配合起來(lái)，無(wú)疑是一款數(shù)據(jù)導(dǎo)入導(dǎo)出的利器。最近，開發(fā)小伙伴在測(cè)試環(huán)境使用select into file進(jìn)行數(shù)據(jù)導(dǎo)出時(shí)，碰到了一個(gè)問(wèn)題，覺得很有必要跟大家分享一下。

02 問(wèn)題概述

客戶某系統(tǒng)（以下簡(jiǎn)稱ebank）開發(fā)小伙伴報(bào)告說(shuō)自己的一個(gè)腳本使用了select into outfile對(duì)數(shù)據(jù)進(jìn)行導(dǎo)出。然而數(shù)據(jù)無(wú)法導(dǎo)出。以下是該問(wèn)題的排查過(guò)程。

03 排查過(guò)程及思路

1.查看數(shù)據(jù)庫(kù)用戶是否具有file權(quán)限

首先通過(guò)show grants命令查看ebank用戶是否具有導(dǎo)出數(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)限時(shí)不必指明該權(quán)限專門賦予哪一個(gè)schema，若指明schema，則會(huì)報(bào)錯(cuò)。

mysql> grant file on test.* to "ebank"@"%";
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

若在賦予file權(quán)限時(shí)不對(duì)schema作限制，則可以賦權(quán)成功。

mysql> grant file on *.* to "ebank"@"%";
Query OK, 0 rows affected (0.00 sec)

2.查看數(shù)據(jù)庫(kù)的全局參數(shù)secure_file_priv

用戶權(quán)限已經(jīng)賦予，接下來(lái)要看數(shù)據(jù)庫(kù)的全局參數(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的取值有三種，其一為一個(gè)具體的路徑，那么使用select into outfile導(dǎo)出數(shù)據(jù)時(shí)，則只能導(dǎo)出在secure_file_priv指定的路徑下；其二為一個(gè)空字符串，在此種取值下，那么數(shù)據(jù)庫(kù)不會(huì)對(duì)導(dǎo)出的路徑做限制，只要select into outfile指定導(dǎo)出的路徑對(duì)于操作系統(tǒng)層面的mysql用戶具有讀寫執(zhí)行的權(quán)限，則數(shù)據(jù)即可正常導(dǎo)出；其三取值為null，此種取值代表數(shù)據(jù)庫(kù)不能使用select into outfile導(dǎo)出數(shù)據(jù)。

由于此時(shí)數(shù)據(jù)庫(kù)secure_file_priv的取值為null,是不能導(dǎo)出數(shù)據(jù)的，因此需要在配置文件中重新指定secure_file_priv的取值，由于數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄為/data2，因此將/data2設(shè)置select into outfile的導(dǎo)出路徑。

mysql> show variables like "secure_file_priv";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| secure_file_priv | /data2/ |
+------------------+---------+
1 row in set (0.01 sec)

測(cè)試一下是否能夠正常導(dǎo)出數(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ù)導(dǎo)出成功。

3.開發(fā)的特殊需求

測(cè)試完select into outfile導(dǎo)出數(shù)據(jù)成功后，對(duì)于DBA的工作已然完成。將數(shù)據(jù)庫(kù)調(diào)整的結(jié)果告訴開發(fā)后，但開發(fā)仍然認(rèn)為不符合需求。由于開發(fā)在此測(cè)試環(huán)境的服務(wù)器上也有一個(gè)操作系統(tǒng)用戶ebank，因此開發(fā)需要將數(shù)據(jù)導(dǎo)出的目錄設(shè)置為/home/ebank/data。遵從開發(fā)的需求，將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

由于已經(jīng)有了之前的測(cè)試，而這次的修改在本人看來(lái)，和前一次只有路徑上的差別，因此，在配置文件中修改secure_file_priv的取值為/home/ebank/data后，重啟數(shù)據(jù)庫(kù)，并未手工進(jìn)行select into outfile導(dǎo)出的測(cè)試，就通知開發(fā)可以進(jìn)行數(shù)據(jù)的導(dǎo)出了。然而問(wèn)題還是出現(xiàn)了，開發(fā)仍然反饋無(wú)法成功導(dǎo)出數(shù)據(jù)。

收到此反饋后，便手動(dòng)進(jìn)行了一次數(shù)據(jù)導(dǎo)出測(cè)試。

[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

奇怪的是這次的確無(wú)法導(dǎo)出數(shù)據(jù)，看到這個(gè)報(bào)錯(cuò)，首先確認(rèn)是否是數(shù)據(jù)庫(kù)的配置出了問(wèn)題。

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ù)庫(kù)的配置沒有問(wèn)題，且之前設(shè)置的/home/mysql/data目錄的屬主也為mysql，此時(shí)對(duì)于這個(gè)問(wèn)題的研究陷入了瓶頸。

百思不得其解之際，請(qǐng)教了大佬，經(jīng)過(guò)大佬的指點(diǎn)，方知此問(wèn)題的關(guān)鍵點(diǎ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)建之初的訪問(wèn)權(quán)限為700,數(shù)據(jù)庫(kù)導(dǎo)出數(shù)據(jù)的存放路徑為/home/ebank/data,雖然data目錄的屬主為mysql，但由于上層路徑ebank目錄的屬主為700，即除ebank用戶外的所有用戶都對(duì)此目錄無(wú)執(zhí)行權(quán)限，因此使用select into outfile導(dǎo)出數(shù)據(jù)時(shí)會(huì)報(bào)錯(cuò)。

根據(jù)此原因可以通過(guò)以下方法解決該問(wèn)題：

• 將/home/ebank的訪問(wèn)權(quán)限改為701，即任何用戶對(duì)/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]#

通過(guò)上述配置，數(shù)據(jù)導(dǎo)出成功。

04 select into outfile的替代方案

• select into outfile可以方便的把表中的數(shù)據(jù)導(dǎo)出為csv文件，且可以根據(jù)需求篩選需要的字段。但有時(shí)需要導(dǎo)出多張表，且對(duì)表的字段沒有篩選需求時(shí)，對(duì)每一張表一條條的去寫導(dǎo)出的SQL語(yǔ)句未免顯得麻煩。這時(shí)候就可以使用mysqldump來(lái)對(duì)數(shù)據(jù)進(jìn)行導(dǎo)出。

• 使用mysqldump將數(shù)據(jù)導(dǎo)出成csv格式時(shí)，需要加一個(gè)參數(shù)--tab，該參數(shù)指定文件導(dǎo)出的路徑。對(duì)于每一張表，會(huì)生成兩個(gè)文件，一個(gè)txt文件，以csv格式保存了表中的數(shù)據(jù)，一個(gè)sql文件，保存了表結(jié)構(gòu)。

#如下語(yǔ)句為導(dǎo)出test庫(kù)下的所有表
[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問(wèn)題的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。