use mysql;
update user set authentication_string=PASSWORD("mynewpassword") where User='root';
flush privileges;
quit
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';ALTER USER 'username' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
exit;If you don't remember your current root password and want to install new instance of MySQL and you have applied other ways like "-init-file.txt", but still failed.
There is another solution which worked for me. Uninstalling MySQL from control panel didn't remove all files, it left some references in the system.
To delete MySQL files completely, open the folder path C:Usersyour pc nameAppDataRoaming and delete the MySQL folder.
Then remove one more reference which is in C:ProgramDataMySQL, if not visible check your folder view options and uncheck "Don't show hidden files".
The last reference of MySQL exists in our system services:
Type "Services" in the search box of the taskbar. Find services related to MySQL and note them down. I have two in my case(MySQLRouter and MYSQL80).
Open the command prompt with administrator and type:
sc delete "ServiceName",
In my case:
sc delete MySQL80
sc delete MySQLRouter
Ensure all services related to MySQL are removed by using the above command. Restart your computer and install your MySQL instance with a new configuration.1. Confirm MySQL version
Firstly, you must confirm which version of MySQL on Ubuntu you are running as commands will be different.
mysql -V
If on MySQL version 8, you will see something like:
mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
If you are on MySQL version 5, you will see something similar to:
mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper
2. Restart MySQL with skip-grant-table
In order to skip the grant tables and reset the root password, we must first stop the MySQL service. Enter your Linux password if prompted.
sudo /etc/init.d/mysql stop
Ensure the directory /var/run/mysqld exists and correct owner set.
sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld
Now start MySQL with the --skip-grant-tables option. The & is required here.
sudo mysqld_safe --skip-grant-tables&
You should see something similar:
[1] 1283
user@server:~$ 2019-02-12T11:15:59.872516Z mysqld_safe Logging to syslog.
2019-02-12T11:15:59.879527Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-02-12T11:15:59.922502Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Now press ENTER to return to the Linux BASH prompt.
3. Change MySQL Root Password
You can now log in to the MySQL root account without a password.
sudo mysql --user=root mysql
Save
Once logged in, you will see the mysql> prompt.
MySQL 8 – Reset Root Password
For MySQL 8 on Ubuntu, run following commands.
UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
Replace your_password_here with your own. (Generate a strong password here)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
Flush privileges again.
flush privileges;
Exit MySQL.
exit
4. Test New Root Password
Make sure all MySQL processes are stopped before starting the service again.
sudo killall -u mysql
If you see a message similar to below, press ENTER to continue.
2020-05-30T07:23:38.547616Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid ended
Start MySQL again.
sudo /etc/init.d/mysql start
Log in to MySQL again and you should now be prompted for a password.
sudo mysql -p -u root
Enter your MySQL root password. If correct, you should see something like:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>