Recovering MySQL Password and Permission Problems

If you garble your GRANT commands or forget passwords and find that you don’t have access to the critical mysql table even as the root user don’t panic. You can access MySQL without password. Superuser can access MySQL using mysqld_safe command and you can change mysql password. Become the superuser on the operating system (we’re talking now about the Unix root, not the MySQL root) and kill the MySQL process.

Step : 1

Stop MySQL server

shahid@shahid:~$ sudo /etc/init.d/mysql stop
[sudo] password for shahid:
 * Stopping MySQL database server mysqld                                 [ OK ]
shahid@shahid:~$

Step : 2

Make sure MySQL is was not running

shahid@shahid:~$ ps aux | grep mysql
shahid   18751  0.0  0.0   3004   756 pts/5    S+   11:54   0:00 grep mysql
shahid@shahid:~$

Step : 3

Start Mysql using mysqld_safe

shahid@shahid:~$ sudo mysqld_safe --skip-grant-tables
nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[19285]: started

Step : 4

Make sure you can now get access to the mysql database:

shahid@shahid:~$ mysql mysql
mysql>

Assign a password once again to the MySQL root user:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root' AND Host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>

Step : 5

Terminate the MySQL server and restart it in the usual way. Make any necessary changes to the privileges through GRANT commands, running mysql as the root user.

shahid@shahid:~$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                                 [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
shahid@shahid:~$