Reading Time: 2minutes
Whether you have to administrate a bunch of chaotic organized MySQL servers and amount of work is more than you can bear it is very common you make stupid mistakes, like loosing MySQL root adminsitrator password. There is way to recover password by stopping SQL server and starting it with –skip-grant-tables options via SSH , however if you do it that way there is at least few seconds of down time and as its not a good idea on productive servers Debian and Ubuntu Linux admins have better way to do it by using MySQL default user used to check whether all is fine with database on MySQL server initialization via /etc/init.d/mysql. User with GRANT PRIVILEGES, (all MySQL administrator users have grant privileges) on Debian based distributions is debian-sys-maint and whether you have root access to server you can easily obtain password with:
# grep -i -E 'user|pass' /etc/mysql/debian.cnf |uniq
user = debian-sys-maint
password = k6x6tBUBfHN3ZxHv
Using this password then you can login via mysql cli or via PhpMyAdmin, whether installed and do any normal SQL operation you do as root. Of course having this password in plain text file can be very dangerous, by default it is configured to be only red by root be careful not to change this permissions by default as anyone who has access to system can then access your SQL as administrator.
To reset MySQL root password once logged in run:
UPDATE USER set password=PASSWORD('NEW_PASS_WORD') where USER='root';