Controllare gli errori di accesso a MySQL

Questa pagina qualche suggerimento su come controllare gli errori di accesso a MySQL.
Gli errori di connessione alla base dati sono importanti perche', oltre ad impedire il collegamento all'utente interessato, se ripetuti oltre un certo limite, bloccano tutte successive connessioni dallo stesso IP anche a tutti gli altri utenti.

Di solito sul database MySQL scrivo di aspetti piu' complessi o articolati... questa volta si tratta di banali comandi SQL, che ho voluto riportare lo stesso!

Dal client

Utilizzando un client la prova di accesso a MySQL e' molto semplice:
mysql --host=myhost --user=myname --port=3306 -p

Eventuali errori sono riportati in modo esplicito!

ERROR 1045 (28000): Access denied for user 'meo'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'meo'@'localhost' (using password: YES)
ERROR 2005 (HY000): Unknown MySQL server host 'xenia01' (0)
ERROR 2003 (HY000): Can't connect to MySQL server on 'mysql01.xenialab.it' (60)
ERROR 2005 (HY000): Unknown MySQL server host 'mysql02.xenialab.it' (0)
...

Con la sola avvertenza che l'utente in MySQL e' composto da utente e host di provenienza tutti gli errori si risolvono facilmente.

Se non si dispone del client mysql e' comunque possibile controllare la sola raggiungibilita' del DB con in semplice comando:
telnet mylhost 3306

MySQL logging

Se la connessione e' effettuata da un'applicazione gli errori di connessione potrebbero non essere presentati in modo evidente ed e' quindi necessario controllare sul DB server.

MySQL utilizza principalmente tre log:

General log: su cui vengono scritti tutti gli statement SQL e gli accessi eseguiti.
Viene posto sul file hostname.log per default e, sempre per default, e' disattivo perche' rallenta troppo e cresce a dismisura.
Error log: su cui vengono scritti tutti gli errori ed i riavvi di MySQL.
Viene posto sul file hostname.err per default ed e' generalmente attivo. MySQL non scrive molte cose su questo file...
Slow log: su cui vengono scritti tutti gli statement SQL che hanno impiegato troppo tempo ad essere eseguiti.
Viene posto sul file hostname-slow.log per default e, sempre per default, utilizza un timeout di 10 secondi.

Poiche' il problema di accesso non e' un problema a livello di database il file hostname.err non riporta nessun errore! La funzionalita' di logging esteso (general log) su MySQL e' semplice ma tipicamente disabilitata perche' troppo pesante: effettua il log di ogni statement SQL eseguito sulla base dati!

L'unica evidenza della presenza degli errori di connessione e' la crescita di contatori nella tabella performance_schema.host_cache [NdA disponibile dalla versione 5.6].
Come fare quindi per comprendere se si sono errori di accesso e quali? Il trucco e' di attivare il log per qualche secondo [Nda si puo' fare dalla versione 5.1] e poi verificare il contenuto del log. Ecco come:

mysql> SET GLOBAL general_log = 'ON'; Query OK, 0 rows affected (0.00 sec) mysql> select sleep(10); -- Ovviamente opzionale, utile per misuare il tempo... mysql> SET GLOBAL general_log = 'OFF'; Query OK, 0 rows affected (0.04 sec) -rw-rw---- 1 mysql mysql 28347771 Feb 14 09:06 hostname.log [root@myHost mydata]# grep Access myHost.log 119285 Connect Access denied for user 'wrongUser1'@'myHost.myDomain' (using password: YES) 119864 Connect Access denied for user 'wrongUser2'@'myHost.myDomain' (using password: YES) 119869 Connect Access denied for user 'wrongUser2'@'myHost.myDomain' (using password: YES)

Varie ed eventuali

Per implementare un logging piu' selettivo e' possibile utilizzare MySQL Proxy. Va tuttavia sottolineato che MySQL Proxy non e' uno strumento ufficiale ed e' distribuito fino alla versione 0.8.5 alpha (non e' mai stato in GA).

Anche se meno utilizzata rispetto alla Community Edition e' importante ricordare che viene distribuita da Oracle Corp. anche la MySQL Enterprise Edition [NdE a pagamento] che comprende, tra le altre funzionalita' aggiuntive, MySQL Enterprise Audit.
MySQL Enterprise Audit utilizza l'API MySQL Audit per attivare un logging basato su policy. Come default il file di log e' chiamato audit.log, posto nella data-directory ed e' in formato XML.
Questo e' lo strumento ufficiale suggerito per gli ambienti di produzione.

L'interfaccia per la scrittura di Plugin di Audit e' aperta e quindi sono disponibili tool di terze parti ed e' possibile sviluppare propri plugin di auditing [NdA l'API e' cambiata in modo significativo nell'ultima versione MySQL 5.7].

Titolo: Controllare gli errori di accesso a MySQL
Livello: Medio (2/5)
Data: 14 Febbraio 2015
Versione: 1.0.2 - 14 Febbraio 2016
Autore: mail [AT] meo.bogliolo.name