Can't restore mysql database

[bhupendra2895]

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]

Hi bhupendra2895,

This may work:

Find these lines in your backup.sql

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

And change them to something like:

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]

@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]

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]

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.