How To Change MySQL Root Password

Method 1. How to Change MySQL Root Password Using mysqladmin Command?

You can change the mySQL root password using mysqladmin command as shown below. Please note that there is no space between -p and currentpassword.

# mysqladmin -u root -pcurrentpassword password 'newpassword'

Once you’ve changed it make sure you can login with your new password successfully as shown below.

# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
mysql>

Method 2. How to Change MySQL Root Password From MySQL Prompt Using UPDATE SQL Command?

You can also use the standard update SQL command combined with the MySQL password function to change the password as shown below.

Login to MySQL root account using old password

# mysql -u root -poldpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
mysql>

Use the UPDATE Command to change root password.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verify the new MySQL root password

Once you’ve changed it make sure you can login with your new password successfully as shown below.

# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
mysql>

Method 3. How to Set MySQL Root Password Using mysqladmin Command?

This method works only if there is no password currently assigned for the root account. If you don’t have any password assigned to the root account, set the password without giving current password as shown below.

# mysqladmin -u root password 'newpassword'
[Note: There is no currentpassword for root in this example]

How to Change MySQL Regular User (non-root) Password Using mysqladmin Command?

You can use the same mysqladmin command to change password for a mySQL end-user account as shown below.

# mysqladmin -u jsmith -pcurrentpassword password 'newpassword'

How to Change MySQL Regular User (non-root) Password From MySQL Prompt Using UPDATE SQL Command?

You can also use the standard update SQL command combined with the MySQL password function to change the password of a non-root user as shown below.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='ramesh';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

copied from : http://www.thegeekstuff.com/2009/07/change-reset-mysql-root-password-on-unix-linux/

How To: Change Mysql root Password

Changing Mysql password is one of the very essential & critical task which you need to do while taking care of the database. By default “root” comes up with admin privileges and no password for root. Just to be clear, this root user is different from what your Linux/Unix system root user is, so changing password for this user, won’t change password for root user for operating system.

Mysql stores the password in the “user” table in “mysql” database. To change/update a password, we need to update that table data. That can be done either at the OS level with “mysqladmin” command or either at the Mysql level with sql command.

Changing Password with mysqladmin command:

mysqladmin command is one way to interact with the information stored in user table from OS command line interface. If you have done a fresh installation of Mysql, then there is no password set for root user. To set a new password for the same, use this command:

$ mysqladmin -u root password NEWPASSWORD

This will create the password for the root user.

Also, to change the password of root user, one needs to use the command like this:

$ mysqladmin -u root -p'oldpassword' password 'NEWPASSWORD'

If you don’t provide any old password with “-p”, then it will prompt you for a pasword after executing this command.

To change the password of normal user, same procedure is followed:

$ mysqladmin -u napster -p'oldpassword' password 'NEWPASSWORD'

Changing password with Mysql sql command:

You can also update the information in the user table with the sql command. Following steps needs to be followed for the same.

Login to Mysql server

$ mysql -u root -p'password'

Go to mysql database

mysql> use mysql;

Change password for napster user

mysql> update user set password=PASSWORD("NEWPASSWORD") where user="napster";

Now reload the privileges, which will update the cache and flush the old password

mysql> flush privileges;

mysql> ctrl+D

copied from : http://www.geekride.com/howto-change-mysql-root-password/

recover mysql password

One of the very worst scenarios for a system administrator is when they forget a password for the root account. That is the same case when a Database admin does the same for the root account for Mysql Database. So to help here, these are the following steps which one can perform to recover the root password back for Mysql database.

Stop Mysql Server:

$ sudo /etc/init.d/mysql stop

Then, start the mysql server with skip-grant-tables options. –skip-grant-tables option will force the mysql process not to read the user table into memory, so that when you try to login to the Mysql server, it won’t ask for the password.

$ sudo mysqld_safe --skip-grant-tables  &

This command is very dangerous if you Mysql server is connected to the network. While you are recovering your password, someone can login to your server remotely and do whatever he/she wants. To keep that safe, start your server with “–skip-networking” option, which will not open a network port for the server

$ mysqld_safe --skip-grant-tables --skip-networking &

Now connect to mysql server using mysql client without giving any password

$ mysql -u root -p

Now setup the new password for the root user:

mysql> use mysql;

mysql> update user set password=PASSWORD("NEWPASSWORD") where user="root";

mysql> flush privileges;

mysql> Ctrl+D

Now stop the mysql server

$ sudo /etc/init.d/mysql stop

Now start the mysql server normally, so that it could read the password information.

$ sudo /etc/init.d/mysql start

duplicated from : http://www.geekride.com/recover-mysql-root-password/