tag:blogger.com,1999:blog-865923359735383241.post1389666661751295910..comments2023-10-29T07:27:09.012-06:00Comments on Ccna final exam - java, php, javascript, ios, cshap all in one: MySQL INTO OUTFILE overide existing file?Unknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-865923359735383241.post-22227687611252092012-04-12T03:16:58.837-06:002012-04-12T03:16:58.837-06:00these exports where created using 'SELECT * IN...these exports where created using 'SELECT * INTO OUTFILE.....'<br /><br />mysql> \! ls -l /tmp/rooster*.sql<br />-rw-rw-rw- 1 mysql mysql 371960 Mar 21 13:55 /tmp/rooster1.sql<br />-rw-rw-rw- 1 mysql mysql 371960 Mar 21 13:55 /tmp/rooster2.sql<br /><br /><br />but when i'm trying to delete the file:<br /><br />mysql> \! rm -f /tmp/rooster*.sql<br />rm: cannot remove `/tmp/rooster1.sql': Operation not permitted<br />rm: cannot remove `/tmp/rooster2.sql': Operation not permittedUserhttps://www.blogger.com/profile/11557173689529910046noreply@blogger.comtag:blogger.com,1999:blog-865923359735383241.post-3395631713697030292012-04-12T03:16:57.930-06:002012-04-12T03:16:57.930-06:00Why not rm -f /home/sites/example.com/www/files/ba...Why not rm -f /home/sites/example.com/www/files/backup.csv in the script ran by cron?<br /><br />You can run this from inside mysql. Just escape to the shell with '!'<br />For example:<br /><br />Mysql> \! rm -f /home/sites/example.com/www/files/backup.csvUserhttps://www.blogger.com/profile/11557173689529910046noreply@blogger.comtag:blogger.com,1999:blog-865923359735383241.post-28085709045553017832012-04-12T03:16:57.292-06:002012-04-12T03:16:57.292-06:00Kind of a dated question, but hopefully this will ...Kind of a dated question, but hopefully this will help someone.<br /><br />Simply escape to a shell from within mysql and execute a rm command to remove the file before you attempt to write it. For example:<br /><br />Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv<br /><br />Enjoy!Userhttps://www.blogger.com/profile/11557173689529910046noreply@blogger.comtag:blogger.com,1999:blog-865923359735383241.post-29250354339854464832012-04-12T03:16:54.367-06:002012-04-12T03:16:54.367-06:00There is no way.
Only one possible you can procedu...There is no way.<br />Only one possible you can procedure with dynamic statement.<br /><br />CREATE PROCEDURE export_dynamic(IN file_name char(64)) <br />BEGIN <br />set @myvar = concat('SELECT * INTO OUTFILE ',"'",file_name,"'",' FROM Table1') ; <br />PREPARE stmt1 FROM @myvar; <br />EXECUTE stmt1; <br />Deallocate prepare stmt1; <br />END; <br /><br />Regards,<br />Mani B<br />Pace Automation<br />ChennaiUserhttps://www.blogger.com/profile/11557173689529910046noreply@blogger.comtag:blogger.com,1999:blog-865923359735383241.post-14023049042177141972012-04-12T03:16:51.809-06:002012-04-12T03:16:51.809-06:00For a job like this I would place it into a bash f...For a job like this I would place it into a bash file, delete the file<br /><br />#!/bin/bash<br />rm /path/to/backup.csv<br />./backup_sql_query.sh <<-- This contains the script to backup to CSV.<br /><br /><br />The better option is to actually add a timestamp though. Disk space isn't expensive in this day and age.Userhttps://www.blogger.com/profile/11557173689529910046noreply@blogger.comtag:blogger.com,1999:blog-865923359735383241.post-5821608009625291152012-04-12T03:16:50.848-06:002012-04-12T03:16:50.848-06:00No, there's no way to overwrite it. From the d...No, there's no way to overwrite it. From the docs:<br /><br /><br /> file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.<br /><br /><br />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.Userhttps://www.blogger.com/profile/11557173689529910046noreply@blogger.com