Selectively dumping data with mysqldump

mysqldump is a command line tool for outputting table structures and data and can be used for backups etc. By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump. This post looks at how to do this.

The examples in this post have a table called "mytable" in a database called "test". mytable has three columns: mytable_id, category_id and name, and we will be selectively exporting data that matches a specific category_id.

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');

You can also abbreviate –where as -w like so:

mysqldump -t -u [username] -p test mytable -wcategory_id=1

If you need to have spaces in the where query or other special shell characters (such as > and <) then you need to put quotes around the where clause like so:

mysqldump -t -u [username] -p test mytable --where="category_id = 1"
OR
mysqldump -t -u [username] -p test mytable -w"category_id = 1"

You can also use the –where flag to selectively dump data from more than one table, but obviously the columns specified in the where clause need to be in both tables.

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"

If category_id exists in both tables then the dump will run without error. If the column doesn’t exist, you’ll see an error like this:

mysqldump: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `anothertable` WHERE category_id=1': Unknown column 'category_id' in 'where clause' (1054)
mysqldump: Got error: 1054: Unknown column 'category_id' in 'where clause' when retrieving data from server

mysqldump is an excellent tool for exporting data from MySQL databases. Using the –where or -w flags allows you to selectively export data from one or more tables which saves you having to export all data from a table if you only need a specific subset.