How to change MySQL root Password

How to change MySQL root Password

Having a securr MySQL password is one of the most important things you must do to protect your data. In MySQL the root user is admin account user. MySQL the server uses passwords stored in the user table, this means that passwords are distinct from passwords for logging in to your operating system.

Solution 1: Works when you remember root password or password is blank

When you install MySQL it should prompt you for the root password, but sometimes it will leave the password blank. Leaving the password black for root user could be a serious security issue.

You can use mysqladmin to set the password when is blank with the command:

mysqladmin -u root password NEWPASSWORD

If you want to change the password you will need to provide it with "-p":

mysqladmin -u root -p'currentPassword' password newPassword

Remeber to use a secure random password of at least 20 chards for maximum securirty. Your application should never use the root account to access database, create new users for applications.

You can use mysql client to test the new password:

mysql -u root -p'newPass' databaseName

Solution 2: Changing MySQL root with queries

MySQL uses the table "users" to store database users. You can use SQL queries to change the users passwords.

mysql -u root -p

A MySQL terminal should be open with "mysql>" promot:

use mysql;
update user set password=PASSWORD("newPass") where User='root';
flush privileges;

Remeber to use the PASSWORD function which will hash you "newPass" password.

Solution 3: You don't remeber root password of MySQL

If you have access to sudo or root user of the operation system, you can start MySQL in safe mode to reset the password.

First stop the MySQL service:

sudo /etc/init.d/mysql stop

Then start MySQL with:

sudo mysqld_safe --skip-grant-tables &

Now you can connect with "root" user without password:

mysql -uroot

Execute the following queries to change the root password:

use mysql;
update user set password=PASSWORD("newPass") where User='root';
flush privileges;
quit

Restart without "mysql_safe"

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start