Reset a MySQL root password

1. Linux

Stop the MySQL service
(Ubuntu and Debian) Run the following command:

sudo /etc/init.d/mysql stop

 (CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:

sudo /etc/init.d/mysqld stop

Start MySQL without a password:

sudo mysqld_safe --skip-grant-tables &

Connect to MySQL:

mysql -uroot

Set a new MySQL root password:

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

Stop and start the MySQL service:
(Ubuntu and Debian) Run the following commands:

sudo /etc/init.d/mysql stop
...
sudo /etc/init.d/mysql start

(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following commands:

sudo /etc/init.d/mysqld stop
...
sudo /etc/init.d/mysqld start

Log in to the database: Test the new password by logging in to the database.

mysql -u root -p

2. Windows (In case if you have Xampp installed.)

• Goto C:\xampp\mysql\bin
• Open my.ini file
• Put skip-grant-tables under [mysqld]
• Goto windows services, stop and start mysql service
• Trigger this command from command prompt C:\xampp\mysql\bin\mysql
• Now, reset the root password with the MySQL query update mysql.user set password=PASSWORD('root') where user='root';
• Exit the command prompt.
• Restart the mysql windows service.
• Now you will be able to login to mysql using password as root.

Others:

0) shut down service mysql56

1) go to C:\ProgramData\MySQL\MySQL Server 5.6, note that ProgramData is a hidden folder

2) looking for file my.ini, open it and add one line skip-grant-tables below [mysqld],save[mysqld] skip-grant-tables


3) start service mysql56

4) by right, you can access the database, run mysql

5) and use the query below to update the passwordupdate mysql.user set password=PASSWORD('NEW PASSWORD') where user='root';


6) shut down the service again, remove the line skip-grant-tables save it, and start the service again. try to use the password you set to login.