How to Exclude a Table from mysqldump (September 2026)

If you need to exclude a table from a mysqldump backup, add the --ignore-table=dbname.tablename flag to your command. Repeat the flag for each table you want to skip. That is the canonical pattern that works on every MySQL and MariaDB release, and it is what I reach for whenever a production backup keeps grabbing a noisy log table or a session cache I do not need.

In this guide I will walk you through the exact command, the syntax rules that catch most people out, and a ready-to-paste bash script you can drop into a cron job. You will also see how to exclude tables by prefix, how to keep a table’s schema but drop its data, and how to fix the cryptic “Illegal use of option ignore-table database table” error. By the end you will be able to run a partial mysqldump with confidence and understand exactly what ends up in the resulting SQL file.

Quick Answer

To exclude a table from mysqldump, pass --ignore-table=dbname.tablename on the command line, repeating the flag for each table you want to skip:

mysqldump -u user -p my_database --ignore-table=my_database.logs --ignore-table=my_database.cache > backup.sql

The table name must always be qualified with the database name (for example my_database.logs, not just logs) – that one rule trips up most newcomers and is the source of the “Illegal use of option ignore-table database table” error.

The Canonical Command to Exclude a Single Table from mysqldump

The --ignore-table flag tells mysqldump to skip both the schema and the data of one specific table. Here is the smallest complete command that demonstrates the pattern:

mysqldump -u root -p my_shop --ignore-table=my_shop.audit_log > my_shop_backup.sql

Breaking it down: -u root selects the MySQL user, -p prompts for the password, my_shop is the database being dumped, and --ignore-table=my_shop.audit_log names the table to skip. The final > my_shop_backup.sql redirects the SQL output to a file.

After running the command, verify the dump with grep -i "CREATE TABLE.*audit_log" my_shop_backup.sql. If the output is empty, the table was excluded correctly. I run that grep every time on production jobs because a missed flag is easier to spot at the shell than after a restore.

Excluding Multiple Tables by Repeating –ignore-table

mysqldump has no built-in “exclude these tables” array. The official pattern is to repeat the --ignore-table flag once per table. Each occurrence is parsed independently, so order does not matter and you can stack as many as your shell can hold:

mysqldump -u root -p my_shop
--ignore-table=my_shop.audit_log
--ignore-table=my_shop.cache
--ignore-table=my_shop.sessions
> my_shop_backup.sql

Each flag is fully qualified with the database name. If your backup script lives in version control, I recommend keeping the list as a bash array (covered below) instead of inlining it – it scales better when you go from three excluded tables to thirty.

Why the Table Name Must Be Qualified With the Database Name

This is the rule that trips up almost everyone the first time. The --ignore-table option expects a fully-qualified identifier in the form dbname.tablename. If you write just --ignore-table=logs, mysqldump has no way to know which database logs belongs to, and it aborts with the error mysqldump: Got error: 1064: You have an error in your SQL syntax... when using LOCK TABLES or, more commonly, Illegal use of option ignore-table database table.

The fix is mechanical: prefix every ignored table with its database. If you dump three databases in one command, every --ignore-table value still has to point at the correct database – --ignore-table=shop.logs and --ignore-table=blog.logs are two different tables.

I keep this rule printed at the top of every backup script I maintain, because once you have stared at a 4 a.m. cron failure for ten minutes you never forget it again.

Schema-Only vs Data-Only Exclusion with –ignore-table

By default --ignore-table removes both the CREATE TABLE statement and the INSERT statements for that table. Sometimes you want a finer cut – keep the schema so a restore does not break foreign keys, but skip the rows. You do that by combining --ignore-table with --no-data or --no-create-info.

To dump only the schema of every table (no data anywhere), use --no-data:

mysqldump -u root -p my_shop --no-data > schema_only.sql

To dump only the data of every table (no CREATE TABLE statements), use --no-create-info. Pair it with --ignore-table to keep the data of specific tables out:

mysqldump -u root -p my_shop --no-create-info --ignore-table=my_shop.audit_log > data_only.sql

The reverse case – keep the schema of one table but drop its data – is the trickiest. You achieve it by running two passes: one full schema dump (--no-data) and one data dump (--no-create-info --ignore-table=...) for every table except the one you want empty. It is verbose, but it is the only way to keep a placeholder CREATE TABLE while stripping its rows. A reviewer on StackOverflow hit this exact question with a 200-table database, so you are not alone if it feels awkward.

A Reusable Bash Script for Excluding Tables in Cron Jobs

For automated backups the cleanest pattern is a bash array of excluded table names, expanded into repeated --ignore-table flags. Drop this into a file, make it executable with chmod +x backup.sh, and call it from cron or systemd:

#!/usr/bin/env bash
set -euo pipefail

DB_USER="backup_user"
DB_PASS="your_password"
DB_NAME="my_shop"
OUT="/var/backups/mysql/${DB_NAME}-$(date +%F).sql"

EXCLUDED_TABLES=(
"${DB_NAME}.audit_log"
"${DB_NAME}.cache"
"${DB_NAME}.sessions"
"${DB_NAME}.temp_results"
)

IGNORES=""
for table in "${EXCLUDED_TABLES[@]}"; do
IGNORES+=" --ignore-table=${table}"
done

mysqldump
-u "${DB_USER}"
-p"${DB_PASS}"
--single-transaction
--routines
--triggers
${IGNORES}
"${DB_NAME}" > "${OUT}"

