How to use a where clause with mysqldump

mysqldump allows you to use a WHERE clause when creating a backup and only the rows fulfilling the given condition will be included in the dump.

How to use the --where flag?

# Dump only the rows with the id column bigger than 500
mysqldump my_db_name my_table_name --where="id > 500" > my_backup.sql

# Dump only the rows with the created_at column in the given interval
mysqldump my_db_name my_table_name --where="date between '2020-09-01' and '2020-09-30'" > my_backup.sql

Apply multiple where conditions

The --where flag can be extended to contatename multiple conditions like in a normal query. The following command will dump all rows from the users table with the id column bigger than 500 and the disabled colum set to 0.

mysqldump my_db_name users --where="id > 500 and disabled = 0" > my_backup.sql

Note

The column (or columns) specified in the --where flag must exist in all the tables included in the dump, otherwise an Unknown column error will be thrown. In case the column does not exist on all the tables, we recommend including in the dump only the tables that have the specified column.