Grant remote access of MySQL database

Grant remote access of MySQL database

52
0
SHARE
Linux Reseller Hosting

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.

 

Windows Shared Hosting

Our Partner

partner-cloudflare
partner-cloudlinux
partner-cpanel-whm
partner-Paralleles-plesk-panel
partner-sitelock
partners-OpenVZ
R1Soft
partners-RV-Globalsoft
partners-Softaculous-auto-installer
partners-solusvm
partners-whmcs
services-cpanel
services-MySQL
services-Perl
services-swsoft-plesk
support-sevices-php