How to Detect and Block 3+ Failed Logins Within 10 Seconds Using MySQL and Redis

1. Login Log Table Approach

Every login attempt, successful or not, is recorded in a backend table. A simple example table includes columns for user account, source, IP, success flag, and timestamp.

Using this log, we can count users with more than three failed attempts within ten seconds. The flowchart illustrates the process.

The backend queries the log using MySQL's window function row_number() to detect consecutive failures. If such a pattern exists, the account is locked and an email notification is sent; otherwise normal login responses are returned.

Problems with this method:

Complex SQL using row_number() and potentially low performance.

Log table grows over time, requiring cold‑data migration (e.g., moving records older than one month to a separate table).

2. Redis Implementation

Redis can track failed attempts by storing a key per user (e.g., 123_login_record ) with the failure count as the value and a 10‑second expiration.

On a login request:

If credentials are correct, delete the Redis key to reset the counter.

If login fails, use SETNX to create the key (or increment it if it already exists). When the count reaches three, send an alert email and temporarily lock the account.

To ensure atomicity of multiple Redis commands, a Lua script can be employed.

Summary:

Both the log‑table and Redis approaches can detect three+ failures within ten seconds.

The log‑table method relies on SQL window functions, which can be complex and slower as data grows.

The Redis method uses key expiration and can be made atomic with Lua scripting.