MySQL root password change

Question

I have been trying to reset my MySQL root password. I have run mysqld_safe --skip-grant-tables, updated the root password, and checked the user table to make sure it is there.

Once restarting the MySQL daemon I tried logging in with the new root password that I just set and still get Access denied for user 'root' errors. I have also tried completely removing and reinstalling MySQL (including removing the my.cnf file) and still no luck. What can I do next?

If your not locked out `SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword');` is safest. — sabgenton, Sep 13 '13 at 17:23
I followed the steps in the video to change password in one of my servers https://youtu.be/gFo5DV_pSg8 — Prem, Dec 31 '18 at 19:21
Possible duplicate of [ERROR 1698 (28000): Access denied for user 'root'@'localhost'](https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost) — SanThee, May 08 '19 at 18:47
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD("1")' at line 1 SET PASSWORD FOR 'root'@'localhost' = PASSWORD("somepassword"); — discipleartem, Jun 30 '20 at 13:18
Ubuntu 20.04 has the root user's auth plugin as: auth_socket. That plugin does not support a password. There is an answer below that talks about it. Solution is to change the plugin and password in one statement : ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password'; The "WITH mysql_native_password" part changes the plugin. — cbmckay, Aug 05 '21 at 18:23

score 240 · Answer 1 · edited Oct 11 '20 at 17:02

240

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
FLUSH PRIVILEGES;

edited Oct 11 '20 at 17:02

T.J. Crowder

1,031,962
187
1,923
1,875

answered Feb 16 '14 at 07:07

kta

19,412
7
65
47

10

This is working for me in 5.7 while the accepted answer is not. – Stoopkid Oct 02 '17 at 19:01
6

This is the correct solution if your MySQL root password is set to blank. – fschuindt Feb 06 '18 at 13:30
7

> UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE user='root'; >FLUSH PRIVILEGES; In MySQL version 5.7.x there is no more password field in the mysql table. It was replaced with authentication_string. – Robert Anthony S. Tribiana Aug 19 '18 at 03:16
@fschuindt Nope, I had MySQL 5.7 root with password, once I login in cli this code change the password to the new one. – KeitelDOG Jan 05 '19 at 22:39
@fschuindt Nope, I had MySQL 5.7 root with password, once I login in cli this code change the password to the new one. – KeitelDOG Jan 05 '19 at 22:39
where am I supposed to enter this? – OZZIE Apr 29 '19 at 08:20
3

This gives a OK to me, but don't actually do anything – Freedo Aug 01 '19 at 08:13
2

It needs an extra command `FLUSH PRIVILEGES` to make effective the change without restarting the database server. – NetVicious Dec 12 '19 at 16:03
I did this to the root account (which already had a password) and now neither password works and I'm locked out. Could be because the flush privileges command was missing... not sure right now. – jeffmjack May 27 '20 at 15:22
@jeffmjack - I don't think it's because of the above command. It can be because of text encode issues. Anyway from memory mysql server daemon has the option to run sql to reset password during startup. It gives you an option to reset master password. – kta Jun 04 '20 at 13:15
1

Note this command is specifically for @'localhost'. I went on a wild goose chase trying to figure out why a different machine could not connect with the new password. See `SELECT User,Host FROM mysql.user;` to see various hosts to set the password for. (in my case @'%' for everywhere else) – jgawrych Feb 15 '21 at 02:32
2

i just remove PASSWORD function. – NecroMancer Oct 18 '21 at 20:16

score 108 · Answer 2 · edited Dec 29 '18 at 11:44

108

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

You can find Resetting the Root Password in the MySQL documentation.

edited Dec 29 '18 at 11:44

Matthijs

2,483
5
22
33

answered Dec 29 '18 at 09:12

iamfaith

1,097
1
7
2

2

This query is worked for me but new password is not working (Access denied). It's only start to work after I recreated root user https://askubuntu.com/a/784347/160368. – Artem P Apr 05 '20 at 22:01
1

works for me on MySQL 8.0.20 and after initializing data folder using this command `mysqld --initialize --console` – Ahmed HENTETI Jun 06 '20 at 19:07
This one returns "OK", whereas `UPDATE mysql.user SET authentication_string=PASSWORD('mypass') WHERE User='root';` and `SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');` return "ERROR 1064 (42000): You have an error in your SQL syntax;" on 8.0.33-0ubuntu0.22.04.2 . – philburns Jun 21 '23 at 14:42

score 83 · Answer 3 · edited Nov 23 '21 at 11:24

