I want a mysqldump –ignore-database option

While working with RDS and Google Cloud SQL I have come to realize that excluding the mysql schema from a mysqldump is important. However with many databases, the –all-databases option enables you only to select all or none. There is however an easy solution to exclude one or more databases in mysqldump with this little gem I created.

$ time 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

An you can exclude as many schemas as you want.

I checked the mysqldump –help, there was no option in MySQL 5.1, asked a colleague just to be sure I wasn’t wasting my time, and it took all of 2 minutes to create and test a working solution.


  1. says

    Interesting .. but:

    1) I think excluding I_S and P_S explicitly like this is not required. To my best knowledge I_S and P_S are not backed up by mysqldump unless specified explicitly.

    2) In SQLyog we had for years options to “backup all databases” 1) with the `mysql`database 2) and without the `mysql` database (and I_S and P_S will also be skipped if not explicitly selected). This is relevant not only for Cloud-based solutions like AWS but also with various type of ‘shared hosting’ and ‘managed hosting’. Users here will (maybe and to some extend) have the privileges to export/backup from `mysql` but not to import/restore to it! I am actually surprised that I did not see it in other tools (but I don’t see everything obviously).