Mysql import an exported sql file

Question

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?

You shouldn't mess with INFORMATION_SCHEMA. Do you have actual databases to restore? — Sergio Tulentsev
– Sergio Tulentsev, Commented Jan 6, 2012 at 21:00
Yes, but phpmyadmin exported that entry along with the ones I had created. — John
– John, Commented Jan 6, 2012 at 21:01

Luc Laverdure · Accepted Answer · 2012-01-06 21:02:31Z

1

information_schema is a database part of the mysql system, you shouldn't replace this database...

answered Jan 6, 2012 at 21:02

Luc Laverdure

1,5002 gold badges21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RolandoMySQLDBA Over a year ago

Actually, you cannot replace the information_schema because you already said information_schema is a database part of the mysql system. mysqld will protect that database from outside users doing any dropping, creating, updating and so forth. Still, +1 for getting to root cause !!!

RolandoMySQLDBA · Accepted Answer · 2012-01-06 21:14:07Z

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:

In mysql SHOW DATABASES; or SELECT schema_name FROM information_schema.schemata;
in the OS, mkdir /var/lib/mysql/matt
In mysql SHOW DATABASES; or SELECT schema_name FROM information_schema.schemata;

The database matt instantly materializes.

This would hold true for every database and table reloaded.

@rola How in the hell is paraphrasing the problem a right answer. My question was to import a phpmyadmin exported sql file. Not tell me what I already know. I would completely remove the parts that include information_schema if I didn't see that it had mentions of my other databases which gives me cause to believe that it contains necessary information for my other databases to function correctly. To even consider this as a correct answer to the question I asked is asinine and foolish. He stated a fact that was already known. He did not explain how to update the schema without replacing it.
Actually, he did explain it. His last sentence said : restore all other databases and everything will go back into it.
+1 to this. delete the references to information_schema in a lightweight text editor (it may be a very large file), then proceed with the sql import.

Justin Martin · Accepted Answer · 2012-01-06 21:04:05Z

0

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.

answered Jan 6, 2012 at 21:04

Justin Martin

1,0131 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

Mysql import an exported sql file

3 Answers 3

1 Comment

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Related