83

Have a look at this from the MySQL reference manual:

First log in to MySQL:

mysql -u root -p

Then at the mysql prompt, run:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

Then

FLUSH PRIVILEGES;

Look at this page for more information: Resetting the Root Password: Unix Systems

UPDATE:

For some versions of mysql, the password column is no longer available and you'll get this error:

ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

In this case, use ALTER USER as shown in the answer below.

edited Nov 23 '21 at 11:24

andromeda

4,433
5
32
42

answered Aug 07 '13 at 09:51

VSB

9,825
16
72
145

1

I got an error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column..." when updating the user's password. See http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench for how to work around this error. – Joe Borysko Dec 01 '16 at 23:18
23

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('MyNewPass') WHERE User='root'' at line 1 – discipleartem Jun 30 '20 at 13:12
3

PASSWORD function seems to have been removed. See this thread for alternatives https://stackoverflow.com/questions/52320576/in-mysql-server-8-0-the-password-function-not-working – Krapow May 17 '21 at 11:35

score 37 · Answer 4 · edited Sep 22 '21 at 20:54

37

Please follow the below steps.

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables
sudo service mysql start
sudo mysql -u root
use mysql;
show tables;
describe user;
update user set authentication_string=password('1111') where user='root';
FLUSH PRIVILEGES;

Log in with password "1111".

edited Sep 22 '21 at 20:54

Peter Mortensen

30,738
21
105
131

answered Feb 27 '17 at 11:16

Lokesh G

831
11
19

5

`flush privileges` is important as a last step. – Khom Nazid Apr 18 '19 at 19:58
It's really done with the ass .I did all the tutorials to reset the password. Everything works except when I log in with the new password, it doesn't work. – Shidomaru NeveRage Jun 15 '20 at 21:14
1

if mysqld is not starting and message like this mysqld daemon with databases from /var/lib/mysql mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended do this: mkdir /var/run/mysqld chomod 777 /var/run/mysqld and start all from the begging – jokermt235 Aug 14 '20 at 23:12
3

