How to reset the root password in MySQL 8.0.11?

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

---

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

---

Try this:

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

---

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

---

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

---

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

---

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

---