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
MySQL dump certain query/queries then load to remote server
Moderator: General Moderators
Re: MySQL dump certain query/queries then load to remote server
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
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
You can dump only specific rows by using the 'where' option for mysqldump.
http://dev.mysql.com/doc/refman/5.1/en/ ... dump_where
http://dev.mysql.com/doc/refman/5.1/en/ ... dump_where
Re: MySQL dump certain query/queries then load to remote server
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
Re: MySQL dump certain query/queries then load to remote server
Hi guys
That's perfect! The where option of mysqldump should do nicely!
Thanks for the tips
That's perfect! The where option of mysqldump should do nicely!
Thanks for the tips