In high-traffic production environments, tracking database access attempts becomes crucial for security auditing while maintaining performance. The standard general_log approach creates significant overhead by logging all queries - a dealbreaker for performance-sensitive systems.

MySQL offers several targeted methods for authentication tracking:

-- Method 1: Error log configuration
[mysqld]
log_error = /var/log/mysql/error.log
log_warnings = 2  # Captures failed auth attempts

-- Method 2: Performance Schema setup
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'statement/sql/connect';

For comprehensive auditing without performance impact:

-- MySQL Enterprise Audit plugin (requires license)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Configuration options:
[mysqld]
audit_log_format=JSON
audit_log_policy=ALL
audit_log_include_accounts=TRUE

For granular control with minimal overhead:

CREATE TABLE login_audit (
    event_time TIMESTAMP,
    user_host VARCHAR(80),
    status ENUM('SUCCESS','FAILURE'),
    connection_id BIGINT
);

DELIMITER //
CREATE TRIGGER after_connection
AFTER CONNECT ON *.*
FOR EACH STATEMENT
BEGIN
    INSERT INTO login_audit 
    VALUES(NOW(), CURRENT_USER(), 'SUCCESS', CONNECTION_ID());
END//

CREATE TRIGGER after_auth_failure
AFTER SERVER_ERROR ON *.*
FOR EACH STATEMENT
BEGIN
    IF (ERROR_NUMBER() = 1045) THEN
        INSERT INTO login_audit 
        VALUES(NOW(), '@'+@@hostname, 'FAILURE', CONNECTION_ID());
    END IF;
END//
DELIMITER ;

For managed database services:

• AWS RDS: Enable Enhanced Monitoring with DB authentication events
• Google Cloud SQL: Use Cloud Audit Logs with admin activity tracking
• Azure Database: Configure Diagnostic Settings to stream logs

Example log processing with grep:

# Find failed attempts in error log
grep "Access denied" /var/log/mysql/error.log | 
awk '{print $1, $2, $5, $6, $NF}' > failed_logins.txt

# Generate hourly report
mysql -e "SELECT 
    DATE_FORMAT(event_time, '%Y-%m-%d %H:00') AS hour,
    status,
    COUNT(*) AS attempts
FROM login_audit
GROUP BY hour, status
ORDER BY hour DESC;"

In production environments, tracking authentication attempts (both successful and failed) is crucial for security auditing and troubleshooting. While MySQL's general_log might seem like the obvious solution, its performance impact makes it unsuitable for high-traffic databases.

Here are three production-friendly methods to log MySQL authentication events:

MySQL Enterprise Edition includes a native audit plugin, but for open-source users, MariaDB's audit plugin works perfectly:

INSTALL PLUGIN server_audit SONAME 'server_audit.so';
SET GLOBAL server_audit_events='connect,query';
SET GLOBAL server_audit_logging=ON;

For MySQL 5.7+ and MariaDB 10.0+, the performance_schema offers low-overhead monitoring:

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';

SELECT EVENT_NAME, COUNT_STAR 
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%connect%';

Configure MySQL to send auth logs to syslog (works on most Linux systems):

SET GLOBAL log_warnings = 2;
SET GLOBAL log_error_verbosity = 3;

Then configure syslog/rsyslog to capture these messages to a dedicated file.

For versions without plugin support, create a login tracking table and trigger:

CREATE TABLE login_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(128),
    event_time DATETIME,
    status ENUM('SUCCESS','FAILURE'),
    source_host VARCHAR(60),
    client_program VARCHAR(255)
);

DELIMITER //
CREATE TRIGGER after_connection_track
AFTER CONNECT ON *.*
FOR EACH STATEMENT
BEGIN
    INSERT INTO login_audit
    VALUES (NULL, USER(), NOW(), 
           IF(COUNT_ERRORS()>0, 'FAILURE', 'SUCCESS'),
           SUBSTRING_INDEX(USER(), '@', -1),
           PROGRAM_NAME());
END//
DELIMITER ;

When implementing any logging solution in production:

• Rotate logs frequently to prevent disk filling
• Consider sampling rather than logging every event for extremely busy servers
• Monitor the logging overhead (typically 2-5% CPU impact)

Use tools like grep, awk or specialized log analyzers:

# Find failed login attempts
grep "Access denied" /var/log/mysql/error.log | 
awk '{print $1,$2,$3,$8,$9}' | 
sort | uniq -c | sort -n

For cloud environments, consider forwarding logs to centralized services like AWS CloudWatch or ELK stack.