Unregistered user that is trying to login to MySQL can generate below error is MySQL error log
2023-10-25T15:13:38.913862Z 1716 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
as you can see none of my user is using sha256_password
mysql> select user, host from mysql.user where user='pmm-dev-tcb';
+-------------+----------------+
| user | host |
+-------------+----------------+
| pmm-dev-tcb | 127.0.0.1 |
| pmm-dev-tcb | 192.168.170.43 |
| pmm-dev-tcb | localhost |
+-------------+----------------+
In MySQL when by example the user Name pmm-dev-tcb@192.168.170.47 is not found, MySQL automatically assign a random plugin before denying the access to that user.
In order to find out which is user is getting access denied and generating that error in MySQL error log we’re going to enable CONNECTION_CONTROLCONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
mysql> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+--------------------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+--------------------------------+-----------------+
| 'pmm-dev-tcb'@'192.168.170.47' | 38 |
+--------------------------------+-----------------+
Voila 🙂 above pmm credential doesn’t exist yet in our environment and is getting access denied as unregistered user filling out our log with the below error
2023-10-25T15:13:38.913862Z 1716 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
Mikael HOUNDEGNON MySQL Wall 