Enable Remote Access To MySQL Database Server
Most of the linux servers do not provide remote access for mysql database. That’s way the website developer use the hostname as localhost while creating a mysql database connection with your website or while installing CMS like Joomla or WordPress.

Turning on the remote access on Linux server is as simple as ABC, only the need is that you should have root access to server.

Following are the steps to enable the remote access for mysql database servers.

1.    Login to mysql server using SSH.

2.    Go to location /etc using following command.
cd /etc

3.    Then open the file my.cnf using following command to edit.
vi my.cnf

4.    Once the file is open search for the [mysqld] in the file.

5.    Make sure that the line skip-networking is commented as below or you can delete the line. This option only require where local servers requests are allowed. As you are enabling the remote access to mysql server the following option should be commented.

# skip-networking
6.    Then you have to add the following line instead. YOUR-SERVER-IP should be replace by your server’s IP address.
bind-address=YOUR-SERVER-IP
7.    For example if the server’s IP address is 192.168.99.88 the [mysqld] block should look like this,

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 192.168.99.88

8.    After this save the file and then close it.
9.    Restart the httpd service using command below.
$ Service httpd restart   OR $ /etc/init.d/mysql restart

10.    Now here we need to grant the access to remote IP addresses.
11.    Login to mysql using following command.
$ mysql -u root -p root_mysql_password
12.    If you wish to grant remote access to your existing database Payroll for user STEVE, you will need to do this using following command.

Mysql> update db set Host=’230.65.20.40’ where Db=’payroll’;
mysql> update user set Host=’230.65.20.40′ where user=’STEVE’;

Mysql> GRANT ALL ON payroll.* TO STEVE@’230.65.20.40’ IDENTIFIED BY ‘PASSWORD’;

13.    In above mysql command the IP address ’230.65.20.40’ is the IP address of remote machine (say IP address of STIVE’S machine).

14.    If you wish to give remote access to new database use the following command.

mysql> CREATE DATABASE newDatabase;

mysql> GRANT ALL ON foo.* TO NewUser@’230.65.20.40’ IDENTIFIED BY ‘PASSWORD’;

15.    Use the following command to add the rule for granting access on port no 3306 into Linux iptable firewall

/sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT

16.    Then save the rules using command below.

service iptables save

17.    That’s it. Now it’s time to test the remove database connection. You can do so using following command.

$ telnet 192.168.99.88 3306

Send to Facebook