How to reset the root password in MySQL 8.0.11?

Question

I have actually lost my root password and I need to change it. I follow these 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.

that we can found on these website : https://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords#recover-mysql-root-password

mysql> use mysql;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("TOOR");
mysql> flush privileges;
mysql> quit

First error, so I tried :

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

Always the same error said :

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 '("TOO
R") WHERE User='root'' at line 1

How can I resolve this?

It looks like it has a problem with the keyword `TOOR`, but I cannot see where you have used that. Would you show us the actual SQL you are running? — halfer, Jun 05 '18 at 09:24
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:23

score 179 · Accepted Answer · answered Sep 30 '18 at 16:29

179

as here says:

This function was removed in MySQL 8.0.11

1.if you in skip-grant-tables mode
in mysqld_safe:

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

and then, in terminal:

mysql -u root

in mysql:

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

2.not in skip-grant-tables mode
just in mysql:

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

answered Sep 30 '18 at 16:29

Jack Chern

1,914
1
7
4

2

For in SKIP-GRANT-TABLES mode - sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables . Works for 8.0.1 – Piash Sarker Oct 04 '18 at 07:59
1

I got `ERROR 1146 (42S02): Table 'mysql.role_edges' doesn't exist` so I had to do a `mysql_upgrade -u root` after I set the root password to null for this to work. – Gregory R. Sudderth Feb 11 '19 at 11:12
10

I had to use `mysql_native_password` instead of `caching_sha2_password` for this to work: `ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpasswd';`. Not sure why, though. – dvlcube Nov 18 '19 at 18:53
@dvlcube this work for me in Mysql 8.0.21-0ubuntu0.20.04.4 – ebuzz168 Oct 07 '20 at 03:51
1

I had trouble with the order of the commands but in ubuntu 20 worked for me: `sudo service mysql stop` `sudo mysqld_safe --skip-grant-tables &` then opening mysql console with `mysql -u root` `UPDATE mysql.user SET authentication_string=null WHERE User='root'; flush_privileges; ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd'; exit;` `ps -fea | grep mysqld` `sudo kill -9 [pid from previous command]` then finally `sudo service mysql stop` – Ulises Layera Apr 18 '21 at 18:04
@Jack Chern What a great person you are, I spent a day solving this problem and there is no article I haven't read. I reset the server many times with no results. Thank you so much. – umutyerebakmaz Oct 27 '21 at 12:09
God bless you! Finally, I changed the root password. – Aleksey Dz May 18 '22 at 05:37
This gives me the same syntax error that the OP has – Native Coder May 21 '23 at 20:03

score 71 · Answer 2 · edited Jun 03 '23 at 14:36

71

Jus login to mysql with sudo

sudo mysql

Then

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

Test it

mysql -u root -p

edited Jun 03 '23 at 14:36

Hidde

11,493
8
43
68

answered Jun 13 '20 at 08:06

Mahdi Raad

729
5
3

thats worked like a charm ! thank you and i dont needed to flush or restart – Ugur Kazdal Jan 28 '21 at 09:58
1

Please note, that using this password algo breaks Java applications with the error "public key retrieval is not allowed" It's better to use the good ol' native password algo ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password; or you may be forced to change the application settings – Kevin Guanche Darias Feb 20 '21 at 19:59
Lot of confusing answers elsewhere. This is the one works best. – Nilesh K Jun 30 '21 at 20:15
I wasted so much time following the actual official MySQL 8 instructions and this is what worked... Thanks – Taimoor Ahmad Aug 28 '21 at 17:38
saved a lot of my time! – showtime Sep 20 '21 at 09:42
this must be the accepted answer – 89n3ur0n Jan 09 '22 at 05:17
This should be the accepted answer. – Thanh Nguyen Jan 18 '22 at 10:46
sudo mysql solves nothing in my case: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) – Sxilderik Apr 19 '22 at 11:52
I cannot believe how long I wasted on other answers, including the useless mysql documentation and reinstalling my system and all I needed to do was type sudo mysql. – leisheng Aug 09 '22 at 14:57
Simple & working solution so far for mysql version 8* – Farooq Ahmed Khan May 01 '23 at 07:06
This did not work for me. I got the exact same permissions error as the OP when I tried. – Native Coder May 21 '23 at 20:02

