mysqldump dump only database with certain prefix

Question

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.

Answer 1

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]