Error 1290 hy000 the mysql server is running with the secure file priv option

На чтение 2 мин Просмотров 1.8к. Опубликовано 06.05.2021

При запуске mysqld сервера вы можете указать параметры программы в файле параметров или в командной строке. Эти параметры предназначены для разблокировки других функций MySQL, изменения переменных или наложения ограничений.

Вот как параметры читаются на сервере MySQL:

• mysqld считывает параметры из [mysqld] и [server] групп
• mysqld_safe считывает параметры из [mysqld], [server],[mysqld_safe]и [safe_mysqld] групп
• mysql.server считывает параметры из [mysqld] и [mysql.server] групп.

Вы можете увидеть краткую сводку опций, поддерживаемых MySQL, используя:

Чтобы увидеть полный список, используйте команду:

$ mysqld —verbose —help

Одна из тех системных переменных, которые можно установить при запуске сервера, это mysqld_secure-file-priv

Содержание

1. Что такое переменная mysqld_secure-file-priv?
2. Изменение каталога переменных secure-file-priv
3. Переменная Diable secure-file-priv

Что такое переменная mysqld_secure-file-priv?

Переменная secure_file_privиспользуется для ограничения эффекта операций импорта и экспорта данных. Пример пораженных операций, выполняемый  LOAD DATA и SELECT ... INTO OUTFILE отчетность и функция LOAD_FILE(). Эти операции разрешены только пользователям, имеющим такую  FILE привилегию.

Чтобы увидеть текущую настройку во время выполнения, используйте SHOW VARIABLESинструкцию.

Войдите в оболочку MySQL как пользователь root

Запускаем

mysql> SHOW VARIABLES LIKE «secure_file_priv»; +—————————+————————————+ | Variable_name    | Value                 | +—————————+————————————+ | secure_file_priv | /var/lib/mysql—files/ | +—————————+————————————+ 1 row in set Time: 0.023s

Вы можете видеть, что набор каталогов /var/lib/mysql-files/

Изменение каталога переменных secure-file-priv

Это значение можно изменить в файле опций MySQL в разделе [mysqld] .

Установите переменную в [mysqld] разделе

[mysqld] secure—file—priv=/mysqlfiles

Затем создайте настроенный каталог

sudo mkdir /mysqlfiles sudo chown —R mysql:mysql  /mysqlfiles/

Перезапустите службу MySQL, чтобы изменения вступили в силу.

sudo systemctl restart mysqld

Войдите снова, чтобы подтвердить новую настройку

mysql> SHOW VARIABLES LIKE «secure_file_priv»; +—————————+———————+ | Variable_name    | Value        | +—————————+———————+ | secure_file_priv | /mysqlfiles/ | +—————————+———————+ 1 row in set (0.00 sec)

Давайте протестируем, чтобы подтвердить, что мы можем экспортировать по указанному пути.

mysql> SELECT * FROM information_schema.processlist into outfile ‘/tmp/mysql_processes.txt’; ERROR 1290 (HY000): The MySQL server is running with the —secure—file—priv option so it cannot execute this statement

Попробуем еще раз написать правильный путь.

mysql> SELECT * FROM information_schema.processlist into outfile ‘/mysqlfiles/mysql_processes.txt’; Query OK, 1 row affected (0.00 sec)

Переменная Diable secure-file-priv

Чтобы отключить его, установите для переменной значение NULL.

[mysqld] secure—file—priv = «»

Перезапуск службы mysqld

sudo systemctl restart mysqld

Подтвердить после перезапуска службы

mysql> SHOW VARIABLES LIKE «secure_file_priv»; +—————————+————+ | Variable_name    | Value | +—————————+————+ | secure_file_priv |       | +—————————+————+ 1 row in set (0.00 sec)

Попробуйте сохранить содержимое QUERY в другую директорию

mysql> SELECT * FROM information_schema.processlist into outfile ‘/tmp/mysql_processes.txt’; Query OK, 1 row affected (0.00 sec)

Все, спасибо за внимание, теперь вы научились настраивать переменную secure-file-priv

When you update your site with new data from a file, you may see the error “Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”.

Why did it happen all of a sudden? Is your data corrupted?

Not to worry. This is quite easy to fix and usually happens after a MySQL upgrade.

