Using MySQLdump for sub-set of database migration

Question

I've been using both mysql and mysqldump to teach myself how to get data out of one database, but I consider myself a moderate MySQL newbie, just for the record.

What I'd like to do is get a sub-set of one database into a brand new database on a different server, so I need to create both the db/table creation sql as well as populating the data records.

Let's say my original db has 10 tables with 100 rows each. My new database will have 4 of those tables (all original columns), but a further-refined dataset of 40 rows each. Those 40 rows are isolated with some not-so-short SELECT statements, one for each table.

I'd like to produce .sql file(s) that I can call from mysql to load/insert my exported data. How can I generate those sql files? I have HEARD that you can call a select statement from mysqldump, but haven't seen relevant examples with select statements as long as mine.

Right now I can produce sql output that is just the results set with column names, but no insert code, etc.

Assistance is GREATLY appreciated.

Answer 1

You will probably have to use mysqldump to dump your tables one at a time and using the where clause

-w, --where='where-condition'

Dump only selected records. Note that quotes are mandatory:
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

For example:

mysqldump database1 table1 --where='rowid<10'

See docs: http://linux.die.net/man/1/mysqldump

Answer 2

mysqldump each table with a where clause like Dennis said above. one table at a time and merge the scripts with cat

cat customer.db order.db list.db price.db > my_new_db.db