MySQL: Reset root password on Debian based distros

[^rooker]

[PROBLEM]
I've locked myself out of my MySQL, because I've forgotten my mysql-root password.

I didn't want to shutdown the MySQL server in order to follow general guides for resetting the password, like this one:
http://www.cyberciti.biz/tips/recover-m ... sword.html


[SOLUTION]
On Debian systems, package scripts sometimes need to initialize database things - so the package system needs a mysql-user with administrator priviledges.

This user is called "debian-sys-maint" - and its password is randomly generated during installation time and can be found in /etc/mysql/debian.cnf

Now, you can use the debian-sys-maint account to log in:

Code: Select all

mysql --user=debian-sys-maint --quick --password

(the "--quick" option is important, otherwise the new root password would be stored in your ~/.mysql_history)

Now within the mysql terminal, type the following to reset the password:

Code: Select all

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit;

That's it. You should now be able to login as mysql-root using your new password.

No service restart, no nothing. perfect!

[^rooker]

I've now found a nice documentation at the mysql website, called "Resetting the Root Password: Generic Instructions", describing a plain-simple and effective way to reset the root password properly.

BUT: You have to stop/restart the mysqld for this - so keep that in mind on production systems, because you'll have a downtime.

Here's a step-by-step HowTo (so I remember it myself):

1) Shutdown the mysql daemon:

Code: Select all

sudo /etc/init.d/mysql stop

(You might also try "sudo stop mysql" on upstart-based systems)

2) Restart the mysqld manually, and disable authorization completely:
Usually, "mysqld" is not running as root, but as user "mysql" - so to avoid ugly side effects of temporarily starting it with wrong (too high) permissions, use "sudo":

Code: Select all

sudo -u mysql /usr/sbin/mysqld --verbose --skip-grant-tables

The "skip-grant-tables" option enables anyone to connect without password

3) Log in to mysql (as any user):

Code: Select all

mysql

4) Reset the root password:

Code: Select all

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;

5) Shutdown the "open" mysql daemon and restart it properly:
Stop it:

Code: Select all

mysqladmin -u root -p shutdown

NOTE: no sudo, no nothing, because mysql currently listens to anyone...

Restart it:

Code: Select all

sudo /etc/init.d/mysql start