As part of our Server Management Services, we help server owners and webmasters resolve MySQL errors such as this.

Today we will help you fix error code 1290 in a few easy steps.

Why this MySQL error code 1290 appears?

From version 5.7.6 onwards MySQL introduced a new security feature.

This will prevent website attackers from loading malicious data directly into your database.

It is done by restricting the databases to load data from only a single special directory in the server.

That directory is accessible only by the server administrator, and therefore attackers won’t be able to load malware.

By default this directory will be “/var/lib/mysql-files/” in Linux servers.

So, if you have a program or script that loads site data from a different location, MySQL will think that it’s an unauthorized upload attempt, and block it.

Is there any solution?

We have two solutions for your problem with code 1290.

• One is moving the data file into the directory
• The other is reconfiguring and restarting MySQL server

Today, let’s discuss these in detail.

Fixing the error by moving the data file into the directory

Recently, one of our customers using Linux approached us with the error code 1290.

He said he was able to load files without failure before. But from the last few days, he is facing this error.

When checked, we found that he recently upgraded his MySQL to 5.7.6 and the default path is restricting the upload.

Let’s see how we fix this.

We asked him to use the query to determine where –secure-file-priv option is.

SHOW VARIABLES LIKE 'secure_file_priv'

This returned a variable along with its value as:

+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

We asked the customer to use  /var/lib/mysql-files/ as the file path to run LOAD DATA as this is the configured directory and he needs to move the data file here.

Finally, suggested running the LOAD DATA again with the full path.

This fixed the error.

For Windows Users

In Windows, we do the following for fixing the MySQL error 1290

We look for the file being used when the service starts at MySQL57 Windows service.

From there, we get a path similar to C:ProgramDataMySQLMySQL Server 5.7my.ini.

Open the file and under [mysqld] section we can see secure-file-priv along with value as shown:

secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.7/Uploads”

This is the path we need to use. So, reissue the LOAD DATA statement with this right path for fixing the error.

Fixing the MySQL error 1290 by reconfiguring and restarting

In some cases, changing the directory won’t solve the issue. In such a case we need to do the following:

For Windows Users

• Go to start menu and type services.msc then press Ctrl+Shift+Enter to run it as an administrator.
• Locate the MySQL service and double-click to open its properties dialog.
• Check the Path to Executable for the –defaults-file option to determine where my.ini file is located.
• In windows, C:ProgramDataMySQLMySQL Server 5.7my.ini is the default file being used when the service starts.

Note: It may vary according to various versions

• Stop the service then close the dialog.
• From the start menu, take notepad then press Ctrl+Shift+Enter to run it as an administrator.
• Then open my.ini file previously located in notepad and search the file for secure-file-priv.
• finally, comment out the line by adding a # in front.

For Linux Users

In Linux, we suggest searching for either of the most common locations: /etc/my.cnf or /etc/mysql/my.cnf.

Search the file for secure-file-priv. We get it along with the value. Comment the line and restart the service.

This will fix the issue.

But, we won’t suggest this as it is not secure.

The secure way is always by moving the data file into the directory.

[Need assistance in fixing MySQL errors? Click here to talk to our experts.]

Conclusion

In short, we discussed in detail on MySQL error code 1290 and saw how our Support Engineers find fix for this in different scenarios.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

How to fix —secure-file-priv option error

Posted on Sep 12, 2021

Learn how to fix —secure-file-priv option error and run import/ export data statements

MySQL database provides you with queries to import and export data using the LOAD DATA and SELECT INTO FILE statements.

For example, suppose you have a students table in your database as follows:

When you want to export the table as a text file, you can use the SELECT INTO FILE statement as shown below:

The SQL statement above should produce the file out.txt in the tmp/ directory as a result of the query execution.

However, MySQL frequently throws the following error when you run a data import or export statement:

The —secure-file-priv option is a system variable used by MySQL to limit the ability of the users to export or import data from the database server.

The option is added to your MySQL database as a global variable named secure_file_priv , so you can check the current value of the option using the SHOW VARIABLES statement.

Here’s an example of retrieving the secure_file_priv value:

The secure_file_priv option can have three possible values:

• NULL value means the data export or import is disabled
• Empty value means data export or import is enabled
• Directory path value means data export or import is enabled only in the specified path

