Access MySQL in Network

The other day, I was just wondering, what if I want to give access to MySQL on my machine to one of my friend who is in LAN.  Well, it was just as simple as it could be.

To give access to other users in network so that they can access the MySQL on your machine, all you need to do is :

 

1. In  MySQL configuration file (ideally located at /etc/mysql/my.cnf), give the IP address to "bind-address"
    e.g.  bind-address = 172.27.141.81

2. Give previleges to that particular IP or all(by giving %) for particular user to do the needful in MySQL.

Make sure, you do not forget to create the MySQL user and manage the permissions accordingly.  It's just that, giving a full access of root to your server to any other user is BIG NO NO.

 

CREATE USER 'test-user'@'%';

GRANT ALL PRIVILEGES ON * . * TO  'test-user'@'%' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

-OR-

REVOKE ALL PRIVILEGES ON * . * FROM  'test-user'@'%';
REVOKE GRANT OPTION ON * . * FROM  'test-user'@'%';

GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , FILE , INDEX , CREATE TEMPORARY TABLES , CREATE VIEW , EVENT, TRIGGER, SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON * . * TO  'root'@'%' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;