Posts Tagged ‘local ip address’

‘host-name’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

Sunday, May 20th, 2012

mysql-logo-host-name-blocked-because-of-many-connection-errors
My home run machine MySQL server was suddenly down as I tried to check my blog and other sites today, the error I saw while trying to open, this blog as well as other hosted sites using the MySQL was:

Error establishing a database connection

The topology, where this error occured is simple, I have two hosts:

1. Apache version 2.0.64 compiled support externally PHP scripts interpretation via libphp – the host runs on (FreeBSD)

2. A Debian GNU / Linux squeeze running MySQL server version 5.1.61

The Apache host is assigned a local IP address 192.168.0.1 and the SQL server is running on a host with IP 192.168.0.2

To diagnose the error I've logged in to 192.168.0.2 and weirdly the mysql-server was appearing to run just fine:
 

debian:~# ps ax |grep -i mysql
31781 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
31940 pts/0 Sl 12:08 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
31941 pts/0 S 0:00 logger -t mysqld -p daemon.error
32292 pts/0 S+ 0:00 grep -i mysql

Moreover I could connect to the localhost SQL server with mysql -u root -p and it seemed to run fine. The error Error establishing a database connection meant that either something is messed up with the database or 192.168.0.2 Mysql port 3306 is not properly accessible.

My first guess was something is wrong due to some firewall rules, so I tried to connect from 192.168.0.1 to 192.168.0.2 with telnet:
 

freebsd# telnet 192.168.0.2 3306
Trying 192.168.0.2…
Connected to jericho.
Escape character is '^]'.
Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Connection closed by foreign host.

Right after the telnet was initiated as I show in the above output the connection was immediately closed with the error:

Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.

In the error 'webserver' is my Apache machine set hostname. The error clearly states the problems with the 'webserver' apache host unable to connect to the SQL database are due to 'many connection errors' and a fix i suggested with mysqladmin flush-hosts

To temporary solve the error and restore my normal connectivity between the Apache and the SQL servers I logged I had to issue on the SQL host:

mysqladmin -u root -p flush-hostsEnter password:

Thogh this temporar fix restored accessibility to the databases and hence the websites errors were resolved, this doesn't guarantee that in the future I wouldn't end up in the same situation and therefore I looked for a permanent fix to the issues once and for all.

The permanent fix consists in changing the default value set for max_connect_error in /etc/mysql/my.cnf, which by default is not too high. Therefore to raise up the variable value, added in my.cnf in conf section [mysqld]:

debian:~# vim /etc/mysql/my.cnf
...
max_connect_errors=4294967295

and afterwards restarted MYSQL:

debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

To make sure the assigned max_connect_errors=4294967295 is never reached due to Apache to SQL connection errors, I've also added as a cronjob.

debian:~# crontab -u root -e
00 03 * * * mysqladmin flush-hosts

In the cron I have omitted the mysqladmin -u root -p (user/pass) input options because for convenience I have already stored the mysql root password in /root/.my.cnf

Here is how /root/.my.cnf looks like:

debian:~# cat /root/.my.cnf
[client]
user=root
password=a_secret_sql_password

Now hopefully, this would permanently solve SQL's 'failure to accept connections' due to too many connection errors for future.

How to make NAT enable hosts in a local network to access the internet, create port forwarding to local IPs behind the router using iptables

Tuesday, August 23rd, 2011

I’m bulding new iptables firewall on one Linux server. The Debian GNU/Linux is required to act as firewall do Network Adress Translation for a small network of office PCs as well as forward some of the inbound ports to hosts from the local network located behind the router.

The local network besides the router had an IP addressing in the class C network e.g. (192.168.1.1-255)

First I procceded and enabled the Network Address Translation via the Linux kernel variable:

linux:~# sysctl -w net.ipv4.ip_forward=1
linux:~# echo 'net.ipv4.ip_forward=1' >> /etc/sysctl.conf

Initially I even forgot to switch on the net.ipv4.ip_forward to 1 (by default this value is set to 0) – GNU/Linux’s default network behaviour is not predetermined to act as network router.
However, since I haven’t configured Network Address Translation for quite some time it completely slipped my mind!

Anyways next the actual iptables rule which makes NAT possible I used is:

