Dump wildcard selected tables

This little tutorial shows how to dump wildcard selected tables from a MySQL database. That’s useful if you don’t want to dump the whole database.

Background

Recently I had to export a few selected tables from a very huge database. For that I could have run a query like that to export only the required tables:

But unfortunately it was a Magento database with over 500 tables and I had to export 25 tables that I didn’t want to list manually. But fortunately there was a pattern in the requested tables (all customer tables, order tables and a few other tables completely named):

So I thought I could just use wildcards like that:

But it wasn’t that simple because the mysql command doesn’t support wildcards for the tablenames to export! :-/

Solution (how to dump wildcard selected tables)

That’s why I had to find a way to dump wildcard selected tables. I found an approach in the answer from sreimer on StackOverflow.

The solution where these shell commads:

As you can see, the first command just selects our resolved wildcardtablenames” from the information_schema database and writes them into a text file. This file is read by the second command. So the tablenames from the textfile ar given to the mysqldump command as table parameters and only theses tables are exported to a gzipped export file.

To run it you need not only access to the actual database but also  to the information_schema database of the MySQL host (at least read-access).

Leave a Reply

Your email address will not be published. Required fields are marked *

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.