select into outfile问题一则

系统管理员 2023-06-06 12:16 194阅读 0赞

format_png

01 背景

select into outfile无论对于开发或DBA来说，都是一个爱不释手的导出数据的方式。相比mysqldump，它能够对需要导出的字段做限制，很好的满足了某些不需要导出主键字段的场景或分库分表的环境下数据的重新导入。且与load data infile配合起来，无疑是一款数据导入导出的利器。最近，开发小伙伴在测试环境使用select into file进行数据导出时，碰到了一个问题，觉得很有必要跟大家分享一下。

02 问题概述

客户某系统（以下简称ebank）开发小伙伴报告说自己的一个脚本使用了select into outfile对数据进行导出。然而数据无法导出。以下是该问题的排查过程。

03 排查过程及思路

1.查看数据库用户是否具有file权限

首先通过show grants命令查看ebank用户是否具有导出数据的file权限，如下代码所示：

  1. mysql> show grants for ebank@"%";
  2. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Grants for ebank@% |
  4. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | GRANT USAGE ON *.* TO 'ebank'@'%' |
  6. | 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'@'%' |
  7. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  8. 2 rows in set (0.00 sec)

由于该用户不具有file权限，因此第一步首先为该用户赋予file权限，由于file权限属于全局权限，因此为ebank用户赋予file权限时不必指明该权限专门赋予哪一个schema，若指明schema，则会报错。

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

若在赋予file权限时不对schema作限制，则可以赋权成功。

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

2.查看数据库的全局参数secure_file_priv

用户权限已经赋予，接下来要看数据库的全局参数secure_file_priv是否打开。

  1. mysql> show variables like "secure_file_priv";
  2. +------------------+-------+
  3. | Variable_name | Value |
  4. +------------------+-------+
  5. | secure_file_priv | null |
  6. +------------------+-------+
  7. 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的导出路径。

  1. mysql> show variables like "secure_file_priv";
  2. +------------------+---------+
  3. | Variable_name | Value |
  4. +------------------+---------+
  5. | secure_file_priv | /data2/ |
  6. +------------------+---------+
  7. 1 row in set (0.01 sec)

测试一下是否能够正常导出数据。

  1. [root@multi-master2 tmp]# mysql -uebank -pebank -h127.0.0.1
  2. mysql> use test
  3. Reading table information for completion of table and column names
  4. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> select * from player into outfile "/data2/player.txt";
  7. Query OK, 4 rows affected (0.00 sec)
  8. mysql> ^DBye
  9. [root@multi-master2 tmp]# cat /data2/player.txt
  10. 2   messi   sf  agen
  11. 4   neymar  wf  brazil
  12. 6   ramos   CB  spain
  13. 8   xavi    AMF spain

数据导出成功。

3.开发的特殊需求

测试完select into outfile导出数据成功后，对于DBA的工作已然完成。将数据库调整的结果告诉开发后，但开发仍然认为不符合需求。由于开发在此测试环境的服务器上也有一个操作系统用户ebank，因此开发需要将数据导出的目录设置为/home/ebank/data。遵从开发的需求，将secure_file_priv的取值改为/home/ebank/data，并将/home/ebank/data目录的属主改为mysql。

  1. [root@multi-master2 ebank]# chown -R mysql:mysql data/
  2. [root@multi-master2 ebank]# ll
  3. total 4
  4. drwxr-xr-x. 2 mysql mysql 4096 Aug 21 03:54 data
  5. [root@multi-master2 ebank]#
  6. [root@multi-master2 ebank]# pwd
  7. /home/ebank

由于已经有了之前的测试，而这次的修改在本人看来，和前一次只有路径上的差别，因此，在配置文件中修改secure_file_priv的取值为/home/ebank/data后，重启数据库，并未手工进行select into outfile导出的测试，就通知开发可以进行数据的导出了。然而问题还是出现了，开发仍然反馈无法成功导出数据。

收到此反馈后，便手动进行了一次数据导出测试。

  1. [root@multi-master2 data]# mysql -uebank -pebank -h127.0.0.1
  2. mysql> use test
  3. Reading table information for completion of table and column names
  4. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> select * from player into outfile "/home/ebank/data/player.txt";
  7. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

奇怪的是这次的确无法导出数据，看到这个报错，首先确认是否是数据库的配置出了问题。

  1. mysql> show variables like "secure_file_priv";
  2. +------------------+-------------------+
  3. | Variable_name | Value |
  4. +------------------+-------------------+
  5. | secure_file_priv | /home/ebank/data/ |
  6. +------------------+-------------------+
  7. 1 row in set (0.00 sec)
  8. mysql> show grants for "ebank"@"%";
  9. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  10. | Grants for ebank@% |
  11. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. | GRANT FILE ON *.* TO 'ebank'@'%' |
  13. | 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'@'%' |
  14. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  15. 2 rows in set (0.00 sec)

数据库的配置没有问题，且之前设置的/home/mysql/data目录的属主也为mysql，此时对于这个问题的研究陷入了瓶颈。

百思不得其解之际，请教了大佬，经过大佬的指点，方知此问题的关键点如下图所示：

  1. [root@multi-master2 data]# cd /home/
  2. [root@multi-master2 home]# ll
  3. total 8
  4. drwx------. 5 ebank ebank 4096 Aug 21 03:54 ebank
  5. 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备份恢复、数据迁移有丰富的实践。

发表评论

表情：
评论列表 （有 0 条评论，194人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL数据库备份（INTO OUTFILE）

    MySQL 数据库管理系统通常会采用有效的措施来维护数据库的可靠性和完整性。但是在数据库的实际使用过程当中，仍存在着一些不可预估的因素，会造成数据库运行事务的异常中断，从而影响

    痛定思痛。痛定思痛。/ 0 赞/ 188 阅读

    相关 MySQL数据库备份（INTO OUTFILE）

    MySQL 数据库管理系统通常会采用有效的措施来维护数据库的可靠性和完整性。但是在数据库的实际使用过程当中，仍存在着一些不可预估的因素，会造成数据库运行事务的异常中断，从而影响

    女爷i女爷i/ 0 赞/ 69 阅读