Setting, Changing And Resetting MySQL Root Passwords

This tutorial explains how you can set, change and reset (if you've forgotten the password) MySQL or MariaDB root passwords. Time and again I see problems like mysqladmin:  connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'. So I thought it's time to remind you how to solve MySQL related password problems. If you are just looking for a quick fix how to reset a MySQL root password you can find that at the bottom of this tutorial.

mysqladmin Command To Change Root Password

Method 1 - Set up root password for the first time

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

mysqladmin -u root password newpass

If you want to change (or update) a root password, then you need to use the following command:

mysqladmin -u root -p oldpassword newpass

Enter password:

If you get...

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

then follow the instructions below on how to recover your MySQL password.

An alternative to using the mysqladmin command when setting the MySQL or MariaDB root password the first time is to use the mysql_secure_conection command. This command will not only ask for the old- and new MySQL root password but will also do some other security settings like disabling the test database.

Here is how to use that command:

mysql_secure_connection

Answer the questions as shown below:

Change the root password? [Y/n] <-- y
New password: <-- Enter a new MySQL root password
Re-enter new password: <-- Repeat the MySQL root password
Remove anonymous users? [Y/n] <-- y
Disallow root login remotely? [Y/n] <-- y
Remove test database and access to it? [Y/n] <-- y
Reload privilege tables now? [Y/n] <-- y 

The above answers are recommendations, you are free to choose other settings e.g. when you prefer to keep the test databases or need remote access for the root user. Note: you do not need remote access to use PHPMyAdmin remotely.

Change MySQL password for other users

To change a normal user password you need to type:

mysqladmin -u user-name -p oldpassword newpass

 

Method 2 - Update or change password

MySQL stores usernames and passwords in the user table inside the MySQL database. You can directly update a password using the following method to update or change passwords:

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';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

 

Recover MySQL root password

You can recover a MySQL database server password with the following five easy steps:

Step # 1: Stop the MySQL server process.

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

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

Step # 4: Set a new root password.

Step # 5: Exit and restart the MySQL server.

Here are the commands you need to type for each step (log in as the root user):

Step # 1: Stop the MySQL service:

service mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: Start the 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 the MySQL server using the MySQL client:

mysql -u root

Output:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56299
Server version: 5.6.34-1 (Debian)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

Step # 4: Set a 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 the MySQL server:

service mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

The output might differ based on the Linux distribution. Don't worry unless it reports an error. Start the MySQL server and test it:

service mysql start
mysql -u root -p

Share this page:

Suggested articles

31 Comment(s)

Add comment

Comments

From: Shlomi Noach at: 2009-01-16 18:45:04

Hi,

Nice post,

 The proper way to change passwords is using the "SET PASSWORD" syntax, like:

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

 Directly manipulating the mysql.users table is less preferred, since you may accidently inject typos.

See: http://dev.mysql.com/doc/refman/5.0/en/set-password.html

Regards,

Shlomi

From: SamTzu at: 2009-01-21 15:12:19

Thx 4 the feedback. I forgot to mention that the most common reason for this error is an empty password. You have to specify the root password while connecting to the Mysql database. mysqladmin -u root -pROOT-PASSWORD

Sam

From: Giuseppe Maxia at: 2009-01-16 18:11:42

Hi.

Your method for updating passwords is not the easiest nor the recommended one.

To change the password for the current user, simply use

SET PASSWORD=password('new_password');  # no FLUSH PRIVILEGES needed

To set the password for another user (as root), use

SET PASSWORD for user_name = password('new_password')

Best regards

Giuseppe

From: Tom Krouper at: 2009-01-16 19:07:25

A safer way to do this would be to add the "init-file=/tmp/grant.sql" to the [mysqld] section of your my.cnf file. In /tmp/grant.sql you would have...

 SET PASSWORD FOR [email protected] = PASSWORD('xxxx');

or whatever SQL that would fix the root user.

Restart MySQL and the password is reset. You can then remove /tmp/grant.sql and remove the init-file line from your configuration.

 Using the skip-grant-tables exposed your entire database to anyone or any service that has access to your mysql instance. If you make sure that the grant.sql file only is readable by the mysql daemon user, you will have less exposure.

 One more thing... instead of going to the mysql db to change the password for a user, you can just type the following at the mysql prompt:

SET PASSWORD FOR [email protected] = PASSWORD('xxxx');

I believe FLUSH PRIVILEGES is optional here, but I always run it out of habit.

From: Shlomi Noach at: 2009-01-16 19:10:04

Hi,

Sorry for double posting.

With regard to recovering root password, please see my post: Dangers of skip-grant-tables

 The post purposes that using an init-file is faster (requires but one restart instead of two) and safer.

Shlomi

From: Anonymous at: 2010-01-14 02:15:29

Thank you very much for this!

 One thing to change if you do not have mysql setup to run as root is the "# mysqld_safe --skip-grant-tables &"  should have a -user=mysql in it.

 

 For me, my mistake was changing the security rights of the root user away from localhost.  So I did this trick listed to get mysqld running, then used phpMyAdmin to fix it.

From: Naeem Rehman at: 2011-03-17 07:25:18

Thanks for this article, its really works for me.

 Thanks again.

From: Anonymous at: 2011-06-16 10:14:33

Really good!! Thanks

From: Recover MySQL root password at: 2013-04-27 12:24:48

Just to know: in debian you can use the debian-sys-maint user to recover the password.

From: at: 2014-09-23 11:38:04

Nice and simple one liner to update root password:
 
  1. mysqladmin -u root -p'oldpassword' password newpassword

As taken from: http://www.linuxtutorial.co.uk/mysql-change-root-password/
 

From: Joi Owen at: 2015-03-17 21:24:27

Which...only works if you have the original root password.  This whole article is about resetting a lost root password. 

From: Pranav at: 2015-07-31 11:25:11

Neither this solution nor other solutions from various blogs solved the same issue with my machine. God khnows why it didnt work when everything seemed to be in order.

 

Hence, as a last resort, I used a very crude way. I backed up all my databases by converting each of them to .sql files. Then I uninstalled both mysql and mysql-server (uninstalling mysql automatically removes mysql-server as dependency). Then deleted everything in /var/lib/mysql library as it is the folder where all the databases are stored. What it did was it deleted the user table along with all other databases.

Then I installed mysql-server again. Now there was no password defined for root user. Then I set password for the root by executing command “ /usr/bin/mysqladmin -u root password MyNewPass “. Now the issue was solved and I was able to login as root.

Note : This is a last resort when all the other solutions are failed.

From: Paul V Franklin at: 2017-02-17 05:30:27

In this case also, You need to give root password while taking backup.

If mysqldump command is run, it'll prompt for root password inorder to finish the backup.

So, Yours is not the correct solution.

 

From: Kat at: 2015-09-07 10:20:41

This worked, thanks Giuseppe Maxia

From: Khairul Azam at: 2015-11-25 18:50:04

Thanks a lot. :) 

