CODEWITHSUNDEEP
mysqlsqldatabase
Carlo Mazzabrutta Cappai
Carlo Mazzabrutta Cappai

Reputation: 93

mysqldump dump only database with certain prefix

I have about 100 different databases and I want to dump with mysqldump just the ones starting with a prefix "asd_"

I tried this but it's not working:

mysqldump -u[user] -p[pwd] -h [server.url] asd_* --single-transaction > backup.sql

I tried also:

mysqldump -u[user] -p[pwd] -h [server.url] "SHOW DATABASES LIKE 'asd_%'" --single-transaction > backup.sql

but does not work neither.

Thanks for your help.

Upvotes: 7

Views: 5668

Answers (2)

Eaten by a Grue
Eaten by a Grue

Reputation: 22931

If you require more complex logic than the single LIKE condition offers with SHOW DATABASES LIKE ..., an alternative is to select the table names directly:

mysqldump --databases $(mysql -Bse "SELECT SCHEMA_NAME from information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE 'prefix1\_%' OR SCHEMA_NAME LIKE 'prefix2\_%'") > dbs.sql

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562260

mysqldump alone does not have support for wildcards for tables or databases.

You'll have to generate the list of databases as a separate step, and then use it in a mysqldump command. You can combine this step in a subcommand like this:

mysqldump ...options... --databases `mysql -B -N -e "SHOW DATABASES LIKE 'asd\_%'"`

Note that I have to backslash the _ character, because it's a metacharacter for LIKE.

I also have to use the --databases option, or else the second and subsequent database names will be interpreted as table names inside the first database. That's because the usage of mysqldump is one of the following:

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

Upvotes: 12

Related Questions