Can't restore mysql database

bhupendra2895

New Member
Messages
554
Reaction score
20
Points
0
I recently installed ubuntu 10.10 on my system.Before doing this I backed up all mysql databases using phpmyadmin.Now after setting development environment in ubuntu 10.10 I can't restore databases.Because the backup.sql contains create procedure for information_schema.So whenever I try to restore databases I get the error 'root' don't have permission to access information_schema.So it results in restoration of databases above information_schema and all database below of it don't get restored.This backup.sql has size of 86 MB.So its tough to edit this file.Is there any way by which I can forcefully delete information_schema database?So I will be able to restore all the databases.
 

gomarc

Member
Messages
516
Reaction score
18
Points
18
Hi bhupendra2895,

This may work:

Find these lines in your backup.sql

Code:
CREATE DATABASE `[B]information_schema[/B]` ... ;
USE `[B]information_schema[/B]`;

And change them to something like:

Code:
CREATE DATABASE `[B][COLOR="blue"]old_schema[/COLOR][/B]` . . . ;
USE `[B][COLOR="blue"]old_schema[/COLOR][/B]`;

After a successful restoration, you can drop old_schema database.
 

bhupendra2895

New Member
Messages
554
Reaction score
20
Points
0
@Gomarc Thanks, what a brilliant idea, this will work definitely.

But its too late now because I removed all queries related to information_schema from backup.sql file, it took half an hour.And I was able to restore my databases.But during this process I accidently dropped a database having name mysql and hence I can't see/create user privileges now.Really next time I will backup all databases individually or I will exclude information_schema from it.

I guess database having name mysql will come back again if I will install mysql again on my system?
 

gomarc

Member
Messages
516
Reaction score
18
Points
18
Yes, you should get mysql database back if you reinstall it and excluding the information_schema or renaming as indicated will help in future backups.

Thanks for the +REP
 

bhupendra2895

New Member
Messages
554
Reaction score
20
Points
0
Thanks, I got database having name mysql in backup file, all I need to do now is to press backspace or left mouse button for a very long time. :)
 
Last edited:
Top