When trying to import a MySQL database, it might stop with following error and fail to load all the dumped database:
Access denied for user 'root'@'localhost' to database 'information_schema'
INFORMATION_SCHEMA is a virtual database where information about all the other databases that the MySQL server maintains are stored. The tables in this schema are actually views not real tables and there is no database directory with that name.
Since it only contains data about the other databases on the server, you shouldn’t actually be importing it. So you shouldn’t have been dumping it in the first place. If you used mysqldump to dump all the databases, you are either using an old version (like 4.1) and should switch to a more current version of mysqldump. Or you use MySQL 5.5 or newer and actually went out of your way to dump INFORMATION_SCHEMA by naming it explicitly using the –skip-lock-tables option.
Anyway, there three solutions for this problem:
- Do not dump INFORMATION_SCHEMA.
- Remove it from the dump before importing it.
- Use the –force option when importing it.
Not dumping INFORMATION_SCHEMA if you use a version of mysqldump which dumps it if you use the -A or –all-databases option, means you’ll need to use the -B or –databases option and provide a list of databases to be dumped.
Removing it from the dump just involves using a text editor and deleting the parts about INFORMATION_SCHEMA since the dump is just a text file containing a list of SQL commands.
–force tells the MySQL client to continue even if an SQL error occurs. So it will produce errors while importing INFORMATION_SCHEMA but will then import the databases dumped after it.