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).