Resetting a Lost MySQL Root Password

From SuperbHosting.net Support Wiki

Jump to: navigation, search

1.We first need to kill all the instances of MySQL daemons running. 

 ps -ef || grep mysql will show all the PIDs.
 kill -9 all those PIDs

2.We then start MySQL with the -Sg option which means "skip grant" - anyone can log in to any database. 

 This is what we want since in order to change root password, we need to login to the MySQL database.
 /usr/local/bin/safe_mysqld -Sg -l &

3.Now we can login. 

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

  Welcome to the MySQL monitor. Commands end with ; or g.
  Your MySQL connection id is 1 to server version: 3.22.27-log

  Type 'help' for help.

  mysql> UPDATE user SET Password<code>PASSWORD('newpasswd') where user</code>'root';
  Query OK, 2 rows affected (0.10 sec)
  Rows matched: 2 Changed: 2 Warnings: 0

  mysql> FLUSH PRIVILEGES;
  Query OK, 0 rows affected (0.03 sec)

  mysql> quit
  Bye

4.Now you must reload MySQL so that the grant table is used. 

  mysqladmin -p reload
  Enter password:
Retrieved from "http://www.superbhosting.net/support-wiki/Resetting_a_Lost_MySQL_Root_Password"
Personal tools