score 17 · Answer 3 · edited May 04 '19 at 13:33

17

Try this:

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

edited May 04 '19 at 13:33

William Desportes

1,412
1
22
31

answered Jul 05 '18 at 08:29

Jitesh Middha

389
3
10

2

This statement is not available before adding skip-grant-tables and after that user gets: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – richardwhitney Sep 03 '20 at 21:41

score 3 · Answer 4 · answered Jun 14 '21 at 06:08

If you are on Windows you can try following steps

Reset MySQL 8.0 root Password in Windows

Stop the MySQL 8.0service from services
Go to path C:\Program Files\MySQL\MySQL Server 8.0\bin and open cmd
Run mysqld --console --skip-grant-tables --shared-memory
Open new cmd in the same path
Run following commands
mysql -u root
select authentication_string,host from mysql.user where user='root';
UPDATE mysql.user SET authentication_string='' WHERE user='root';
Now close both the cmd.
Try to start the MySQL 8.0 service.
Connect using username as root & password as blank.
Change the password from the user management.

Found this at https://gist.github.com/pishangujeniya/0f839d11a7e692dadc49821c274a2394

score 1 · Answer 5 · answered Nov 12 '19 at 10:27

Step 1: Create a new file in your root directory( e.g C:) Step 2: Write this ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc' and save it Step 3: If your Mysql service is already running, please stop this and open your CMD Step 4: Go to your Mysql installation directory (e.g C:\Program Files\MySQL\MySQL Server 8.0\bin) and type this command

mysql --init-file=C:/init.sql

Step 5: Execute this command and you are good to go :)

Refer this video for more clarification : https://www.youtube.com/watch?v=LsdV-7dFOhk

score 1 · Answer 6 · answered Apr 14 '21 at 22:17

I am using 8.0.23 but had multiple issues. Here are the issues and solutions. I followed the mysql docs and was able to do the reset, though I had isues. I have provided my solutions and a sample reset script.

Issues

following steps outlined by others had various failures
user running daemon is mysql and I cannot su to mysql id
getting error can't create lock file /var/run/mysqld/mysqlx.sock.lock , mysqld folder is getting deleted by the mysql service.
had to kill -9 mysql process after running, very ugly

Solutions

use steps from https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
use --user=mysql when running mysql commands
Create folder /var/run/mysqld and chown for mysql:mysql before runing comamnds
use mysqladmin shutdown to stop sql after starting it for reset

In the script, I will reset root to New%Password

# create password reset file
cat << EOF >/tmp/deleteme
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'New%Password';
EOF
# create /var/run/mysqld
mkdir /var/run/mysqld
chown mysql:mysql /var/run/mysqld
# start mysqld and run the command
mysqld --init-file=/tmp/deleteme --user=mysql &
# wait for sql to start
while [ ! -f /var/run/mysqld/mysqlx.sock.lock ]; do sleep 1; done
sleep 3
# stop sql
mysqladmin -u root -pNew%Password shutdown
while [ -f /var/run/mysqld/mysqlx.sock.lock ]; do sleep 1; done
#clean up temp file
rm /tmp/deleteme

score -7 · Answer 7 · edited May 08 '19 at 15:17

Using SQLYog you can execute commands

User Creation

CREATE USER 'tester'@'localhost' IDENTIFIED BY 'Pass123#d'

Authorization

GRANT ALL PRIVILEGES ON sakila.* TO 'tester'@'localhost'

Changing Password in MySQL 8.0

ALTER USER 'tester'@'localhost' IDENTIFIED BY 'Pass123#d';

or if u know the authentication_string directly set it to update

UPDATE mysql.user 
SET authentication_string='*F9B62579F38BE95639ACB009D79427F2D617158F'  
WHERE USER='root'

Changing password in lower versions of mysql

GRANT USAGE ON *.\* TO 'tester'@'localhost' IDENTIFIED BY 'Pass123#d'
SET PASSWORD FOR 'tester'@'localhost' = PASSWORD('Pass123#d');

Can you explain that further? What is "SQLYog"? How does all this work if one cannot login as root? — Nico Haase, May 08 '19 at 13:40

How to reset the root password in MySQL 8.0.11?

7 Answers7

Reset MySQL 8.0 root Password in Windows

Linked

Related