Table of Contents
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.