I was having problem with MySQL root password after a fresh installation on CentOS 6. I cannot login to mysql as i didn’t know what was the default password for root. The installation didn’t prompt me to enter the password for root user.
So if you have a problem like me, or you have forgotten your root password, here’s how to reset it.
1. Shutdown the MySQL
service mysqld stop
2. Start mysql in safe mode
Keep this process running, and don’t close this terminal
3. Open new terminal
4. Login to MySQL as root, and without password
mysql -u root
5. Change to database mysql
6. Now update the password for root user
update user set authentication_string=PASSWORD('YOUR-NEW-PASSWORD-HERE'), password_expired = 'N' where user='root';
notes: In MySQL 5.7, the `password` field was removed, now the field name is `authentication_string`.
7. Don’t forget to flush it
9. Back to terminal 1, and stop the process (or close it). Then start the MySQL
service mysqld start
10. Now you can login to MySQL with your new password
mysql -u root -pYOUR-NEW-PASSWORD-HERE
Hope it helps.