The secure_file_priv value is a read-only value, so you can’t change it directly using SQL query.

The following statement tries to change the secure_file_priv value to tmp/ folder:

The response would be as follows:

To change the value of secure_file_priv variable, you need to create a MySQL configuration file that sets the value of the variable under [mysqld] options.

You need to put the following content in your my.cnf (Mac, Linux) or my.ini (Windows) file:

Once you edit the configuration file, save it and restart your MySQL server. You should be able to import or export data using MySQL LOAD DATA and SELECT INTO FILE statements.

Here’s an example of a successful data export query:

Now the result of the SELECT statement above is saved as out.txt file.

You can import the same text file back into an SQL table by using the LOAD DATA statement.

First, create a copy of your table using CREATE TABLE . LIKE statement as shown below:

Then, load the data from the out.txt file with the following SQL query:

The query above will put the out.txt data into the students_copy table.

What to do when you have no configuration file

Sometimes, your MySQL installation didn’t come with a config file.

This happens to me when I install the official MySQL community edition for Mac OS, which doesn’t add a default my.cnf file to start the server.

In that case, you should create your own configuration file in a location that will be picked up by MySQL.

MySQL will look in the following locations for a .cnf file:

• /etc/my.cnf
• /etc/mysql/my.cnf
• /usr/local/mysql/etc/my.cnf

Or if you’re using Windows:

• C:Windowsmy.ini
• C:Windowsmy.cnf
• C:my.ini
• C:my.cnf
• C:Program FilesMySQLMySQL Server x.xmy.ini
• C:Program FilesMySQLMySQL Server x.xmy.cnf

As for me, I created a new

/.my.cnf file in my Mac machine with the following command:

The content of the .my.cnf file is as follows:

Save all changes and exit the Vim session by pressing semicolon : + wq and then hit Enter .

That will be enough to change the value of the secure_file_priv variable on your computer.

And that’s how you fix the —secure-file-priv option error and allow data import/ export using SQL query. Nicely done! 👍

Level up your programming skills

I’m sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I’ll send new stuff straight into your inbox!

About

Nathan Sebhastian is a software engineer with a passion for writing tech tutorials.
Learn JavaScript and other web development technology concepts through easy-to-understand explanations written in plain English.

Источник

Как решить проблему “MySQL server is running with the –secure-file-priv” Error

При запуске mysqld сервера вы можете указать параметры программы в файле параметров или в командной строке. Эти параметры предназначены для разблокировки других функций MySQL, изменения переменных или наложения ограничений.

Вот как параметры читаются на сервере MySQL:

• mysqld считывает параметры из [mysqld] и [server] групп
• mysqld_safe считывает параметры из [mysqld] , [server] , [mysqld_safe] и [safe_mysqld] групп
• mysql.server c читывает параметры из [mysqld] и [mysql.server] групп.

Вы можете увидеть краткую сводку опций, поддерживаемых MySQL, используя:

Чтобы увидеть полный список, используйте команду:

Одна из тех системных переменных, которые можно установить при запуске сервера, это mysqld_secure-file-priv

Что такое переменная mysqld_secure-file-priv?

Переменная secure_file_priv используется для ограничения эффекта операций импорта и экспорта данных. Пример пораженных операций, выполняемый LOAD DATA и SELECT . INTO OUTFILE отчетность и функция LOAD_FILE() . Эти операции разрешены только пользователям, имеющим такое FILE право.

Чтобы увидеть текущую настройку во время выполнения, используйте SHOW VARIABLES инструкцию.

Войдите в оболочку MySQL как пользователь root

Вы можете видеть, что набор каталогов /var/lib/mysql-files/

Изменение каталога переменных secure-file-priv

Это значение можно изменить в файле опций MySQL в разделе [mysqld] .

Установите переменную в [mysqld] разделе

Затем создайте настроенный каталог

Перезапустите службу MySQL, чтобы изменения вступили в силу.

Войдите снова, чтобы подтвердить новую настройку

Давайте протестируем, чтобы подтвердить, что мы можем экспортировать по указанному пути.

Попробуем еще раз написать правильный путь.

Переменная Diable secure-file-priv

Чтобы отключить его, установите для переменной значение NULL.

Перезапуск службы mysqld

