Taking a MySQL-dump with a wildcard on table names

Mattias Geniar, Thursday, June 13, 2013

Situation: you want to take a mysqldump with all table-names that match a particular pattern, but you don't want all the tables.

Here's how:

root@serv: ~$ mysqldump YOURDBNAME $(mysql -D YOURDBNAME -Bse "SHOW TABLES LIKE 'table_pattern_%'") > /tmp/database-dump.sql

You can modifity the SHOW TABLES LIKE query to include all the table names that you want.

Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek, public speaker and podcaster. Currently working on DNS Spy. Follow me on Twitter as @mattiasgeniar.

I respect your privacy and you won't get spam. Ever.
Just a weekly newsletter about Linux and open source.

SysCast podcast

In the SysCast podcast I talk about Linux & open source projects, interview sysadmins or developers and discuss web-related technologies. A show by and for geeks!

cron.weekly newsletter

A weekly newsletter - delivered every Sunday - for Linux sysadmins and open source users. It helps keeps you informed about open source projects, Linux guides & tutorials and the latest news.

Share this post

Did you like this post? Will you help me share it on social media? Thanks!


Alan Sunday, July 21, 2013 at 11:13

Thanks for the suggestion! FYI – It also works to filter databases this way: mysqldump –databases $(mysql -Bse “SHOW DATABASES LIKE ‘db_pattern_%'”) > /tmp/database-dump.sql


Mike Baxter Friday, April 3, 2015 at 00:38

For those of us stuck using Windows Server, here is a clip that performs like your code, using DOS “For” looping:
for /F %A in ('mysql -u -p -Bse "show tables like '%'"') DO @Echo Dumping %A & CALL mysqldump -q -Q -u -p %A >> dumpfile.sql
Run from Windows command line, and remember not to use the pointy braces surrounding the the parameters for username, pwd, dbname, or pattern. Using double greater than pointers appends each loop result to the dumpfile.


    Mike Baxter Friday, April 3, 2015 at 00:43

    Oops.. All of the stuff I put in brackets was taken as tags and removed:
    for /F %A in ('mysql -u USERNAME -pPWD DBNAME -Bse "show tables like '%PATTERN%'"') DO @Echo Dumping %A & CALL mysqldump -q -Q -u USERNAME -pPWD DBNAME %A >> dumpfile.sql
    Sorry! I know this was an old post to begin with, but … Since I could not find the answer anywhere, I figured others might be stuck like I had been


Bruce Rosen Monday, November 7, 2016 at 23:58

Just what the doctor ordered! Thanks!


Leave a Reply

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