MySQL INTO OUTFILE overrides existing file?

Question

I wrote a big sql script that creates a CSV file. I want to call cronjob every day to create a new CSV file and make it available on the website.

Say, for example, I store my file in the /home/sites/example.com/www/files/backup.csv folder

and my SQL

SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM ( .... 

MySQL gives me an error when a file already exists

The file '/home/sites/example.com/www/files/backup.csv' already exists

Is there a way to get MySQL to overwrite the file?

I could have PHP detect if the file exists and delete it before creating it again, but it would be more concise if I can do it directly in MySQL.

Answer 1

No, there is no way to rewrite it. From docs :

file_name cannot be an existing file, which, among other things, prevents the destruction of files such as / etc / passwd and database tables.

It is best to use a different file name every night, as having multiple backups means that you can recover from problems that existed for more than one day. Then you can save a symbolic link that always points to the latest full version of csv.

Answer 2

Why not rm -f /home/sites/example.com/www/files/backup.csv in a script run by cron?

You can run this from inside mysql. Just exit the shell with \! For example:

Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv

Answer 3

For such a job, I would put it in a bash file, deleting the file

 #!/bin/bash rm /path/to/backup.csv ./backup_sql_query.sh <<-- This contains the script to backup to CSV. 

The best option is to actually add a timestamp. Disk space on this day and age is not expensive.

Answer 4

There is no way.

Only one possible procedure with dynamic instruction.

 CREATE PROCEDURE export_dynamic(IN file_name char(64)) BEGIN set @myvar = concat('SELECT * INTO OUTFILE ',"'",file_name,"'",' FROM Table1') ; PREPARE stmt1 FROM @myvar; EXECUTE stmt1; Deallocate prepare stmt1; END; 

Answer 5

Kind of a dated question, but hopefully this helps someone.

Just exit the shell from mysql and run the rm command to delete the file before trying to write it. For example:

Mysql> \! rm -f / home / sites / example.com / www / files / backup.csv

Enjoy it!

Answer 6

3 steps to do it right. Your backup will be performed every night while you sleep (or not)

STEP 1. Create a stored procedure for SQL

 CREATE PROCEDURE backupCSV() SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM ( .... 

STEP 2. Create a script "/home/backupCSV.sh" to delete the old file and call the stored procedure

 echo "$(date +"%Y-%m-%d %T") START MAINTENANCE SCRIPT " rm /home/sites/example.com/www/files/backup.csv echo "$(date +"%Y-%m-%d %T") SUCCESS >> Old file deleted" mysql --user=[user] --password=[password] [dataBaseName] --execute="CALL backupCSV();" echo "$(date +"%Y-%m-%d %T") SUCCESS >> New file created" echo "$(date +"%Y-%m-%d %T") END MAINTENANCE SCRIPT " 

STEP 3: Update Crontab to Run a Script Every Day

 # mh dom mon dow user command 0 3 * * * root sh -x /home/backupCSV.sh 

STEP 4 (optionnal): thanks to me;)