MySQL dump certain query/queries then load to remote server

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL dump certain query/queries then load to remote server

Post 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
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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;
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post by batfastad »

Hi guys
That's perfect! The where option of mysqldump should do nicely!
Thanks for the tips :D
Post Reply