{"id":6137,"date":"2015-03-31T01:45:59","date_gmt":"2015-03-30T17:45:59","guid":{"rendered":"https:\/\/webhostinggeeks.com\/howto\/?p=6137"},"modified":"2023-04-28T09:47:16","modified_gmt":"2023-04-28T09:47:16","slug":"how-to-enable-and-grant-remote-access-to-mysql-database-server","status":"publish","type":"post","link":"https:\/\/webhostinggeeks.com\/howto\/how-to-enable-and-grant-remote-access-to-mysql-database-server\/","title":{"rendered":"How to Enable and Grant Remote Access to MySQL Database Server"},"content":{"rendered":"

For reasons of security, remote access to MySQL database server is disabled by default because they are considered potential security threats. However, due to some reason, it is necessary to allow access from a remote location or web server. Let assume that we are making connection from remote web server IP called 192.168.0.3<\/strong> for database called db1<\/strong> for user user1<\/strong> at remote MySQL server, 192.168.0.2<\/strong>, then we need to grant access to this IP address.<\/p>\n

If the remote access is not enable you will get this error :<\/p>\n

ERROR 1130 (HY000): Host \u2018192.168.0.3\u2019 is not allowed to connect to this MySQL server\n<\/pre>\n
IP Adress 1 : 192.168.0.2 – MySQL Server
\nIP Adress 2 : 192.168.0.3 – Web Server (Nginx or Apache)<\/strong><\/p>\n
Steps to Enable and Grant Remote Access to MySQL Database Server<\/h7><\/p>\n
1. Edit the my.cnf file :<\/p>\n
# vim \/etc\/mysql\/my.cnf\n<\/pre>\n
Comment out or remove below line :<\/p>\n
#bind-address           = 127.0.0.1\n<\/pre>\n
2. The following command will allow access to the MySQL database(192.168.0.2) from a remote web server IP address(192.168.0.3):<\/p>\n
mysql> create user 'user1'@'192.168.0.3' identified by 'PASSWORD';\nmysql> grant all on db1.* to 'user1'@'192.168.0.3';\n<\/pre>\n
3. Test the connection from the remote web server :<\/p>\n
# mysql -u user1 -pPASSWORD -h 192.168.0.2\n<\/pre>\n
4. Verify the user privileges for user1 :<\/p>\n
mysql> select * from information_schema.user_privileges where grantee like \"'user1'%\";\n<\/pre>\n
5. In case you want to revoke all options the access from all machine or web server(192.168.0.3) only :<\/p>\n
mysql> revoke all privileges, grant option from 'user1'@'%';\n<\/pre>\n
mysql> revoke all privileges, grant option from 'user1'@'192.168.0.3';\n<\/pre>\n
\"database\"<\/p>\n","protected":false},"excerpt":{"rendered":"
For reasons of security, remote access to MySQL database server is disabled by default because they are considered potential security threats. However, due to some reason, it is necessary to…<\/p>\n","protected":false},"author":6,"featured_media":6184,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"wds_primary_category":0,"footnotes":""},"categories":[1006,1008],"tags":[1565,1585],"class_list":["post-6137","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mariadb","category-mysql","tag-mariadb","tag-mysql"],"_links":{"self":[{"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/posts\/6137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/comments?post=6137"}],"version-history":[{"count":0,"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/posts\/6137\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/media\/6184"}],"wp:attachment":[{"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/media?parent=6137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/categories?post=6137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/webhostinggeeks.com\/howto\/wp-json\/wp\/v2\/tags?post=6137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}