0

I have written a test.sh code which fetches data from MySQL server. The version used is 5.6.36.

The command used to export data from MYSQL is as below:

cmd="mysqldump -h $DB_HOST --port=<> -u $DB_USER -p$DB_PASS --single-transaction  --skip-lock-tables --fields-enclosed-by=\\\" --fields-terminated-by=, -T$TARGET_PATH/$dt/$DB_SCHEMA  $DB_SCHEMA ${ary[$TABLE]}  --where=\"date($DATE_FILED)='$prevdt'\""

The codes fails to fetch data from MySql and throws error as below:

Got error:

1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

I did some checks based on the links provided for the similar issue.

The secure_file_priv is set to this path

SELECT @@global.secure_file_priv;

Output is : /var/mysql/dump/

SHOW VARIABLES LIKE "secure_file_priv";

Output is : /var/mysql/dump/

Checked the config at /etc/my.cnf path too .

Since the path is already set for import and export into .csv file from MYSQL , we are not suppose to make any changes to this my.cnf file. As many have mentioned to change the config and disable using the setting as : secure_file_priv = ""( But I did not want to disable the setting).

James Z
  • 11,838
  • 10
  • 25
  • 41
Neha0908
  • 301
  • 1
  • 13
  • Possible duplicate of [How should I tackle --secure-file-priv in MySQL?](https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql) – Nick Mar 06 '19 at 11:36
  • @Nick: The link mentioned sets to disable the secure_file_priv option which is not correct in my case. Moreover files to be saved from exporting the data from MYSQL is already pointing to the directory specified in secure-file-priv. – Neha0908 Mar 06 '19 at 11:59
  • Is it pointing to the exact directory? I seem to recall reading somewhere that you can't use subdirectories of the secure_file_priv directory. – Nick Mar 06 '19 at 12:04
  • Its pointing to exact directory and creates dated subdirectory under which it stores the files. /var/mysql/dump// – Neha0908 Mar 06 '19 at 12:11
  • Moreover it is not that the command is failing everytime but yes whenever it fails the error is same as of --secure_file_priv – Neha0908 Mar 06 '19 at 12:13

0 Answers0