From: Luvita at: 2016-02-25 05:34:49

Thanks a lot. Just 2 line changes for 5.7.11 version.. 

instead of update run, 

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW-ROOT-PASSWORD'; 

And run 'flush privileges;' before the above ALTER command. 

From: larama at: 2017-07-09 20:22:16

Thank YOU sir, I was about to give up and just purge the whole thing :)

From: AVNetmedia at: 2016-03-30 20:12:52

Mysql password reset worked! Kudos! 

From: Ngenge Senior at: 2016-06-11 09:20:02

Thanks alot the  'mysqladmin -u root password newpass' worked

From: Ivan Palacios at: 2016-06-23 20:11:30

Amazing note!!! worked like a champ... thanks very much!!!

From: Emin at: 2016-07-19 19:15:29

thanks for this post. I have been having these problems for several days and I would appreciate it if you could help me out

I downloaded MySQL and didn't jot down the temporary password that pops on the screen. Since then, I have not been able to reset my root password. No matter where I search, I can't find a solution. Any help?

From: Abdullah at: 2016-08-27 11:36:54

Thanks. it helped.

From: Jeremy at: 2017-01-23 03:53:51

I'm having trouble with this. When I stop the mysql service the mysqld directory disappears in the /var/run folder. Then when I try step 2, I receive an error that says /var/run/mysqld directory doesn't exist and the --skip-grant-tables does nothing to allow me to login as root. Am I missing something?

From: Irvan at: 2017-02-18 22:35:52

Thanks bro. There're many tutorials about it, but this is what I want and understand

From: Javed Usmani at: 2017-02-23 14:25:12

Thanks for your article, This is a very useful post for us.

From: Meet Patel at: 2017-03-23 00:31:26

Awesome post. It helped me a lot.

From: Rick at: 2017-04-07 20:22:04

Thanks for the post.  It helped me from a forgotten install :)

From: Kurt Hansen at: 2017-06-03 08:18:12

Step 1. Login as root and restart the MySQL deamon.

Step 2. Login to MySQL as: 

mysql --user=root mysql

Step 3. Show current User/Host/HEX(authentication_string) values.

SELECT User, Host, HEX(authentication_string) FROM mysql.user;

Step 4. Use this command to change the password for the user. (You can leave it blank and run the command from step 3 to verify the previous password was removed, then change it).

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[email protected]$w0rd';

Step 5. Verify that the HEX string has changed using the command from step #3.

Step 6. Exit MySQL and verify that the new password works.

mysql -u root -p

From: jeremy at: 2017-07-28 04:46:38

You literally just save my life

From: Faatimah at: 2017-07-28 13:38:45

Hi,

thank you for your post...but i have a problem and your can solve so can u just tell me what to do?

My problem this the first time am using Mysql on my computer...i use it mostly in school.

 So,when i open the cmd for Mysql it show me, Enter password...that it but the thing is i dont have any password so when i press enter it closed by itself. so can you just help me please. Really need this for my upcoming assignment.

Thank you.

From: Lite at: 2017-10-12 05:40:47

Recent version has changed the field `password` to `authentication_string` in mysql.user table.

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