Подтвердить после перезапуска службы

Попробуйте сохранить содержимое QUERY по другому пути

Это было успешно. Вы научились настраивать secure-file-priv переменную в соответствии со своим вариантом использования. До следующего раза, благодарим за использование нашего руководства для решения проблемы « Сервер MySQL работает с ошибкой –secure-file-priv » при попытке загрузить или сохранить данные.

Leave a Reply Отменить ответ

Privacy Overview

Этот веб-сайт использует файлы cookie, чтобы улучшить вашу работу во время навигации по веб-сайту. Из них файлы cookie, которые классифицируются как необходимые, хранятся в вашем браузере, поскольку они необходимы для работы основных функций веб-сайта. Мы также используем сторонние файлы cookie, которые помогают нам анализировать и понимать, как вы используете этот веб-сайт. Эти файлы cookie будут храниться в вашем браузере только с вашего согласия. У вас также есть возможность отказаться от этих файлов cookie. Но отказ от некоторых из этих файлов cookie может повлиять на ваш опыт просмотра.

Источник

MySQL error code 1290 – How to fix

When you update your site with new data from a file, you may see the error “Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”.

Why did it happen all of a sudden? Is your data corrupted?

Not to worry. This is quite easy to fix and usually happens after a MySQL upgrade.

As part of our Server Management Services, we help server owners and webmasters resolve MySQL errors such as this.

Today we will help you fix error code 1290 in a few easy steps.

Why this MySQL error code 1290 appears?

From version 5.7.6 onwards MySQL introduced a new security feature.

This will prevent website attackers from loading malicious data directly into your database.

It is done by restricting the databases to load data from only a single special directory in the server.

That directory is accessible only by the server administrator, and therefore attackers won’t be able to load malware.

By default this directory will be “/var/lib/mysql-files/” in Linux servers.

So, if you have a program or script that loads site data from a different location, MySQL will think that it’s an unauthorized upload attempt, and block it.

Is there any solution?

We have two solutions for your problem with code 1290.

• One is moving the data file into the directory
• The other is reconfiguring and restarting MySQL server

Today, let’s discuss these in detail.

Fixing the error by moving the data file into the directory

Recently, one of our customers using Linux approached us with the error code 1290.

He said he was able to load files without failure before. But from the last few days, he is facing this error.

When checked, we found that he recently upgraded his MySQL to 5.7.6 and the default path is restricting the upload.

Let’s see how we fix this.

We asked him to use the query to determine where –secure-file-priv option is.

This returned a variable along with its value as:

We asked the customer to use /var/lib/mysql-files/ as the file path to run LOAD DATA as this is the configured directory and he needs to move the data file here.

Finally, suggested running the LOAD DATA again with the full path.

This fixed the error.

For Windows Users

In Windows, we do the following for fixing the MySQL error 1290

We look for the file being used when the service starts at MySQL57 Windows service.

From there, we get a path similar to C:ProgramDataMySQLMySQL Server 5.7my.ini.

Open the file and under [mysqld] section we can see secure-file-priv along with value as shown:

secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.7/Uploads”

This is the path we need to use. So, reissue the LOAD DATA statement with this right path for fixing the error.

Fixing the MySQL error 1290 by reconfiguring and restarting

In some cases, changing the directory won’t solve the issue. In such a case we need to do the following:

For Windows Users

• Go to start menu and type services.msc then press Ctrl+Shift+Enter to run it as an administrator.
• Locate the MySQL service and double-click to open its properties dialog.
• Check the Path to Executable for the –defaults-file option to determine where my.ini file is located.
• In windows, C:ProgramDataMySQLMySQL Server 5.7my.ini is the default file being used when the service starts.

Note: It may vary according to various versions

• Stop the service then close the dialog.
• From the start menu, take notepad then press Ctrl+Shift+Enter to run it as an administrator.
• Then open my.ini file previously located in notepad and search the file for secure-file-priv.
• finally, comment out the line by adding a # in front.

For Linux Users

In Linux, we suggest searching for either of the most common locations: /etc/my.cnf or /etc/mysql/my.cnf.

Search the file for secure-file-priv. We get it along with the value. Comment the line and restart the service.

This will fix the issue.

But, we won’t suggest this as it is not secure.

