How To Enable remote access to MySQL database server?

Ok, The article is because there is no good article how to configure 2 linux servers to use 1 Database server.

The idea is to make two separate servers – 1 server for all static files and the code of the application and another one only for the Database!

So here are the steps which we have to take:

1. Configure mysql server to use remote connection

2. Configure the iptables of both servers to allow connections and ports

3. Change the settings of the application
Let’ Start!

Step 1

1. First we need to configure a file which is located in /etc/ directory of linux. So we go and configure it like this

# cd /etc
# vi  /etc/my.cnf

For example, if your MySQL server IP is 192.162.0.3 then entire block should be look like as follows:

[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.0.3
# skip-networking
....
..
....

Where,

  • bind-address : IP address to bind to.
  • skip-networking : Don’t listen for 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 removed from file or put it in comment state.

Then we have to Restart mysql service to take change in effect:

/etc/init.d/mysql restart

or if it’s with plesk:

/service mysqld restart

The next step is to grant some privileges to the user and database. You can do this through the phpmyadmin but it’s easier and faster with SSH icon smile How To Enable remote access to MySQL database server? .

# mysql -u root -p mysql

Grant access to new database
If you want to add new database called foo for user bar and remote IP 202.54.10.20 then you need to type following commands at mysql> prompt:

mysql> CREATE DATABASE test;
mysql> GRANT ALL ON test.* TO user@'192.168.0.2' IDENTIFIED BY 'PASSWORD';

How Do I Grant access to existing database?

Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database:

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

After you make the necessary changes just exit from the mysql

mysql> exit

Step 2

After we make the changes in the mysql server we have to change the iptables (firewall) settings so everything will work after we turn on the firewall.

On the database machine we have to configure the iptables to allow port 3306

# cd /etc/sysconfig/
# vi iptables

If you don’t have such a file probably it’s located in iptables.d folder.

So you have to put a line like this:

-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

Then just restart the iptables – /etc/init.d/iptables restart

After you make these changes in the DB server, you have to allow OUTPUT connection to the DB server for File server in the same way:

# cd /etc/sysconfig/
# vi iptables

If you don’t have such a file probably it’s located in iptables.d folder.

-A OUTPUT -p tcp -m tcp --dport 3306 -j ACCEPT

Then just restart the iptables again – /etc/init.d/iptables restart

Step 3

After you are ready with all of these things you just try if everything is ready from the Files Server:

# telnet 192.168.0.3 3306

if everything is ok then

# mysql -u user -h 192.168.0.3 -p

Then if the connection is ok everything is configured well.

Finally change the host of your application to the remote host

$host = “192.168.0.3″;

I hope that it will help everybody who wants to know how to configure remote mysql connections icon smile How To Enable remote access to MySQL database server? .

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.