≡ Menu

MySQL: Connect From an Other System / Computer

How do I connect to my MySQL database server from an other server (say Apache or Tomcat app server) in same VLAN under CentOS / Fedora / RHEL / Redhat Linux?

First, you need to turn on the remote access for your database server.

Sample Setup

Consider the following sample setup:

                              +----------- server1 192.168.1.6
                              | 
                              +------------ tomcat1 192.168.1.7
                              | 
 +------------------+         |
 | MySQL Server     | --------+------------ apache2 192.168.1.8
 | 192.168.1.5:3306 | --------+
 +------------------+         |
     LAN      192.168.1.0/24  +------------- pc1 192.168.1.51
                              |
                              |
                              +-------------- pc25 192.168.1.76

You need to allow access to 192.168.1.5 from apache server located at 192.168.1.8.

Step #1: Configure MySQL Server For Remote Access

Open a terminal or login to 192.168.1.5 using the ssh command:
$ ssh root@192.168.1.5
Edit /etc/my.cnf, enter:
# vi /etc/my.cnf
Modify or append as follows:

# make sure the following line is deleted or commented out
# skip-networking
bind-address    = 192.168.1.5

Save and close the file. Restart the mysql server, enter:
# service mysqld restart

Make Sure TCP Port # 3306 is Opened For Business

Verify that the TCP port 3306 is open, enter:
# netstat -tulpn | grep :3306

Step #2: Linux Firewall Configuration For TCP Port # 3306

You need to open TCP port # 3306 at the firewall level, enter:
# iptables -A INPUT -i eth0 -s 192.168.1.8 -p tcp --destination-port 3306 -j ACCEPT
# service iptables save

Step #3: Configure Database Remote Access

You need to grant access to an existing database called salesdb from remote IP called 192.168.1.8 using a username called foo. First, connect to mysql server as root user, enter:
# mysql -u root -p mysql
Type the following command At mysql> prompt, enter:
mysql> update db set Host='192.168.1.8' where Db='salesdb';
mysql> update user set Host='192.1681.8' where user='foo';
mysql> \q

Login to 192.168.1.8 and type the following command to test mysql server remote access:
$ mysql -u foo -h 192.168.1.5 -p salesdb
Sample outputs:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27720995
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 
Share this tutorial on:

Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft, takes a lot of my time and hard work to produce. If you use nixCraft, who likes it, helps me with donations:
Become a Supporter →    Make a contribution via Paypal/Bitcoin →   

Don't Miss Any Linux and Unix Tips

Get nixCraft in your inbox. It's free:



{ 4 comments… add one }
  • marius April 19, 2011, 8:20 am

    great article as always!
    I find it more intuitive and secure to forward a a local port
    ssh -NL 5000:localhost:3306 ssh@server.com
    and then connect locally by specifying the forwarded port
    mysql -u mysqluser -P 5000 -p

  • kean December 6, 2012, 4:47 am

    hi !
    i dont see this # skip-networking on my my.cnf

  • Iraj January 30, 2013, 9:33 pm

    Hi,

    It’s very nice and complete document for newbies such as me..!

    good luck

  • Roman August 8, 2014, 7:51 pm

    Thank you very much for this guide!

    Just wanted to point out that “service iptables save” didn’t work on my Ubuntu server and instead I had to use “iptables-save”.

Security: Are you a robot or human?

Leave a Comment

You can use these HTML tags and attributes: <strong> <em> <pre> <code> <a href="" title="">


   Tagged with: , , , , , , , ,