I am attempting to restore a backed up database from a different site.
The beginning of the .sql file contains
--
-- Database: `information_schema`
--
CREATE DATABASE `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `information_schema`;
I get the error
1044 - Access denied for user 'root'@'localhost' to database 'information_schema'
From phpmyadmin. If I comment out the creation of the database, it still says that I cannot use information_schema.
All three of my roots are set to ALL PRIVILEGES
How can I import a sql file back into phpmyadmin?
information_schema is a database part of the mysql system, you shouldn't replace this database...
You cannot restore information_schema. It's the internal database where mysql keeps all the schema of all other databases. To restore it, restore all other databases and everything will go back into it.
Edited By RolandoMySQLDBA 2012-01-06 16:15 EDT
Every table in INFORMATION_SCHEMA is a temporary table.
Example of information_schema.schemata
mysql> show create table information_schema.schemata\G
*************************** 1. row ***************************
Table: SCHEMATA
Create Table: CREATE TEMPORARY TABLE `SCHEMATA` (
`CATALOG_NAME` varchar(512) NOT NULL DEFAULT '',
`SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',
`DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
`DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
`SQL_PATH` varchar(512) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
There is no manifested folder for INFORMATION_SCHEMA in /var/lib/mysql
To prove that the information_schema is restored as Mathieu Dumoulin specified, perform the following:
SHOW DATABASES; or SELECT schema_name FROM information_schema.schemata;mkdir /var/lib/mysql/mattSHOW DATABASES; or SELECT schema_name FROM information_schema.schemata;The database matt instantly materializes.
This would hold true for every database and table reloaded.
If possible, I would suggest not using PHPMyAdmin. Instead, one should use the MySQL command-line tools.
In particular, you should learn to use the mysqlimport tool, which is designed to facilitate this process.