Monday, June 18, 2012

MySQL: Selectively dumping data with mysqldump

By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump


Examples: db= test   &  table = mytable
 
columns: mytable_id, category_id and name



Using mysqldump to dump all data from the table would look like this, subsituting [username] for your username (the -t flag suppresses the table creation sql from the dump):
mysqldump -t -u [username] -p test mytable
The output from my example table looks like this, once we remove all the extra SQL commands (I've added linebreaks to make it more legible):
INSERT INTO `mytable` VALUES 
  (1,1,'Lorem ipsum dolor sit amet'),
  (2,1,'Ut purus est'),
  (3,2,'Leo sed condimentum semper'),
  (4,2,'Donec velit neque'),
  (5,3,'Maecenas ullamcorper');
If we only wanted to dump data from mytable in category_id 1, we would do this:
mysqldump -t -u [username] -p test mytable --where=category_id=1
which would output this:
INSERT INTO `mytable` VALUES 
  (1,1,'Lorem ipsum dolor sit amet'),
  (2,1,'Ut purus est');

An example of dumping data from two tables using the same where clause could look like this, where we are selecting category_id from tables "mytable" and "anothertable":
mysqldump -t -u [username] -p test mytable anothertable --where="category_id = 1"

Refer: http://www.electrictoolbox.com/mysql-execute-statements-text-file/
...

0 comments:

Post a Comment