Error 1129: Host '' blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Question

When I run a python code which connects to mysql, I see this error; "Error 1129: Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'".

Here is what I have tried: I increased max_connection_errors values and I also executed 'flush hosts' command. I also checked mysql log, but there is none. In addition, I checked network connection, and it's fine too.

$netstat -an | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

Here is mysql setting:

mysql> SHOW VARIABLES LIKE '%error%';

error_count | 0
log_error | /var/log/mysql/error.log max_connect_errors | 4294967295
max_error_count | 64
slave_skip_errors | OFF

mysql> SHOW VARIABLES LIKE '%max_connections%';

max_connections | 5000

mysql> show status like '%onn%';

Aborted_connects | 6
Connections | 4802
Max_used_connections | 26
Ssl_client_connects | 0
Ssl_connect_renegotiates | 0
Ssl_finished_connects | 0
Threads_connected | 1

mysql>'SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name = "Connections

Connections | 4996
Threads_cached | 7
Threads_connected | 1
Threads_created | 1950
Threads_running | 1

mysql> show processlist;

4800 | root | localhost | NULL | Query | 0 | NULL | show processlist
4807 | root | localhost | db1 | Sleep | 0 | | NULL

Can anyone please help me find what went wrong and what's the way to fix it?

Answer 1

This is not a fault in your MySQL server software. It's evidence of a fault in some other software running on the same machine you're using to run your python program. Error 1129 means MySQL's crude anti-cybercriminal feature has been activated: programs running on the same machine you're using have tried and failed to connect many times (100 by default). When that happens MySQL concludes the machine is compromised (or running amok) and refuses to accept any more connections from it.

What could cause this? Is there a cronjob or other automatically running program on your machine that's rigged up with an invalid user name or password? If so, it might be hammering away on the MySQL server racking up connection failures.

If it's a Linux or BSD-based machine use the ps axuww shell command a few times and look for processes you don't recognize. (If you're not sure about how to do that ask on the forum for your operating system.)

You might consider rebooting the machine.

Edit Your machine seems to be under attack. You mentioned that you're getting a lot of failed password messages in your authlog. Can you track down the source of these attacks? If they're internal -- that is, from a machine you or your colleagues control -- they are probably some kind of program that's configured

• to run too often, or
• to use an incorrect password,
• or both.

Look for a pattern in the ip addresses. Do these failures come from one or a very few addresses? Are they from private addresses like 192.168.. or 10...*, or even from localhost (127.0.0.1)? Or do they come from all over the place? If you don't recognize the addresses, try putting them into the forms in these three web sites:

https://ripe.net
https://apnic.net
https://arin.net

At least one of these three will tell you the country and telecom provider from which the failures are coming. If they're from someplace where you have no relationships you are under attack.

If the attacks are external, you need to take steps to block them before they reach your database machine. Most machines running MySQL are isolated from the wider internet by firewalls because MySQL isn't as robust as some other software against cybercriminal and script-kiddie attack.

Look for a timing pattern. Is the timing of these failures once every five minutes, or is it as rapid as possible, or what? If it's regular, it seems likely that you have some sort of misbehaving program someplace.

If none of this makes sense to you, ask a network engineer to help you. If your MySQL server holds sensitive or valuable information, don't delay in confronting this problem. Good luck. This kind of thing has ruined some days for me.