Ssh

[Forbairt]

Are you exporting and importing in the same encoding as well ?

was it you I responded to before regarding importing a sql file ?

are both servers running the same versions ?
Somehow I doubt running mysqldump will improve matters

 

[webman1]

Don't think it was me regarding importing before.

The databases are stored on the same shared Hosting365 server

 

[Forbairt]

what is the problem when you get to importing ?

you've created the other database yes ? ... is the Collation the same on both databases ?

Does the database contain binary data ?

 

[webman1]

Yes, i created a new database in CPanel. No data exists in it.

I exported from main database and tried to import it into new database but getting access denied errors.

Databases are same collation

 

[Forbairt]

you'll have to give create / potentially drop .. and various other permissions to the user you're importing as

 

[webman1]

I login with the Admin user for the database? I don't know whats the story

Perhaps i'm exporting it incorrectly.

This is what i am doing.

I log in to database and go to Export
I choose Select All
The DatabaseName is highlighted and i click go and it saves to file

Then i logout, and i login to new database with other admin credentials and choose import and select file and go.

Am i doing something wrong?

 

[Forbairt]

from the cpanel ...

Databases -> MySQL -> MySQL DBs

select your new database that you created ...

Database Users should list some users ...
click the edit icon beside the user you are using ... and make sure they have all the boxes ticked ..

only use this user to administer the database

Create a new user after that that can read .. and if needs be read / write to the database

hopefully that should solve your problem ?

When you assign a user to the database it by default I think has Read only access you've to change it to DBA which is database admin ...

let us know how you get on

 

[webman1]

I only have one user per database and both are set to DBA privileges with all options ticked

They sure don't make things easy

 

[Forbairt]

Hmm.. All I can think is you've logged in with the wrong user when you go back to phpmyadmin ...


I just tried it there and its working fine ... so I say give hosting365 a trouble ticket and see what comes back ..

 

[webman1]

Thanks for your assistance earlier Forbairt

Me being a dumbass was exporting the whole database instead of clicking into the database first and then selecting all tables and then exporting all

 

[Forbairt]

No worries ... its nearly always something rather silly

would explain the not having permissions as you aren't able to create the database ... then it probably selects the database and hence screws up the rest of the import (can't remember if phpmyadmin dies on first error it encounters though ... I have a suspicion it does)

And this way you've learned about ssh ... cron jobs ... mysqldump ... and have one less mistake to worry about in future no doubt