How-To: Set/Change/Reset MySQL Passwords

MySQL is a rock solid relational database management systems (RDBMS) used in intensive web applications and large-scale databases. It is also a major component in the AMP stack along with Apache, (MySQL) and php. MySQL is now a subsidiary of Sun Microsystems, Inc. however the project’s source code is available under terms of the GNU General Public License.

Setting and changing MySQL passwords is relatively easy so here’s a great rundown of how to do that as well as change and reset and existing password.

This tutorial will teach you how to master these three important functions that every MySQL user or Administrator should know.

Here’s how to set your MySQL password for the first time.

You should be logged in as root. Execute the mysqladmin command as follows:

mysqladmin -u root password newpass

Where newpass is your new password. To change your existing password, use the following command:

mysqladmin -u root -p oldpassword newpass
enter password:

To change/update your MySQL password, do the following:

1) Login to the MySQL server, type the following command at the shell prompt:

$ mysql -u root -p

2) Use the mysql database (type commands at the mysql> prompt):

mysql> use mysql;

3) Change password for a user:

mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

SET PASSWORD FOR 'someuser'@'somehost' = PASSWORD('newpassword');

4) Reload privileges:

mysql> flush privileges;
mysql> quit

To recover your MySQL password please take the following steps:

Step # 1: Stop the MySQL server process.

/etc/init.d/mysql stop

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for a password.

mysqld_safe --skip-grant-tables &

Step # 3: Connect to the MySQL server as the root user.

mysql -u root

Step # 4: Set a new root password.

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

Step # 5: Exit and restart the MySQL server.

/etc/init.d/mysql stop

/etc/init.d/mysql start

mysql -u root -p