Question: I forgot the MySQL root password. How do I reset it?
# mysql -u root Access denied for user 'root'@'localhost' (using password: NO)'
Solution:
The MySQL root password can be set using the following procedure:
1. Stop the MySQL service:
# service mysqld stop Stopping MySQL: [ OK ]
2. Start MySQL in safe mode as it does not ask for root password in safe mode.
# /usr/bin/mysqld_safe --skip-grant-tables &
Note: mysql_safe is a shell script which invokes mysqld, but additionally traps any forceful terminations of the MySQL server and avoids any database corruption.
3. Change the password of the root user:
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
4. Restart mysqld using mysqladmin to ensure that the service shuts down successfully (as it was started manually in step 2).
# mysqladmin -p shutdown
5. Restart the MySQL service as per normal:
# service mysqld start