CODEWITHSUNDEEP
phpmysqlphpmyadminwampinformation-schema
joshkrz
joshkrz

Reputation: 519

How to restore the information_schema database with a backup

I have a backup from a WAMP server MYSQL database from home and I need to install it on my work WAMP setup.

I currently don't have access to my original database and so, can't just export specific tables.

Here is the phpmyadmin message:

SQL query:

--
-- Database: `information_schema`
--
CREATE DATABASE  `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


MySQL said: 

#1044 - Access denied for user 'root'@'localhost' to database 'information_schema'

The information_schema database appears to be locked and the restore fails in phpmyadmin. In the MYSQL Workbench it carries on through the backup ignoring the "access denied" messages but crashes halfway through.

I have tried removeing the database_schema lines in the sql file but it just looks like a mess of text and can't figure out how to remove it.

Another thing is will I actually need it? Or can I remove it. I'm not too sure what information is stored there.

Thank you.

Upvotes: 1

Views: 16960

Answers (2)

joshkrz
joshkrz

Reputation: 519

I have managed to do this myself with the help of @GeoPhoenix.

I opened the sql file in Dreamweaver then found "-- -- Database:" using ctrl-F and deleted all information up to the next "-- -- Database:" part and repeated the process until I was left with the information I needed to keep.

Thank you all for your help.

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272306

The MySQL INFORMATION_SCHEMA database is a system database:

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

It is modified by MySQL when you create or modify databases and tables. You cannot modify the INFORMATION_SCHEMA database:

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

A sane MySQL export utility will omit system tables when exporting databases and you should not import this database at all. Just import "user" databases; MySQL will build/update the system databases itself.

Upvotes: 6

Related Questions