MySQL: Dump tables with wildcard

Dumping a certain table with mysqldump goes like this:

mysqldump -u root -pPWD exampledb table1 > exampledb_table1.dump

This command exports the table “table1” from DB exampledb and stores it in the file “exampledb_table1.dump”.

To export more than one table we pass their names to this command:

mysqldump -u root -pPWD exampledb table1 table2 table3 > exampledb_tables.dump

If we need to export some tables e.g. with a given prefix (like “smw_”), we may want to use a wildcard to save us from stringing together all tablenames. This can be done like this:

mysqldump -u root -pPWD exampledb `echo "show tables like 'smw_%';" | mysql -u root -pPWD exampledb | sed '/Tables_in/d'` > exampledb.dump

Edit 2016-11-13:

Or a little smarter:

mysqldump -u root -pPWD exampledb `mysql -u root -pPWD -B -N -e "show tables like 'smw_%'" exampledb` > exampledb.dump

The “-B” and “-N” suppress the table-format and colum-header and “-e” executes the given command “show tables…”.

Advertisements

2 Responses to MySQL: Dump tables with wildcard

  1. Magne says:

    mysqldump -u root -pPWD exampledb `echo “show tables like ‘smw_%’;” | mysql -u root -pPWD exampledb | sed ‘/Tables_in/d’` > exampledb.dump

    Is not working for me. I get all tables seeded to my dump file. Any idea why?

    • logbuffer says:

      I guess the

      “echo “show tables like ‘smw_%’;” | mysql -u root -pPWD exampledb | sed ‘/Tables_in/d’”

      won’t show you anything. Hence you end up with an empty string for table-names and nothing shows up right after the databasename (e.g. exampledb) to restrict export to only the desired tables. So, you’ll get this line which does a full export, as you said so:

      mysqldump -u root -pPWD exampledb > exampledb.dump

      Please check if “show tables like ‘smw_%'” really applies to your environment to filter your tables.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: