View previous topic :: View next topic |
Author |
Message |
Korr.ban Tux's lil' helper
Joined: 05 Jul 2004 Posts: 98 Location: Ex Inferis
|
Posted: Sun Dec 26, 2004 6:29 am Post subject: [SOLVED] Importing MySQL DB (.sql file) |
|
|
I am trying to move to a new webhost and saved my forums DB for the move. I tryed using phpmyadmin to restore the DB but it takes for ever loading the new DB and then it doesn't even succeed. It just finishes loading and nothing has changed.
I had tried importing the .sql file in console (my fav way).
Say my
username: trucker
password: rekcurt
databasename = mywebsiteaddr_com_-_forums (taken directly from host db info)
I do the following:
Code: | mysql -p mywebsiteaddr_com_-_forums < /path/to/my/saved_db.sql |
OUTPUT
Code: | ERROR 1142 at line 19 in file: 'saved_db.sql': create command denied to user: 'trucker@localhost' for table 'access'
|
From this, I gather that trucker does not have access to "Create" in mysql. This is not possible however since trucker is the admin of that db.
Does anyone have any idea as to whats going on?
Thank you. _________________ Registered Linux User #375052
DevShell - Viva La Revolusion!
Last edited by Korr.ban on Mon Dec 27, 2004 10:00 pm; edited 1 time in total |
|
Back to top |
|
|
gen2fox Guru
Joined: 25 May 2004 Posts: 544
|
Posted: Sun Dec 26, 2004 2:03 pm Post subject: |
|
|
What do you mean "trucker is the admin of the db"? Did you create the user and give it privs using the SQL command grant, cPanel, or something like that?
If you try to login to mysql, and create a table manually, does it work?
Code: | $ mysql -u trucker -p
mysql> use mywebsiteaddr_com_-_forums;
mysql> create sometable (somefield int);
|
|
|
Back to top |
|
|
Korr.ban Tux's lil' helper
Joined: 05 Jul 2004 Posts: 98 Location: Ex Inferis
|
Posted: Sun Dec 26, 2004 9:54 pm Post subject: |
|
|
gen2fox wrote: | What do you mean "trucker is the admin of the db"? Did you create the user and give it privs using the SQL command grant, cPanel, or something like that?
If you try to login to mysql, and create a table manually, does it work? |
trucker is the admin of the db
The host set this user as mysql user when I registered.
I have not tryed using GRANT in mysql since I have yet to figure out the syntax of granting a user CREATE, INSERT.
Here is what happens with grant:
Code: | grant ALL ON mywebsite_com_-_forums TO trucker;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-_forums TO trucker' at line 1 |
Here is what happens in mysql when I try to create a table.
Code: | mysql> create table testtabl;
ERROR 1142: create command denied to user: 'trucker@localhost' for table 'testtabl'
|
BTW: mysql Ver 11.18 Distrib 3.23.58 _________________ Registered Linux User #375052
DevShell - Viva La Revolusion! |
|
Back to top |
|
|
gen2fox Guru
Joined: 25 May 2004 Posts: 544
|
Posted: Mon Dec 27, 2004 4:08 pm Post subject: |
|
|
Does your host run cPanel? Click on MySQL Databases, create a new user, and give it full privs on the db, and then try to login using the new username/password.
As for grant syntax:
http://dev.mysql.com/doc/mysql/en/GRANT.html
Code: | GRANT ALL ON mywebsite_com_-_forums.* TO trucker IDENTIFIED BY 'password'; |
For some reason, you can't create databases or users using SQL if the host is running cPanel, or at least on the hosts I've tried, so I suggest you use cPanel if your hosts runs it. |
|
Back to top |
|
|
Korr.ban Tux's lil' helper
Joined: 05 Jul 2004 Posts: 98 Location: Ex Inferis
|
Posted: Mon Dec 27, 2004 9:33 pm Post subject: |
|
|
Sadly this host does not run cPanel. I wish they did, I wish even more they had given me proper access to mysql through SSH.
As for the grant command. I have tried that and it gives me an error every time. Either a syntax error for the command text passed mywebsite_-_forums
It tells me everything starting with -_forums TO ... is incorrect syntax.
I have also tried using mywebsite_com_.* as the DB but it tells me ACCESS DENIED LOL.
Thanks for your help.
P.S. Are all webhosts so bad with importing old mysql DBs or am I just lucky?
I recently found a host that offers 400gigs/month but I hope they have some better way of managing their databases.
www.canaca.ca is the 400gig/month host incase you need some bandwidth. _________________ Registered Linux User #375052
DevShell - Viva La Revolusion! |
|
Back to top |
|
|
Korr.ban Tux's lil' helper
Joined: 05 Jul 2004 Posts: 98 Location: Ex Inferis
|
Posted: Mon Dec 27, 2004 10:00 pm Post subject: |
|
|
Oh my GOD! The solution was as simple as DELETING the mywebsite_com_-_forums and creating a new DB named mywebsite_com_-_forum (Without the s). I then went into SSH and did the following:
>mysql -ppassword
>use mywebsite_com_-_forum
>\. saved_db.sql
QUERY OK: Imported ...
....
...
...
DONE!
SOLVED _________________ Registered Linux User #375052
DevShell - Viva La Revolusion! |
|
Back to top |
|
|
|