Many developers assume mysqldump supports wildcards in --ignore-table parameters like exam_*, but this functionality simply doesn't exist natively. The command strictly requires fully qualified table names in database.table format.

Here are three proven methods to exclude tables with common prefixes:

Method 1: Shell Script with mysqlshow

TABLES_TO_EXCLUDE=$(mysqlshow dbname | awk '/^exam_/ {print "--ignore-table=dbname."$1}')
mysqldump dbname $TABLES_TO_EXCLUDE > backup.sql

Method 2: Using information_schema

EXCLUDED=$(mysql -N -e "SELECT CONCAT('--ignore-table=dbname.',table_name) 
FROM information_schema.tables 
WHERE table_schema='dbname' AND table_name LIKE 'exam_%'")
mysqldump dbname $EXCLUDED > backup.sql

Method 3: Explicit Listing (For Small Sets)

When dealing with few tables, the simplest solution is explicit enumeration:

mysqldump dbname \
--ignore-table=dbname.exam_results \
--ignore-table=dbname.exam_users \
--ignore-table=dbname.exam_logs \
> backup.sql

Each --ignore-table parameter adds slight overhead. For databases with hundreds of tables, consider:

• Dumping the schema first with --no-data
• Using separate dumps for included/excluded tables
• Parallel dumping with mydumper if performance is critical

For more advanced pattern matching, consider these alternatives:

# Using mydumper
mydumper --regex '^(?!exam_).*' --database dbname

# Using pg_dump (PostgreSQL equivalent)
pg_dump --exclude-table='exam_*' dbname

When working with large databases containing dozens of tables, you might encounter situations where you need to exclude multiple tables sharing a common naming pattern from your dump. The natural approach would be to use wildcards like exam_* or exam_% with the --ignore-table parameter, but this doesn't work as expected.

The mysqldump utility doesn't support wildcard expansion in the --ignore-table parameter. This is because:

• The parameter expects complete table names in database.table format
• Shell wildcard expansion happens before mysqldump receives the arguments
• MySQL's pattern matching syntax isn't supported in this context

Here are three effective approaches to solve this problem:

1. Multiple --ignore-table Parameters

The most straightforward solution is to list all tables explicitly:

mysqldump -u username -p database_name \
--ignore-table=database_name.exam_1 \
--ignore-table=database_name.exam_2 \
--ignore-table=database_name.exam_logs \
--result-file=dump.sql

2. Automated Table Exclusion with Shell Script

For dynamic exclusion of tables matching a pattern:

#!/bin/bash
DB_NAME="your_database"
USERNAME="your_username"
PASSWORD="your_password"
DUMP_FILE="dump.sql"

# Get list of tables to exclude
EXCLUDED_TABLES=$(mysql -u $USERNAME -p$PASSWORD $DB_NAME -e "SHOW TABLES LIKE 'exam_%';" | grep -v "Tables_in" | tr '\n' ' ')

# Build ignore parameters
IGNORE_PARAMS=""
for TABLE in $EXCLUDED_TABLES; do
  IGNORE_PARAMS="$IGNORE_PARAMS --ignore-table=$DB_NAME.$TABLE"
done

# Execute dump
mysqldump -u $USERNAME -p$PASSWORD $DB_NAME $IGNORE_PARAMS > $DUMP_FILE

3. Using mysqlpump (MySQL 5.7+)

The newer mysqlpump utility supports wildcard exclusion:

mysqlpump -u username -p \
--exclude-tables=exam_% \
database_name > dump.sql

If you've already created a full dump, you can filter out unwanted tables:

sed -n '/^-- Table structure for table exam_/,/^-- Table structure for table/p' dump.sql | grep -v '^-- Table structure for table exam_' > filtered_dump.sql

• Always test your dump files after exclusion to verify data integrity
• Remember that foreign key constraints might be affected when excluding tables
• For very large databases, consider using --single-transaction for consistent dumps