-1

I have to export the MySQL table data into CSV file for every 5 minutes with same file location and file name. I run the following query to export:

SELECT *  INTO OUTFILE ' c:/newfolder/matrix.csv ' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM tablename

When I export the data, first time it exported correctly, but second time it shows the error "SQL Error(1086) File:' c:/newfolder/matrix.csv ' already exists". I need to overwrite the existing file.

Can any one help to solve this problem?

Laas
  • 5,978
  • 33
  • 52
Arulmurugan
  • 509
  • 1
  • 5
  • 6
  • Not being able to overwrite is a security feature intended to prevent anyone who gains database access from deleting important files. If you want to do this you're likely going to have to make a shell command to do it by using `\! rm 'my_file.csv'`. – scragar Jun 26 '14 at 12:25
  • possible duplicate of [MySQL INTO OUTFILE overide existing file?](http://stackoverflow.com/questions/960627/mysql-into-outfile-overide-existing-file) – vhu Jun 26 '14 at 12:51
  • Backup every 5 minutes? Maybe binary logging with 24-hour backup would be better? (easily use a date-time stamp on the files, and delete extra files as needed to free up space in a script or cron job) PS - If you want to edit your own post, I suggest logging in with the account that made the original post. –  Jun 26 '14 at 12:56

1 Answers1

0

I guess you are using a SELECT ... INTO OUTFILE statement.

According to documentation the file cannot exists or the statement will fail:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file...

Use a file with a mysqldump instead and put in crontab

*/5 * * * * /path/to/your/file/backup.sh
Sal00m
  • 2,938
  • 3
  • 22
  • 33