I have a two part question:

  1. In cases were this error comes up, is there a way to see if a host is close to being blocked? And fire off an alert?

    PDOException: SQLSTATE[HY000] [1129] Host `hostname` is blocked because of many connection errors; unblock with `mysqladmin flush-hosts`
  2. What guidelines should one follow when increasing max_connect_errors-- ours is at 10? Is it recommended to run mysqladmin flush-hosts daily, via cron?

Running MySQL 5.5.13 (Enterprise) on Red Hat 6.

  • this is bad cuz you're opening your db to DOS attacks. is there anyway to whitelist certain hostnames so they never hit this issue?
    – user10204
    Jul 17, 2012 at 19:58

1 Answer 1


Actually, you should raise the max_connect_errors way beyond the default of 10.

The range is 1-18446744073709547520. I would set it to 1000 for now.

However, you have a bigger problem. mysqladmin requires you connect to mysqld before it does anything. If max_connect_errors is reached too quickly, you cannot make mysqladmin do anything at that point. Even a cron job will fail in this respect.

Since you are using MySQL 5.5, your best option is create a scheduled event within the MySQL Instance to go off every 5 minutes that will only do one thing: run FLUSH HOSTS.

I just did this in MySQL 5.5.12 on my PC

mysql> use test
Database changed
    ->       EVERY 5 MINUTE
Query OK, 0 rows affected (0.02 sec)


Great, no complaints from mysqld. Of course, I arbitrarily chose 5 minutes. You can choose whatever schedule you prefer.

Give it a Try !!!

UPDATE 2011-08-25 11:22 EDT

For everybody using release of MySQL prior to 5.1, I have a worthwhile suggestion:

Write a perl script that keeps an open connection, making it heartbeat itself by retrieving something (i.e., uptime, aborted_connects, etc) and every 10 minutes kicking out FLUSH HOSTS. Whatever user you connect as, make sure it has RELOAD privilege.

For example: run this command:

GRANT RELOAD on *.* to 'whateveruser'@'localhost' identified by 'whateverpassword';

Here is a perl script I call heartbeat.pl to do this using that user and password:


use DBI;
use DBD::mysql;


$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;

$db = DBI->connect("DBI:mysql:information_schema;host=",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                        my $uptime = $row->{Value};
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
                if ( -f $MYSQL_FLATLINE_FILE )
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                sleep 1;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
                $heartbeat_count = 0;


In the OS, call the perl script to heartbeat every 6 seconds. When it heartbeats 100 times, that 600 seconds (10 minutes), make it run FLUSH HOSTS. Call it like this:

nohup perl heartbeat.pl 6 100 &

Here are other schedules:

nohup perl heartbeat.pl  6  50 & (heartbeat every  6 sec,FLUSH HOSTS every 5 minutes)
nohup perl heartbeat.pl 10 360 & (heartbeat every 10 sec,FLUSH HOSTS every 1 hour)
nohup perl heartbeat.pl 60 180 & (heartbeat every 60 sec,FLUSH HOSTS every 3 hours)

To stop the program, run this:

perl heartbeat.pl
  • Thanks again for a comprehensive answer, I'll share it with the DBA to implement it.
    – KM.
    Aug 31, 2011 at 14:56
  • good idea. does FLUSH HOSTS do anything but reset connection errors?
    – Geoduck
    Dec 1, 2018 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.