MySQL – Mysqldump Ignore Table with Wildcard

ignoreMySQLsqlwildcards

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.

Best Answer

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.

Related Solutions

MySQL – Out of Resources for mysqldump Solution

According to here - "OS error code 24: Too many open files" which lines up with the more general error 23 "Out of resources".

So it seems as though you are running out of file handles. This is usually server-end setting/problem, either in MySQL, or in the OS itself.

Perhaps check/adjust the --open-files-limit setting in MySQL itself and see if that helps.

Also, perhaps try running the dump, while no one else is using the DB, with the --single-transaction setting instead of --Lock-File, as several people suggest this will work one table at a time instead of opening them all at once (therefore using less file handles).

Beyond that you'll probably have to find a root cause as to why this particular server is running out of resources. Which would probably involve troubleshooting by disabling as many services/processes as possible and see if the dump goes through. Then figure out from there who the culprit is that's eating too many resources and perhaps not freeing them correctly.

Mysql – thesqldump skip one table

You can use the --ignore command line switch

   ·   --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.

someting like

shell> mysqldump -u backupuser -p database --ignore-table=database.huge_table > db_rest.sql

Best Answer

Related Solutions

MySQL – Out of Resources for mysqldump Solution

Mysql – thesqldump skip one table

Related Topic