Taking a MySQL-dump with a wildcard on table names

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.

The Social Box

You can sign up for more updates via Twitter or Facebook below. On Twitter, I regularly talk about technology or tweet about interesting stories. Topics that don't necessarily make it to this blog. Facebook contains a steady update of blogposts and some more lightweight stories.


Write a Comment

Do you care about the markup if your comment? You can use the following HTML tags:

<code>command</code>: command highlighting
<pre>text</pre>: pre-formatted code, can be multi-line (black background, white letters)

example <pre> tag
<blockquote>text</blockquote> quoted text
quoted example


None of this is needed of course, it's all optional!

Comment

*

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

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

    • 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