1

I've got a 3 servers & 1 Windows machine running in a LAN

HTTP server (Apache2, PHP5 & phpmyadmin) - 10.0.0.10
DNS - 10.0.0.1
MySQL - 10.0.0.40
Windows - 10.0.0.157

I've just installed a fresh copy of mysql on the mysql server. I've commented the bind-address out first off. I've given privileges by doing the following.

CREATE USER 'https'@'localuser' IDENTIFIED BY 'password';
CREATE USER 'https'@'10.0.0.10' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'https'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'https'@'10.0.0.10';

However every time I try to connect to mysql via the http server the following happens

mysql -u https -p
enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 'var/run/mysqld/mysqld.sock'(2)

When I try

mysql -h 10.0.0.40 -P 3306 -u https -p

I get a different error

ERROR 1045 (28000): Access denied for user 'https'@'10.0.0.10' (Using password: Yes)`

I'm not sure why this is happening as far as I'm aware, I've done everything I need to configure? :(

In my mysql table for users I have the following

user  | host  
https  %
https   10.0.0.10
sqlserver   127.0.0.1
sqlserver ::1
debian-sys-maint  localhost
https    localhost
sqlserver    localhost
sqlserver    raspberrypi

Can someone point me in the right direction please? I've searched around and everything I see just points towards commenting the bind address line out.

Edit I can't even log in using locally using any user apart from sqlserver which was the root account that has been renamed. I've tried adding new accounts and logging in as them locally, still the same issue!

  • 1
    You should not comment out the bind address line (in /etc/my.cnf). Instead, you should point it to the server's IP address, i.e. 10.0.0.40, and restart MySQL. – Jos Apr 13 '15 at 19:52
  • I'll give this a try tomorrow and post the results – Xiah Apr 13 '15 at 20:02
  • Tried this and i now get error 1130 (hy000): host '10.0.0.10' is not allowed to connect to this mysql server when trying mysql -h 10.0.0.40 -u https -p and with mysql -u https -p i get error 2002 (hy000): can't connect to local mysql server through socket '/var/run/mysqld/mysqld.sock'(2) – Xiah Apr 14 '15 at 10:24
  • I did GRANT ALL PRIVILEGES ON *.* TO ‘https’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION; and GRANT ALL PRIVILEGES ON *.* TO ‘https’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION; and I can at least login locally to mysql, still can't connect remotely though :-( – Xiah Apr 14 '15 at 10:29
  • I tell a lie! I can connect using mysql -h 10.0.0.40 -u https -p fine. However using mysql -u https -p i still get the error 2002. But I don't care, it works! I can only assume I should have done GRANT ALL PRIVILEGES ON *.* TO ‘https’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION; and was missing the IDENTIFIED BY 'password' which has caused it to mess up. – Xiah Apr 14 '15 at 10:32
2

To answer my own question which Jos partly answered.

You should not comment out the bind address line (in /etc/my.cnf). Instead, you should point it to the server's IP address, i.e. 10.0.0.40, and restart MySQL.

That combined with

GRANT ALL PRIVILEGES ON *.* TO ‘https’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
instead of
GRANT ALL PRIVILEGES ON *.* TO 'https'@'10.0.0.10';
has fixed the issue. Hopefully this will help others having the same problem.

Thanks for the help guys!

1

if you execute

netstat -an | grep 3306

do you see an entry with LISTEN for port 3306?

  • I do this and nothing happens on my HTTP server – Xiah Apr 14 '15 at 10:24
  • Now that I can connect I get tcp 0 0 10.0.0.10:42605 10.0.0.40:3306 TIME_WAIT I can only assume that this is now correct and working fine? – Xiah Apr 14 '15 at 10:36
1

Is the service started? If not (or you're not sure), run:

sudo service mysql start
  • The server is already started thats correct – Xiah Apr 13 '15 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.