MySQL logs failed login attempts by default, but you need to ensure proper logging configuration. Check your MySQL configuration file (typically my.cnf or my.ini):

[mysqld]
log-error = /var/log/mysql/error.log
log-warnings = 2

The log-warnings setting controls how detailed the logging should be for connection errors. Value 2 will log all failed connection attempts.

Create a new Fail2ban filter for MySQL in /etc/fail2ban/filter.d/mysql-auth.conf:

[Definition]
failregex = ^%(__prefix_line)s.*Access denied for user '\S+'@''( to database '\S+')?$
            ^%(__prefix_line)s.*Host '\S+' is blocked because of many connection errors.*
ignoreregex =

Add this to your /etc/fail2ban/jail.local (create if doesn't exist):

[mysql-auth]
enabled  = true
filter   = mysql-auth
logpath  = /var/log/mysql/error.log
maxretry = 3
findtime = 86400
bantime  = 86400
port     = 3306
action   = iptables[name=MySQL, port=3306, protocol=tcp]

Adjust these parameters as needed:

• maxretry: Number of failed attempts before banning
• findtime: Time window for counting failures (in seconds)
• bantime: Duration of ban (in seconds)
• port: Your MySQL port (change if using non-default)

For custom MySQL ports or additional security:

[mysql-customport]
enabled  = true
filter   = mysql-auth
logpath  = /var/log/mysql/error.log
maxretry = 5
findtime = 3600
bantime  = 259200
port     = 12345
action   = iptables[name=MySQL-Custom, port=12345, protocol=tcp]

After making changes, test your regex pattern:

fail2ban-regex /var/log/mysql/error.log /etc/fail2ban/filter.d/mysql-auth.conf

Then restart Fail2ban and check status:

systemctl restart fail2ban
fail2ban-client status mysql-auth

Regularly check your Fail2ban logs to ensure proper functioning:

tail -f /var/log/fail2ban.log

For persistent offenders, consider increasing bantime or implementing permanent bans.

---

MySQL logs failed authentication attempts by default, but the logging format varies depending on your MySQL version and configuration. For modern MySQL/MariaDB installations, you'll typically find these entries in either:

/var/log/mysql/error.log
/var/log/mysqld.log
/var/log/mysql/mysql.log

Before setting up Fail2Ban, examine your MySQL logs to understand the failure patterns. A typical failed login entry looks like:

2023-11-15T14:23:45.123456Z 45 [Note] Access denied for user 'root'@'192.168.1.100' (using password: YES)

First, create a custom filter in /etc/fail2ban/filter.d/mysql-auth.conf:

[Definition]
failregex = ^%(__prefix_line)s\d+ $$Note$$ Access denied for user '.*'@'' $using password: (YES|NO)$$
            ^%(__prefix_line)s\d+ $$Note$$ Aborted connection .* to db: .* user: .* host: '' $.*$$
ignoreregex =

Add this to your /etc/fail2ban/jail.local:

[mysql-auth]
enabled  = true
port     = 3306,3307,3308
filter   = mysql-auth
logpath  = /var/log/mysql/error.log
maxretry = 3
findtime = 86400
bantime  = 86400

For enhanced security, consider these additional parameters:

# For multiple log files
logpath  = /var/log/mysql/mysql.log,/var/log/mysql/error.log

# Whitelist your own IPs
ignoreip = 192.168.1.0/24 10.0.0.1

# Adjust ban action if using non-standard ports
action = iptables-multiport[name=mysql, port="3306,3307,3308", protocol=tcp]

Always verify your setup before enabling:

# Test the filter
fail2ban-regex /var/log/mysql/error.log /etc/fail2ban/filter.d/mysql-auth.conf

# Check jail status
fail2ban-client status mysql-auth

If you've changed the default MySQL port, update the jail configuration:

[mysql-auth]
port = your_custom_port_number

For high-traffic databases, consider these optimizations:

# In jail.local
maxretry = 5
findtime = 3600
bantime  = 7200
backend = polling  # or auto if using systemd

Regularly check your Fail2Ban operations:

# View banned IPs
fail2ban-client status mysql-auth

# Unban an IP if needed
fail2ban-client set mysql-auth unbanip 192.168.1.100