Can I mysqldump an entire database while using where='condition' on individual tables?

Question

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?

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