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):

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.