user3413723 user3413723 - 3 months ago 7
MySQL Question

Can't connect remotely without bind-address, can't connect locally with it

I'm having an issue with mysql. I am trying to allow remote access. I found that if I write in my.cnf:

bind-address=my.ip.address

It works remotely, but using a php script that tries to access the database using the same ip doesn't work anymore!

If I remove this line, I can connect locally, but not remotely.

I tried setting it to 0.0.0.0, which has the same effect as if it were not there at all.

I saw that you can't bind to multiple addresses unfortunately. Any way to resolve?

Answer

MYSQL User accounts have 2 parts

  1. The userid
  2. The domain from which they can connect to this MYSQL Server instance

EG 'root'@'localhost'

So you need to create a new user account, you dont want to give access to the root id externally, and make this new userid allowed to connect from either a specific ip address (more secure) or any ip address

From the command line mysql.exe processor you woudl do this

CREATE USER 'somone'@'11.22.33.44' IDENTIFIED BY 'mypass';

for a specific ip address

Or

CREATE USER 'somone'@'%' IDENTIFIED BY 'mypass';

To allow access from any ip adddress in the universe, so better make the password a good strong one

Then you allocate privilages to one or more databases to this new account

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON database_name.*
TO 'someone'@'%';

References

CREATE USER http://dev.mysql.com/doc/refman/5.7/en/create-user.html

Grant http://dev.mysql.com/doc/refman/5.7/en/adding-users.html