MySQL is a well known Open Source database application which has been used by many individuals, big companies and corporations around the world (including me myself). MySQL is a simple, fast, and robust database application which is available on Windows and GNU/Linux platform. The application comes in form of command line application where you have to type all the commands and/or query on a terminal or command prompt. There has been several application that was built to make life easier, by simply providing a GUI-based application, such as MySQL Administrator, MySQL Query, and PHPMyAdmin (my favourite).
One problem that often arise in MySQL troubleshooting is how to restore a forgotten MySQL password. This article will give a solution on that problem. There are two ways which can be used and it's up to you what way are you going to take. Both have no risk at all, since it won't modify any of your data (as usuall, backup is always recommended as it's just copying a directory containing your database data to another location).
First of all, you will have to login into your system and switch your user into root account or the same user where the MySQL daemon runs as (usually mysql). Next, locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system's hostname. You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command:
kill `cat /mysql-data-directory/host_name.pid`
Note : the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.
Another way is by using the service startup script and give stop parameter to stop the service. Again, the location of the startup script will differ in each distro. In Slackware, we can use :
/etc/rc.d/rc.mysql stop
For last effort, you can also use ps -aux | grep mysql command to find MySQL PID and use kill <PID> to kill MySQL process (although it's not recommended unless you are so desperate).
Next, create a text file and place the following command within it on a single line:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
Save the file with any name. For this example the file will be ~/mysql-init. Restart the MySQL server with the special --init-file=~/mysql-init option:
mysqld_safe --init-file=~/mysql-init &
The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init. After doing this steps, you should be able to connect using the new password. Don't forget to stop the service and restart the service again with the startup script, but with a start parameter. This will ensure you that the password has been changed, since if the system is rebooted, the startup script will be used rather than our command, so we can use it for testing purposes also.
Alternatively, you can set the new password using the mysql client (but this approach is less secure). Stop mysqld and restart it with the --skip-grant-tables --user=root options
mysqld_safe --skip-grant-table --user=root
This option will skip the all authentication. Next, connect to the mysqld server with this command:
mysql -u root
Since, the authentication is skipped, you don't have to specify any password for root account. You can change the root password by using this SQL query :
UPDATE mysql.user SET Password=PASSWORD('MyNewPassword') WHERE user='root';FLUSH PRIVILEGES;
After executing this query, your root password has been changed and it's a good idea to stop the service again and start it again with the proper way to test whether the password has been changed or not.
MySQL doesn't offer password recovering, but the documentation has give a wonderfull step by step on how to restore your MySQL password in case you forgot or somebody has change it without telling you about it. If you had problem with MySQL, the documentation is your first aid. Don't forget to install MySQL documentation when you install MySQL application.
Last Update : 9 July 2006 :: 10:11:22
Comments