MySQL INTO OUTFILE override existing file?

No, there's no way to overwrite it. From the docs:

file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

It might be a better idea to use a different filename each night, as having multiple backups means you can recover from problems that have existed for more than a day. You could then maintain a symlink that always points at the latest complete csv.

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

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

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

There is no way.

Only one possible you can procedure with dynamic statement.

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; 

For a job like this I would place it into a bash file, delete the file

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

The better option is to actually add a timestamp though. Disk space isn't expensive in this day and age.