1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need Help Setting Up Separate MySQL Database Server

Discussion in 'Server Configuration and Hosting' started by BamaStangGuy, Apr 5, 2012.

  1. BamaStangGuy

    BamaStangGuy Well-Known Member

    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.

    Code:
    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:
    Code:
    [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:

    Code:
    [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:

    Code:
    $config['db']['host'] = '10.179.130.242';
    $config['db']['port'] = '3306';
    Trying to connect via web server:

    Code:
    [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/
     
  2. BamaStangGuy

    BamaStangGuy Well-Known Member

    my.cnf file:

    Code:
    [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
    
     
  3. bambua

    bambua Well-Known Member

    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
     
  4. BamaStangGuy

    BamaStangGuy Well-Known Member

    Ping is successful

    Code:
    [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:
    Code:
    mysql> GRANT ALL ON sec_mysql.* TO sec_xenforo@'10.179.133.27' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.00 sec)
     
  5. bambua

    bambua Well-Known Member

    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.
     
  6. BamaStangGuy

    BamaStangGuy Well-Known Member

    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.
     
  7. bambua

    bambua Well-Known Member

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

    Ghan_04 Active Member

    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).
     
    Darkimmortal likes this.
  9. bandit

    bandit Member

    He got it working. It was an iptables issue. :)
     
    BamaStangGuy likes this.

Share This Page