Why my site is blocked by too many connection errors?

Question

I've created a simple website with a few pages on my local computer. I have Drupal installed in my XAMPP directory, so the directory looks like this: c:\xampp\htdocs\drupal\<all the drupal files sit here>.

I've signed up for a domain and I'm trying to upload my Drupal site to the web hosting company to go live.

I searched for many sites for step-by-step instructions how to do it. This is what I got and this is what I did:

1. Backup my drupal database on my computer.
2. Create a new blank database on the host.
3. Upload my local files on my computer to the host (I assume I need to upload all the files inside the Drupal directory, is that correct?)
4. Import my backed up database to the host.

5. Change the settings.php in the host. Is that the only file and place I need to change?

   This is what I changed:

   $databases = array (
   'default' =>
     array (
       'default' =>
       array (
       'database' => 'yyyyy_xxx',   <== Can I use a different database name than the one I use on my localhost?
       'username' => 'yyyyy_xxx',   <== Can I use a different username than the one I use on my localhost?
       'password' => '12345',       <== Can I use a different password than the one I use on my localhost?
       'host' => '<www.my new site's domain name.com>',    <== Should I be using my new site's domain name here or localhost since all the files are sitting at the web hosting company's server?
       'port' => '',
       'driver' => 'mysql',
       'prefix' => '',
      ),
    ),
   );
   

6. When refreshed my webpage and I've got the following error:

   Error:
   The website encountered an unexpected error. Please try again later.
   Error message:
   PDOException: SQLSTATE[HY000] [1129] Host 'box373.bluehost.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' in lock_may_be_available() (line 167 of /home3/<my website>/public_html/includes/lock.inc).
   

I've logged into my account on the hosting company using Putty and went to public_html/ and entered mysqladmin flush-hosts. Now I've got the error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'xxx'@'localhost' (using password: NO)'

The xxx is the database name I used on my localhost. The new database name on the host is yyyy_xxx. Is it still referring to my localhost database even though I've changed the settings in settings.php file on the host?

I would appreciate if somebody can help me and give me some insights how to fix this problem.

Is this the correct procedure to upload files to the host for my website to go live? Besides settings.php file, are there any other files I need to change? How do I unblock mysqladmin using flush-hosts? Some websites suggest to include username and password to login to mysqladmin. I've entered mysqladmin -uyyyy_xxx -p12345 flush-hosts and I got the following error:

mysqladmin: refresh failed; error: 'Access denied; you need (at least one of) the RELOAD privilege(s) for this operation'

How do I prevent this problem to happen again? I just uploaded a few files to the host and I got this problem. In the future, I am going to upload the complete website to the host with way more files. I would like to find out the correct way to launch a Drupal website.

Answer 1

MySQL blocks clients which error made while connecting to protect MySQL from malformed client.

So first, you need to find what sort of error it is.

In example by checking MySQL error log in data directory (typically hostname.err) or if you've drush installed, by drush watchdog-show.

You may also increase max_connect_errors (what is current value?) maximum value depends on architecture (check Manual).

But this is not real solution if error is frequently occurred.

FLUSH HOSTS can help you to eliminate blocked host. E.g.

mysql> FLUSH HOSTS;

Source: Why MySQL connection is blocked of many connection errors?

Please note that to use mysqladmin properly, you need to have access to root account. If you don't have it, use FLUSH HOSTS command as above.