{"id":1804,"date":"2012-03-13T10:59:11","date_gmt":"2012-03-13T14:59:11","guid":{"rendered":"http:\/\/linuxhostingsupport.net\/blog\/?p=1804"},"modified":"2013-03-20T08:38:23","modified_gmt":"2013-03-20T12:38:23","slug":"how-to-enable-remote-access-to-mysql-database-server","status":"publish","type":"post","link":"https:\/\/linuxhostingsupport.net\/blog\/how-to-enable-remote-access-to-mysql-database-server","title":{"rendered":"How to Enable Remote Access to Mysql database server?"},"content":{"rendered":"

By default, Mysql doesn’t allow remote connections for security reasons.<\/strong> However, sometimes you or your client may want to acess the database remotely i.e. from a different server Or from home Or a developer who is working on the website.<\/p>\n

To grant remote access to a Mysql databas, the first thing is to make sure ‘skip-networking’ is not enabled in Mysql configuration<\/strong> i.e. in the my.cnf file.<\/p>\n

On a CentOS\/RHEL server<\/strong>, the file is located at \/etc\/my.cnf<\/strong>
\nOn a Debian\/Ubuntu server<\/strong>, the file is located at \/etc\/mysql\/my.cnf<\/strong><\/p>\n

1)<\/strong> SSH to the server and comment\/remove the line from my.cnf that says:<\/p>\n

\n
skip-networking<\/span><\/pre>\n<\/blockquote>\n
where,
\n        skip-networking means don’t listen on TCP\/IP connections but only from Unix sockets<\/strong>. This is recommended on systems where only local connections are allowed. However, as you need to allow remote connections, skip-networking must be removed\/commented in my.cnf as stated above.<\/p>\n
2)<\/strong> Save the file and restart the mysql service<\/p>\n
\n
# \/etc\/init.d\/mysqld restart<\/span><\/pre>\n<\/blockquote>\n
3)<\/strong> Connect to the Mysql server to grant access to a remote IP address.<\/p>\n
\n
# mysql -uroot -p<\/span><\/pre>\n<\/blockquote>\n
4)<\/strong> Now, for example, if you want to allow access to database ‘db_name’ for user ‘db_user’ and the remote IP is 1.1.1.1, the mysql command should be as follows:<\/p>\n
\n
mysql> use mysql<\/span>\r\nmysql> GRANT ALL ON db_name.* TO 'db_user'@'1.1.1.1' IDENTIFIED\\<\/span>\r\nBY 'PASSWORD';<\/span><\/pre>\n<\/blockquote>\n
If your IP is NOT static and you want to grant access to the database from any IP, use % instead of IP, i.e.<\/p>\n
\n
mysql> GRANT ALL ON db_name.* TO 'db_user'@'%' IDENTIFIED\\<\/span>\r\nBY 'PASSWORD';<\/span><\/pre>\n<\/blockquote>\n
5)<\/strong> Logout of Mysql and make sure port 3306 is open in your server firewall. Test the remote mysql connection from your local computer Or a different server:<\/p>\n
\n
# mysql -u db_user -h serverIP -D db_name -p<\/span><\/pre>\n<\/blockquote>\n
where,
\n        -u db-user<\/strong>: db_user is the database username.
\n        -h serverIP<\/strong>: ServerIP is the IP of your mysql server.
\n        -D db_name<\/strong>: db_name is the database name
\n        -p<\/strong>: password of the database user<\/p>\n","protected":false},"excerpt":{"rendered":"
By default, Mysql doesn’t allow remote connections for security reasons. However, sometimes you or your client may want to acess the database remotely i.e. from a different server Or from home Or a developer who is working on the website.
\nTo grant remote access to a Mysql databas, the first thing is to make sure ‘skip-networking’ is not enabled in Mysql configuration i.e. in the my.cnf file.
\nOn a CentOS\/RHEL server, the file is located at \/etc\/my.cnf
\nOn a Debian\/Ubuntu server, the file is located at \/etc\/mysql\/my.cnf
\n1) SSH to the server […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[111],"tags":[1364,1367,1363,1366,1362,1365],"_links":{"self":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1804"}],"collection":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/comments?post=1804"}],"version-history":[{"count":10,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1804\/revisions"}],"predecessor-version":[{"id":2084,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1804\/revisions\/2084"}],"wp:attachment":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/media?parent=1804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/categories?post=1804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/tags?post=1804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}