mysqldump ignore table with wildcard
No, there is no option like that in the mysqldump
command, as the docs say:
--ignore-table=db_name.tbl_name
Do not dump the given table, which must be specified using both the
database and table names. To ignore multiple tables, use this option
multiple times. This option also can be used to ignore views.
You can get the table names you want from mysql, and then use them to build your mysql dump parameters.
In the example below, just replace "someprefix" with your prefix (e.g. "exam_").
The SHOW TABLES
query can be altered to find other sets of tables. Or you could use a query against the INFORMATION_SCHEMA
table to use even more criteria.
#/bin/bash
#this could be improved but it works
read -p "Mysql username and password" user pass
#specify your database, e.g. "mydb"
DB="mydb"
SQL_STRING='SHOW TABLES LIKE "someprefix%";'
DBS=$(echo $SQL_STRING | mysql -u $user -p$pass -Bs --database=$DB )
#next two lines untested, but intended to add a second excluded table prefix
#ANOTHER_SQL_STRING='SHOW TABLES LIKE "otherprefix%";'
#DBS="$DBS""\n"$(echo $ANOTHER_SQL_STRING | mysql -u $user -p$pass -Bs --database=$DB )
#-B is for batch - tab-separated columns, newlines between rows
#-s is for silent - produce less output
#both result in escaping special characters
#but the following might not work if you have special characters in your table names
IFS=$'\n' read -r -a TABLES <<< $DBS
IGNORE="--ignore_table="$DB"."
IGNORE_TABLES=""
for table in $TABLES; do
IGNORE_TABLES=$IGNORE_TABLES" --ignore_table="$DB"."$table
done
#Now you have a string in $IGNORE_TABLES like this: "--ignore_table=someprefix1 --ignore_table=someprefix2 ..."
mysqldump $DB --routines -u $user -p$pass $IGNORE_TABLES > specialdump.sql
This was built with help from this answer about getting "all tables with excluding in bash": https://stackoverflow.com/a/9232076/631764
and this answer about skipping tables with some bash used: https://stackoverflow.com/a/425172/631764