Method 1: Using the SET PASSWORD Command
Syntax:
mysql> SET PASSWORD FOR username@localhost = PASSWORD('new_password');
Example:
mysql> SET PASSWORD FOR root@localhost = PASSWORD('123');
Method 2: Using mysqladmin
Syntax:
mysqladmin -u username -p old_password password new_password
Example:
mysqladmin -uroot -p123456 password 123
Method 3: Directly Editing the user Table with UPDATE
1. Log in to MySQL.
2. Switch to the mysql database:
mysql> USE mysql;
3. Update the password:
mysql> UPDATE user SET password=PASSWORD('123') WHERE user='root' AND host='localhost';
4. Refresh privileges:
mysql> FLUSH PRIVILEGES;
Method 4: If the Root Password is Forgotten
Example (on Windows):
1. Stop the running MySQL service.
2. Open a DOS window and navigate to the mysql\bin directory.
3. Start MySQL without privilege tables:
mysqld --skip-grant-tables
This skips grant table authentication during startup.
4. Open another DOS window (as the first one is now occupied) and navigate to the mysql\bin directory.
5. Enter mysql to log in. If successful, the MySQL prompt (>) will appear.
6. Switch to the mysql database:
USE mysql;
7. Update the root password:
UPDATE user SET password=PASSWORD("123") WHERE user="root";
(Don’t forget the semicolon at the end.)
8. Refresh privileges (mandatory step):
9. Exit:
quit
10. Restart the system, then log in using the username root and the newly set password 123.
