I’ve found that in my journies, the easist way to reset a mysql password, assuming you have SSH / Shell access is to log into the server and change it. To do this, you need to do the following;
1. Login to the server via SSH.
2. Once logged in, you’ll need to modify the my.cnf file to force your mysql to skip the grant tables. This will allow you to reset mysql so you can gain access without providing the password. One of the benefits of SSH access.
The my.cnf file will look like this;
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
#skip-grant-tables <— This is the line we add. Remove the # to enable it. Once done remove that or place the # back into place.
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3. Now that you added this, reset mysql.
4. Once mysql is restarted, you can gain access to it directly by typing ;
$ mysql -u root
It may ask for a password, but you can ignore it.
5. Now that you are logged in, will need to access the mysql database;
use mysql;
6. Lastly, we need to change the password by running the following;
UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’root’;
7. Now you can remove the line in the my.cnf that we added in step 2. Once you remove it, save the configuration and restart mysql.
That’s it. The password has been changed!
