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
mysqld_safe --skip-grant-tables
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
use 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
FLUSH PRIVILEGES;
8. Exit
exit;
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.