Page 1 of 1

MySQL dump certain query/queries then load to remote server

Posted: Tue Oct 20, 2009 5:42 am
by batfastad
Hi everyone
I'm working on a project where a company wants to make a selection of its data available to website visitors.
They have their own corporate intranet MySQL DB.

Is there any way I can just export a single query or selection of queries to a dump file?
Obviously mysqldump exports the whole lot - is there anything that can take a .sql query file and dump only those records returned by the query?
I could just export it myself in PHP but didn't want to start doing that if there's a tool that alreaedy exists for this kind of thing.

My idea was to set up a cron job to dump those queries overnight every night, upload them using scp then trigger a shell script to truncate the remote tables and insert the new data.

If I moved their whole intranet DB out onto this remote MySQL server, the intranet users would see a slight slowdown when searching/updating their data because of latency. So doing dump of only selected data sounds like the best plan.

Any ideas/suggestions?

Cheers, B

Re: MySQL dump certain query/queries then load to remote server

Posted: Thu Oct 29, 2009 4:45 pm
by batfastad
Anyone got any suggestions?
I'm not particularly keen on writing my own dump script. Guest it wouldn't be too horrible but if there's something out there that someone knows already then I'd appreciate a heads up!

Cheers, B

Re: MySQL dump certain query/queries then load to remote server

Posted: Thu Oct 29, 2009 5:45 pm
by Eran
You can dump only specific rows by using the 'where' option for mysqldump.
http://dev.mysql.com/doc/refman/5.1/en/ ... dump_where

Re: MySQL dump certain query/queries then load to remote server

Posted: Thu Oct 29, 2009 7:54 pm
by VladSun
http://dev.mysql.com/doc/refman/5.0/en/select.html
Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

Re: MySQL dump certain query/queries then load to remote server

Posted: Fri Oct 30, 2009 12:31 pm
by batfastad
Hi guys
That's perfect! The where option of mysqldump should do nicely!
Thanks for the tips :D