Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a two part question:

1.In cases were this error comes up:

PDOException: SQLSTATE[HY000] [1129] Host `hostname` is blocked because of many connection errors; unblock with `mysqladmin flush-hosts`

is there a way to see if a host is close to being blocked? And fire off an alert ... ?

2.What guidelines should one follow when increasing max_connect_errors (our's is at 10)? Is it recommended to run mysqladmin flush-hosts, say daily, via cron?

Running MySQL 5.5.13 (Enterprise) on RedHat 6.

Many thanks!

share|improve this question
    
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 '12 at 19:58

1 Answer 1

up vote 5 down vote accepted

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
mysql> CREATE EVENT ev ON SCHEDULE
    ->       EVERY 5 MINUTE
    -> STARTS (NOW() + INTERVAL 1 MINUTE)
    -> DO FLUSH HOSTS;
Query OK, 0 rows affected (0.02 sec)

mysql>

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:

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$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";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        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=127.0.0.1",$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};
                }
                $st_globalstatus->finish();
                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;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

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 3600 & (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
share|improve this answer
    
Thanks again for a comprehensive answer, I'll share it with the DBA to implement it. –  KM. Aug 31 '11 at 14:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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