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 :

1. Stop the MySQL server process.
2. Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for a password.
3. Connect to the MySQL server as the root user.

That can be 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?

Clotildecloture

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? – Minutiae

What is the first error? – Dominoes

Answer 1

As this link 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 the terminal:

   mysql -u root
   

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

Alisha

For in SKIP-GRANT-TABLES mode - sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables . Works for 8.0.1 – Erbes

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. – Fewness

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. – Horrified

@Horrified this work for me in Mysql 8.0.21-0ubuntu0.20.04.4 – Hangnail

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 – Someone

@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. – Monostome

God bless you! Finally, I changed the root password. – Spanker

This gives me the same syntax error that the OP has – Hectare

Answer 2

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

Bushire

thats worked like a charm ! thank you and i dont needed to flush or restart – Agnola

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 – Lowry

Lot of confusing answers elsewhere. This is the one works best. – Canine

I wasted so much time following the actual official MySQL 8 instructions and this is what worked... Thanks – Matchmark

saved a lot of my time! – Schulze

this must be the accepted answer – Berfield

This should be the accepted answer. – Cerveny

sudo mysql solves nothing in my case: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) – Wildfowl

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. – Beeswax

Simple & working solution so far for mysql version 8* – Stortz

This did not work for me. I got the exact same permissions error as the OP when I tried. – Hectare

Answer 3

Try this:

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

Kirkcudbright

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 – Joappa

Answer 4

If you are on Windows you can try following steps.

Reset MySQL 8.0 root Password in Windows

1. Stop the MySQL 8.0 service from services.
2. Go to path C:\Program Files\MySQL\MySQL Server 8.0\bin and open cmd.
3. Run mysqld --console --skip-grant-tables --shared-memory.
4. Open new cmd in the same path.
5. Run following commands.
6. mysql -u root.
7. select authentication_string,host from mysql.user where user='root';
8. UPDATE mysql.user SET authentication_string='' WHERE user='root';
9. Now close both the cmd.
10. Restart the MySQL 8.0 service.
11. Connect using username as root and password as blank.
12. Change the password from the user management.

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

Tousle

Answer 5

1. Create a new file in your root directory( e.g C:)

2. Write this ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc' and save it

3. If your Mysql service is already running, please stop this and open your CMD

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
   

5. Execute this command and you are good to go

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

Faretheewell

Answer 6

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

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

Solutions

1. use steps from https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
2. use --user=mysql when running mysql commands
3. Create folder /var/run/mysqld and chown for mysql:mysql before runing comamnds
4. 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

Diversity

Answer 7

This answer is for MySQL Ver 8.0.34-0 on Ubuntu 22.04

mysql --version
sudo /etc/init.d/mysql stop
sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld/
sudo mysqld_safe  --skip-grant-tables &

You need to enter after few lines display in skip grant table command

sudo mysql --user=root mysql
mysql>exit
mysql -u root -p

Hypertension

Answer 8

There are definitely a lot of answers here, and on the Internet in general, about how to change the MySQL root user password. The main takeaway I got from this is that:

The mysqld_safe (binary/shell script) is definitley no longer a part of the MySQL 8 package (8.0.36) in Red Hat 8 and possible other Linux distros.

It might be gone (as a binary/shell script) from the MySQL install on systemd managed systems since at least MySQL 5.7.6 according to this other answer:

“As of MySQL 5.7.6, for MySQL installation using an RPM distribution, server startup and shutdown is managed by systemd on several Linux platforms. On these platforms, mysqld_safe is no longer installed because it is unnecessary. For more information, see Section 2.5.10, “Managing MySQL Server with systemd”.”

Regardless of the reason, if you get this message when attempting to run mysqld_safe:

mysqld_safe: command not found

And your system runs systemd (aka: systemctl) then this is definitely the modern answer that will solve your problems in 4 easy steps! Here we go!

1. Stop mysqld and restart it with the proper MYSQLD_OPTS:

   sudo service mysqld stop;
   sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking";
   sudo service mysqld start;
   

2. Once MySQL has started, login to MySQL as root with no password:

   mysql -u root;
   

3. Now, in MySQL run these commands; note that [password] should be changed to whatever MySQL password you want it yo be set to:

   FLUSH PRIVILEGES;
   ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '[password]';
   FLUSH PRIVILEGES;
   exit;
   

   Note, I believe the first FLUSH PRIVILEGES; is connected starting up MySQL with the MYSQLD_OPTS set to --skip-grant-tables. Regardless, running FLUSH PRIVILEGES; doesn’t pose any risk/danger.
4. Now, roll back the MYSQLD_OPTS and restart MySQL as follows:

   sudo service mysqld stop;
   sudo systemctl unset-environment MYSQLD_OPTS;
   sudo service mysqld start;
   

Now with all that done, try to login to MySQL as root with a password like this:

mysql -u root -p

Hit Enter/Return, then enter your newly set root password and… et voilà! You should be logged into MySQL as root!

---

Note: I chose to set the password with caching_sha2_password authentication plugin, but if you want to be more modern, you can use mysql_native_password as follows:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';

Comate

Answer 9

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

Pretonic

Can you explain that further? What is "SQLYog"? How does all this work if one cannot login as root? – Prying