Change root password mysql 8 ubuntu

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 : //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?

asked Jun 5, 2018 at 4:20

Mangue SutcliffMangue Sutcliff

1,2091 gold badge10 silver badges16 bronze badges

3

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, 2018 at 16:29

Jack ChernJack Chern

1,7461 gold badge7 silver badges4 bronze badges

7

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

answered Jun 13, 2020 at 8:06

Mahdi RaadMahdi Raad

6015 silver badges3 bronze badges

9

Try this:

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

answered Jul 5, 2018 at 8:29

1

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 : //www.youtube.com/watch?v=LsdV-7dFOhk

answered Nov 12, 2019 at 10:27

AshitAshit

496 bronze badges

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 //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

answered Apr 14, 2021 at 22:17

0

If you are on Windows you can try following steps

Reset MySQL 8.0 root Password in Windows

1. Stop the MySQL 8.0service 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. Try to start the MySQL 8.0 service.
11. Connect using username as root & password as blank.
12. Change the password from the user management.

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

answered Jun 14, 2021 at 6:08

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

answered May 8, 2019 at 11:05

1

How do I change the root password in MySQL 8?

How can I change MySQL root password in Ubuntu?

How do I change MySQL root query password?

How do I change the root password in MySQL 8.0 27?