59

I got a database server failure, says host is blocked because of many connection errors. It ask me to unblock with 'mysqladmin flush-hosts'

how and where should I run this command to our amazon rds database server?

thank you

Viccari
  • 9,029
  • 4
  • 43
  • 77
user955461
  • 631
  • 1
  • 6
  • 5

10 Answers10

84

For normal MySQL, just connect as the 'root' administrative super user, and issue the command:

FLUSH HOSTS

Even in the case of too many connections, MySQL should be keeping a connection in reserve so that a super user can connect.

The mysqladmin client generally connects as root anyway and issues the above SQL.

Lee Goddard
  • 10,680
  • 4
  • 46
  • 63
barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • 3
    liThanks, this is exactly what I needed: a little context. fwiw mysqladmin will work with RDS too (as long as you're using a user with permission to do so (ie, root) - I wasn't). – dsummersl Jun 12 '13 at 11:51
  • This is a great answer but not applicable to AWS. – Gardner Bickford Jan 24 '14 at 04:16
  • Why not? Can you not run FLUSH HOSTS on AWS? – barryhunter Jan 24 '14 at 11:11
  • 2
    This worked well for me, but do take into account that in RDS the admin user is rarely called `root`. When you create the instance you are asked to enter an account name, and that account is the administrator account. – Guss Jul 20 '14 at 18:25
  • 1
    In addition to this, I also ran the following to make sure any corruption was dealt with : `mysqlcheck --auto-repair --all-databases -h HOST -u USER -p` – Nate Ritter May 12 '15 at 22:26
30

Login to any other EC2 instance you have that has access to the RDS instance in question and has mysqladmin installed and run

mysqladmin -h <RDS ENDPOINT URL> -P 3306 -u <USER> -p flush-hosts

you will be prompted for your password

Chris
  • 1,231
  • 10
  • 4
  • 1
    It need not be from any EC2 instance. It can be done from a local machine as well. Where ever mysql is installed. – Michael Massey Dec 01 '13 at 03:47
  • 7
    This does not work from a beanstalk EC2 instance to an RDS mysql host. Using the mysqladmin command to connect to the host produces an error: mysqladmin: connect to server at 'challengefinder-rds.crstas8123.us-west-1.rds.amazonaws.com' failed error: 'Host 'ip-10-170-12-12.us-west-1.compute.internal' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'' – Gardner Bickford Jan 24 '14 at 04:23
  • You can use another computer - just add your IP address to the security group for that RDS instance and then you can use mysqladmin from your workstation. – Guss Jul 20 '14 at 18:27
16

When an Amazon RDS instance is blocked because the value of max_connect_errors has been exceeded, you cannot use the host that generated the connection errors to issue the "flush hosts" command, as the MySQL Server running on the instance is at that point blocking connections from that host.

You therefore need to issue the "flush hosts" command from another EC2 instance or remote server that has access to that RDS instance.

mysqladmin -h [YOUR RDS END POINT URL] -P 3306 -u [DB USER] -p flush-hosts

If this involved launching a new instance, or creating/modifying security groups to permit external access, it may be quicker to simply login to the RDS user interface and reboot the RDS instance that is blocked.

Garreth McDaid
  • 2,427
  • 22
  • 34
11

I fixed this error on my RDS instance by rebooting it from the AWS management console. HTH

[edit: lol downvotes]

Alex W
  • 719
  • 7
  • 14
5

On Amazon RDS FLUSH HOSTS; can be executed from default user ("Master Username" in RDS info), and it helps.

Atis Lezdins
  • 59
  • 1
  • 1
5

Since the hosts is blocked. try connect it from other host and execute the mysqladmin flush-hosts command.

mysqladmin -h <RDS ENDPOINT URL> -P <PORT> -u <USER> -p flush-hosts
Ryan
  • 3,085
  • 5
  • 27
  • 31
2

You will have to connect your RDS through a computer which as mysql installed on it I used one of my hosting VPS using SSH

After i was logged in my VPS ( i used putty ) It was simple, in the prompt i entered the following command:

mysqladmin -h [YOUR RDS END POINT URL] -P 3306 -u [DB USER] -p flush-hosts
Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
Ram m.
  • 21
  • 1
1

You can restart the database on RDS Admin.

Scudelletti
  • 1,470
  • 17
  • 19
0

You can flush hosts local MySQL using following command:

mysqladmin -u [username] -p flush-hosts
**** [MySQL password]

or

mysqladmin flush-hosts -u [username] -p
**** [MySQL password]

Though Amazon RDS database server is on network then use the following command as like as flush network MySQL server:

mysqladmin -h <RDS ENDPOINT URL> -P <PORT> -u <USER> -p flush-hosts
mysqladmin -h [YOUR RDS END POINT URL] -P 3306 -u [DB USER] -p flush-hosts

In additional suggestion you can permanently solve blocked of many connections error problem by editing my.ini file[Mysql configuration file]

change variables max_connections = 10000;

or

login into MySQL using command line -

mysql -u [username] -p
**** [MySQL password]

put the below command into MySQL window 

SET GLOBAL max_connect_errors=10000;
set global max_connections = 200;

check veritable using command- 

show variables like "max_connections";
show variables like "max_connect_errors";
Syeful Islam
  • 3,785
  • 1
  • 20
  • 19
  • 2
    On RDS, it's not possible to run any commands locally, because you can't login to the database host. Over the network also doesn't work, because mysqladmin also can't connect, for the same reason. ("error: 'Host 'your.client.ip.address' is blocked because of many connection errors") – Alex Nauda Oct 22 '17 at 16:27
  • You can reboot the rds server (take a snapshot if needed as well) and then the problem will be solved. – Jananath Banuka May 14 '21 at 10:52
-1

got this error today on a customer rds while they were using Heidi Sql client.

We simply used 'mysqlroot' on the ec2 that talks to the rds in question to connect, followed by issuing the 'flush hosts;' cmd.

mcgriddletime
  • 19
  • 4