Need Help Setting Up Separate MySQL Database Server

[Brent W]

I am trying to setup a dedicated MySQL server that my Web Server can connect to but I am having issues and I think it may be iptables related.

Here is what I have done so far:

10.179.133.27 = Web Server Private IP
10.179.130.242 = Database Server Private IP

Installed MySQL and have it running successfully. I have created the database and imported the data I need into it. I can query the database fine from the database server.

I then created a user and gave it permission for that database. 10.179.133.27 is my webserver private IP.

mysql> GRANT ALL ON sec_mysql.* TO sec_xenforo@'10.179.133.27' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT User, Host, Password FROM mysql.user;
+-------------+---------------+-------------------------------------------+
| User        | Host          | Password                                  |
+-------------+---------------+-------------------------------------------+
| root        | localhost    |  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  |
| root        | Database      |  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  |
| root        | 127.0.0.1    | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root        | ::1          |  |xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| sec_xenforo | 10.179.133.27 |  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+-------------+---------------+-------------------------------------------+

This is the iptables on my database server:

[root@Database mysql]# iptables --list
Chain INPUT (policy ACCEPT)
target    prot opt source              destination
ACCEPT    all  --  anywhere            anywhere            state RELATED,ESTABLISHED
ACCEPT    icmp --  anywhere            anywhere
ACCEPT    all  --  anywhere            anywhere
ACCEPT    tcp  --  anywhere            anywhere            state NEW tcp dpt:ssh
REJECT    all  --  anywhere            anywhere            reject-with icmp-host-prohibited
ACCEPT    tcp  --  10.179.133.27        10.179.130.242      tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
 
Chain FORWARD (policy ACCEPT)
target    prot opt source              destination
REJECT    all  --  anywhere            anywhere            reject-with icmp-host-prohibited
 
Chain OUTPUT (policy ACCEPT)
target    prot opt source              destination
ACCEPT    tcp  --  10.179.130.242      10.179.133.27      tcp spt:mysql dpts:1024:65535 state ESTABLISHED

Web Server iptables:

[root@WebServer httpd-2.4.1]# iptables --list
Chain INPUT (policy ACCEPT)
target    prot opt source              destination
ACCEPT    all  --  anywhere            anywhere            state RELATED,ESTABLISHED
ACCEPT    icmp --  anywhere            anywhere
ACCEPT    all  --  anywhere            anywhere
ACCEPT    tcp  --  anywhere            anywhere            state NEW tcp dpt:ssh
ACCEPT    tcp  --  anywhere            anywhere            state NEW tcp dpt:http
ACCEPT    tcp  --  anywhere            10.179.130.142      tcp spt:mysql dpts:1024:65535 state ESTABLISHED
REJECT    all  --  anywhere            anywhere            reject-with icmp-host-prohibited
 
Chain FORWARD (policy ACCEPT)
target    prot opt source              destination
REJECT    all  --  anywhere            anywhere            reject-with icmp-host-prohibited
 
Chain OUTPUT (policy ACCEPT)
target    prot opt source              destination
ACCEPT    tcp  --  10.179.130.142      anywhere            tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED

My xenforo config:

$config['db']['host'] = '10.179.130.242';
$config['db']['port'] = '3306';

Trying to connect via web server:

[root@WebServer httpd-2.4.1]# mysql -u sec_xenforo -p -h 10.179.130.242
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '10.179.130.242' (113)

What am I missing or doing wrong?

Live website that is down because it can't connect to database: http://www.secfootballforums.com/

 

[Brent W]

my.cnf file:

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql
 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
bind-addres=10.179.130.242
port=3306
# skip-networking
#
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so
 
# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1
 
# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema
 
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

[bambua]

Just as a test, from your web server can you:

• ping 10.179.130.242
• telnet 10.179.130.242 3306

Also when you set up the database, for the user what did you use for the host setting? Is it % for any host, or just localhost, or did you set up an entry for 10.179.133.27

 

[Brent W]

Ping is successful

[root@WebServer httpd-2.4.1]# telnet 10.179.130.242 3306
Trying 10.179.130.242...
telnet: connect to address 10.179.130.242: No route to host

This is how I created the user:

mysql> GRANT ALL ON sec_mysql.* TO sec_xenforo@'10.179.133.27' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

 

[bambua]

Ping is successful

[root@WebServer httpd-2.4.1]# telnet 10.179.130.242 3306
Trying 10.179.130.242...
telnet: connect to address 10.179.130.242: No route to host

This is how I created the user:

mysql> GRANT ALL ON sec_mysql.* TO sec_xenforo@'10.179.133.27' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

The failure for the telnet means that somewhere, either your web machine is denying you to connect to a remote machine on that port, or the remote machine is refusing the connection.

 

[Brent W]

I figured it was an iptables issue. I have tried all kinds of rules with no success. How I have it setup currently is listed above.

 

[bambua]

I figured it was an iptables issue. I have tried all kinds of rules with no success. How I have it setup currently is listed above.

Sadly, I don't know iptables very well :/

 

[Ghan_04]

Well, just as a test, you could try flushing your iptables and restarting MySQL to confirm that that is indeed the issue.
However, in any case, I would probably use an SSH tunnel for MySQL so that the traffic is guaranteed secure. That can also allow you to still use "localhost" on the client side when connecting.

On a related note, is there any particular reason that you want to use a dedicated database server? My experience has been that there is a noticeable latency increase when you do this unless you have a really fast private network (1 Gbps or more).

 

[bandit]

He got it working. It was an iptables issue.