Analysis and solution for MySQL connection blocked problem (from error to fix)

1. Introduction

In Java application development, database connection is an indispensable part. However, when using MySQL, we may encounter errors similar to the following:

Host '124.221.131.191' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

This error indicates that the MySQL server automatically blocked the connection request from the host due to detection of excessive connection failures. This article will analyze the causes of this problem in depth and provide a complete solution, including how to optimize database connection management at the code level.

2. Problem background and error analysis

2.1 Error log analysis

As can be seen from the error log, the Druid connection pool fails when trying to establish a MySQL connection. The key error message is as follows:

: null, message from server: 
"Host '124.221.131.191' is blocked because of many connection errors; 
unblock with 'mysqladmin flush-hosts'"

2.2 Why does this error occur?

MySQL has a security mechanism when a client (IP) fails to connect multiple times in a short period of time (defaultmax_connect_errors=100), MySQL will think that the host may have a malicious attack or configuration error, thereby automatically blocking its connection.

Common trigger reasons:

Database account password error
Network instability causes connection timeout
The database connection pool configuration is unreasonable (such as the initial connection number is too large, but the database cannot host it)
Insufficient database server resources (full number of CPU, memory, connections)

3. Solution

3.1 Temporary solution: Unblock MySQL IP

Execute the following command on the MySQL server:

FLUSH HOSTS;

Or usemysqladminOrder:

mysqladmin flush-hosts -u root -p

3.2 Long-term solution: Optimize connection pool configuration

(1) Adjust the max_connect_errors of MySQL

-- View the current value
SHOW VARIABLES LIKE 'max_connect_errors';

-- Revise（Need to restartMySQLOr dynamically adjust）
SET GLOBAL max_connect_errors = 1000;

(2) Optimize Druid connection pool configuration

existorAdjust the Druid parameters:

spring:
  datasource:
    url: jdbc:mysql://124.221.131.191:3306/kwan?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai
    username: your_username
    password: your_password
    driver-class-name: 
    type: 
    druid:
      initial-size: 5       # Initial number of connections      min-idle: 5           # Minimum idle connection      max-active: 20        # Maximum active connection      max-wait: 60000       # Get the connection timeout (milliseconds)      validation-query: SELECT 1  # Connection detection SQL      test-while-idle: true # Detect connections when idle      test-on-borrow: false  # No detection when obtaining connections (impacting performance)      test-on-return: false  # No detection when returning the connection      time-between-eviction-runs-millis: 60000 # Detection interval      min-evictable-idle-time-millis: 300000    # Minimum free time

(3) Use try-with-resources to ensure that the connection is closed correctly

import ;
import ;
import ;
import ;
import ;

public class UserDao {
    private final DataSource dataSource;

    public UserDao(DataSource dataSource) {
         = dataSource;
    }

    public String getUserNameById(int id) {
        String sql = "SELECT username FROM users WHERE id = ?";
        try (Connection conn = ();
             PreparedStatement stmt = (sql)) {
            (1, id);
            try (ResultSet rs = ()) {
                if (()) {
                    return ("username");
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to query user", e);
        }
        return null;
    }
}

4. In-depth analysis: How to avoid connection failure?

4.1 Monitor database connection status

Use Druid's own monitoring:

@Bean
public ServletRegistrationBean<StatViewServlet> druidStatViewServlet() {
    ServletRegistrationBean<StatViewServlet> registrationBean = 
        new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
    ("loginUsername", "admin");
    ("loginPassword", "admin");
    return registrationBean;
}

Visithttp://localhost:8080/druidYou can view the connection pool status.

4.2 Using the retry mechanism

public Connection getConnectionWithRetry(DataSource dataSource, int maxRetries) {
    int retryCount = 0;
    while (retryCount &lt; maxRetries) {
        try {
            return ();
        } catch (SQLException e) {
            retryCount++;
            if (retryCount &gt;= maxRetries) {
                throw new RuntimeException("Failed to get connection after " + maxRetries + " retries", e);
            }
            try {
                (1000); // Try again after 1 second            } catch (InterruptedException ie) {
                ().interrupt();
                throw new RuntimeException("Interrupted while waiting for retry", ie);
            }
        }
    }
    throw new IllegalStateException("Should not reach here");
}

4.3 Optimize MySQL server configuration

[mysqld]
max_connections = 200          # Maximum number of connectionswait_timeout = 28800          # Non-interactive connection timeout (seconds)interactive_timeout = 28800   # Interactive connection timeoutmax_connect_errors = 1000     # Increase the connection error threshold

5. Summary

5.1 Review of key points

Cause of error: MySQL automatically blocks IP due to multiple connection failures.
Temporary fix:FLUSH HOSTSormysqladmin flush-hosts。
Long-term optimization: adjust the connection pool parameters, optimize the code, and monitor the connection status.

5.2 Best Practices

Configure the connection pool reasonably (the number of initial connections should not be too large)
Use try-with-resources to ensure the connection is closed
Monitor database connection status (Druid monitoring panel)
Optimize MySQL server parameters (max_connect_errors, max_connections)

Through the above methods, the problem of MySQL connection being blocked can be effectively avoided and the stability and reliability of the system can be improved.

This is the article about the analysis and solution of MySQL connection blocked problem (from errors to repairs). For more related content about MySQL connection blocked, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!