I received a panic call from a newbie MySQL DBA. Or should I say the ‘Linux Admin’/’MySQl DBA’/’CSS guru’/’PHP Programmer’/’Network Admin’/’Backup Operator’/’CIO’ of a small business. He had reset his password was was now locked out. Luckily, he had only changed his password and still had root access.
What he did:
SET PASSWORD for 'mrdoesall'@'10.%' to 'bigsecret';
Long time MySQL DBAs should be groaning at this with a wince remembering when it happened to them. For those of you who did not catch the problem, what happened is that he value in the user.password table is set to the string ‘bigsecret’. When our friend tries to login, the password is encrypted and compared to the value in user.Password. The comparison of the encrypted value does not equate to the unencrypted value and the login fails.
What he meant to do:
SET PASSWORD for 'mrdoesall'@'10.%' = PASSWORD('bigsecret');
So with the help of the root account, all was resolved. The CLI interface can let you step on your own feet which is one of the reasons I recommend MySQL Workbench to novices and non full-time DBAs.