A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.
As a workaround, he proposes:
mysqldump --databases `mysql --skip-column-names \
-e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
FROM information_schema.schemata WHERE schema_name \
NOT IN ('mysql','performance_schema','information_schema');" \
It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)
There are two alternative methods.
This method lets shell commands filter the wanted databases. In its simplest way, it goes
DATABASE_LIST=$(mysql -NBe 'show schemas' | grep -wv 'mysql\|personnel\|buildings')
mysqldump --all-databases $DATABASE_LIST
Notice that, when you use
--all-databases, information_schema and performance_schema are filtered off by default.
This method works, because the default length of the command line in Unix is much longer than group_concat_max_len:
$ getconf ARG_MAX
131072 # Linux
$ getconf ARG_MAX
262144 # Mac OSX
(in Windows it's much shorter: 8191, but since I haven't used Windows for ages, I don't really care).
A more elaborate method would require a scripting wrapper around the above instructions, but I think that as it is, it's simple enough to be remembered.
If the length of all your database names combined is more than the maximum allowed by the shell, and all you want is filtering a few databases off a huge list, there is still hope.
Let's assume that you want to ignore mysql, personnel, and buildings from your backup.
echo '[mysqldump]' > mydump.cnf
mysql -NBe "select concat('ignore-table=', table_schema, '.', table_name) \
from information_schema.tables \
where table_schema in ('mysql', 'personnel', 'buildings')" \
Now the options file looks like this:
What we need to do is tell mysqldump to get its information from this options file, and it will duly skip all the tables that are listed in there.
mysqldump --defaults-file=./mydump.cnf -u $DBUSER -p$DBPWD --all-databases
There are two drawbacks with this approach:
DROP DATABASE IF EXISTSand
CREATE DATABASEfor each of the excluded schemas, although no tables will be dumped.
--default-options-file. Theoretically, it should also work with
--default-extra-file, but it doesn't. Therefore, if you are relying on an options file for connection parameters, they should be added to this file or listed in the command line.
Summing up, there is no perfect solution, but there are enough hacks available that you probably would find what suits you.