dumping huge sql file

[faqall]

well lately i have had some problems restoring a sql backup file.. i have a 200+mb .sql file i need to restore.

below is the command i am using

root@sarah [~]# mysql -u username_test -p database_ibr < /localhost.sql
Enter password: (then i type the pass here)
ERROR 1049: Unknown database 'database_ibr'

ok so thats about as far as i get. ive tried having the database exist and ive tried having it not exist (same with users). still a no go. i also tried with 'mysqldump'.... nothing.. i must be overlooking something ... any ideas?


i tried to restore the gzip backup via cpanel but its huge and it basically kills my computer (bursts my ram up to 300+mb used on local machine)..

again any ideas would be helpful

fedore core 2 + cpanel


thanks in advanced

[thrassos]

Hi,

The command is correct and it works with Mysql 4.x. The only think it needs is for an empty database to exist. Strange.... :confused:

Do you receive the error immediately after the execution of the command? Also are you trying this with root permissions in Mysql? It might be permission related.


Regards,

[faqall]

Well ive tried with an empty database... and it didnt work. No matter what as soon as i hit enter after putting in the password it instantly gives me the error.

Do the tables need to exist?

[thrassos]

No tables do not need to exist. Even if they do they will be droped. I suppose that the problem is related to permissions.

With what username are you creating the database and with what username are you trying the restore?

[faqall]

Im not sure i follow. Im logged in as root (su -). Then i simply typed the command above.

[thrassos]

Permitions in mysql are different from the shell.

When you use the command:
mysql -u username_test -p database_ibr < /localhost.sql
you do not have root access unless username is root.

Try to create a database from CPANEL for the specified user. Then create a database user (if you do not have any) and then add this user will ALL permitions to the database you created.

Then from shell run the command where username_test is the username you created before. I think it should work now.

[faqall]

thank you for your continued support


i tried as you suggested (i already did it but i tried again) and still wasent able to do it. this is what it returned:


root@sarah [/]# mysql -u edited_test -p edited_ibrd1 < /localhost.sql
Enter password:
ERROR 1007 at line 12: Can't create database 'edited_ibrd1'. Database exists

[thrassos]

Last thought... I suppose that the dump has the command CREATE DATABASE edited_ibrd1; at the begining.

You can either comment that line or remove database and restore with username root and after that you can grant permitions to specific user.

[faqall]

if you were to edit a 200+mb file what editer would you suggest. i have never really had to edit a file so large so i dont want to render my server completely useless by opening a 200mb file.

[thrassos]

They try restoring with root permissions in the database. Just drop the empty database before and after the restore ends modify permissions from cpanel.

[faqall]

I droped the database as you said. Then i attempted to restore using root. Again it was a no go. I also tried with the database existing:

database droped
root@sarah [/]# mysql -u root -p edited_ibrd1 < /localhost.sql
Enter password:
ERROR 1049: Unknown database 'edited_ibrd1'

database existing
root@sarah [/]# mysql -u root -p edited_ibrd1 < /localhost.sql
Enter password:
ERROR 1007 at line 12: Can't create database 'edited_ibrd1'. Database exists
root@sarah [/]#

[spt1224]

If you can download the sql file I have edited 50 mb files with this program:

http://www.snapfiles.com/get/edxor.html

[faqall]

Humm how do i actually limit how much memory that app uses?

[spt1224]

i'm not sure

[faqall]

humm ok. ill have to find another way to do this