Export a subset of a database table to reproduce a query

One frequently needs to export some MySQL tables along with certain queries that will work with that table for testing. Usually one only needs a subset of records from the table which will work with the selected queries, rather then the complete table. This can be required when the table contains thousands or millions of records and we do not want to export the complete data set, as this can be time consuming during import, or maybe we only want to provide the other user with some selected records for security reasons.

Say we have a simple WordPress database with a SELECT statement like the following:

SELECT * FROM wp_posts WHERE post_status = 'draft'

If you want to only export records that will match the corresponding query above, then we can use the ‘mysqldump’ command-line utility with the ‘–where’ option.

mysqldump -uUSER -pPASS --where="post_status = 'draft'" wp331 wp_posts > subset.sql

This will only export records that have the ‘post_status’ field set to ‘draft’. This is better than exporting the complete wp_posts table. You can also include multiple conditions with the ‘–where’ option.

mysqldump -uUSER -pPASS --where="post_status = 'draft' 
           AND comment_status = 'closed'" wp331 wp_posts > subset.sql

The general format of the command is the following.

mysqldump -uUSER -pPASS --where="where condition" DB_NAME TABLE_NAME > subset.sql

Leave a Reply

Your email address will not be published.