Resetting MySQL root password

MySQL root user is the built in user with administrative privileges on MySQL server. Even though this is the default administrator user, you can add any number of new users with varying administrative rights on MySQL server. In many occasions system administrators tend to forget the MySQL root password, but this can be fixed with out much pain.

Stop the MySQL server. If you have start/stop service scripts installed you can do it that way:

[root@linuxbox1 ~]#service mysqld stop OR

[root@linuxbox1 ~]#/etc/rc.d/init.d/mysqld stop or whatever other script you use normally.

In some cases, this may fail if the server is overloaded, in such a case you will have to kill the mysql process

You can get the PID of the process using ps command:

[root@linuxbox1 ~]# ps aux|grep mysqld OR

looking in the mysql pid file ( found usually in MYSQL_DATA_DIRECTORY/mysql.pid or /var/run/mysqld/mysqld.pid MySQL data/installation directory is usually /var/lib/mysql ). Then kill the process

[root@linuxbox1 ~]#kill `cat /var/run/mysqld/mysqld.pid`

Now since the mysql server has stopped, start mysqld with the –skip-grant-tables option

[root@linuxbox1 ~]# /usr/bin/mysqld_safe –skip-grant-tables

Now set new root password

1) Using mysqladmin

[root@linuxbox1 ~]# mysqladmin -u root password ‘my_newpass#123‘

Note that you don.t need to provide a password to use mysqladmin. Now flush the privilege tables in mysql to take the effect of password change

[root@linuxbox1 ~]# mysqladmin -u root flush-privileges

2) Using mysql client

[root@linuxbox1 ~]# mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD(’my_newpass#123′) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

Password is reset now. Now you have to restart MySQL service. First kill the mysqld_safe process, use the ps command to get the pid of mysqld_safe. Start MySQL service as usual.

[root@linuxbox1 ~]#/etc/rc.d/init.d/mysqld start

Now you will be able to connect to MySQL with new password ‘my_newpass#123‘

[root@linuxbox1 ~]#mysql -u root -pmy_newpass#123