file not found after mysql export

Question

i need to export data from a table to a csv. i have the following structure (not really my table but for demo purposes)

CREATE TABLE `mytable` (
  `id` int(11) DEFAULT NULL,
  `mycolumn` varchar(25) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

with data (about 3000 records). now i want to export some of these records (from a script i run via cronjob)

SELECT * INTO OUTFILE '/tmp/mytable.sql'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM mytable;

it shows:

Query OK, 3 rows affected (0.00 sec)

then i do:

ls: cannot access /tmp/mytable.sql: No such file or directory

where is my file?

Azores

Is there other script running that wipes out the /tmp folder? – Wits

Answer 1

When you use the INTO OUTFILE command, it export the data to the local folder of the server not the one you are executing the query.

Example: you are on your computer (ip: 192.168.0.100) and you connect to the mysqlserver (ip: 192.168.0.101) using the mysql command: mysql -uuser -h192.168.0.101 -A database. By executing the SELECT * INTO OUTFILE the file is saved on the mysqlserver (ip: 192.168.0.101) and NOT on your computer (ip: 192.168.0.100).

Now, you can use a script that creates a CSV file (in your cronjob - you select all the data, generate the file and send via scp to the other server).

Or - You can also have a NFS mounted on /shared/ and when you create the file automatically the other server has it.

Or - you can simply run a mysql command in a bash script like this from your first server.

mysql -uroot test -B -e "select * from test.mytable;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/filename.csv

source: http://tlug.dnho.net/node/209

Malvaceous

the NFS is a good solution if you have to generate a lot of data (like lots of files). the "generate a csv file" can be very easy to build and can have customization to it (like params etc...) the command line, well very easy and fast but you have to be careful of characters like double quotes, that might be a little hard to debug (with the SED command). – Malvaceous

i see the file on the mysql server now, i would never guess thanks for your help, i think ill go with the generate files to csv. – Azores