I need to take a dump of a database containing 50 odd tables of which I want to exclude about 15 odd that have prefix exam_

I tried mysqldump --ignore-table=dbname.exam_* and even tried --ignore-table=dbname.exam_% It didn't work as expected. I had to use --ignore-table multiple times.

Edit: I have seen a few shell scripts that list tables not like tablename_% and pass it to mysqldump.

However, I would like to know if there is an option in mysqldump or mysql, as such to do the same without having to script it.

Edit-add: Eventually used a script to dump DB excluding tables, using ignore-table= multiple times.

  • can you post the script you used? maybe host it at gist.github.com – rubo77 Nov 6 '15 at 4:17
  • @rubo77 : I don't have that script any more. It was a basic script. Using mysql command I got a list of tables I wanted to exclude. Hard-coded the list to a BASH script to produce dump whenever needed. Fortunately for me, the list was constant. Alternatively, a solution posted here may be useful with respect to scripting. – anup Nov 18 '15 at 10:26
  • Better still: TABLES=`mysql --skip-column-names [DB-NAME] -e 'show tables' | grep -v 'exam_'` mysqldump [DB-NAME] $TABLES > mysqldump.sql – anup Nov 18 '15 at 10:39

No, there is no option like that in the mysqldump command, as the docs say:


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.

  • As an aside, it might be worth creating a mysql dump user that has no access to those tables, then trying a mysqldump --all-databases just to see if it errors out, or just skips over to the next dDB.. – NickW May 28 '13 at 11:04
  • Haven't tried it, but sound good. As for the solution, I wrote a shell script to avoid the tables. – anup Jun 12 '13 at 2:31
  • @NickW That might work. But if you want more flexibility, you'd need to do a separate query to get the table names. See my answer below. – Buttle Butkus Dec 9 '18 at 7:31
  • quick and dirty version: dupe the database. use phpMyAdmin's web interface to easily drop the tables you don't want to see. then dump and no wildcard needed. if you want CLI execution and don't mind a third step sitepoint.com/community/t/drop-tables-with-wildcard/18537/4 – TheSatinKnight Mar 1 '20 at 18:16

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.


#this could be improved but it works
read -p "Mysql username and password" user pass

#specify your database, e.g. "mydb"

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
#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


for table in $TABLES; do
        IGNORE_TABLES=$IGNORE_TABLES" --ignore_table="$DB"."$table

#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

  • Based on the selected answer, I wrote a script similar to your answer. Thanks for putting this up. Edit: just remembered that I used two commands with pipe and grep exclude pattern. – anup Dec 31 '18 at 6:47

I think using the information_schema is a good route to take for this.

select group_concat(concat('--ignore-table=', TABLE_SCHEMA, '.', table_name) SEPARATOR ' ') 
from information_schema.tables 
where TABLE_SCHEMA = 'Actual_DB_NAME' and TABLE_NAME like 'Actual_TABLE_NAME%';
  • There is two issues with your query that is worth to say. First if your Actual_TABLE_NAME contains "_" then it should be escaped to "\_". Second, group_concat has a limit to 1024 char, so your output can be truncated. You can increase the limit by query: SET SESSION group_concat_max_len = 1000000; – MrHetii Jan 28 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.