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.
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).
@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.
“exclude-pattern” option is also available in ZRM community for ignore some databases
[...] 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. [...]
[...] working with RDS and Google Cloud SQL Ronald Bradford has come to realize that excluding the mysql schema from a mysqldump is [...]
[...] month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database. [...]