gzip "${OUT}"
echo "Backup written to ${OUT}.gz"

A few details worth knowing: --single-transaction wraps the dump in a single transaction so InnoDB tables see a consistent snapshot; --routines and --triggers include stored procedures and triggers that mysqldump skips by default. Hard-coding a password in a script is acceptable for service accounts on a locked-down host – if you would rather not, use --defaults-file=/etc/mysql/backup.cnf with a 0600 permission file.

Wildcard and Prefix-Based Exclusion Using a Shell Loop

mysqldump does not support a native wildcard on --ignore-table. If you want to exclude every table whose name starts with a prefix, such as exam_, generate the flags in a loop. The ServerFault thread asking for exactly this is still active more than a decade later, so the need is real:

#!/usr/bin/env bash
set -euo pipefail

DB_NAME="school"
PREFIX="exam_"

IGNORES=""
mapfile -t TABLES < <(mysql -N -B -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='${DB_NAME}' AND TABLE_NAME LIKE '${PREFIX}%';")

for table in "${TABLES[@]}"; do
IGNORES+=" --ignore-table=${DB_NAME}.${table}"
done

echo "Excluding ${#TABLES[@]} tables with prefix ${PREFIX}"
mysqldump -u backup_user -p "${DB_NAME}" ${IGNORES} > "school_no_exams.sql"

The script queries information_schema for matching tables, builds the flag string, and invokes mysqldump. If the matching set is empty, the loop produces no flags and the dump proceeds normally – a safe default.

Fixing the “Illegal Use of Option ignore-table Database Table” Error

If you see the message mysqldump: Illegal use of option ignore-table database table (or the longer Illegal use of option ignore-table database table when dumping table ...), the cause is almost always one of three things.

First, the table name is not qualified with the database name. --ignore-table=logs is wrong; --ignore-table=mydb.logs is correct. mysqldump requires the dbname.tablename form for every flag.

Second, the database name is misspelled or the table does not exist in that database. Run SHOW TABLES IN my_database; inside the MySQL client and confirm the exact name, including case on case-sensitive filesystems.

Third, you are running an older mysqldump against a newer server (or vice versa) that has stricter parsing. Upgrading the client to match the server version usually clears it up.

The fastest debug is to run mysqldump with -v (verbose) and watch which table it complains about. Whatever it names in the error is the one you need to verify against information_schema.TABLES.

Excluding Tables vs Including Specific Tables – Two Strategies Compared

mysqldump gives you two ways to produce a partial backup. You can either exclude a list of tables from a full database dump, or you can include a specific list and skip everything else. Each has trade-offs:

Excluding tables with --ignore-table is the safest default when you do not know exactly which tables will exist in the future. New tables are picked up automatically; only the ones you name are skipped. The downside is that the command line grows long if you exclude many tables, and a typo silently fails to exclude the table.

Including tables by listing them as positional arguments after the database name is explicit and visible: mysqldump my_database users orders products > partial.sql. It is easier to read in code review. The downside is that any new table you add next month is missing from the backup until you edit the script – a classic source of silent data loss.

For most production setups I prefer the exclude-by-default approach for the safety it provides, and I keep the exclude list in a version-controlled bash array so changes are reviewable.

Avoiding Locks With –single-transaction

mysqldump takes locks by default to keep the dump consistent. On InnoDB tables the recommended way to skip those locks is --single-transaction, which wraps the dump in a START TRANSACTION with a consistent snapshot:

mysqldump -u root -p --single-transaction --ignore-table=my_shop.audit_log my_shop > backup.sql

For MyISAM tables --single-transaction does not help – MyISAM does not support transactional snapshots, and mysqldump will still lock each table. If your database is mixed, accept the lock or switch the MyISAM tables to InnoDB. This is exactly the constraint that pushes teams toward Percona XtraBackup or MySQL Enterprise Backup for large production jobs.

When mysqldump Is the Wrong Tool

mysqldump is a logical backup – it generates SQL statements, which is great for small databases and selective dumps but slow and memory-hungry on multi-hundred-gigabyte systems. If your database is too large for mysqldump to finish inside your backup window, or if you need hot backups without locking, consider:

mysqlpump (the official successor to mysqldump) supports parallel dumping via --default-parallelism=N and partial dumps via --exclude-databases and --exclude-tables. It uses the same flag style for table exclusion but handles very large schemas more gracefully.

Percona XtraBackup is a physical backup tool that copies InnoDB data files while the database is online, with no meaningful lock. It excels at large production servers where a logical dump would take hours. The trade-off is that backups are not portable SQL; restoring is binary-level.

MySQL Enterprise Backup is the Oracle-supported equivalent of XtraBackup, included with a commercial subscription.

If you are routinely excluding tables to keep mysqldump fast, that is usually a sign that the database has outgrown mysqldump and a physical-backup tool would serve you better.

Frequently Asked Questions

Conclusion

To exclude a table from mysqldump, use --ignore-table=dbname.tablename and repeat the flag for every additional table. Always qualify the table with its database name, pair the flag with --single-transaction on InnoDB to avoid locks, and keep your exclusion list in a version-controlled bash script so changes are reviewable. If you outgrow mysqldump, move to mysqlpump or Percona XtraBackup rather than excluding more and more tables.