Mysql: Skip certain tables with mysqldump

Is there a way to restrict certain tables from the mysqldump command?

For example, I'd use the following syntax to dump only table1 and table2:

mysqldump -u username -p database table1 table2 > database.sql

But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentation, so is brute-force (specifying all the table names) the only way to go?

ou can use the --ignore-table option. So you could do

mysqldump -u username -p database --ignore-table=database.table1 > database.sql

If you want to ignore multiple tables you can use a simple script like this

#!/bin/bash
PASSWORD=XXXXXX
HOST=XXXXXX
USER=XXXXXX
DATABASE=databasename
DB_FILE=dump.sql
EXCLUDED_TABLES=(
table1
table2
table3
table4
tableN   
)

IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data ${DATABASE} > ${DB_FILE}

echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info ${IGNORED_TABLES_STRING} >> ${DB_FILE}

---

for multiple databases:

mysqldump -u user -p --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 --databases db1 db2 ..

---

To exclude some table data, but not the table structure. Here is how I do it:

Dump the database structure of all tables, without any data:

mysqldump -u user -p --no-data database > database_structure.sql

Then dump the database with data, except the excluded tables, and do not dump the structure:

mysqldump -u user -p --no-create-info \
    --ignore-table=database.table1 \
    --ignore-table=database.table2 database > database_data.sql

Then, to load it into a new database:

mysql -u user -p newdatabase < database_structure.sql
mysql -u user -p newdatabase < database_data.sql