Remote MySQL gone sideways

Discussion in 'General' started by john boy, Sep 21, 2011.

  1. john boy

    john boy New Member

    Hi all
    Need to setup Remote access via port 3306
    Followed this help file
    /etc/Bastille/bastille-firewall.cfg
    Add port 3306 to line below so it reads
    TCP_PUBLIC_SERVICES="21 22 25 53 80 81 110 143 443 3306 10000"

    Restart firewall -
    /etc/init.d/bastille-firewall restart
    then
    /etc/mysql/my.cnf
    bind-address = *.*.*.*
    /etc/init.d/mysql restart

    No sites, admn console and no putty via wan
    No admin console, buthave sites and putty via lan

    This didn't work caus I was using Failban2 hummm...
    So i just changed it back
    Still not working via lan

    Please Help any direction is good

    John Boy
     
  2. till

    till Super Moderator

    The bind-address = *.*.*.* is wrong. To configure mysql to listen on all interfaces you have to comment out the bind address line.

    Regarding batsille firewall, never edit the config files manually, use always the ispconfig interface.
     
  3. john boy

    john boy New Member

    Thanks Till for the reply

    But as I said "So i just changed it back"
    So Iremarked out the bind-address = *.*.*.*
    restarted service
    This has the result of putty, website and admin console is access by lan access only
    Still no access via WAN address
     
  4. Mark_NL

    Mark_NL New Member

    - Add 3306 to the firewall config VIA ISPConfig web admin.
    - comment "bind-address" in my.cnf
    - restart mysql
    - show us the output of:
    Code:
    netstat -tapn | grep 3306
    iptables-save
    
     
  5. john boy

    john boy New Member

    Thanks Mark for that pointer

    netstat -tapn | grep 3306
    tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3023/mysqld

    Still no acess to sites, console or Putty to server via WAN
    But have full LAN access
     
  6. john boy

    john boy New Member

    Add this as well

    netstat -tap
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
    tcp 0 0 localhost:10025 *:* LISTEN 1268/master
    tcp 0 0 *:mysql *:* LISTEN 6044/mysqld
    tcp 0 0 *:www *:* LISTEN 1369/apache2
    tcp 0 0 *:81 *:* LISTEN 1369/apache2
    tcp 0 0 *:tproxy *:* LISTEN 1369/apache2
    tcp 0 0 *:ftp *:* LISTEN 1281/pure-ftpd (SER
    tcp 0 0 econ2.accc.net.a:domain *:* LISTEN 915/named
    tcp 0 0 localhost:domain *:* LISTEN 915/named
    tcp 0 0 *:ssh *:* LISTEN 792/sshd
    tcp 0 0 *:smtp *:* LISTEN 1268/master
    tcp 0 0 localhost:953 *:* LISTEN 915/named
    tcp 0 0 *:https *:* LISTEN 1369/apache2
    tcp 0 0 econ2.accc.net.au:ssh ns3.accc.net.au:2874 ESTABLISHED 2569/sshd: root@not
    tcp 0 0 econ2.accc.net.au:ssh ns3.accc.net.au:3210 ESTABLISHED 2933/sshd: root@not
    tcp 0 52 econ2.accc.net.au:ssh ns3.accc.net.au:3725 ESTABLISHED 5874/0
    tcp6 0 0 [::]:imaps [::]:* LISTEN 1144/couriertcpd
    tcp6 0 0 [::]:pop3s [::]:* LISTEN 1178/couriertcpd
    tcp6 0 0 [::]:pop3 [::]:* LISTEN 1158/couriertcpd
    tcp6 0 0 [::]:imap2 [::]:* LISTEN 1124/couriertcpd
    tcp6 0 0 [::]:ftp [::]:* LISTEN 1281/pure-ftpd (SER
    tcp6 0 0 [::]:domain [::]:* LISTEN 915/named
    tcp6 0 0 [::]:ssh [::]:* LISTEN 792/sshd
    tcp6 0 0 localhost:953 [::]:* LISTEN 915/named
     
  7. john boy

    john boy New Member

    # iptables-save
    # Generated by iptables-save v1.4.4 on Thu Sep 22 11:45:20 2011
    *mangle
    :pREROUTING ACCEPT [11429:913259]
    :INPUT ACCEPT [4593:539925]
    :FORWARD ACCEPT [0:0]
    :OUTPUT ACCEPT [3563:2232985]
    :pOSTROUTING ACCEPT [3551:2229025]
    COMMIT
    # Completed on Thu Sep 22 11:45:20 2011
    # Generated by iptables-save v1.4.4 on Thu Sep 22 11:45:20 2011
    *nat
    :pREROUTING ACCEPT [7118:414299]
    :OUTPUT ACCEPT [188:11459]
    :pOSTROUTING ACCEPT [188:11459]
    COMMIT
    # Completed on Thu Sep 22 11:45:20 2011
    # Generated by iptables-save v1.4.4 on Thu Sep 22 11:45:20 2011
    *filter
    :INPUT DROP [0:0]
    :FORWARD DROP [0:0]
    :OUTPUT ACCEPT [439:37815]
    :INT_IN - [0:0]
    :INT_OUT - [0:0]
    :pAROLE - [0:0]
    :pUB_IN - [0:0]
    :pUB_OUT - [0:0]
    :fail2ban-ssh - [0:0]
    -A INPUT -d 127.0.0.0/8 ! -i lo -p tcp -j DROP
    -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
    -A INPUT -i lo -j ACCEPT
    -A INPUT -s 224.0.0.0/4 -j DROP
    -A INPUT -i eth+ -j PUB_IN
    -A INPUT -i ppp+ -j PUB_IN
    -A INPUT -i slip+ -j PUB_IN
    -A INPUT -i venet+ -j PUB_IN
    -A INPUT -j DROP
    -A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
    -A FORWARD -j DROP
    -A OUTPUT -o eth+ -j PUB_OUT
    -A OUTPUT -o ppp+ -j PUB_OUT
    -A OUTPUT -o slip+ -j PUB_OUT
    -A OUTPUT -o venet+ -j PUB_OUT
    -A INT_IN -p icmp -j ACCEPT
    -A INT_IN -j DROP
    -A INT_OUT -p icmp -j ACCEPT
    -A INT_OUT -j ACCEPT
    -A PAROLE -j ACCEPT
    -A PUB_IN -p icmp -m icmp --icmp-type 3 -j ACCEPT
    -A PUB_IN -p icmp -m icmp --icmp-type 0 -j ACCEPT
    -A PUB_IN -p icmp -m icmp --icmp-type 11 -j ACCEPT
    -A PUB_IN -p icmp -m icmp --icmp-type 8 -j ACCEPT
    -A PUB_IN -p tcp -m tcp --dport 20 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 21 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 22 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 25 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 53 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 80 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 81 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 110 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 143 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 443 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 3306 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 8080 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 8081 -j PAROLE
    -A PUB_IN -p tcp -m tcp --dport 10000 -j PAROLE
    -A PUB_IN -p udp -m udp --dport 53 -j ACCEPT
    -A PUB_IN -p udp -m udp --dport 80 -j ACCEPT
    -A PUB_IN -p udp -m udp --dport 3306 -j ACCEPT
    -A PUB_IN -p icmp -j DROP
    -A PUB_IN -j DROP
    -A PUB_OUT -j ACCEPT
    -A fail2ban-ssh -j RETURN
    COMMIT
     
  8. Mark_NL

    Mark_NL New Member

    Ok, so we know mysql is running on all interfaces and there are rules in the firewall that should allow connections from the outside.

    - Is there a router between your server and the WAN which possibly need some port forwarding?
    - Clear your iptables and try to connect (so we can exclude the firewall if the problem still occurs)
     
  9. john boy

    john boy New Member

    There is a router with forwarding on ports all working

    Clear iptable is done by
    # iptables --flush
    Done and still no luck
     
  10. Mark_NL

    Mark_NL New Member

    Your INPUT is defaulted to DROP

    so you might want to be sure and set it to ACCEPT :)

    you want this to be sure everything is cleared:
    Code:
    Chain INPUT (policy ACCEPT)
    target     prot opt source               destination
    
    Chain FORWARD (policy ACCEPT)
    target     prot opt source               destination         
    
    Chain OUTPUT (policy ACCEPT)
    target     prot opt source               destination
    
    then try to connect from the WAN (remeber that when you try to conncet to the external ip from within your own local network some routers don't know how to handle this, so try from a completely differnt machine outside of your network
     
  11. john boy

    john boy New Member

    Linux isn't my strong
    How do I do this, just type in these at the command prompt ?

    Sorry Mark...
     
  12. Mark_NL

    Mark_NL New Member

    You can disable the Firewall functionality in ISPConfig web interface .. that should clear the complete iptables config and set all to default.

    System -> Server Services -> select servers -> uncheck "Firewall" .. after a minute the cron should make the changes and then run:

    Code:
    iptables-save
    for me, and show me the output
     
  13. john boy

    john boy New Member

    Mailserver
    Webserver
    DNS-Server
    Fileserver
    DB-Server
    VServer-Server
    Are the only services ???
    No Firewall tab
     
  14. falko

    falko Super Moderator

    It's on System > Firewall.
     
  15. Mark_NL

    Mark_NL New Member

    ow yes, i'm sorry, my bad .. "Firewall" option is in svn ;-)
     
  16. john boy

    john boy New Member

    Removal of the Firewall tick cause the ssh to fail
    Which means I have to travel to the server to restart the service
     
  17. falko

    falko Super Moderator

    Is there maybe another firewall interfering with ISPConfig's firewall? That's the only explanation I have, because ISPConfig does not stop or restart SSH.

    Don't you have a rescue system on your server?
     
  18. john boy

    john boy New Member

    Okay No Rescue system make a good tutorial (to late now)
    Have put a windows server on the address and there is no port blocked from router
    Put the ISPconfig box on and now all ports are closed
    I have noticed the is a big failure in ISPconfig with the only fix so far of reloading the whole box, of which you already know that.....
    This make me raise great concerns
    I have been following ISPconfig for years now and have noticed that a lot of the problems end very quick with no resolution. This just validates to me that it was a reload

    Guy this is a great product, don't let it slip away from ya
    To me this is a learning curve away from windows and these bumps are the facts of life

    All we look for is solutions and so far there all 2 pages there is nothing
    So can anyone point me in the right direction
     
  19. till

    till Super Moderator

    You mix up several things here. Servers are complex systems consisting of thousands of individual applications. Many problems reported here are not caused or directly related to ispconfig. For example, falko explained to you above that the ssh stop on your server cousld not have been initiated by ispconfig, as ispconfig is not able to do that. It does not contain any code thatconfigures or starts / stops a ssh daemon. Not every problem that happens in a near time after a action that you did is caused by that action.

    Your system has no visible signs in iptables or netstat that the problem is related to the server software. I looked onto your server directly and i am not able to find any problem on that servr too, everything works fine and there is definatley no firewall active on the server. Mysql is listening on the external ip and i can connect to mysql on this ip if i use the mysql command. So complaining here that poeple who did not had the chance to look directly at your serevr were not able to answer the problem while i could confirm that there is really no direct problem on the server, is a bit hard in my opinion.

    So maybe we should take a step back and you explain your network topology. For example, why can i only connect to your linux server from a windows system which seems to be in the same subnet while iam not able to connect to the server from outside. This is normally a strong indication that there is a external firewall or similar software that blocks requests from the internet into that subnet.

    And one additional question, is your problem only about accessing mysql or do you have problems with other ports as well. If you mena just ssl, are you sure that you do not mix up that mysql is inaccessible with the error that remote access might be disabled fora specific mysql user? For example, the root mysql user has no external access priveliges by default. So you have to create a new mysql root user with host # in phpmyadmin to acess the databses with the root user. If you created the databse user in ispconfig, you can enable external access by checking the external access checkbox.
     
    Last edited: Sep 24, 2011
  20. john boy

    john boy New Member

    Okay soon far I have
    Phyically removed the linux box and put in a windows test box on the same connection and ip address. All impotant ports are open and access is straight through. then I checked via a online port scanner via external source all okay

    Next I put the Linux box back in and connected up on the same connections. rechecked and found that all ports are closed, no connection from external resources.
    Then I changed ip address and still the same results

    Topology is a simple, Router-Hub branching out to servers. There is DNS's, Mail and web servers on there own ip address range

    Problem stared with me trying to setup remote mysql on port 3306 via backend. It failed so I reversed my step and set it back the way it was.
    That when I discovered that all port were blocked from external WAN address. But local LAN address are fine

    Linux isn't my strength and as I stated above, this is a learning curve
    But Window Box and networks are my strong points are and all these are tell me that the problem is in the linix box.

    I haven't figured out how the linix firewall apache2 and ISPconf interact. but this good and these are great system.
     
: remote mysql

Share This Page