Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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'),
('NorthAmerica'),('SouthAmerica'),
('Australia'),('Antarctica');

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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