MySQL connection ERROR 1129

Today I had to configure a MySQL database cluster and I faced the following error

ERROR 1129 (HY000): Host '192.168.1.12' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

while I was trying to access a MySQL instance via HaProxy. After some searching in dba.stackexchange.com, I found that my failed connection attempts had surpassed the max_connect_errors variable which is set by default to 10. In order to be able to connect to the cluster again you have to follow these steps in every MySQL server that belongs to the cluster:

  1. Open a command prompt (or shell in Linux) with administrative privilleges
  2. If you are in Windows set character set to unicode. Linux is using UTF-8 by default.
    chcp 65001
  3. Flush all hosts in MySQL using mysqladmin:
    mysqladmin flush-hosts -u root -p
  4. Open my.cnf (Linux) or my.ini (Windows) and change max_connect_errors variable to a large number. I used:
    max_connect_errors= 1000000
  5. Restart MySQL server
    Linux: sudo /etc/init.d/mysql restart
    Windows: net stop mysql
    net start mysql

You are done!

Advertisements

About cmanios

programming
This entry was posted in Databases, HaProxy, Linux, mysql and tagged , , , , , , . Bookmark the permalink.

One Response to MySQL connection ERROR 1129

  1. Pingback: Host ‘host_name’ is blocked because of many connection errors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s