Tuesday, May 20, 2014

Configure Mysql so it can be accessed from remote console.

By default remote access to the MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. This post will explain how to setup a user account and access a mysql server remotely on a Linux or Unix-like systems. 

1. You need to confirm that your Mysql Server has static address. And you need to find that IP address.
2. Find the MySQL server configuration file my.cnf, 
§  If you are using Debian/Ubuntu Linux file is located at /etc/mysql/my.cnf location.
§  If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location.
§  If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf location.
3. Using a text editor such as nano to edit my.cnf

# vi /etc/my.cnf

4. Once the file is opened:

Make sure comment line start with skip-networkingskip-networking means MySql Server Doesn’t listen to TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf.

Then add following line to the configuration file, you need to replace YOUR-SERVER-IP with your server IP address. 
5. Now Connect to mysql server to grant access to a Database, please keep in mind the remote access is granted based on Database Level, not Server Level.

$ mysql -u root -p mysql
Let us assume that you are always making connection from IP range,To grant access to these IP addresses type the following command At mysql> prompt for existing database, enter:
mysql> grant all on magento.* to 'username'@'192.168.0.%' identified by 'password';
Type exit command to logout mysql:
mysql> exit

That should be it, you might need to open your server port for MySql, if that is the case, check other instruction for that if needed. 

No comments:

Post a Comment