Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of select into outfile problems in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

01 background

Select into outfile is a favorite way to export data for both developers and DBA. Compared with mysqldump, it can limit the fields that need to be exported, and satisfies the re-import of data in some scenarios where there is no need to export primary key fields or in the environment of sub-database and sub-tables. And together with load data infile, it is undoubtedly a sharp weapon for data import and export. Recently, the developer encountered a problem when using select into file for data export in the test environment, and felt it necessary to share it with you.

02 Overview of the problem

A customer's developer partner of a system (hereinafter referred to as ebank) reported that one of his scripts used select into outfile to export data. However, the data cannot be exported. The following is the troubleshooting process for this problem.

03 investigation process and train of thought

1. Check whether the database user has file permissions

First, check whether the ebank user has file permission to export data through the show grants command, as shown in the following code:

Mysql > show grants for ebank@ "%" +- -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)

Since the user does not have file permission, the first step is to assign the user file permission. Because the file permission belongs to the global permission, it is not necessary to specify which schema is specifically assigned to the file permission to the ebank user. If schema is specified, an error will be reported.

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

Authorization can be successful if you do not restrict schema when granting file permissions.

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

two。 View the global parameter secure_file_priv of the database

Now that the user permission has been granted, the next step is to see whether the global parameter secure_file_priv of the database is turned on.

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

There are three values for secure_file_priv, one is a specific path, then when using select into outfile to export data, it can only be exported under the path specified by secure_file_priv; the other is an empty string, under which the database will not restrict the exported path. As long as the path specified by select into outfile has read and write permissions for mysql users at the operating system level, the data can be exported normally. The third value is null, which means that the database cannot export data using select into outfile.

Since the value of database secure_file_priv is null, the data cannot be exported, so you need to re-specify the value of secure_file_priv in the configuration file. Because the data directory of the database is / data2, set / data2 to the export path of select into outfile.

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

Test whether the data can be exported normally.

[root@multi-master2 tmp] # mysql-uebank-pebank-h227.0.0.1mysql > use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > 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.txt2 messi sf agen4 neymar wf brazil6 ramos CB spain8 xavi AMF spain

The data was exported successfully.

3. After the special requirements of the development test the select into outfile export data successfully, the work for DBA has been completed. After telling the developer the result of the database adjustment, the developer still thinks that it does not meet the requirements. Since the developer also has an operating system user ebank on the server in this test environment, the developer needs to set the directory for data export to / home/ebank/data. To comply with the requirements of the development, change the value of secure_file_priv to / home/ebank/data and the owner of the / home/ebank/data directory to mysql. [root@multi-master2 ebank] # chown-R mysql:mysql data/ [root@multi-master2 ebank] # lltotal 4drwxr-xr-x. 2 mysql mysql 4096 Aug 21 03:54 data [root@multi-master2 ebank] # [root@multi-master2 ebank] # pwd/home/ebank

Since there is already a previous test, and in my opinion, there is only a difference on the path between this modification and the previous one, therefore, after changing the value of secure_file_priv to / home/ebank/data in the configuration file, restart the database, and do not manually test the select into outfile export, inform the developer that the data can be exported. However, there was a problem, and the developer still reported that the data could not be successfully exported.

After receiving this feedback, a data export test was conducted manually.

[root@multi-master2 data] # mysql-uebank-pebank-h227.0.0.1mysql > use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > 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

The strange thing is that the data cannot be exported this time. When you see this error, first confirm whether there is something wrong with the configuration of the database.

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@% | 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)

There is no problem with the configuration of the database, and the owner of the previously set / home/mysql/data directory is mysql, so the research on this problem is in a bottleneck.

At a time when I was puzzled, I consulted the boss, and after the boss's guidance, I knew that the key point of the problem was as follows:

[root@multi-master2 data] # cd / home/ [root@multi-master2 home] # lltotal 8drwxMurray. 5 ebank ebank 4096 Aug 21 03:54 ebankdrwx-. 7 mysql mysql 4096 Aug 20 14:34 mysql

At the beginning of creation, the access right of ebank user home directory is 700. the storage path of database exported data is / home/ebank/data. Although the owner of data directory is mysql, because the owner of ebank directory in the upper path is 700, that is, all users except ebank users do not have the right to execute this directory, so there will be an error when exporting data using select into outfile.

For this reason, the problem can be resolved in the following ways:

Change the access to / home/ebank to 701, which means that any user has execute rights to the / home/ebank directory.

[root@multi-master2 home] # lltotal 8drwxmuri. 5 ebank ebank 4096 Aug 21 03:54 ebankdrwx-. 7 mysql mysql 4096 Aug 20 14:34 mysql [root@multi-master2 home] # chmod 701 ebank/ [root@multi-master2 home] # lltotal 8drwx-x. 5 ebank ebank 4096 Aug 21 03:54 ebankdrwx-. 7 mysql mysql 4096 Aug 20 14:34 mysql [root@multi-master2 home] # mysql-uebank-pebank-h227.0.0.1mysql > use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from player into outfile "/ home/ebank/data/player1.txt" Query OK, 4 rows affected (0.01sec) mysql > ^ DBye [root@multi-master2 home] # cat / home/ebank/data/player1.txt2 messi sf agen4 neymar wf brazil6 ramos CB spain8 xavi AMF spain [root@multi-master2 home] #

Through the above configuration, the data is exported successfully.

The alternative to 04 select into outfile

Select into outfile can easily export the data in the table to a csv file, and can filter the required fields according to the requirements. However, sometimes when you need to export multiple tables, and there is no filtering requirement for the fields of the table, it is troublesome to write the exported SQL statements for each table one by one. At this point you can use mysqldump to export the data.

When you use mysqldump to export data to csv format, you need to add a parameter, tab, which specifies the path to which the file is exported. For each table, two files are generated, an txt file that saves the data in the table in csv format, and a sql file that saves the table structure.

# the following statement is to export all tables under the test library [root@multi-master2 data] # mysqldump-- single-transaction-uebank-pebank-h227.0.01-- tab= "/ home/ebank/data" testmysqldump: [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= 0Mustok-GTID state at the beginning of the backup--SET @ @ GLOBAL.GTID_PURGED='8d52b2f3-c316-11e9-8b39-000c29a27f67:1-40 percent set @ @ SESSION.SQL_LOG_BIN= @ MYSQLDUMP_TEMP_LOG_BIN [root@multi-master2 data] # lltotal 16 RW Murray. 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 's article on "sample Analysis of select into outfile problems in mysql" ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.