1

I have a MySQL server running on one PC (WinXP).

On another PC (WinXP), I'd like to backup tables to csv files so I have a c++ program connect to the MySQL database and then I issue a command like this:

SELECT data FROM table WHERE something=ABC
INTO OUTFILE c\tmp.txt
FIELDS TERMINATED BY ';' 
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ";";

the data seems to get exported but on the Servers "c:\" not on my PC:s "c:\". I can't just fetch the data over the LAN either as my program is running as a windows service.

I have seen this post but it seems I can't use "FIELDS TERMINATED BY" etc. with that solution.

Can I export csv data locally from a distant server or do I have to migrate the data locally first?

Problem "solved" : As it seems you can't do it in a 'simple' way, I run the service on the PC with MySQL and have a DCom server periodically move the data to the other PC.

Community
  • 1
  • 1
Valmond
  • 2,897
  • 8
  • 29
  • 49
  • why not back up with mysqldump? why csvs? dumps are just for what you want. Also, this would be much much easier to do in a rich scripting language like python than with C++. – Not_a_Golfer Apr 24 '12 at 09:56
  • I already do that (mysqldump-ing) and it works fine but I also need a "human readable" version. For the python question, I don't have access to scripting languages and I must implement the program as a windows service. – Valmond Apr 24 '12 at 10:10
  • Yeah and the best comes the day the company will start to blend in Windows Seven in it all... – Valmond Apr 24 '12 at 12:48
  • actually there's probably a nice CSV engine in .net, maybe just write the dumper yourself and get it over with? – Not_a_Golfer Apr 24 '12 at 12:54
  • .net is a no goer too, only c++ (and VB6 actually, *shrugs*). – Valmond Apr 25 '12 at 07:51

3 Answers3

3

Intended to be a comment (but I don't have enough rep points to comment). Not sure if you are able to install things, but you could try using cygwin + sqsh (http://www.sqsh.org/sqsh_home.html). I'm a linux user and sqsh is a great tool for grabbing data from databases.

Scottymac
  • 476
  • 6
  • 9
  • Well, I definitely can't install cygwin but even if sqsh can work without it, would it solve my problem and if so how? ps. +1 towards commenting ;-) – Valmond Apr 24 '12 at 12:12
  • @Valmond thx for the +1. Unfortunately, I'm Not aware of a way for you to run sqsh without cygwin :-( and you would have to do some additional formatting of the output to get it into the csv format you desire. Have you seen: http://markalexanderbain.suite101.com/using-a-mysql-databases-with-c-a70097 might be helpful... – Scottymac Apr 24 '12 at 12:41
1

SELECT ... INTO OUTFILE obviously writes the file on the local filesystem of the mysql-demon. One option might be to share a directory of your client-PC, open it on your server and use its path for the outfile-option. If this is not an option you might have to select the data in your c++ program and write it (kind of manually) to a local csv-file.

miherrma
  • 336
  • 1
  • 5
0

use the following command, if you don't want to install extra.

mysql -h remotedb.db -u ident -p -B -e "your query ;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > localfile.csv

-B means make the output as tab separated.

tomriddle_1234
  • 3,145
  • 6
  • 41
  • 71