How to mysqldump to a subset of rows in specific table during a complete backup

Question

I have a database (myDB) that holds some tables. During my backup process I have one of those tables that should be filtered. So I want to backup only a subset of rows inside table1, when I backup the entire database.

I am using mysqldump like this:

mysqldump -uUSER -pPASSWORD --routines --add-drop-database   --databases myDB --tables table1 -w "id<3"> myDB_BK.sql

The subset rows is correct for table1 but I dont have the entire database. Only the specified table1.

I've tried all combination with no success.

Any Idea in how to accomplish this task?

Answer 1

Dumping subsets of table data can only be done backing up individual tables.

I once answered a post entitled Is it possible to mysqldump a subset of a database required to reproduce a query?. In that post, I used mysqldump strictly against the tables in question.

The only thing you can do is to produce two dumps

• mysqldump of the database without table1 (means you have to name all other tables (See my old post How do you mysqldump specific table(s)?)
• mysqldump only table1 using -w or --where
• concatenate the second dump to the first dump

Give it a Try !!!