Using MySQLdump for sub-set of database migration
import, mysql, select
Solution
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
Problem
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.