The secure way is always by moving the data file into the directory.

[Need assistance in fixing MySQL errors? Click here to talk to our experts.]

Conclusion

In short, we discussed in detail on MySQL error code 1290 and saw how our Support Engineers find fix for this in different scenarios.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

The MySQL server is running with the — secure-file-priv option so it cannot execute this statement ERROR 1290 (HY000)

The other day I had to export some data from a client database and have received this error on his Windows 7 machine.
These are the commands I executed.

use my_app_db;
SELECT * FROM customers WHERE id > 1000 ORDER BY `last_name` INTO OUTFILE ‘/customers.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ LINES TERMINATED BY ‘n’;
ERROR 1290 (HY000): The MySQL server is running with the — secure-file-priv option so it cannot execute this statement

It seems after a certain version (5.5?) there’s a setting the MySQL ini file called secure-file-priv which restricts where the exported file can be saved.
secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.7/Uploads”

I’ve tried editing the my.ini file from C:/ProgramData/MySQL/MySQL Server 5.7/ but the file was locked. I stopped the mysql service and the mysqld program from services and task manager respectively.
I still wasn’t able to comment out the above mentioned line. Since this was one time export I didn’t want to spend too much time on this.

The solution

Save the file in white listed folder. Make sure you use the same slashes.

Connect to the mysql database and enter the root password
“C:Program FilesMySQLMySQL Server 5.7binmysql” -uroot -p

use my_app_db;
SELECT * FROM customers WHERE id > 1000 ORDER BY `last_name` INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/customers.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ LINES TERMINATED BY ‘n’;

The code above will export the data but won’t export the heading columns which is weird.

Источник

error «—secure-file-priv option» when save selection to csv

I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server

I execute query as root , but I think there is still some problem with privileges

Error Code: 1290. The MySQL server is running with the —secure-file-priv option so it cannot execute this statement

Thank you in advance for any help !

6 Answers 6

I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:

When I run above commands, I got

«ERROR 1290 (HY000): The MySQL server is running with the —secure-file-priv option so it cannot execute this statement»

I tried many methods to solve the problem. Finally, I changed the ‘ ‘ characters in the file path to ‘ / ‘ characters.

I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is » C:/ProgramData/MySQL/MySQL Server 8.0/Uploads «. So, this solution makes sense.

On Win2012 I found file my.ini.

In the file there is a definition of secure_file_priv — a folder when I can download to or uplload from ,

So I change my query as following:

Following Mehmet’s answer, for me escaping the «» character did the trick.

C:ProgramDataMySQLMySQL Server 5.7Uploadsfile.csv

C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\file.csv

I have also had these issues and did some experimenting, using the different answers I found on the Internet. I have gotten this to work on my local WAMP (3.1.7) server on a Windows 10 63-bit PC. I did this within PHP, logged on as root administrator.

First I simply nullified the —secure-file-priv problem by entering secure-file-priv = «» under the [mysqld] heading in my.ini .

Secondly I created an SQL query and executed it:

This works perfectly for me. Now I am working on how to save the file on the web hotel server, using the same changes and syntax.

Let’s assume you want to store the output in a subdirectory to make it easier to manage e.g. ./my_script_data/ inside the secure folder, but you might run this script on multiple servers, possibly with different secure directories.

Firstly, for each server, set the secure_file_priv to the most appropriate directory, perhaps on another drive e.g. X:/mysql_secure . Do this by modifying the my.ini file (create a backup first), searching for secure-file-priv and providing an absolute path:
secure-file-priv=»X:/mysql_secure»

NOTE: Make sure to save your file in the same encoding that it was originally written in (usually UTF-8). If you do not, the MySQLxx service will not start.

In your SQL script, store your output directory in a variable, using CONCAT() to append other file paths as needed (they will have to be created manually before executing, unfortunately). That means the following path must already exist: X:/mysql_secure/my_script_data/

To create a timestamped CSV file with your output use the following:

This produces a file: X:/mysql_secure/my_script_data/my_file_prefix_2021-05-24_190827_suffix.csv .

I omitted field terminations, enclosures, and line terminations because they were the default values. These could be inserted into the @statement variable if needed.

This was tested using Windows 10 and Windows Server 2016, both running MySQL 8.0.x.

Источник