How to Build Real‑Time User Login Dashboards with MySQL Binlog & Logtail

Environment Preparation

Use a MySQL‑compatible database (e.g., RDS, DRDS) with binlog enabled in ROW mode. Ensure the account has mysql slave and SELECT privileges on the tables to be collected.

User Login Table Schema

The user_login table records login ID, timestamp, IP, device, user ID, result, consecutive failure count, and next verification type. Validation rules include simple password match, captcha after >3 failures or IP change across provinces, and SMS verification after >5 failures.

Data Collection with Logtail

Install Logtail (version 0.16.0 or later) following the official documentation. In the Log Service console, create a new Logstore and select MySQL binlog as the data source.

Configuration Steps

Enable MySQL Binlog collection.

Configure key field indexes and statistical settings.

Query and analyze abnormal accounts.

Set up alerts for abnormal logins.

Configure a visual dashboard.

Back up historical login data for audit purposes.

Indexing and Data Preview

After applying the configuration to the machine group, add key indexes in the index query page. Use the preview feature to verify that log events (including operation type, GTID, etc.) are being collected. Logtail adds old_ prefixes for pre‑update values.

Custom Queries and Analysis

Examples of useful queries:

Detect possible account compromise:

SELECT ip_tp_province(login_ip) AS login_province, ip_tp_country(login_ip) AS login_country FROM user_login WHERE login_id=256525;

Count login results per province:

SELECT login_province, login_result, COUNT(1) AS total FROM user_login GROUP BY login_province, login_result ORDER BY total DESC LIMIT 100;

Calculate abnormal‑login percentage:

SELECT SUM(CASE WHEN ip_tp_province(login_ip)!=ip_tp_province(old_login_ip) THEN 1 ELSE 0 END)*1.0/COUNT(1) AS abnormal_login_percentage FROM user_login;

Dashboard Construction

Build a CEO‑level dashboard with the following metrics:

Daily UV & PV:

SELECT COUNT(DISTINCT usr_id) AS uv, COUNT(1) AS pv FROM user_login;

Device distribution:

SELECT dev_type, COUNT(1) AS count FROM user_login GROUP BY dev_type;

5‑minute UV & PV trend:

SELECT COUNT(1) AS uv, COUNT(DISTINCT usr_id) AS pv, FROM_UNIXTIME(__time__ - __time__ % 300) AS time FROM user_login GROUP BY time ORDER BY time LIMIT 1440;

Top‑10 login cities:

SELECT ip_to_city(login_ip) AS login_city, COUNT(1) AS count FROM user_login GROUP BY login_city ORDER BY count DESC LIMIT 10;

Province distribution:

SELECT ip_tp_province(login_ip) AS login_province, COUNT(1) AS count FROM user_login GROUP BY login_province ORDER BY count DESC LIMIT 100;

Alert for Abnormal Login

Define a quick query named abnormal_login using the abnormal‑login percentage SQL above, and configure an alert to trigger when the percentage exceeds 1%.

Data Backup

Store login logs in Log Service for a configurable retention period (e.g., 30 days, 6 months, 1 year). For long‑term archival, enable Log Service’s data delivery to OSS, allowing auditors to retrieve historical data as needed.