mysql export with partial data

Question

How do I export the structure of a mysql database I have, keeping the structure for all the tables but only exporting the data for some of the tables? After exporting how do I import it into another mysql database on a different machine.

I can have the list of table names if it helps and I would prefer it if the solution will use command line but GUI is also acceptable (on windows).

Thanks

Answer 1

1. To dump only table structures

a. dump

mysqldump -d -u root -p"password" --all-databases > /tmp/dumpfile.sql

b. restore

mysql -u root -p "password" "dbname" < /tmp/dumpfile.sql

2. To dump only data not structure

a. dump

mysqldump -uroot -p"password" --no-create-info "Db" "TableName"> /tmp/dumpfile.sql

b. restore

mysql -u root -p password "dbname" < /tmp/dumpfile.sql

3. To dump inserts only for specific Columns

a.dump

mysqldump -t -uroot -p"pawword" "Db" "TableName" --where ="Columnname in (1,2)" > /tmp/dumpfile.sql

b. Restore

mysql -u root -p password "dbname" < /tmp/dumpfile

• Yann39: 3. should be "for specific rows" or "for specific column values" but unfortunately there is no way to exclude entire columns (for example exclude BLOB columns which would be very useful).

Answer 2

Familiarize yourself with mysqldump command options, you will need to execute two sets of mysqldump backups, one for table structure only without data (--no-data) and another one that includes data for selected tables only.