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');" \ >` >/mysql/backup/rds2.sql
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.
The all-shell methodThis 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.
The options file method
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')" \ >> mydump.cnf
Now the options file looks like this:
[mysqldump] ignore-table=mysql.db ignore-table=mysql.host ignore-table=mysql.user [...]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-databasesThere are two drawbacks with this approach:
- There will be a
DROP DATABASE IF EXISTSand
CREATE DATABASEfor each of the excluded schemas, although no tables will be dumped.
- This method only works with
--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.