Change MySQL root password

So you forgot your MySQL root password ? Don’t worry, logging in is easy as 1,2,3:

  1. Stop all running mysqld processes (/etc/init.d/mysqld stop or killall mysqld)
  2. # mysqld –skip-grant-tables
  3. $ mysql -uroot and you’re in, no password required

Till here, you’ll be able to operate with your DB…but, you’ll not be able to change your password right away:

mysql> SET PASSWORD FOR root = PASSWORD('will_not_forget_again');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables
option so it cannot execute this statement

Here’s the workaround:

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user
    -> SET PASSWORD=PASSWORD('really:I_will_not_forget_again')
    -> WHERE user="root";
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Thanks to netadmintools for this quick recipe :)