MySql: Change Root Password

1. Use Mysqladmin Command To Change Root  User Password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p oldpassword newpass

Enter password:

2.  Reset /Recover MySQL Root Password

In case you can not remember the root password, following steps can be used to reset the password.
Here are commands you need to type for each step (login as the root user):

 Step # 1 : Stop mysql service

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables;

Output:

[1] 5988

Starting mysqld daemon with databases from /var/lib/mysql

mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Output:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 5.0.1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;

mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';

mysql>flush privileges;

mysql>quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld

STOPPING server from pid file /var/run/mysqld/mysqld.pid

 Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p

Enter password.