Importing all MySQL databases

Question

I mysqldump --all-databases nightly as a backup. But on importing this dump into a clean installation, I obviously run into a couple issues.

1. I obviously can't (and don't want to) overwrite the new information_schema.
2. All my users and permissions settings are lost, unless I overwrite the mysql database.

What is standard practice in this situation? Parse out information_schema from .sql file before uploading? And do I overwrite the mysql database or not?

Answer 1

you will not have problems with the info schema

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it.

Answer 2

For excluding database, try this bash script.

 for DB in $(echo "show databases" | mysql -u <username> -p'<password>' | grep -v Database  grep -v <some_db_to_exclude>)
do
    mysqldump -u <username> -p'<password>' ${DB}
done