The other day I needed to reset the root password for a mysql database because I forgot it (oops!). Fortunately, there is a simple solution to resetting root password in mysql.
he first thing you need to do is login to your server as root. Next,you need to stop mysql with the following command:
/etc/init.d/mysql stop
Once Mysql is stopped, you need to restart it, but in the background with the mysql_safe command like this:
/usr/bin/mysqld_safe –skip-grant-tables &
The & shoves the process into the background. Once MySQL is up and running you will see the following output:
[1] 6831
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6961]: started
You can see the new job number, in this case, [1] has started and the server is running with the process ID of 6831. By using the –skip-grant-tables option you can connect to the MySQL database without a password, which will allow you to change it to a password that you know.
VERY IMPORTANT: You must enter the following commands exactly as shown. Particularly, words in upper case, and having a semi-colon ( ; ) at the end of each command.
OK now we are going to login to the MySQL database with the following command:
mysql –user=root mysql
It will ask you for a password. Just press the [Enter] key.
At the mysql> prompt enter the following:
update user set Password=PASSWORD(‘new-password-here’) WHERE User=’root’;
NOTE: in the above command, you must use single quotes around the new-password-here part. MySQL will return the following:
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Next type the following: flush privileges;
And then: exit
Now that the root password has been changed, you need to stop the MySQL server so that you can go back to running a secure MySQL server with password restrictions in place. So you need to bring the MySQL server running in the background into the foreground by typing “fg” and then kill it by pressing “Ctrl+C” afterwards.
Now just start the MySQL server with the following command:
/etc/init.d/mysql start
You now should be able to login to your MySQL server with your new root password by using the following command:
mysql –user=root –pass=new-password
This should let you in to your MySQL server.
This article was originally posted on www.mikestechblog.com Any reproduction on any other site is prohibited and a violation of copyright laws.