Enable Remote Access to Database Server

centos-logo

I have two servers for serve as website, 10.0.0.10 as webserver and 10.0.0.11 as database server. And then how to setup that. Both of servers run CentOS 6.7.

In Database Server:

  1. Install mysql-server | this step from rackspace.com
    $ sudo yum install mysql-server
    $ sudo service mysqld start
  2. Then, run the following command:
    $ sudo /usr/bin/mysql_secure_installation

    when section ask about remote access, I do yes, server can act remote acces

  3. Launching at reboot
    $ sudo chkconfig mysqld on
  4. Setup firewall to enable access from only webserver (10.0.0.10), end open port 3306
    $ sudo su
    # iptables -A INPUT -i eth0 -s 10.0.0.10 -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
    # iptables -A OUTPUT -p tcp --sport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
    # service iptables save
    # service iptables restast
    # iptables -L
  5. Setting my.cnf to set server bind-address | step from cyberciti.biz
    $ sudo vim /etc/my.cnf

    At [mysqld] section add line:

    bind-address=10.0.0.11
    $ sudo service mysqld restart

Testing from both servers, database server and webserver

  1. Using telnet
    $ echo X | telnet -e X 10.0.0.11 3306
  2. Using direct login
    $ mysql -u root -h 10.0.0.11 -p

Error

But after try to connect to a remote MySQL database from web server, I get “ERROR 1130: Host is not allowed to connect to this MySQL server”. I forget add permission for spesific user and ipaddress in mysql. Do it in database server.

$ mysql -u root -p
Enter password:
mysql> use mysql
mysql> GRANT ALL ON *.* to root@'10.0.0.10' IDENTIFIED BY 'your-root-password'; 
mysql> FLUSH PRIVILEGES;
mysql> exit:

End do testing again. Thanks to thegeekstuff.com for this guide.

If you had problem with wrong rule in iptables, this guide from DigitalOcean maybe can help you.

That all.
Estu~