linux:~# /sbin/iptables -t nat -A POSTROUTING -s 192.168.1.0/24 ! -d 192.168.1.0/24 -j SNAT --to-source xxx.xxx.xxx.xxx

Whether xxx.xxx.xxx.xxx is the External IP address assigned to the router on eth0

With this very simple rules now Network the local network is capable of accessing the Internet withotu problem.

It’s a good time to say that still many system administrators, still erroneously use MASQUERADE rules instead of SNAT .
IP MASQUERADING is an ancestry from ipchains and these days should be completely abandonded, especially where no often change of primary IP address to access the internet is made.
For dial-ups or other kind of networking, where the IP addresses are often changed still IP MASQUERADING might be a good idea though.

My next goal was to make the Linux router to do port forwarding of Traffic which arrives on port 80 to a IIS server assigned with a local IP address of 192.168.1.5
I did the webserver (port 80), port forwarding from IP xxx.xxx.xxx.xxx to 192.168.1.5 with the iptables rule:

linux:~# /sbin/iptables -t nat -A PREROUTING -d xxx.xxx.xxx.xxx/32 -p tcp -m tcp --dport 80 -j DNAT --to-destination 192.168.1.5:80

There was a requirement to do port forwarding for a Windows remote Desktop running on standard port 3389 from the router to the internal Windows IP address running the IIS webserver, however the company required me to only allow access to the rdesktop 3389 port to certain real IP addresses.
Initially I thought about using the above PREROUTING rule which makes the port redirection to the IIS server and only change port 80 to port 3389 , and then use filter table INPUT chain rules like:

/sbin/iptables -A INPUT -s xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx -p tcp -m tcp --dport 3389 -j ACCEPT/sbin/iptables -A INPUT -p tcp -m tcp --dport 3389 -j REJECT --reject-with icmp-port-unreachable
32

However this did not work out, so I decided to give a try to do the same within the filter table using the FORWARD chain, like so:

FORWARD/sbin/iptables -A FORWARD -p tcp -m tcp -s xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx -p tcp -m tcp --dport 3389 -j ACCEPT
/sbin/iptables -A FORWARD -p tcp -m tcp --dport 3389 -j REJECT --reject-with icmp-port-unreachable

Adding this rules did not added any filtering to the forwarded remote desktop port. I suspected that somehow probably my above PREROUTING nat rules are read before any other rules and therefore automatically allows any IP address to port fortward traffic.
I’ve checked the iptables documentation and it seems my guess was partially right.

When some kind of network traffic enters the iptables firewall it first goes through the PREROUTING channel and then the traffic flows in a certain order.
iptables packet flow diagram

The iptables network packets flow is clearly seen in above’s diagram a thorough looks gives a very good idea on how packet is being processed by iptables

Finally as I couldn’t think about a good solution on how to only filter the port redirected traffic, which always firstly entered in the POSTROUTING chain, I’ve consulted with the guys in irc.freenode.net in #Netfilter.

I’m quite thanksful as a guy nicknamed Olipro has given me a pretty good picture on the port forwarding POSTROUTING problem and has provided me with a very logical easy and great fix.
He suggested that I only do port forwarding for certain IP addresses instead of allowing all IP addresses and then lookup for a way to allow only some of them and filter the rest.

The iptables rule to restrict the incoming traffic to the remote desktop forwarded port 3389 to few only allowed IP addresses looks like so:

linux:~# /sbin/iptables -t nat -A PREROUTING -d xxx.xxx.xxx.xxx/32 -s xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx -p tcp -m tcp –dport 3389 -j DNAT –to-destination 192.168.1.5:3389

Now the three sample IPs passed xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx has added to port forward traffic on 3389 to 192.168.1.5

By the way I did not know that newer versions of iptables support passing by multiple IP addresses to the –source or –destination IP. This is really great feature I’ve learned from the good guys from #Netfilter. However one should be careful when using the multiple IPs with -s or -d, it’s really important that the passed consequent IPs has no space between the , delimiter.

Now that’s all my task is completed. All computerse inside the Network 192.168.1.1-255 on the Linux router freely can access the Internet, all IPs are also capable to access the IIS server located behind the NAT as well as only certain IPs are capable of accessing to the IIS remote desktop.
Hope the article helps somebody 😉