Skip certain tables with mysqldump

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

mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table=DATABASE.table1 > database.sql

There is no whitespace after -p (this is not a typo).

To ignore multiple tables, use this option multiple times, this is documented to work since at least version 5.0.

If you want an alternative way to ignore multiple tables you can use a script like this:

#!/bin/bashPASSWORD=XXXXXXHOST=XXXXXXUSER=XXXXXXDATABASE=databasenameDB_FILE=dump.sqlEXCLUDED_TABLES=(table1table2table3table4tableN   ) IGNORED_TABLES_STRING=''for TABLE in "${EXCLUDED_TABLES[@]}"do :   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"doneecho "Dump structure"mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE} > ${DB_FILE}echo "Dump content"mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}

Building on the answer from @Brian-Fisher and answering the comments of some of the people on this post, I have a bunch of huge (and unnecessary) tables in my database so I wanted to skip their contents when copying, but keep the structure:

mysqldump -h <host> -u <username> -p <schema> --no-data > db-structure.sqlmysqldump -h <host> -u <username> -p <schema> --no-create-info --ignore-table=schema.table1 --ignore-table=schema.table2 > db-data.sql

The resulting two files are structurally sound but the dumped data is now ~500MB rather than 9GB, much better for me. I can now import these two files into another database for testing purposes without having to worry about manipulating 9GB of data or running out of disk space.

for multiple databases:

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