Grant remote access of MySQL database

Grant remote access of MySQL database

If you try to connect to a remote MySQL database from your client system, you will get “ERROR 1130: Host is not allowed to connect to this MySQL server” message as shown below.

$ mysql -h 192.168.1.8 -u root -p
Enter password:
ERROR 1130: Host '192.168.1.4' is not allowed to connect to this MySQL server

A) Change mysql config

1: Start with editing mysql config file

vim /etc/mysql/my.cnf

2: Comment out following lines.

#bind-address           = 127.0.0.1
#skip-networking

If you do not find skip-networking line, then add it and comment out it.

3: Restart mysql server.

service mysql restart

B) Change GRANT privilege

1: Run a command like below to access from all machines.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

2: Run a command like below to give access from specific IP.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

3: Check final outcome by:

SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";

4: Finally, you may also need to run:

mysql> FLUSH PRIVILEGES;

3) Test Connection

1: From terminal/command-line:

mysql -h HOST -u USERNAME -pPASSWORD

Note: If you get a mysql shell, don’t forget to run show databases; to check if you have right privileges from remote machines.

4) Bonus-Tip: Revoke Access

1: Following will revoke all options for USERNAME from all machines:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';

2: Following will revoke all options for USERNAME from particular IP:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';

3: To check information_schema.user_privileges table after running REVOKE command.