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.
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′;
In rare cases, you may need to restart the mysql or OS.