I have used mysqldump to dump an entire database,

However, when I want to use --where=condition", on individual tables, I have dumped individual tables

mysqldump  --where='condition' mydb table > table.sql

and then reconstructed the database table by table

 for i in *sql; do mysql newdb < i; done

Is it possible to use the where clause with mysqldump on specific tables when dumping an entire database?


You will have to custom script each table

The --where option is designed to help mysqldump a single table

Here is a post I made back in August 15, 2011

Is it possible to mysqldump a subset of a database required to reproduce a query?

Here is an crazy example

Suppose the table is this in the myworld database:

CREATE TABLE data_for_world
    id int not auto_increment,
    continent varchar(16),
    primary key (id)
insert into data_world (continent)
values ('Asia'),('Africa'),('Eupore'),

and you want to break it up into two dumps

  • one with continents that start with A
  • one with continents that do not start with A

You will need three files (one for the database schema, and two for the data)

mysqldump --no-data --database myworld > myworld_schema.sql
mysqldump --no-create-info myworld data_for_world --where="substr(continent,1,1)='A'" > data1.sql
mysqldump --no-create-info myworld data_for_world --where="substr(continent,1,1)<>'A'" > data2.sql
  • thanks for the answer, that post has been very useful. I never second-guessed it until I wanted to subset the entire database and only use a simple select on one of the tables. It seems easier to just dump/restore the whole database, and then delete from the restored database.
    – Abe
    Mar 14 '12 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.