Here is a background – I have two desktops and a laptop that I generally use for development purposes. One of the desktop is my database server. However, when I tried to access the database from either my development laptop or a development desktop, I could not remotely connect to the mysql database that was running just a feet away under my desk within the same network.
Then a little research revealed that the mysql needs to approve a remote usage. There were tons of solutions online, not as close to this one.
All I basically had to do was three basic steps
- Add the remote user with it’s ip address in the mysql database (mysql.user)
- Grant permissions for this new user.
- Then flush the privileges.
My tip is create a unique user id that is not used locally or anonymously (‘user’@’localhost’ or ‘user’@’%’). Although the localhost one should not be a problem, the anonymous one (%) did create a problem by trumping my other ip addresses.
Here is how you can create user id – I suggest you do this from the database server, although it is possible to do it if you already have a remote access established.
NOTE: These commands are run either mysql prompt or your GUI CLIENT
CREATE USER 'someuser'@'192.168.0.77' IDENTIFIED BY 'somepassword';
This will add a user on mysql.user table.
The step to grant permission is:
GRANT ALL ON *.* TO ‘someuser’@‘192.168.0.77’;
This is granting all permissions, but you can choose your specific ones.
Finally flush the privileges.
FLUSH PRIVILEGES;
If your client’s password changes (for example due to router restart), then you can update the user table to change the host.
update user set host=’192.168.1.10′ where host=’192.168.0.8′;
FLUSH PRIVILEGES;
In rare cases, you may need to restart the mysql or OS.