Have you ever dealt with different Mysql Installations on different machines?
Have you ever mixed up root passwords of different installations?
I recently had that happened to me, I decided to look further into it and find a good way to do a password reset for my root account.
Here is what we will do:
- Stop the mysql daemon.
- Start mysql with "--skip-grant-table" option.
- login with your root account with no password.
- update the mysql.users table with your new password.
- restart the mysql default daemon.
Step1: stop the running mysql daemon
/etc/init.d/mysql stop
Output:
* Stopping MySQL database server mysqld [ OK ]
Note: Make sure it is stopped with the following command
ps aux | grep mysql
You should see only one entry for the grep command you just typed.
Step2: we start mysql with "--skip-grant-table" option.
mysqld_safe --skip-grant-table
You should find output similar when you hit
ps aux | grep mysql7090 pts/1 S 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-table7129 pts/1 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-grant-table7131 pts/1 S 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld7246 pts/2 S+ 0:00 grep --color=auto mysql
You should login successfully without any password.
mysql -uroot
Step4: change your root password
use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit
/etc/init.d/mysql stop/etc/init.d/mysql start
You should be able to login normally with your new password.