We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Trying to connect directly to a mysql database using SQLYog

purfus
purfus asked
on
Medium Priority
1,599 Views
Last Modified: 2012-06-27
Ok, for those of you that dont use SQLYog (I feel bad for you if your using phpmyadmin crap) it allows you to either connect directly to a mysql database or to connect threw what it calls a tunnel.  Which is basically a php script on the server that the program uses as an interface to the server.  The tunnel works just ducky but sqlyog pro does not support tunnelling and the trial to enterprise ran out.  I already paid 9 bucks for pro and I dont want to pay another 70 for enterprise.  I have a dedicated server with the mysql database on it and I have ssh access, and plesk ofcourse to do whatever I want with the thing.  So I see no reason why I shouldn't be able to connect directly to the mysql server.  I have used my login and password in ssh to log into the mysqladmin interface.  I use the user and password to connect in my php files so I know the user/pass I am trying to use is valid.  When I try to connect in sqlyog it says error # 1045, access denied for bla bla bla, user password (yes).  Same thing I get in ssh if I use a bad user/pass.  This tells me it is communicating with the mysql server because if I put in a bad ip address it just times out.  Like I said I know the user/pass I have is good.  Anyone have any idea what might be causing this?  I've tried refreshing the mysql server but the damn thing tells me I dont have permission.  Which is really weird because my user/pass should have full access to everything because its the account I gave to it in plesk.  I'm not opposed to reinstalling the damn mysql server.  But I tried the other day and got a bit frightened off by all the dependencies it gave.  Plesk gives me like next to no help...  I'm logging into plesk with the admin account and it doesn't let me do much of anything with database.  I can alter my account and change a few preferences but its pretty limited.  Which doesn't surprise me, sometimes I think I would have been better off not getting plesk and just forcing myself to learn all the stuff I need to learn to administer threw ssh.  Well I hope someone can help me with this.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
When you log in with SSH, you are not logging in to MySQL remotely, it's using your local account to access MySQL.  You need to grant priviledges to your MySQL account for remote access so that it allows your hostname to connect:

grant all privileges on yourdatabase.* to username@'%' identified by 'password'

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for the help.  I just tried that and it told me access denied for user...  I'm logged in as root, and logged into mysql with the only username I know to exist on there 'purfus'.

Author

Commented:
Ok I just found the admin account password and got in there and did the query and it said 0 rows affected.
I put in:

grant all privileges on goclans.* to purfus@'%' identified by 'password';

Author

Commented:
i know the database name is correct and the username is also correct.

Author

Commented:
mysql> SELECT Host, User, password FROM mysql.user;
+-----------+--------+------------------+
| Host      | User   | password         |
+-----------+--------+------------------+
| localhost | admin  | ************** |
| localhost | horde  | ************** |
| localhost | purfus | ************** |
| %         | horde  |                  |
| %         | purfus | ************** |
+-----------+--------+------------------+
5 rows in set (0.00 sec)

Author

Commented:
I think it is doing it despite what it says because I did the privilege query on admin and did a select statement and got this, which now has the admin account in a % as well.


mysql> SELECT Host, User, password FROM mysql.user;                             +-----------+--------+------------------+
| Host      | User   | password         |
+-----------+--------+------------------+
| localhost | admin  | ************** |
| localhost | horde  | ************** |
| localhost | purfus | ************** |
| %         | horde  |                  |
| %         | purfus | ************** |
| %         | admin  | ************** |
+-----------+--------+------------------+
6 rows in set (0.00 sec)

Author

Commented:
I did a FLUSH PRIVILEGES and tried to login in and recieved the same results.

Author

Commented:
DOH, I got it WEEEE  Thank you soo much.  The problem I was having was I was putting in password instead of my password thinking it was reffering to a column name.  Only reason I noticed was the password in the select was a different hash value.  lol...  Well thank you, you've solved my problem.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.