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

Mysql: Dump & Restore Database

Mysql Dump Syntax/Command

We use mysqldump command to do database backup

# mysqldump -uroot -p --databases filename > backupname.sql

eg: mysqldump -uroot -p --databases staff > staff130509.sql
Then, to dump back/restore the backup database to server, use this command:

#mysql -uroot -p < staff130509.sql

But, for UTF8 case, use this command:

#mysql -uroot -p --default-character-set=utf8 < staff130509.sql

Then to check the current process list in mysql server:

#mysql -uroot -pPASSWORD
mysql> show processlist;


To show processlist with out all the sleeping processes...
mysql> \P grep -v Sleep