It could be your admin user is connecting from admin@127.0.0.1
.
Assuming admin is the actual user you have privileges on, try connecting through:
> mysql -h 127.0.0.1 -u admin -p
The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.
If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.
SELECT User, Host FROM mysql.user
The message itself looks like an authentication issue.
Here is what you should do: If you can connect to the hosting company's mysql database from the mysql client, run this command:
SELECT USER() RequestedUserLogin,CURRENT_USER() AllowedUserLogin;
What will these functions give you ?
- USER() reports how you attempted to authenticate in MySQL
- CURRENT_USER() reports how you were allowed to authenticate in MySQL
It's the second function you need to be concerned with because it says you have the same user permissons as AllowedUserLogin
.
@Abdul's answer reveals a deeper issue: You created the mysqldump which has the command to create the database. When you mysqldump with the --databases option, the create database mydb;
is inserted before the use mydb;
. You can hide the create database mydb;
by doing
mysqldump -u... -p... --no-data --databases mydb > mydbschema.sql
mysqldump -u... -p... --no-create-info mydb > mydbdata.sql
This will place the database and table creation statements in mydbschema.sql
while the bulk INSERTs are placed in mydbdata.sql
.
If you cannot run mydbschema.sql
then have the hosting company create the database for you. You could also use whatever DB tools they have setup for you to create the database yourself.
You could then load the data using
mysql -umyuser -pmypassword -h(IP-of-DBServer) -Dmydb < mydbdata.sql
I also noticed the user mysql was expecting 'myuser'@'10.%' as the user to authenticate. You might need 'myuser'@'IP-of-DBServer' defined on the database mydb with this:
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer';
Give it a Try !!!
Best Answer
Please keep in mind that when you run
USER() reports how you attempted to authenticate in mysqld
CURRENT_USER() reports how you were allowed to authenticate by mysqld
This means you should run this query
or just
to actually see your credentials. If you run,
you may not see credentials if they do not exist in
mysql.user
.Just try to make the appropriate user on the MySQL Instance.
UPDATE 2013-03-05 16:51 EST
In you comment, you said
Since you only have rights to the
lfc_123awards
database, please make sure you are specifying that database when you connect. If you do not set the default database at authentication, it will not let you connect at all.Here is what I mean: Let say you trying connecting from the command line
This should fail, whereas
should work.