Step 2 - I advice to use: `sudo mysqld_safe --skip-grant-tables --skip-networking &` It is important to run the command ending with `&` so that it runs in the background. Passing `–skip-networking` option to skip networking that prevents other clients from connecting to the MySQL server. [Source](https://www.journaldev.com/39274/change-reset-mysql-mariadb-root-password) – MaxiGui Feb 09 '21 at 15:55
[chmod 777](https://chmodcommand.com/chmod-777/) = *"sets permissions so that, (U)ser / owner can read, can write and can execute. (G)roup can read, can write and can execute. (O)thers can read, can write and can execute."*. How secure is that? – Peter Mortensen Sep 22 '21 at 20:21
Brevity is good, but can you elaborate in your answer? E.g., ***why*** are the first three steps required? What will happen if they are not followed? Aren't steps 6 and 7 for informational purposes only (which ones?) and not really required? Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/42484420/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Sep 22 '21 at 20:57

score 32 · Answer 5 · edited Sep 22 '21 at 21:02

32

This is the updated answer for WAMP v3.0.6 and up.

In the MySQL command-line client, phpMyAdmin or any MySQL GUI:

UPDATE mysql.user
SET authentication_string=PASSWORD('MyNewPass')
WHERE user='root';

FLUSH PRIVILEGES;

In MySQL version 5.7.x there is no more password field in the MySQL table. It was replaced with authentication_string. (This is for the terminal/CLI.)

In the MySQL command-line client, phpMyAdmin or any MySQL GUI:

UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE user='root';

FLUSH PRIVILEGES;

edited Sep 22 '21 at 21:02

Peter Mortensen

30,738
21
105
131

answered Apr 20 '17 at 04:33

Robert Anthony S. Tribiana

1,012
12
16

This answer is one of the few who says that `mysql.user` table should be updated. Notice that if You are using any other database (set by the `use` command), You will get an error that the table `user` does not exist. Thank You, sir! – Aleksandar Nov 05 '18 at 12:29
1

works for me . I am using 5.7.x , there's no `mysql.user` table. other answers are out-dated – Siwei Dec 26 '18 at 09:38
Happy to help!! – Robert Anthony S. Tribiana Aug 23 '19 at 03:25
I roll it back because it is my intention to use > because of the semicolon per line which is intended for the terminal – Robert Anthony S. Tribiana Feb 04 '20 at 08:44
0 rows affected, rows matched: 3 changed: 0 ... ? – trainoasis Mar 05 '20 at 09:37
looks like that is a newer version. What version of MYSQL are you working on with? – Robert Anthony S. Tribiana Mar 06 '20 at 16:58
6

In MySQL 8.0+ the function PASSWORD can NOT EXISTS. See https://stackoverflow.com/questions/52320576/in-mysql-server-8-0-the-password-function-not-working . So, instead `PASSWORD('mypass')` you can use `CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass')))))` that works as the old PASSWORD function worked. – FlameStorm Mar 30 '21 at 16:11
2

@FlameStorm This saves the day. I think you should write an answer. – kayochin May 09 '21 at 12:31

SanThee · Answer 6 · 2021-09-24T16:02:52.810

31

I searched around as well and probably some answers do fit for some situations,

my situation is Mysql 5.7 on a Ubuntu 18.04.2 LTS system:

(get root privileges)

$ sudo bash

(set up password for root db user + implement security in steps)

# mysql_secure_installation

(give access to the root user via password in stead of socket)

(+ edit: apparently you need to set the password again?)

(don't set it to 'mySecretPassword'!!!)

# mysql -u root

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> set password for 'root'@'localhost' = PASSWORD('mySecretPassword'); 
mysql> FLUSH PRIVILEGES;
mysql> exit;

# service mysql restart

Many thanks to zetacu (and erich) for this excellent answer (after searching a couple of hours...)

Enjoy :-D

S.

Edit (2020):

This method doesn't work anymore, see this question for future reference...

edited Sep 24 '21 at 16:02

answered May 10 '19 at 13:33

SanThee

2,301
3
23
35

1

This is the best answer so far, at least for mysql server 5.7. – Zoltán Hajdú Feb 15 '20 at 15:22
1

The BEST answer. Thank you. – Luciano Fantuzzi May 20 '20 at 21:59
3

`mysql_secure_installation` worked for me after a fresh installation. – Troy Dec 31 '20 at 18:11

score 23 · Accepted Answer · edited Sep 22 '21 at 19:31

23

I found it! I forgot to hash the password when I changed it. I used this query to solve my problem:

update user set password=PASSWORD('NEW PASSWORD') where user='root';

I forgot the PASSWORD('NEW PASSWORD') and just put in the new password in plain text.

edited Sep 22 '21 at 19:31

Peter Mortensen

30,738
21
105
131

answered Sep 25 '11 at 02:14

nick

2,256
2
14
14

make sure you come back and accept your answer to allow others to see the solution – ghostJago Sep 27 '11 at 12:31
1

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html shows how to do this with `--init-file=` if your looked out. – sabgenton Sep 13 '13 at 17:25
4

Any idea why when I use the same command, the following error is returned? Error 1054 (42S22): unknown column 'Password' in 'field list' – Adrian M. Jan 07 '16 at 07:23
5

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('NEW PASSWORD') where user='root'' at line 1 – discipleartem Jun 30 '20 at 13:06
1

I too get this error. syntax error. Please add the solution. – indianwebdevil Dec 22 '20 at 15:18
where to run this query i cannot get anything even for mysql -v – ahmednawazbutt Aug 10 '21 at 15:18

score 22 · Answer 8 · answered Dec 31 '20 at 13:37

22

On MySQL 8 you need to specify the password hashing method:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new-password';

answered Dec 31 '20 at 13:37

Hokascha

1,709
1
23
41

1

The only worked for me. – code2be Oct 31 '22 at 08:56
Don't forget the `FLUSH PRIVILEGES` too. This works if MySQL uses the new password hashing (I think it was introduced in MySQL 8.0, and was not mandatory at installation time, just recommended). – Yvan Jun 20 '23 at 16:18

score 21 · Answer 9 · edited Sep 23 '21 at 02:20

On MySQL 8.0.4+

To update the current root user:

select current_user();
set password = 'new_password';

To update another user:

set password for 'otherUser'@'localhost' = 'new_password';

To set the password policy before updating the password:

set global validate_password.policy = 0;
set password = 'new_password';
set password for 'otherUser'@'localhost' = 'new_password';

Another / better way to update the root password:

mysql_secure_installation

Do you want to stick with 5.x authentication, so you can still use legacy applications?

In my.cnf file

default_authentication_plugin = mysql_native_password

To update root:

set global validate_password.policy = 0;
alter user 'root'@'localhost' identified with mysql_native_password by 'new_password';

score 12 · Answer 10 · answered Nov 04 '18 at 14:20

12

For MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

For MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

answered Nov 04 '18 at 14:20

Eagle_Eye

1,044
1
14
26

score 12 · Answer 11 · edited Sep 23 '21 at 02:21

12

This worked for me -

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Chapter 4 Resetting the Root Password: Windows Systems

edited Sep 23 '21 at 02:21

Peter Mortensen

30,738
21
105
131

answered Dec 09 '19 at 13:22

Eduards

1,734
2
12
37

1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'MyNewPass'' at line 1 – trainoasis Mar 05 '20 at 09:38
Just copied the exact over in sql via CLI :o – trainoasis Mar 05 '20 at 09:58
Of course, sshed to my server + mysql -u root -p to get to the mysql cli – trainoasis Mar 05 '20 at 13:31

score 9 · Answer 12 · edited Sep 23 '21 at 02:32

9

For me, only these steps could help me setting the root password on version 8.0.19:

mysql
SELECT user,authentication_string FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass_here';
FLUSH PRIVILEGES;
SELECT user,authentication_string FROM mysql.user;

If you can see changes for the root user, then it works. Source: Can't set root password MySQL Server

edited Sep 23 '21 at 02:32

Peter Mortensen

30,738
21
105
131

answered Feb 04 '21 at 11:52

Sergiy Zaharchenko

1,490
1
12
11

I don't know why setting the password for the MySQL `root` user has to be so obscure, but this worked on Ubuntu 21, MySQL 8. Thanks Sergey. – Joe Aug 28 '21 at 22:19
this is the ONLY answer that works along many answers on many questions for mysql 8 – nluizsoliveira Jan 12 '22 at 07:22
This is the only answer that worked for me on 5.7.40 – Shobhana Sriram Dec 13 '22 at 11:25

score 6 · Answer 13 · edited Sep 22 '21 at 20:09

6

You have to reset the password! Steps for Mac OS X (tested and working) and Ubuntu:

Stop MySQL

sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

sudo mysqld_safe --skip-grant-tables

(The above line is the whole command.)

This will be an ongoing command until the process is finished, so open another shell/terminal window and log in without a password:

mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Start MySQL

sudo /usr/local/mysql/support-files/mysql.server start

Your new password is 'password'.

edited Sep 22 '21 at 20:09

Peter Mortensen

30,738
21
105
131

answered Feb 19 '15 at 07:51

tk_

16,415
8
80
90

I've got a message : no database selected – Mimouni Aug 19 '19 at 14:14
@Mimouni try this : https://stackoverflow.com/questions/21944936/error-1045-28000-access-denied-for-user-rootlocalhost-using-password-y/25883967#25883967 – tk_ Aug 19 '19 at 14:16

score 6 · Answer 14 · edited May 25 '23 at 20:40

6

Update from 2022

I've tried a few of the answer but the one that works for me is the following

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Courtesy of StrongDM

Note: I'm using the MySQL client for Windows 10 and I'm also logging as the root user.

edited May 25 '23 at 20:40

informatik01

16,038
10
74
104

answered May 16 '22 at 14:27

Andy K

4,944
10
53
82

score 5 · Answer 15 · answered Dec 01 '18 at 06:00

5

Using the mysqladmin command-line utility to alter the MySQL password:

mysqladmin --user=root --password=oldpassword password "newpassword"

Source

answered Dec 01 '18 at 06:00

Darush

11,403
9
62
60

This works, and works better as `mysqladmin -p password` prompting for old password once, and the new password twice. – Elvin Sep 25 '22 at 15:55

score 5 · Answer 16 · edited Sep 23 '21 at 01:55

5

For the current latest MySQL version (8.0.16), none of these answers worked for me.

After looking at several different answers and combining them together, this is what I ended up using that worked:

update user set authentication_string='test' where user='root';

edited Sep 23 '21 at 01:55

Peter Mortensen

30,738
21
105
131

answered May 29 '19 at 22:48

Suhas

550
4
11

`ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)` and then `[Warning] [MY-010319] [Server] Found invalid password for user: 'root@localhost'; Ignoring user` – Eugene Gr. Philippov Dec 24 '20 at 16:26

score 3 · Answer 17 · edited Sep 23 '21 at 01:46

3

I tried the answer from kta, but it didn't work for me.

I am using MySQL 8.0.

This worked for me in the MySQL command-line client (executable mysql):

SET PASSWORD FOR 'root'@'localhost' = 'yourpassword'

edited Sep 23 '21 at 01:46

Peter Mortensen

30,738
21
105
131

answered Mar 11 '19 at 11:35

chainstair

681
8
18

1

Please add some explanation to your code - how exactly does this solve a seven years old question? – Nico Haase Mar 11 '19 at 15:22
1

It looks like the syntax has been changed over the seven years. It's not wrapping password with PASSWORD() https://dev.mysql.com/doc/refman/8.0/en/set-password.html – kangkyu Apr 16 '19 at 16:48
Password hash should be a 41-digit hexadecimal number – Mimouni Aug 19 '19 at 14:21

score 3 · Answer 18 · edited Sep 23 '21 at 02:26

This is for Mac users.

On 8.0.15 (maybe already before that version) the PASSWORD() function does not work. You have to do:

Make sure you have Stopped MySQL first (above). Run the server in safe mode with privilege bypass:

sudo mysqld_safe --skip-grant-tables

Replace this mysqld_safe with your MySQL path like in my case it was

sudo /usr/local/mysql/bin/mysqld_safe –skip-grant-tables

then you have to perform the following steps.

mysql -u root

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Then

mysql -u root

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

This worked for me on Ubuntu 21.04, MySQL 8 – Kalnode Jul 07 '21 at 22:43 — Kalnode, Jul 07 '21 at 22:43

Vittore Marcas · Answer 19 · 2022-01-13T23:50:40.263

Now just use:

SET PASSWORD FOR <user> = '<plaintext_password>'

Because 'SET PASSWORD FOR <user> = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release.(Warning in 04/12 2021) Please use SET PASSWORD FOR <user> = '<plaintext_password>' instead.

Update 04/12 2021 AM 2:22:07 UTC/GMT -5 hours.

Use the following statement to modify directly in the mysql command line:

mysql> SET PASSWORD FOR'root'@'localhost' = PASSWORD('newpass');

or 1.The terminal enters the bin directory of MySQL

cd /usr/local/mysql/bin

2.Open MySQL

mysql -u root -p

3.At this time you can use your default password

4.Perform operations in MySQL at this time

show databases;

5.You will be prompted to reset the root user password.

So how to reset the root password? I checked a lot of information but it didn’t take effect.

Including entering to modify the database in safe mode, using the mysqladmin command: "Mysqladmin -u root password"your-new-password"" etc., Will not work.

The correct steps are as follows:

1.It is still in the cd /usr/local/mysql/bin/ directory

2.sudo su

After entering, you will be asked to enter your computer password.

When you enter it, nothing is displayed. After you enter it, press Enter

Then press enter

3.Cross the authorization verification

sh-3.2# ./mysqld_safe --skip-grant-tables &

If the execution of the command is stopped, and the execution has been completed at this time,

press Enter directly, and then exit to exit:

sh-3.2# exit

4.Re-enter MySQL at this time, no -p parameter, no password

./mysql -u root

5.Select the database MySQL (here MySQL refers to a database in MySQL,

there are other databases in MySQL, you can view it through show databases;)

use mysql;

6.Update the password of the root user in the database table:

update user set authentication_string=‘123456’ where User='root';

Note: The password field here is authentication_string,

not the password circulated on the Internet.

It is estimated that MySQL was updated later.

Re-enter MySQL and use the password you just set, is it all right?

Because you have just set to bypass the authorization authentication,

you can log in to MySQL directly without a password.

My stupid way is to restart the computer and log in to MySQL with the password again to see if the modification is effective;

score 2 · Answer 20 · edited Sep 22 '21 at 21:08

2

In MySQL 5.7, the password is replaced with 'authentication_string'. Use

update user set authentication_string=password('myfavpassword') where user='root';

edited Sep 22 '21 at 21:08

Peter Mortensen

30,738
21
105
131

answered May 14 '18 at 11:34

Kamal Reddy

2,610
5
28
36

score 2 · Answer 21 · edited Sep 23 '21 at 02:17

2

So many comments, but I was helped by this method:

sudo mysqladmin -u root password 'my password'

In my case after installation I had got the MySQL service without a password for the root user, and I needed to set the password for my security.

edited Sep 23 '21 at 02:17

Peter Mortensen

30,738
21
105
131

answered Jun 11 '19 at 07:57

Konstantin Romanovsky

29
3

It could be used to reset mysql root user password without knowing the old one. But make sure no one has access to your syslog, as this password is logged: `Aug 07 16:21:55 ubuntupc sudo[93250]: user : TTY=pts/0 ; PWD=/home/user ; USER=root ; COMMAND=/usr/bin/mysqladmin -u root password 'my password'` – Gryu Aug 07 '20 at 13:26

score 1 · Answer 22 · edited Sep 22 '21 at 18:39

1

A common error I run into from time to time, is that I forget the -p option, so be sure to use:

mysql -u root -p

edited Sep 22 '21 at 18:39

Peter Mortensen

30,738
21
105
131

answered Sep 23 '11 at 19:53

Marian Theisen

6,100
29
39

MohitGhodasara · Answer 23 · 2021-09-23T10:49:26.593

Exit from WAMP and Stop all WAMP services.

Open Notepad and then type:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');

Then save it to the C: drive with any name... like this "c:/example.txt"

Now go to your "wamp" folder: wamp → bin → mysql → mysql (your version) → bin

In my case the path is "C:\wamp\bin\mysql\mysql5.6.17\bin".

Now copy your path, run CMD with (Ctrl + R), and then type "cmd" (Enter).

Type cd, right click on CMD, and paste the path (Enter).

Now type (mysqld --init-file=C:\\example.txt) without braces and (Enter).

Then restart the PC or open Task Manager and kill mysqld.exe.

Start WAMP and your password will be removed...

On Windows, I presume (not stated). – Peter Mortensen Sep 22 '21 at 20:15 — Peter Mortensen, Sep 22 '21 at 20:15

score 0 · Answer 24 · edited Sep 23 '21 at 01:39

For macOS users, if you forget your root password, thusharaK's answer is good, but there are a few more tricks:

If you are using a system preference to start MySQL serverside, simply

sudo mysqld_safe --skip-grant-tables

might not work for you.

You have to make sure the command-line arguments are the same with the system start configuration.

The following command works for me:

/usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --keyring-file-data=/usr/local/mysql/keyring/keyring --early-plugin-load=keyring_file=keyring_file.so --skip-grant-tables

You can use

ps aux | grep mysql

to check your own.

score 0 · Answer 25 · answered Sep 16 '20 at 12:55

0

Or just use interactive configuration:

sudo mysql_secure_installation

answered Sep 16 '20 at 12:55

shilovk

11,718
17
75
74

score 0 · Answer 26 · answered Jun 17 '22 at 07:01

0

Resetting root password.

sudo mysql --defaults-file=/etc/mysql/debian.cnf
alter user 'root'@'localhost' identified with mysql_native_password by 'new_password';

answered Jun 17 '22 at 07:01

Pratik Patel

5,995
1
18
39

THIS FILE IS OBSOLETE. STOP USING IT IF POSSIBLE. This file exists only for backwards compatibility for tools that run '--defaults-file=/etc/mysql/debian.cnf' and have root level access to the local filesystem. With those permissions one can run 'mariadb' directly anyway thanks to unix socket authentication and hence this file is useless. – Hoang Do Jan 24 '23 at 09:02

score 0 · Answer 27 · edited Mar 27 '23 at 17:20

0

The other answers did not work for me.

That said, here is how I did it for the password change to the root SQL profile if you can log in already.

While logged in:

Tool Bar -> Servers -> Users and Privileges -> select root - > from here you can change the password, or expire it and change it in the login.

edited Mar 27 '23 at 17:20

Eric Aya

69,473
35
181
253

answered Mar 27 '23 at 16:02

JQTs

142
2
11

score 0 · Answer 28 · answered Jun 08 '23 at 07:00

0

You can excute: ALTER USER "root"@"localhost" IDENTIFIED BY "test_pwd";

answered Jun 08 '23 at 07:00

quangdang

116
7

score -1 · Answer 29 · edited Sep 22 '21 at 20:17

-1

On Ubuntu,

sudo dpkg-reconfigure mysql-server-5.5

Replace 5.5 with your current version and you will be asked for the new root password.

edited Sep 22 '21 at 20:17

Peter Mortensen

30,738
21
105
131

answered Sep 26 '16 at 09:02

Mouneer

12,827
2
35
45

In what OS? This makes no sense. – Khom Nazid Apr 17 '19 at 14:10

score -2 · Answer 30 · edited Sep 23 '21 at 02:23

-2

On Mac open system preferences MySQL.
In the configuration section of MySQL, check for "Initialize Database".
Change the password in the prompt.

edited Sep 23 '21 at 02:23

Peter Mortensen

30,738
21
105
131

answered Aug 27 '20 at 10:12

N N K Teja

197
1
7

MySQL root password change

30 Answers30

Update from 2022

Linked

Related