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.

6 Responses to “I want a mysqldump –ignore-database option”

  1. 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).

  2. @Peter_Laursen
    I_S and P_S are excluded if you run –all-databases, If you put them in a database list, they are backed up.

  3. Cédric says:

    “exclude-pattern” option is also available in ZRM community for ignore some databases

  4. [...] Ronald Bradford and Giuseppe Maxia (hey guys!) wrote about different ways to ignore a database when using mysqldump –all-databases over the past couple of days. [...]

  5. [...] working with RDS and Google Cloud SQL Ronald Bradford has come to realize that excluding the mysql schema from a mysqldump is [...]

  6. [...] month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database. [...]