mysql root password forgotten

I did not use PHP MySQL for quite a while and now I need to use it again. But the problem is I forget the password for the MySQL console. and getting error #1045 when trying to login in to PHPMyAdmin.

In the MySQL site I saw an article how to reset root password( )

Steps are

create a mysql-init.txt file containing UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root'; FLUSH PRIVILEGES; 

I saved it as C:\me\mysql-init

and in command prompt I wrote--

C:\wamp\bin\mysql\mysql5.5.8\bin\mysqld --init-file=C:\me\mysql-init.txt 

I tried with double backslashes also..but it is not working. MySQL console is asking for a password and it's not taking the new-one. What am I doing wrong? I have several tables there.what to do?

Thanks in advance.

7 Answers

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