Change mysql config
Start with editing mysql config file
Comment out following lines.
#bind-address = 127.0.0.1 #skip-networking
If you do not find skip-networking line, add it and comment out it.
Restart mysql server.
service mysql restart
Change GRANT privilege
You may be surprised to see even after above change you are not getting remote access or getting access but not able to all databases.
By default, mysql username and password you are using is allowed to access mysql-server locally. So need to update privilege.
Run a command like below to access from all machines.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
Run a command like below to give access from specific IP.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'22.214.171.124' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
You can replace
126.96.36.199 with your IP. You can run above command many times to GRANT access from multiple IPs.
You can also specify a separate
PASSWORD for remote access.
You can check final outcome by:
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";
Finally, you may also need to run:
mysql> FLUSH PRIVILEGES;
mysql -h HOST -u USERNAME -pPASSWORD
If you get a mysql shell, don’t forget to run
show databases; to check if you have right privileges from remote machines.