Selective Export of MySQL database

Question

I have a webapp that spans many different users, each with selective permissions about what they are able to see. The app is built on top of a MySQL database.

One feature I am interested in providing to my "power users" is a sql dump of all their data so that they can run off and do their own things with it. Now I can't just use mysqldump because there are things belonging to other users that should not be made available to anybody else on download.

Is there any other easy way to get data in and out of MySQL that allows you to selectively specify what to export, and without having to jump through all kinds of hoops? Note that I need control at the query level - i.e. being able to specify a list of tables is NOT sufficient. In an ideal world, such a tool would automatically find all relationships based on traversal of foreign keys, but if I have to write queries at the table-level I'm willing to, provided it's easy for others to get the data back into mysql without too much trouble.

Anyone know if such a tool exists, or if I am in "roll my own" territory?

Answer 1

Mysqldump does have a "--where" flag that you can use to selectively return rows. I think you should be able to do something like:

mysqldump --where="foreign_key_id=5"

Which should return only those specific rows, more documentation on the MySQL Site

However, I'm not sure you wouldn't be further ahead to do an export as comma separated value files. CSV files can be imported back into MySQL as well as give your users many other options for ways to work with their data (spreadsheets, other RDBMS, text analysis).