HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

mysql export with partial data

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withmysqlexportpartialdata

Problem

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

Solution


  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


  1. 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


  1. 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

Code Snippets

mysqldump -d -u root -p"password" --all-databases > /tmp/dumpfile.sql
mysql -u root -p "password" "dbname" < /tmp/dumpfile.sql
mysqldump -uroot -p"password" --no-create-info "Db" "TableName"> /tmp/dumpfile.sql
mysql -u root -p password "dbname" < /tmp/dumpfile.sql
mysqldump -t -uroot -p"pawword" "Db" "TableName" --where =”Columnname in (1,2)” > /tmp/dumpfile.sql

Context

StackExchange Database Administrators Q#148125, answer score: 4

Revisions (0)

No revisions yet.