Mysql Root Password Forgotten

Here are the steps to be followed:

1. Locate the MySQL configuration file using: $ mysql --help | grep -A 1 "Default options"

On Ubuntu 16, the file location is typically /etc/mysql/mysql.conf.d/mysqld.cnf

2. Edit the configuration file using: $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

3. Add skip-grant-tables under [mysqld] block and save the changes.

4. Restart MySQL service using: sudo service mysql restart

5. Check MySQL service status: sudo service mysql status

6. Login to mysql with: $ mysql -u root

7. And change the root password:

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';

8. Revert back the MySQL configuration file changes by removing skip-grant-tables line or commenting it with a # (hash).

9. Finally restart the MySQL service and you are good to go.

I couldn't get mysqld in Adelave's answer to work. But this worked for me instead

stop and start mysql with --skip-grant-tables

service mysql.server stop
service mysql.server start --skip-grant-tables

then connect to your mysqld without username/password

mysql

then update the password on mysql command line

 mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; 
 mysql> FLUSH PRIVILEGES;
 mysql> \q

then restart mysql normally

 service mysql.server restart

try to start mysql with --skip-grant-tables

mysqld --skip-grant-tables

then connect to your mysqld without username/password using mysql command line

shell> mysql

then issue command

> mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
>        WHERE User='root'; mysql> FLUSH PRIVILEGES;

If other answer could not help, you can try to uninstall/re-install mysql. It works on my ubuntu server:

$sudo apt-get purge mysql*
$sudo apt-get autoremove
$sudo apt-get autoclean

Update distribution

$sudo apt-get dist-upgrade

And re-install

$sudo apt-get install mysql-server

Mac OS Mojave

$ brew services stop mysql

$ pkill mysqld

// warning: deletes all tables
$ rm -rf /usr/local/var/mysql/

$ brew postinstall mysql

$ brew services restart mysql

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'p4ssword';
Query OK, 0 rows affected (0.04 sec)

mysql> exit
Bye

Then you are back to normal for dev.

$ sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

I don't like to see the word root in my .env files, so I usually do this after, if making a site such as :

#
CREATE DATABASE hockeysticks;

CREATE USER 'hockeysticks'@'localhost' IDENTIFIED BY 'hockeysticks';
GRANT ALL PRIVILEGES ON hockeysticks.* TO 'hockeysticks'@'localhost';

Then your localdev .env file is simple:

DB_DATABASE=hockeysticks
DB_USERNAME=hockeysticks
DB_PASSWORD=hockeysticks

Note: If you need to retain your databases, use the skip-grant-tables method. That has 3 hard parts:

1. Make sure MySQL is stopped, so you can restart it with skip-grant-tables

2. Make sure your password update SQL syntax is correct for your MySQL version

3. Make sure you append the end of the query with FLUSH PRIVELEGES;

Using windows command prompt you can change the password

1. Go to windows+R and run services.msc and then stop the MySQL services and see the properties of MySQL by right click and copy the path --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" as it required later.
2. Create text file reset.txt with the text ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your New Password'; and save in C drive.
3. open the command prompt as administrator then change the directory where your MySQL is installed "C:\Program Files\MySQL\MySQL Server 8.0\bin".
4. Type the command mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file=C:\\reset.txt

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